AES_DECRYPT(crypt_str,key_str[,init_vector][,kdf_name][,salt][,info | iterations])

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

Statements that use AES_DECRYPT() are unsafe for statement-based replication.

AES_ENCRYPT(str,key_str[,init_vector][,kdf_name][,salt][,info | iterations])

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. By default these functions implement AES with a 128-bit key length. Key lengths of 196 or 256 bits can be used, as described later. 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 (binary) string in hexadecimal format. (To obtain the string as plaintext, cast the result to CHAR. Alternatively, start the mysql client with --skip-binary-as-hex to cause all binary values to be displayed as text.) If either function argument is NULL, the function returns NULL. If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.

These functions support the use of a key derivation function (KDF) to create a cryptographically strong secret key from the information passed in key_str. The derived key is used to encrypt and decrypt the data, and it remains in the MySQL Server instance and is not accessible to users. Using a KDF is highly recommended, as it provides better security than specifying your own premade key or deriving it by a simpler method as you use the function. The functions support HKDF (available from OpenSSL 1.1.0), for which you can specify an optional salt and context-specific information to include in the keying material, and PBKDF2 (available from OpenSSL 1.0.2), for which you can specify an optional salt and set the number of iterations used to produce the key.

AES_ENCRYPT() and AES_DECRYPT() permit control of the block encryption mode. The block_encryption_mode system variable controls the mode for block-based encryption algorithms. Its default value is aes-128-ecb, which signifies encryption using a key length of 128 bits and ECB mode. For a description of the permitted values of this variable, see Section 7.1.8, “Server System Variables”. The optional init_vector argument is used to provide an initialization vector for block encryption modes that require it.

Statements that use AES_ENCRYPT() or AES_DECRYPT() are unsafe for statement-based replication.

If AES_ENCRYPT() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

The arguments for the AES_ENCRYPT() and AES_DECRYPT() functions are as follows:

mysql> SET block_encryption_mode = 'aes-256-cbc';
mysql> SET @key_str = SHA2('My secret passphrase',512);
mysql> SET @init_vector = RANDOM_BYTES(16);
mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
mysql> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR);
+-------------------------------------------------------------+
| CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) |
+-------------------------------------------------------------+
| text                                                        |
+-------------------------------------------------------------+

COMPRESS(string_to_compress)

Compresses a string and returns the result as a binary string. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The return value is also NULL if string_to_compress is NULL. The compressed string can be uncompressed with UNCOMPRESS().

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
        -> 21
mysql> SELECT LENGTH(COMPRESS(''));
        -> 0
mysql> SELECT LENGTH(COMPRESS('a'));
        -> 13
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
        -> 15

The compressed string contents are stored the following way:

If COMPRESS() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

MD5(str)

Calculates an MD5 128-bit checksum for the string. The value is returned as a string of 32 hexadecimal digits, or NULL if the argument was NULL. The return value can, for example, be used as a hash key. See the notes at the beginning of this section about storing hash values efficiently.

The return value is a string in the connection character set.

If FIPS mode is enabled, MD5() returns NULL. See Section 8.8, “FIPS Support”.

mysql> SELECT MD5('testing');
        -> 'ae2b1fca515949e5d54fb22b8ed95575'

This is the “RSA Data Security, Inc. MD5 Message-Digest Algorithm.”

See the note regarding the MD5 algorithm at the beginning this section.

RANDOM_BYTES(len)

This function returns a binary string of len random bytes generated using the random number generator of the SSL library. Permitted values of len range from 1 to 1024. For values outside that range, an error occurs. Returns NULL if len is NULL.

RANDOM_BYTES() can be used to provide the initialization vector for the AES_DECRYPT() and AES_ENCRYPT() functions. For use in that context, len must be at least 16. Larger values are permitted, but bytes in excess of 16 are ignored.

RANDOM_BYTES() generates a random value, which makes its result nondeterministic. Consequently, statements that use this function are unsafe for statement-based replication.

If RANDOM_BYTES() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

SHA1(str), SHA(str)

Calculates an SHA-1 160-bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a string of 40 hexadecimal digits, or NULL if the argument is NULL. One of the possible uses for this function is as a hash key. See the notes at the beginning of this section about storing hash values efficiently. SHA() is synonymous with SHA1().

The return value is a string in the connection character set.

mysql> SELECT SHA1('abc');
        -> 'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1() can be considered a cryptographically more secure equivalent of MD5(). However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section.

SHA2(str, hash_length)

Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The first argument is the plaintext string to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Otherwise, the function result is a hash value containing the desired number of bits. See the notes at the beginning of this section about storing hash values efficiently.

The return value is a string in the connection character set.

mysql> SELECT SHA2('abc', 224);
        -> '23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7'

This function works only if MySQL has been configured with SSL support. See Section 8.3, “Using Encrypted Connections”.

SHA2() can be considered cryptographically more secure than MD5() or SHA1().

STATEMENT_DIGEST(statement)

Given an SQL statement as a string, returns the statement digest hash value as a string in the connection character set, or NULL if the argument is NULL. The related STATEMENT_DIGEST_TEXT() function returns the normalized statement digest. For information about statement digesting, see Section 29.10, “Performance Schema Statement Digests and Sampling”.

Both functions use the MySQL parser to parse the statement. If parsing fails, an error occurs. The error message includes the parse error only if the statement is provided as a literal string.

The max_digest_length system variable determines the maximum number of bytes available to these functions for computing normalized statement digests.

mysql> SET @stmt = 'SELECT * FROM mytable WHERE cola = 10 AND colb = 20';
mysql> SELECT STATEMENT_DIGEST(@stmt);
+------------------------------------------------------------------+
| STATEMENT_DIGEST(@stmt)                                          |
+------------------------------------------------------------------+
| 3bb95eeade896657c4526e74ff2a2862039d0a0fe8a9e7155b5fe492cbd78387 |
+------------------------------------------------------------------+
mysql> SELECT STATEMENT_DIGEST_TEXT(@stmt);
+----------------------------------------------------------+
| STATEMENT_DIGEST_TEXT(@stmt)                             |
+----------------------------------------------------------+
| SELECT * FROM `mytable` WHERE `cola` = ? AND `colb` = ?  |
+----------------------------------------------------------+

STATEMENT_DIGEST_TEXT(statement)

Given an SQL statement as a string, returns the normalized statement digest as a string in the connection character set, or NULL if the argument is NULL. For additional discussion and examples, see the description of the related STATEMENT_DIGEST() function.

UNCOMPRESS(string_to_uncompress)

Uncompresses a string compressed by the COMPRESS() function. If the argument is not a compressed value, the result is NULL; if string_to_uncompress is NULL, the result is also NULL. This function requires MySQL to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL.

mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
        -> 'any string'
mysql> SELECT UNCOMPRESS('any string');
        -> NULL

UNCOMPRESSED_LENGTH(compressed_string)

Returns the length that the compressed string had before being compressed. Returns NULL if compressed_string is NULL.

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
        -> 30

VALIDATE_PASSWORD_STRENGTH(str)

Given an argument representing a plaintext password, this function returns an integer to indicate how strong the password is, or NULL if the argument is NULL. The return value ranges from 0 (weak) to 100 (strong).

Password assessment by VALIDATE_PASSWORD_STRENGTH() is done by the validate_password component. If that component is not installed, the function always returns 0. For information about installing validate_password, see Section 8.4.3, “The Password Validation Component”. To examine or configure the parameters that affect password testing, check or set the system variables implemented by validate_password. See Section 8.4.3.2, “Password Validation Options and Variables”.

The password is subjected to increasingly strict tests and the return value reflects which tests were satisfied, as shown in the following table. In addition, if the validate_password.check_user_name system variable is enabled and the password matches the user name, VALIDATE_PASSWORD_STRENGTH() returns 0 regardless of how other validate_password system variables are set.