AES_ENCRYPT

Syntax

AES_ENCRYPT(str,key_str[,init_vector])

Purpose

Encrypts the string str using the key string key_str and returns the encrypted binary string. The str parameter has no length limit, and the function automatically pads str to the nearest multiple of the encryption block size. The length of the ciphertext 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 AES (Advanced Encryption Standard) algorithm to encrypt data. The AES standard supports various key lengths, with a default of 128 bits. You can also use 192 or 256-bit key lengths. Longer key lengths provide higher security but may reduce encryption speed. The AES_ENCRYPT() and AES_DECRYPT() functions can control block encryption modes using an initialization vector (IV):

• The block_encryption_mode system variable specifies the mode of the block-based encryption algorithm. The default value is aes-128-ecb, which means that encryption is performed using a 128-bit key length and ECB mode.

• The init_vector parameter specifies the initialization vector:

  • When the encryption mode requires the init_vector parameter, its length must be at least 16 bytes (any bytes exceeding 16 will be ignored). If the init_vector parameter is missing, an error occurs.

  • When the encryption mode does not require the init_vector parameter, it is ignored.

  For encryption modes that require an initialization vector, 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 set
  

  The following table lists the supported block encryption modes, SSL libraries, and whether the initialization vector parameter is required.

  Block encryption mode	Supported SSL library	Requires initialization vector
  ECB	OpenSSL	No
  CBC	OpenSSL	Yes
  CFB1	OpenSSL	Yes
  CFB8	OpenSSL	Yes
  CFB128	OpenSSL	Yes
  OFB	OpenSSL	Yes

Examples

• Encrypts 'hello world' using the key 'key' and stores the encrypted string in @pass.

  obclient> SET @pass=AES_ENCRYPT('hello world', 'key','1111111111111111');
  Query OK, 0 rows affected
  

• Views the length of the encrypted string (which is a power of 2).

  obclient> SELECT CHAR_LENGTH(@pass);
  +--------------------+
  | CHAR_LENGTH(@pass) |
  +--------------------+
  |                 16 |
  +--------------------+
  1 row in set
  

• Decrypts the encrypted string using AES_DECRYPT().

  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