How to decrypt data with the AES (Advanced Encryption Standard) algorithm using the AES_DECRYPT() function?

AES_DECRYPT(cipher, key, init, kdf, salt, info, iterations) is a MySQL built-in function that decrypts a given cipher text with a given secret key using the AES (Advanced Encryption Standard) algorithm. For example:

SELECT AES_DECRYPT(x'124405E4F8AEC1205789E63B3D882A5A', 'MyKey');
  -- +-----------------------------------------------------------+
  -- | AES_DECRYPT(x'124405E4F8AEC1205789E63B3D882A5A', 'MyKey') |
  -- +-----------------------------------------------------------+
  -- | FYIcenter.com                                             |
  -- +-----------------------------------------------------------+

By default, MySQL uses the ECB (Electronic CodeBook) encryption mode, which requires no initial vector. If the cipher text was encrypted with another mode, CBC (Cipher Block Chaining), CFB (Cipher FeedBack), or OFB - (Output FeedBack), you must provide the same initial vector used in the original encryption process. For example:

SHOW VARIABLES LIKE 'block_encryption_mode';
  -- +-----------------------+-------------+
  -- | Variable_name         | Value       |
  -- +-----------------------+-------------+
  -- | block_encryption_mode | aes-128-ecb |
  -- +-----------------------+-------------+

SET SESSION block_encryption_mode = 'aes-128-cbc';

SELECT HEX(AES_ENCRYPT('FYIcenter.com', 'MyKey', x'00000000000000000000000000000000'));
  -- +---------------------------------------------------------------------------------+
  -- | HEX(AES_ENCRYPT('FYIcenter.com', 'MyKey', x'00000000000000000000000000000000')) |
  -- +---------------------------------------------------------------------------------+
  -- | 124405E4F8AEC1205789E63B3D882A5A                                                |
  -- +---------------------------------------------------------------------------------+

SELECT AES_DECRYPT(x'124405E4F8AEC1205789E63B3D882A5A', 'MyKey',
  x'00000000000000000000000000000000');
  -- +-------------------------------------------------------------+
  -- | AES_DECRYPT(x'124405E4F8AEC1205789E63B3D882A5A', 'MyKey',
x'00000000000000000000000000000000')                          |
  -- +-------------------------------------------------------------+
  -- | FYIcenter.com                                               |
  -- +-------------------------------------------------------------+

SELECT AES_DECRYPT(x'124405E4F8AEC1205789E63B3D882A5A', 'MyKey',
  x'00000000000000000000000000000001');
  -- +-------------------------------------------------------------+
  -- | AES_DECRYPT(x'124405E4F8AEC1205789E63B3D882A5A', 'MyKey',
  x'00000000000000000000000000000001')                        |
  -- +-------------------------------------------------------------+
  -- | NULL                                                        |
  -- +-------------------------------------------------------------+

AES_DECRYPT() also supports KDF (Key Derivation Function) which allows you to generate strong keys from weak keys. This is done through kdf, salt, info, and iterations arguments, which must be the same values used in the encryption process. Note that the KDF feature is not working in MySQL 8.0. The example statement given in the MySQL reference guide returns an error:

SELECT AES_DECRYPT('mytext','mykeystring', '', 'pbkdf2_hmac','salt', '2000');
ERROR 1582 (42000): Incorrect parameter count in the call to native 
function 'AES_ENCRYPT'

Reference information of the AES_DECRYPT() function:

AES_DECRYPT(cipher, key, init, kdf, salt, info, iterations): clear
  Encrypts a given clear text with a given secret key using the 
  AES algorithm.

Arguments, return value and availability:
  cipher: Required. The cipher text to be decrypted.
  key: Required. The secret key used in the encryption process.
  init: Optional. The initial vector needed only for 
    CBC, CFB, or OFB encryption mode. 
  kdf: Optional. The KDF algorithm name.
  salt: Optional. The salt value used by the KDF algorithm.
  info: Optional. Additional information for the KDF algorithm.
  iterations: Optional. The number of iterations used by the KDF algorithm.
  clear: Return value. The decrypted text.
  Available since MySQL 4.1.

Related MySQL functions:

• AES_ENCRYPT() - AES Data Encryption
• AES_DECRYPT() - AES Data Decryption

⇒ AES_ENCRYPT() - AES Data Encryption

⇐ MySQL Functions for Encryption and Compression

⇑ MySQL Functions for Encryption and Compression

⇑⇑ MySQL Function References