AES_ENCRYPT

Syntax

AES_ENCRYPT(str,key_str[,init_vector])

Purpose

AES_ENCRYPT uses the key specified by key_str to encrypt the string specified by str and returns the encrypted binary string. It does not limit the length of str, and automatically pads str to a length that is an integral multiple of the encrypted blocks. The length of the ciphertext is calculated by using the following formula:

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

If any argument is NULL, the function returns NULL.

AES_ENCRYPT() encrypts data by using the official Advanced Encryption Standard (AES) algorithm. The AES algorithm allows various key lengths. By default, the key length is 128 bits. You can also use a key of 196 or 256 bits. The longer the key, the higher the security, but the lower the encryption speed. The AES_ENCRYPT() and AES_DECRYPT() functions specify the block encryption mode by using an initialization vector:

• The system variable block_encryption_mode specifies the mode for block-based encryption. The default value aes-128-ecb indicates that a 128-bit key and the electronic codebook (ECB) mode are used for encryption.

• init_vector indicates the initialization vector.

  • When the init_vector argument is required in an encryption mode, it must be 16 bytes or greater in length. Bytes in excess of 16 are ignored. If init_vector is missing, an error occurs.

  • When the init_vector argument is not required in an encryption mode, it is ignored.

  If an encryption mode requires an initialization vector for encryption, the same vector must be used for 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 describes the details of block encryption modes, supported SSL libraries, and whether an initialization vector is required.

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

Examples

• Use the key 'key' to encrypt 'hello world', and store the encrypted string in @pass.

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

• View the length of the encrypted string. The length is an integral power of 2.

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

• Use 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