How to encrypt data with the AES (Advanced Encryption Standard) algorithm using the AES_ENCRYPT() function?

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

  -- +--------------------------------------------+
  -- | HEX(AES_ENCRYPT('FYIcenter.com', 'MyKey')) |
  -- +--------------------------------------------+
  -- | 124405E4F8AEC1205789E63B3D882A5A           |
  -- +--------------------------------------------+

By default, MySQL uses the ECB (Electronic CodeBook) encryption mode, which requires no initial vector. If you want to use another encryption mode like CBC (Cipher Block Chaining), CFB (Cipher FeedBack), or OFB - (Output FeedBack), you need to specify an 128-bit initial vector. 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'));
ERROR 1582 (42000): Incorrect parameter count in the call to native 
function 'aes_encrypt'

SELECT HEX(AES_ENCRYPT('FYIcenter.com', 'MyKey', x'0123456789abcdef'));
ERROR 1882 (HY000): The initialization vector supplied to aes_encrypt is 
too short. Must be at least 16 bytes long

SELECT HEX(AES_ENCRYPT('FYIcenter.com', 'MyKey', RANDOM_BYTES(16)));
  -- +--------------------------------------------------------------+
  -- | HEX(AES_ENCRYPT('FYIcenter.com', 'MyKey', RANDOM_BYTES(16))) |
  -- +--------------------------------------------------------------+
  -- | BBBB896BCCA90A5B18CC036092B6083F                             |
  -- +--------------------------------------------------------------+

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

AES_ENCRYPT() 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. However, the KDF feature is not working in MySQL 8.0. The example statement given in the MySQL reference guide returns an error:

SELECT AES_ENCRYPT('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_ENCRYPT() function:

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

Arguments, return value and availability:
  clear: Required. The clear text to be encrypted.
  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.
  cipher: Return value. The encrypted text.
  Available since MySQL 4.1.

Related MySQL functions:

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

⇒ COMPRESS() - Compressing Data

⇐ AES_DECRYPT() - AES Data Decryption

⇑ MySQL Functions for Encryption and Compression

⇑⇑ MySQL Function References