MariaDB AES_DECRYPT() Function

MariaDB AES_DECRYPT() returns the plaintext for data encrypted with AES_ENCRYPT() when the matching key and encryption parameters are supplied. The result is binary data. See the official AES_ENCRYPT() and AES_DECRYPT() documentation.

The example keys and IVs below are placeholders only. Never use them for real data. Production keys must be protected separately from ciphertext and managed with an appropriate key-management process.

Syntax

MariaDB 11.2 and later:

AES_DECRYPT(crypt_str, key [, iv [, mode]])

Earlier MariaDB releases support the two-argument form:

AES_DECRYPT(crypt_str, key_str)

When no mode is supplied, MariaDB uses the session’s block_encryption_mode setting. The encryption and decryption calls must use matching parameters.

Decrypt sample text

This two-argument example works with the syntax used by earlier releases:

SET @ciphertext = AES_ENCRYPT('sample text', 'demo-only-key');

SELECT CONVERT(
    AES_DECRYPT(@ciphertext, 'demo-only-key')
    USING utf8mb4
) AS plaintext;

For MariaDB 11.2 and later, include the same IV and mode in both calls:

SET @ciphertext = AES_ENCRYPT(
    'sample text',
    'demo-only-key',
    '0123456789abcdef',
    'aes-256-cbc'
);

SELECT CONVERT(
    AES_DECRYPT(
        @ciphertext,
        'demo-only-key',
        '0123456789abcdef',
        'aes-256-cbc'
    )
    USING utf8mb4
) AS plaintext;

The literal key and IV are for a short demonstration, not production use. Follow the selected mode’s requirements and use a unique IV where required.

Invalid ciphertext or key

If the ciphertext has invalid padding, AES_DECRYPT() returns NULL. MariaDB also warns that invalid ciphertext or a wrong key can produce a non-NULL value containing garbage. Therefore, a non-NULL result does not prove that decryption succeeded or that the ciphertext is authentic.

The functions do not manage application keys or verify ciphertext integrity. Keep keys outside the database that stores ciphertext, and use an integrity mechanism appropriate for the encryption design. For general key-management guidance, see the OWASP Cryptographic Storage Cheat Sheet.

Summary

AES_DECRYPT() reverses AES_ENCRYPT() only when the matching key, IV, and mode are used. Its result must not be treated as authenticated data, and sample keys must never be reused in production.