AES_ENCRYPT

Syntax

AES_ENCRYPT(str,key_str[,init_vector])

Purpose

You can call this function to use the key_str key string to decrypt the encrypted string and return the original str string in plaintext. It does not limit the length of str, and automatically pads str to a length that is an integer 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 parameter block_encryption_mode specifies the mode for block-based encryption algorithms. The default value aes-128-ecb indicates that a 128-bit key and 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 and 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 (0.00 sec)
  

  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 (0.00 sec)
  

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

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

• 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 (0.00 sec)