Declaration
AES_ENCRYPT(str,key_str[,init_vector])
Description
Encrypts the string str using the key string key_str and returns the encrypted binary string. There is no length limit for str; this function automatically pads str to an integer multiple of the encryption block size. The ciphertext length is calculated as follows:
16 * (trunc(string_length / 16) + 1)
If any function parameter is NULL, the function returns NULL.
The AES_ENCRYPT() function uses the official Advanced Encryption Standard (AES) algorithm to encrypt data. The AES standard supports various key lengths, with a default of 128 bits. It also supports 196-bit and 256-bit key lengths. Longer key lengths provide higher security but slower encryption speeds. The AES_ENCRYPT() and AES_DECRYPT() functions support block encryption modes using initialization vectors:
The
block_encryption_modesystem variable controls the mode of the block-based encryption algorithm. The default value isaes-128-ecb, which indicates using a 128-bit key length and the ECB mode for encryption.The
init_vectorparameter indicates the initialization vector:When the encryption mode requires setting the
init_vectorparameter, its length must be 16 bytes or more (bytes exceeding 16 will be ignored). An error occurs ifinit_vectoris missing.If the encryption mode does not require setting the
init_vectorparameter, this parameter will be ignored.
For encryption modes that require initializing vectors, the same vector must be used for both encryption and decryption.
obclient> SET block_encryption_mode = 'aes-256-cbc'; obclient> SET @key_str ='My secret passphrase'; obclient> SET @init_vector = '1111111111111111'; obclient> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector); obclient> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector); +-----------------------------------------------+ | AES_DECRYPT(@crypt_str,@key_str,@init_vector) | +-----------------------------------------------+ | text | +-----------------------------------------------+ 1 row in setThe following table describes the block encryption modes, supported SSL libraries, and whether an initialization vector parameter is required.
Block Encryption ModeSupported SSL LibraryInitialization Vector RequiredECB OpenSSL No CBC OpenSSL Yes CFB1 OpenSSL Yes CFB8 OpenSSL Yes CFB128 OpenSSL Yes OFB OpenSSL Yes
Examples
Encrypt the string
'hello world'using the key'key'. Save the encrypted string in@pass.obclient> SET @pass=AES_ENCRYPT('hello world', 'key','1111111111111111'); Query OK, 0 rows affectedCheck the length of the encrypted string (it must be a power of 2).
obclient> SELECT CHAR_LENGTH(@pass); +--------------------+ | CHAR_LENGTH(@pass) | +--------------------+ | 16 | +--------------------+ 1 row in setUse
AES_DECRYPT()for decryption.obclient> SELECT AES_DECRYPT(@pass, 'key'); ERROR 1582 (42000): Incorrect parameter count obclient> SELECT AES_DECRYPT(@pass, 'key','1111111111111111'); +----------------------------------------------+ | AES_DECRYPT(@pass, 'key','1111111111111111') | +----------------------------------------------+ | hello world | +----------------------------------------------+ 1 row in set