12.13 Encryption and Compression Functions

Encryption Functions

Name Description Deprecated

'AES_DECRYPT()'

Decrypt using AES

'AES_ENCRYPT()'

Encrypt using AES

'COMPRESS()'

Return result as a binary string

'DECODE()'

Decode a string Yes encrypted using
ENCODE()

'DES_DECRYPT()'

Decrypt a string Yes

'DES_ENCRYPT()'

Encrypt a string Yes

'ENCODE()'

Encode a string Yes

'ENCRYPT()'

Encrypt a string Yes

'MD5()'

Calculate MD5 checksum

'PASSWORD()'

Calculate and Yes return a
password string

'RANDOM_BYTES()'

Return a random byte vector

'SHA1()', 'SHA()'

Calculate an SHA-1 160-bit checksum

'SHA2()'

Calculate an SHA-2 checksum

'UNCOMPRESS()'

Uncompress a string compressed

'UNCOMPRESSED_LENGTH()'

Return the length of a string before compression

'VALIDATE_PASSWORD_STRENGTH()'

Determine strength of password

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a note 'VARBINARY': binary-varbinary. or note 'BLOB': blob. binary string data type. This avoids potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (note 'CHAR': char, note 'VARCHAR': char, *note 'TEXT': blob.).

Some encryption functions return strings of ASCII characters: 'MD5()', 'PASSWORD()', 'SHA()', 'SHA1()', 'SHA2()'. Their return value is a string that has a character set and collation determined by the 'character_set_connection' and 'collation_connection' system variables. This is a nonbinary string unless the character set is 'binary'.

If an application stores values from a function such as 'MD5()' or 'SHA1()' that returns a string of hex digits, more efficient storage and comparisons can be obtained by converting the hex representation to binary using 'UNHEX()' and storing the result in a *note 'BINARY(N)': binary-varbinary. column. Each pair of hexadecimal digits requires one byte in binary form, so the value of N depends on the length of the hex string. N is 16 for an 'MD5()' value and 20 for a 'SHA1()' value. For 'SHA2()', N ranges from 28 to 32 depending on the argument specifying the desired bit length of the result.

The size penalty for storing the hex string in a *note 'CHAR': char. column is at least two times, up to eight times if the value is stored in a column that uses the 'utf8' character set (where each character uses 4 bytes). Storing the string also results in slower comparisons because of the larger values and the need to take character set collation rules into account.

Suppose that an application stores 'MD5()' string values in a *note 'CHAR(32)': char. column:

 CREATE TABLE md5_tbl (md5_val CHAR(32), ...);
 INSERT INTO md5_tbl (md5_val, ...) VALUES(MD5('abcdef'), ...);

To convert hex strings to more compact form, modify the application to use 'UNHEX()' and *note 'BINARY(16)': binary-varbinary. instead as follows:

 CREATE TABLE md5_tbl (md5_val BINARY(16), ...);
 INSERT INTO md5_tbl (md5_val, ...) VALUES(UNHEX(MD5('abcdef')), ...);

Applications should be prepared to handle the very rare case that a hashing function produces the same value for two different input values. One way to make collisions detectable is to make the hash column a primary key.

Note:

Exploits for the MD5 and SHA-1 algorithms have become known. You may wish to consider using another one-way encryption function described in this section instead, such as 'SHA2()'.

Caution:

Passwords or other sensitive values supplied as arguments to encryption functions are sent as cleartext to the MySQL server unless an SSL connection is used. Also, such values appear in any MySQL logs to which they are written. To avoid these types of exposure, applications can encrypt sensitive values on the client side before sending them to the server. The same considerations apply to encryption keys. To avoid exposing these, applications can use stored procedures to encrypt and decrypt values on the server side.

'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 plaintext string. 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.

As of MySQL 5.7.40, 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 *note 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 note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.

The arguments for the 'AES_ENCRYPT()' and 'AES_DECRYPT()' functions are as follows:
1. STR
  
  The string for 'AES_ENCRYPT()' to encrypt using the key string KEY_STR, or (as of MySQL 5.7.40) the key derived from it by the specified KDF. The string can be any length. 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.
2. CRYPT_STR
  
  The encrypted string for 'AES_DECRYPT()' to decrypt using the key string KEY_STR, or (from MySQL 5.7.40) the key derived from it by the specified KDF. The string can be any length. The length of CRYPT_STR can be calculated from the length of the original string using this formula:
```
   16 * (trunc(STRING_LENGTH / 16) + 1)
```
3. KEY_STR
  
  The encryption key, or the input keying material that is used as the basis for deriving a key using a key derivation function (KDF). For the same instance of data, use the same value of KEY_STR for encryption with 'AES_ENCRYPT()' and decryption with 'AES_DECRYPT()'.
  
  If you are using a KDF, which you can from MySQL 5.7.40, KEY_STR can be any arbitrary information such as a password or passphrase. In the further arguments for the function, you specify the KDF name, then add further options to increase the security as appropriate for the KDF.
  
  When you use a KDF, the function creates a cryptographically strong secret key from the information passed in KEY_STR and any salt or additional information that you provide in the other arguments. 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.
  
  If you are not using a KDF, 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))));
```
  If you exceed the maximum key length of 128 bits, a warning is returned. If you are not using a KDF, do not pass a password or passphrase directly to KEY_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.
4. INIT_VECTOR
  
  An initialization vector, for block encryption modes that require it. The 'block_encryption_mode' system variable controls the mode. For the same instance of data, use the same value of INIT_VECTOR for encryption with 'AES_ENCRYPT()' and decryption with 'AES_DECRYPT()'.
  
  Note:
  
  If you are using a KDF, you must specify an initialization vector or a null string for this argument, in order to access the later arguments to define the KDF.
  
  For modes that require an initialization vector, it must be 16 bytes or longer (bytes in excess of 16 are ignored). An error occurs if INIT_VECTOR is missing. For modes that do not require an initialization vector, it is ignored and a warning is generated if INIT_VECTOR is specified, unless you are using a KDF.
  
  The default value for the 'block_encryption_mode' system variable is 'aes-128-ecb', or ECB mode, which does not require an initialization vector. The alternative permitted block encryption modes CBC, CFB1, CFB8, CFB128, and OFB all require an initialization vector.
  
  A random string of bytes to use for the initialization vector can be produced by calling 'RANDOM_BYTES(16)'.
5. KDF_NAME
  
  The name of the key derivation function (KDF) to create a key from the input keying material passed in KEY_STR, and other arguments as appropriate for the KDF. This optional argument is available from MySQL 5.7.40.
  
  For the same instance of data, use the same value of KDF_NAME for encryption with 'AES_ENCRYPT()' and decryption with 'AES_DECRYPT()'. When you specify KDF_NAME, you must specify INIT_VECTOR, using either a valid initialization vector, or a null string if the encryption mode does not require an initialization vector.
  
  The following values are supported:
```
1. 'hkdf'

   HKDF, which is available from OpenSSL 1.1.0.  HKDF
   extracts a pseudorandom key from the keying material then
   expands it into additional keys.  With HKDF, you can
   specify an optional salt (SALT) and context-specific
   information such as application details (INFO) to include
   in the keying material.

2. 'pbkdf2_hmac'

   PBKDF2, which is available from OpenSSL 1.0.2.  PBKDF2
   applies a pseudorandom function to the keying material,
   and repeats this process a large number of times to
   produce the key.  With PBKDF2, you can specify an
   optional salt (SALT) to include in the keying material,
   and set the number of iterations used to produce the key
   (ITERATIONS).
```
  In this example, HKDF is specified as the key derivation function, and a salt and context information are provided. The argument for the initialization vector is included but is the empty string:
```
   SELECT AES_ENCRYPT('mytext','mykeystring', '', 'hkdf', 'salt', 'info');
```
  In this example, PBKDF2 is specified as the key derivation function, a salt is provided, and the number of iterations is doubled from the recommended minimum:
```
   SELECT AES_ENCRYPT('mytext','mykeystring', '', 'pbkdf2_hmac','salt', '2000');
```
6. SALT
  
  A salt to be passed to the key derivation function (KDF). This optional argument is available from MySQL 5.7.40. Both HKDF and PBKDF2 can use salts, and their use is recommended to help prevent attacks based on dictionaries of common passwords or rainbow tables.
  
  A salt consists of random data, which for security must be different for each encryption operation. A random string of bytes to use for the salt can be produced by calling 'RANDOM_BYTES()'. This example produces a 64-bit salt:
```
   SET @salt = RANDOM_BYTES(8);
```
  For the same instance of data, use the same value of SALT for encryption with 'AES_ENCRYPT()' and decryption with 'AES_DECRYPT()'. The salt can safely be stored along with the encrypted data.
7. INFO
  
  Context-specific information for HKDF to include in the keying material, such as information about the application. This optional argument is available from MySQL 5.7.40 when you specify 'hkdf' as the KDF name. HKDF adds this information to the keying material specified in KEY_STR and the salt specified in SALT to produce the key.
  
  For the same instance of data, use the same value of INFO for encryption with 'AES_ENCRYPT()' and decryption with 'AES_DECRYPT()'.
8. ITERATIONS
  
  The iteration count for PBKDF2 to use when producing the key. This optional argument is available from MySQL 5.7.40 when you specify 'pbkdf2_hmac' as the KDF name. A higher count gives greater resistance to brute-force attacks because it has a greater computational cost for the attacker, but the same is necessarily true for the key derivation process. The default if you do not specify this argument is 1000, which is the minimum recommended by the OpenSSL standard.
  
  For the same instance of data, use the same value of ITERATIONS for encryption with 'AES_ENCRYPT()' and decryption with 'AES_DECRYPT()'.
  
  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 AES_DECRYPT(@crypt_str,@key_str,@init_vector); +-----------------------------------------------+ | AES_DECRYPT(@crypt_str,@key_str,@init_vector) | +-----------------------------------------------+ | 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 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:

* Empty strings are stored as empty strings.

* Nonempty strings are stored as a 4-byte length of the
  uncompressed string (low byte first), followed by the
  compressed string.  If the string ends with space, an extra
  '.' character is added to avoid problems with endspace
  trimming should the result be stored in a *note 'CHAR': char.
  or *note 'VARCHAR': char. column.  (However, use of nonbinary
  string data types such as *note 'CHAR': char. or *note
  'VARCHAR': char. to store compressed strings is not
  recommended anyway because character set conversion may occur.
  Use a *note 'VARBINARY': binary-varbinary. or *note 'BLOB':
  blob. binary string column instead.)

If 'COMPRESS()' is invoked from within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.

'DECODE(CRYPT_STR,PASS_STR)'

'DECODE()' decrypts the encrypted string CRYPT_STR using PASS_STR as the password. CRYPT_STR should be a string returned from 'ENCODE()'.

Note:

The 'ENCODE()' and 'DECODE()' functions are deprecated in MySQL 5.7, and should no longer be used. Expect them to be removed in a future MySQL release. Consider using 'AES_ENCRYPT()' and 'AES_DECRYPT()' instead.
'DES_DECRYPT(CRYPT_STR[,KEY_STR])'

Decrypts a string encrypted with 'DES_ENCRYPT()'. If an error occurs, this function returns 'NULL'.

This function works only if MySQL has been configured with SSL support. See *note encrypted-connections::.

If no KEY_STR argument is given, 'DES_DECRYPT()' examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the DES key file to decrypt the message. For this to work, the user must have the 'SUPER' privilege. The key file can be specified with the '--des-key-file' server option.

If you pass this function a KEY_STR argument, that string is used as the key for decrypting the message.

If the CRYPT_STR argument does not appear to be an encrypted string, MySQL returns the given CRYPT_STR.

Note:

The 'DES_ENCRYPT()' and 'DES_DECRYPT()' functions are deprecated in MySQL 5.7, are removed in MySQL 8.0, and should no longer be used. Consider using 'AES_ENCRYPT()' and 'AES_DECRYPT()' instead.
'DES_ENCRYPT(STR[,{KEY_NUM|KEY_STR}])'

Encrypts the string with the given key using the Triple-DES algorithm.

This function works only if MySQL has been configured with SSL support. See *note encrypted-connections::.

The encryption key to use is chosen based on the second argument to 'DES_ENCRYPT()', if one was given. With no argument, the first key from the DES key file is used. With a KEY_NUM argument, the given key number (0 to 9) from the DES key file is used. With a KEY_STR argument, the given key string is used to encrypt STR.

The key file can be specified with the '--des-key-file' server option.

The return string is a binary string where the first character is 'CHAR(128 | KEY_NUM)'. If an error occurs, 'DES_ENCRYPT()' returns 'NULL'.

The 128 is added to make it easier to recognize an encrypted key. If you use a string key, KEY_NUM is 127.

The string length for the result is given by this formula:
```
  NEW_LEN = ORIG_LEN + (8 - (ORIG_LEN % 8)) + 1
```
Each line in the DES key file has the following format:
```
  KEY_NUM DES_KEY_STR
```
Each KEY_NUM value must be a number in the range from '0' to '9'. Lines in the file may be in any order. DES_KEY_STR is the string that is used to encrypt the message. There should be at least one space between the number and the key. The first key is the default key that is used if you do not specify any key argument to 'DES_ENCRYPT()'.

You can tell MySQL to read new key values from the key file with the 'FLUSH DES_KEY_FILE' statement. This requires the 'RELOAD' privilege.

One benefit of having a set of default keys is that it gives applications a way to check for the existence of encrypted column values, without giving the end user the right to decrypt those values.

Note:

The 'DES_ENCRYPT()' and 'DES_DECRYPT()' functions are deprecated in MySQL 5.7, are removed in MySQL 8.0, and should no longer be used. Consider using 'AES_ENCRYPT()' and 'AES_DECRYPT()' instead.
```
  mysql> SELECT customer_address FROM customer_table
       > WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
```
If 'DES_ENCRYPT()' is invoked from within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.
'ENCODE(STR,PASS_STR)'

'ENCODE()' encrypts STR using PASS_STR as the password. The result is a binary string of the same length as STR. To decrypt the result, use 'DECODE()'.

Note:

The 'ENCODE()' and 'DECODE()' functions are deprecated in MySQL 5.7, and should no longer be used. Expect them to be removed in a future MySQL release.

If you still need to use 'ENCODE()', a salt value must be used with it to reduce risk. For example:
```
  ENCODE('cleartext', CONCAT('my_random_salt','my_secret_password'))
```
A new random salt value must be used whenever a password is updated.

If 'ENCODE()' is invoked from within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.
'ENCRYPT(STR[,SALT])'

Encrypts STR using the Unix 'crypt()' system call and returns a binary string. The SALT argument must be a string with at least two characters or else the result is 'NULL'. If no SALT argument is given, a random value is used.

Note:

The 'ENCRYPT()' function is deprecated in MySQL 5.7, are removed in MySQL 8.0, and should no longer be used. For one-way hashing, consider using 'SHA2()' instead.
```
  mysql> SELECT ENCRYPT('hello');
          -> 'VxuFAJXVARROc'
```
'ENCRYPT()' ignores all but the first eight characters of STR, at least on some systems. This behavior is determined by the implementation of the underlying 'crypt()' system call.

The use of 'ENCRYPT()' with the 'ucs2', 'utf16', 'utf16le', or 'utf32' multibyte character sets is not recommended because the system call expects a string terminated by a zero byte.

If 'crypt()' is not available on your system (as is the case with Windows), 'ENCRYPT()' always returns 'NULL'.

If 'ENCRYPT()' is invoked from within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.
'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.
```
  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.
'PASSWORD(STR)'

Note:

This function is deprecated in MySQL 5.7 and is removed in MySQL 8.0.

Returns a hashed password string calculated from the cleartext password STR. The return value is a string in the connection character set, or 'NULL' if the argument is 'NULL'. This function is the SQL interface to the algorithm used by the server to encrypt MySQL passwords for storage in the 'mysql.user' grant table.

The 'old_passwords' system variable controls the password hashing method used by the 'PASSWORD()' function. It also influences password hashing performed by note 'CREATE USER': create-user. and note 'GRANT': grant. statements that specify a password using an 'IDENTIFIED BY' clause.

The following table shows, for each password hashing method, the permitted value of 'old_passwords' and which authentication plugins use the hashing method.

Password Hashing Method old_passwords Associated Authentication Value Plugin

MySQL 4.1 native hashing

0 'mysql_native_password'

SHA-256 hashing

2 'sha256_password'

SHA-256 password hashing ('old_passwords=2') uses a random salt value, which makes the result from 'PASSWORD()' nondeterministic. Consequently, statements that use this function are not safe for statement-based replication and cannot be stored in the query cache.

Encryption performed by 'PASSWORD()' is one-way (not reversible), but it is not the same type of encryption used for Unix passwords.

Note:

'PASSWORD()' is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider a more secure function such as 'SHA2()' instead. Also see section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

Caution:

Under some circumstances, statements that invoke 'PASSWORD()' may be recorded in server logs or on the client side in a history file such as '~/.mysql_history', which means that cleartext passwords may be read by anyone having read access to that information. For information about the conditions under which this occurs for the server logs and how to control it, see note password-logging::. For similar information about client-side logging, see note mysql-logging::.
'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.

'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 and cannot be stored in the query cache.

If 'RANDOM_BYTES()' is invoked from within the note 'mysql': mysql. client, binary strings display using hexadecimal notation, depending on the value of the '--binary-as-hex'. For more information about that option, see note mysql::.
'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 was '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 *note encrypted-connections::.

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

Uncompresses a string compressed by the 'COMPRESS()' function. If the argument is not a compressed value, the result is '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.
```
  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. The return value ranges from 0 (weak) to 100 (strong).

Password assessment by 'VALIDATE_PASSWORD_STRENGTH()' is done by the 'validate_password' plugin. If that plugin is not installed, the function always returns 0. For information about installing 'validate_password', see note validate-password::. To examine or configure the parameters that affect password testing, check or set the system variables implemented by 'validate_password'. See note validate-password-options-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.

Password Test Return Value

Length < 4 0

Length ≥ 4 and < 25 'validate_password_length'

Satisfies policy 1 ('LOW') 50

Satisfies policy 2 ('MEDIUM') 75

Satisfies policy 3 ('STRONG') 100

File: manual.info.tmp, Node: locking-functions, Next: information-functions, Prev: encryption-functions, Up: functions