AES_ENCRYPT

Syntax

AES_ENCRYPT(str,key_str[,init_vector])

Description

Encrypts the string str using the key string key_str and returns the encrypted binary string. str has no length limit, and the function automatically pads str to a multiple of the encryption block size. The length of the ciphertext is calculated as follows:

16 * (trunc(string_length / 16) + 1)

If any function argument is NULL, the function returns NULL.

AES_ENCRYPT() implements data encryption using the official AES (Advanced Encryption Standard) algorithm. The AES standard allows for 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 result in slower encryption speeds. The AES_ENCRYPT() and AES_DECRYPT() functions can control block encryption mode using an initialization vector:

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

• The init_vector parameter specifies the initialization vector:

  • When the encryption mode requires setting the init_vector parameter, its length must be 16 bytes or more (any bytes exceeding 16 will be ignored). If the init_vector is missing, an error will occur.

  • When the encryption mode does not require setting the init_vector parameter, it will be ignored.

  For encryption modes that require an initialization vector, the same vector must be used for both encryption and decryption.

  SET block_encryption_mode = 'aes-256-cbc';
  SET @key_str ='My secret passphrase';
  SET @init_vector = '1111111111111111';
  SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
  SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
  +-----------------------------------------------+
  | AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
  +-----------------------------------------------+
  | text                                          |
  +-----------------------------------------------+
  1 row in set (0.001 sec)

  The following table lists the block encryption modes, supported SSL libraries, and whether an 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 result in @pass.

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

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

  SELECT CHAR_LENGTH(@pass);
  +--------------------+
  | CHAR_LENGTH(@pass) |
  +--------------------+
  |                 16 |
  +--------------------+
  1 row in set (0.001 sec)

• Decrypts using AES_DECRYPT().

  SELECT AES_DECRYPT(@pass, 'key');
  ERROR 1582 (42000): Incorrect parameter count
  SELECT AES_DECRYPT(@pass, 'key','1111111111111111');
  +----------------------------------------------+
  | AES_DECRYPT(@pass, 'key','1111111111111111') |
  +----------------------------------------------+
  | hello world                                  |
  +----------------------------------------------+
  1 row in set (0.001 sec)