12.13 Encryption and Compression Functions
Table 12.17 Encryption Functions
Name | Description |
---|---|
AES_DECRYPT() |
Decrypt using AES |
AES_ENCRYPT() |
Encrypt using AES |
ASYMMETRIC_DECRYPT() |
Decrypt ciphertext using private or public key |
ASYMMETRIC_DERIVE() |
Derive symmetric key from asymmetric keys |
ASYMMETRIC_ENCRYPT() |
Encrypt cleartext using private or public key |
ASYMMETRIC_SIGN() |
Generate signature from digest |
ASYMMETRIC_VERIFY() |
Verify that signature matches digest |
COMPRESS() |
Return result as a binary string |
CREATE_ASYMMETRIC_PRIV_KEY() |
Create private key |
CREATE_ASYMMETRIC_PUB_KEY() |
Create public key |
CREATE_DH_PARAMETERS() |
Generate shared DH secret |
CREATE_DIGEST() |
Generate digest from string |
DECODE() (deprecated) |
Decode a string encrypted using ENCODE() |
DES_DECRYPT() (deprecated) |
Decrypt a string |
DES_ENCRYPT() (deprecated) |
Encrypt a string |
ENCODE() (deprecated) |
Encode a string |
ENCRYPT() (deprecated) |
Encrypt a string |
MD5() |
Calculate MD5 checksum |
PASSWORD() (deprecated) |
Calculate and 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 VARBINARY
or BLOB
binary string data type. This will avoid 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 (CHAR
, VARCHAR
, TEXT
).
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 BINARY(
column. Each pair of hexadecimal digits requires one byte in binary form, so the value of N
)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 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 CHAR(32)
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 BINARY(16)
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.
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()
.
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 will 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
])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 and cannot be stored in the query cache.AES_ENCRYPT(
str
,key_str
[,init_vector
])AES_ENCRYPT()
andAES_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 stringstr
using the key stringkey_str
and returns a binary string containing the encrypted output.AES_DECRYPT()
decrypts the encrypted stringcrypt_str
using the key stringkey_str
and returns the original plaintext string. If either function argument isNULL
, the function returnsNULL
.The
str
andcrypt_str
arguments can be any length, and padding is automatically added tostr
so it is a multiple of a block as required by block-based algorithms such as AES. This padding is automatically removed by theAES_DECRYPT()
function. The length ofcrypt_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 returnsNULL
. However, it is possible forAES_DECRYPT()
to return a non-NULL
value (possibly garbage) if the input data or the key is invalid.AES_ENCRYPT()
andAES_DECRYPT()
permit control of the block encryption mode and take an optionalinit_vector
initialization vector argument:The
block_encryption_mode
system variable controls the mode for block-based encryption algorithms. Its default value isaes-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 5.1.7, “Server System Variables”.The optional
init_vector
argument provides an initialization vector for block encryption modes that require it.
For modes that require the optional
init_vector
argument, it must be 16 bytes or longer (bytes in excess of 16 are ignored). An error occurs ifinit_vector
is missing.For modes that do not require
init_vector
, it is ignored and a warning is generated if it is specified.A random string of bytes to use for the initialization vector can be produced by calling
RANDOM_BYTES(16)
. For encryption modes that require an initialization vector, the same vector must be used for encryption and decryption.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 | +-----------------------------------------------+
The following table lists each permitted block encryption mode, the SSL libraries that support it, and whether the initialization vector argument is required.
Block Encryption Mode SSL Libraries that Support Mode Initialization Vector Required ECB OpenSSL, yaSSL No CBC OpenSSL, yaSSL Yes CFB1 OpenSSL Yes CFB8 OpenSSL Yes CFB128 OpenSSL Yes OFB OpenSSL Yes Statements that use
AES_ENCRYPT()
orAES_DECRYPT()
are unsafe for statement-based replication and cannot be stored in the query cache.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 alwaysNULL
. The compressed string can be uncompressed withUNCOMPRESS()
.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 aCHAR
orVARCHAR
column. (However, use of nonbinary string data types such asCHAR
orVARCHAR
to store compressed strings is not recommended anyway because character set conversion may occur. Use aVARBINARY
orBLOB
binary string column instead.)
DECODE()
decrypts the encrypted stringcrypt_str
usingpass_str
as the password.crypt_str
should be a string returned fromENCODE()
.NoteThe
ENCODE()
andDECODE()
functions are deprecated in MySQL 5.7, will be removed in a future MySQL release, and should no longer be used. Consider usingAES_ENCRYPT()
andAES_DECRYPT()
instead.DES_DECRYPT(
crypt_str
[,key_str
])Decrypts a string encrypted with
DES_ENCRYPT()
. If an error occurs, this function returnsNULL
.This function works only if MySQL has been configured with SSL support. See Section 6.3, “Using 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 theSUPER
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 givencrypt_str
.NoteThe
DES_ENCRYPT()
andDES_DECRYPT()
functions are deprecated as of MySQL 5.7.6, will be removed in a future MySQL release, and should no longer be used. Consider usingAES_ENCRYPT()
andAES_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 Section 6.3, “Using 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 akey_num
argument, the given key number (0 to 9) from the DES key file is used. With akey_str
argument, the given key string is used to encryptstr
.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 |
. If an error occurs,key_num
)DES_ENCRYPT()
returnsNULL
.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 from0
to9
. 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 toDES_ENCRYPT()
.You can tell MySQL to read new key values from the key file with the
FLUSH DES_KEY_FILE
statement. This requires theRELOAD
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.
NoteThe
DES_ENCRYPT()
andDES_DECRYPT()
functions are deprecated as of MySQL 5.7.6, will be removed in a future MySQL release, and should no longer be used. Consider usingAES_ENCRYPT()
andAES_DECRYPT()
instead.mysql> SELECT customer_address FROM customer_table > WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
ENCODE()
encryptsstr
usingpass_str
as the password. The result is a binary string of the same length asstr
. To decrypt the result, useDECODE()
.NoteThe
ENCODE()
andDECODE()
functions are deprecated in MySQL 5.7, will be removed in a future MySQL release, and should no longer be used.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.
Encrypts
str
using the Unixcrypt()
system call and returns a binary string. Thesalt
argument must be a string with at least two characters or the result will beNULL
. If nosalt
argument is given, a random value is used.NoteThe
ENCRYPT()
function is deprecated as of MySQL 5.7.6, will be removed in a future MySQL release, and should no longer be used. For one-way hashing, consider usingSHA2()
instead.mysql> SELECT ENCRYPT('hello'); -> 'VxuFAJXVARROc'
ENCRYPT()
ignores all but the first eight characters ofstr
, at least on some systems. This behavior is determined by the implementation of the underlyingcrypt()
system call.The use of
ENCRYPT()
with theucs2
,utf16
,utf16le
, orutf32
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 returnsNULL
.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 wasNULL
. 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.
-
Note
This function is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
Returns a hashed password string calculated from the cleartext password
str
. The return value is a string in the connection character set, orNULL
if the argument isNULL
. This function is the SQL interface to the algorithm used by the server to encrypt MySQL passwords for storage in themysql.user
grant table.The
old_passwords
system variable controls the password hashing method used by thePASSWORD()
function. It also influences password hashing performed byCREATE USER
andGRANT
statements that specify a password using anIDENTIFIED 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 Value Associated Authentication 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 fromPASSWORD()
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.NotePASSWORD()
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 asSHA2()
instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)) , for more information about handling passwords and authentication securely in your applications.CautionUnder 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 Section 6.1.2.3, “Passwords and Logging”. For similar information about client-side logging, see Section 4.5.1.3, “mysql Client Logging”. This function returns a binary string of
len
random bytes generated using the random number generator of the SSL library. Permitted values oflen
range from 1 to 1024. For values outside that range, an error occurs.RANDOM_BYTES()
can be used to provide the initialization vector for theAES_DECRYPT()
andAES_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.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 wasNULL
. 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 withSHA1()
.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 ofMD5()
. However, see the note regarding the MD5 and SHA-1 algorithms at the beginning this section.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 isNULL
. 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 6.3, “Using Encrypted Connections”.
SHA2()
can be considered cryptographically more secure thanMD5()
orSHA1()
.UNCOMPRESS(
string_to_uncompress
)Uncompresses a string compressed by the
COMPRESS()
function. If the argument is not a compressed value, the result isNULL
. This function requires MySQL to have been compiled with a compression library such aszlib
. Otherwise, the return value is alwaysNULL
.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 thevalidate_password
plugin. If that plugin is not installed, the function always returns 0. For information about installingvalidate_password
, see Section 6.4.3, “The Password Validation Plugin”. To examine or configure the parameters that affect password testing, check or set the system variables implemented byvalidate_password
. See Section 6.4.3.2, “Password Validation Plugin 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 othervalidate_password
system variables are set.Password Test Return Value Length < 4 0 Length ≥ 4 and < validate_password_length
25 Satisfies policy 1 ( LOW
)50 Satisfies policy 2 ( MEDIUM
)75 Satisfies policy 3 ( STRONG
)100