AES Encryption and Decryption in MySQL

MySQL allows encrypting and decrypt data using the official AES (Advanced Encryption Standard) algorithm.

The AES_ENCRYPT() function encrypts the string with the specified key and returns the encrypted data in the binary format. We use the HEX() function to convert a binary data into readable hexadecimal string.

SELECT HEX(AES_ENCRYPT('Hello', 'ycGNDx5oT1oyED0J'));

The AES_DECRYPT() function decrypts the encrypted data with the specified key and returns the plaintext string. We store encrypted data as a hexadecimal string. By using the UNHEX() function, a hexadecimal string is converted into binary data.

SELECT AES_DECRYPT(UNHEX('925F31F2F7F9679EB9896462A99F5AEC'), 'ycGNDx5oT1oyED0J');

By default, AES_ENCRYPT() and AES_DECRYPT() functions uses a 128-bit key length and ECB mode. If the specified key is too small, it will be null-padded.

These functions accept an initialization vector (IV) as a third optional argument. IV is required for some block encryption modes.

The block_encryption_mode system variable allows to define block encryption mode. Default value is aes-128-ecb.

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = '3C5QYgFQr9AARjMyLNNQ3fL8QauXLTz0';
SET @iv = 'kaNUE3JAIVB9Em9v';
SET @ciphertext = AES_ENCRYPT('Hello', @key_str, @iv);
SELECT AES_DECRYPT(@ciphertext, @key_str, @iv);