MYSQL ENCRYPT

• AES_DECRYPT(crypt_str,key_str)

  This function decrypts data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of AES_ENCRYPT().

• AES_ENCRYPT(str,key_str)

  AES_ENCRYPT() and AES_DECRYPT() implement encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as “Rijndael.” The AES standard permits various key lengths. These functions implement AES with a 128-bit key length, but you can extend them to 256 bits by modifying the source. The key length is a trade off between performance and security.

  AES_ENCRYPT() encrypts the string str using the key string key_str and returns a binary string containing the encrypted output. AES_DECRYPT() decrypts the encrypted string crypt_str using the key string key_str and returns the original cleartext string. If either function argument is NULL, the function returns NULL.

  The str and crypt_str arguments can be any length, and padding is automatically added to str so it is a multiple of a block as required by block-based algorithms such as AES. This padding is automatically removed by the AES_DECRYPT() function. The length of crypt_str can be calculated using this formula:

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

  For a key length of 128 bits, the most secure way to pass a key to the key_str argument is to create a truly random 128-bit value and pass it as a binary value. For example:

  INSERT INTO t
  VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));

  A passphrase can be used to generate an AES key by hashing the passphrase. For example:

  INSERT INTO t
  VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));

  Do not pass a password or passphrase directly to crypt_str, hash it first. Previous versions of this documentation suggested the former approach, but it is no longer recommended as the examples shown here are more secure.

  If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible forAES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.