Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about encryption, hashing, and compression functions. This section details SQL functions for securing data, generating hashes, and compressing/decompressing information within your database.
Decrypt data using AES. This function decrypts a string that was encrypted using the Advanced Encryption Standard (AES) algorithm.
AES_ENCRYPT(crypt_str, key_str, [, iv [, mode]])AES_DECRYPT(crypt_str,key_str)This function allows decryption of data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of .
The function supports an initialization vector, and control of the block encryption mode. The default mode is specified by the system variable, which can be changed when calling the function with a mode. mode is aes-{128,192,256}-{ecb,cbc,ctr} for example: "AES-128-cbc".
For modes that require it, the initialization_vector iv should be 16 bytes (it can be longer, but the extra bytes are ignored). A shorter iv, where one is required, results in the function returning NULL. Calling will generate a random series of bytes that can be used for the iv.
Examples
The function does not support an initialization vector.
This page is licensed: GPLv2, originally from
SELECT HEX(AES_ENCRYPT('foo', 'bar', '0123456789abcdef', 'aes-128-ctr')) AS x;
+--------+
| x |
+--------+
| C57C4B |
+--------+
SELECT AES_DECRYPT(x'C57C4B', 'bar', '0123456789abcdef', 'aes-128-ctr');
+------------------------------------------------------------------+
| AES_DECRYPT(x'C57C4B', 'bar', '0123456789abcdef', 'aes-128-ctr') |
+------------------------------------------------------------------+
| foo |
+------------------------------------------------------------------+Encrypt a string. This function encrypts a string using a password, returning a binary string. It is the reverse of the DECODE function.
ENCODE(str,pass_str)ENCODE is not considered cryptographically secure, and should not be used for password encryption.
Encrypt str using pass_str as the password. To decrypt the result, use .
The result is a binary string of the same length as str.
The strength of the encryption is based on how good the random generator is.
It is not recommended to rely on the encryption performed by the ENCODE function. Using a salt value (changed when a password is updated) will improve matters somewhat, but for storing passwords, consider a more cryptographically secure function, such as .
This page is licensed: GPLv2, originally from
Derive a key. This function derives a key from a password using a Key Derivation Function (KDF), enhancing security for password storage.
KDF(key_str, salt [, {info | iterations} [, kdf_name [, width ]]])KDF is a key derivation function, similar to OpenSSL's EVP_KDF_derive(). The purpose of a KDF is to be slow, so if the calculated value is lost/stolen, the original key_str is not achievable easily with modern GPU. KDFs are therefore an ideal replacement for password hashes. KDFs can also pad out a password secret to the number of bits used in encryption algorithms.
For generating good encryption keys for a less expensive but cryptographically secure function like is recommended.
kdf_name is "hkdf" or "pbkdf2_hmac" (default).
Width (in bits) can be any number divisible by 8, by default it's taken from @@block_encryption_mode.
Iterations must be positive, and is 1000 by default.
Note that OpenSSL 1.0 doesn't support HKDF, so in this case NULL is returned. This OpenSSL version is still used in SLES 12 and CentOS 7.
This page is licensed: CC BY-SA / Gnu FDL
It is not recommended to use ENCRYPT() with utf16, utf32 or ucs2 multi-byte character sets because the crypt() system call expects a string terminated with a zero byte.
Note that the underlying crypt() system call may have some limitations, such as ignoring all but the first eight characters.
If the have_crypt system variable is set to NO (because the crypt() system call is not available), the ENCRYPT function will always return NULL.
This page is licensed: GPLv2, originally from fill_help_tables.sql
ENCRYPT(str[,salt])In the default mode, DECODE decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE(). The resulting string will be the original string only if pass_str is the same.
In , DECODE compares expr to the search expressions, in order. If it finds a match, the corresponding result expression is returned. If no matches are found, the default expression is returned, or NULL if no default is provided.
NULL values are treated as equivalent.
DECODE_ORACLE is a synonym for the Oracle-mode version of the function, and is available in all modes.
NULL values are treated as equivalent:
This page is licensed: GPLv2, originally from fill_help_tables.sql
DECODE(crypt_str,pass_str)UNCOMPRESSED_LENGTH() returns NULL or an incorrect result if the string is not compressed.Returns MYSQL_TYPE_LONG, or int(10), if the result fits within 32-bits.
This page is licensed: GPLv2, originally from fill_help_tables.sql
UNCOMPRESSED_LENGTH(compressed_string)ENCODE('not so secret text', CONCAT('random_salt','password'))Decrypt data using DES. This function decrypts a string that was encrypted using the Data Encryption Standard (DES) algorithm.
DES_DECRYPT is deprecated and will be removed in a future release.
DES_DECRYPT(crypt_str[,key_str])Decrypts a string encrypted with . If an error occurs, this function returns NULL.
This function works only if MariaDB has been configured with .
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, MariaDB returns the given crypt_str.
This page is licensed: GPLv2, originally from
Encrypt data using DES. This function encrypts a string using the Data Encryption Standard (DES) algorithm.
DES_ENCRYPT is deprecated and will be removed in a future release.
Encrypts the string with the given key using the Triple-DES algorithm.
This function works only if MariaDB has been configured with .
The encryption key to use is chosen based on the second argument toDES_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-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 isCHAR(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:
Each line in the DES key file has the following format:
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 MariaDB 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.
This page is licensed: GPLv2, originally from
Compress a string. This function compresses a string argument and returns the result as a binary string, useful for saving storage space.
COMPRESS(string_to_compress)Compresses a string and returns the result as a binary string. This function requires MariaDB 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 .
The server system variable indicates whether a compression library is present.
This page is licensed: GPLv2, originally from
SELECT ENCRYPT('encrypt me');
+-----------------------+
| ENCRYPT('encrypt me') |
+-----------------------+
| 4I5BsEx0lqTDk |
+-----------------------+DECODE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr])DECODE_ORACLE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr])SELECT DECODE_ORACLE(2+1,3*1,'found1',3*2,'found2','default');
+--------------------------------------------------------+
| DECODE_ORACLE(2+1,3*1,'found1',3*2,'found2','default') |
+--------------------------------------------------------+
| found1 |
+--------------------------------------------------------+
SELECT DECODE_ORACLE(2+4,3*1,'found1',3*2,'found2','default');
+--------------------------------------------------------+
| DECODE_ORACLE(2+4,3*1,'found1',3*2,'found2','default') |
+--------------------------------------------------------+
| found2 |
+--------------------------------------------------------+
SELECT DECODE_ORACLE(2+2,3*1,'found1',3*2,'found2','default');
+--------------------------------------------------------+
| DECODE_ORACLE(2+2,3*1,'found1',3*2,'found2','default') |
+--------------------------------------------------------+
| default |
+--------------------------------------------------------+SELECT DECODE_ORACLE(NULL,NULL,'Nulls are equivalent','Nulls are not equivalent');
+----------------------------------------------------------------------------+
| DECODE_ORACLE(NULL,NULL,'Nulls are equivalent','Nulls are not equivalent') |
+----------------------------------------------------------------------------+
| Nulls are equivalent |
+----------------------------------------------------------------------------+SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
+-----------------------------------------------+
| UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30))) |
+-----------------------------------------------+
| 30 |
+-----------------------------------------------+select hex(kdf('foo', 'bar', 'infa', 'hkdf'));
+----------------------------------------+
| hex(kdf('foo', 'bar', 'infa', 'hkdf')) |
+----------------------------------------+
| 612875F859CFB4EE0DFEFF9F2A18E836 |
+----------------------------------------+DES_ENCRYPT(str[,{key_num|key_str}])OLD_PASSWORD() returns the value of the old (pre-MySQL 4.1) implementation of PASSWORD() as a string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to a more recent MySQL server version, or any version of MariaDB, without locking them out.The return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables.
The return value is 16 bytes in length, or NULL if the argument was NULL.
This page is licensed: GPLv2, originally from fill_help_tables.sql
NULL is returned if the argument was NULL.
This page is licensed: GPLv2, originally from fill_help_tables.sql
MD5(str)The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. The return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SHA1(str), SHA(str)new_len = orig_len + (8 - (orig_len % 8)) + 1key_num des_key_strSELECT customer_address FROM customer_table
WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
+------------------------------------+
| LENGTH(COMPRESS(REPEAT('a',1000))) |
+------------------------------------+
| 21 |
+------------------------------------+
SELECT LENGTH(COMPRESS(''));
+----------------------+
| LENGTH(COMPRESS('')) |
+----------------------+
| 0 |
+----------------------+
SELECT LENGTH(COMPRESS('a'));
+-----------------------+
| LENGTH(COMPRESS('a')) |
+-----------------------+
| 13 |
+-----------------------+
SELECT LENGTH(COMPRESS(REPEAT('a',16)));
+----------------------------------+
| LENGTH(COMPRESS(REPEAT('a',16))) |
+----------------------------------+
| 15 |
+----------------------------------+OLD_PASSWORD(str)SELECT MD5('testing');
+----------------------------------+
| MD5('testing') |
+----------------------------------+
| ae2b1fca515949e5d54fb22b8ed95575 |
+----------------------------------+SELECT SHA1('some boring text');
+------------------------------------------+
| SHA1('some boring text') |
+------------------------------------------+
| af969fc2085b1bb6d31e517d5c456def5cdd7093 |
+------------------------------------------+Generate random bytes. This function returns a binary string of random bytes of a specified length, suitable for cryptographic use.
The RANDOM_BYTES function generates a binary string of random bytes.
Given a length from 1 to 1024, generates a binary string of length consisting of random bytes generated by the SSL library's random number generator.
See the RAND_bytes() function documentation of your SSL library for information on the random number generator. In the case of , a cryptographically secure pseudo random generator (CSPRNG) is used.
Statements containing the RANDOM_BYTES function are .
An error occurs if length is outside the range 1 to 1024.
This page is licensed: CC BY-SA / Gnu FDL
Calculate SHA-2 checksum. This function computes the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512).
SHA2(str,hash_len)Given a string str, calculates an SHA-2 checksum, which is considered more cryptographically secure than its equivalent. The SHA-2 family includes SHA-224, SHA-256, SHA-384, and SHA-512, and the hash_len must correspond to one of these, i.e. 224, 256, 384 or 512. 0 is equivalent to 256.
The return value is a nonbinary string in the connection , determined by the values of the and system variables.
NULL is returned if the hash length is not valid, or the string str is NULL.
SHA2 only works if MariaDB is configured with .
This page is licensed: CC BY-SA / Gnu FDL
Encrypt data using AES. This function encrypts a string using the Advanced Encryption Standard (AES) algorithm and returns a binary string.
AES_ENCRYPT(str, key, [, iv [, mode]])AES_ENCRYPT(str,key_str)AES_ENCRYPT() and allow encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as "Rijndael." Encoding with a 128-bit key length is used (from , this is the default, and can be changed). 128 bits is much faster and is secure enough for most purposes.
AES_ENCRYPT() encrypts a string str using the key key_str, and returns a binary string.
AES_DECRYPT() decrypts the encrypted string and returns the original string.
The input arguments may be any length. If either argument is NULL, the result of this function is also NULL.
Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula:
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.
MariaDB starting with
The function supports an initialization vector, and control of the block encryption mode. The default mode is specified by the system variable, which can be changed when calling the function with a mode. mode is aes-{128,192,256}-{ecb,cbc,ctr} for example: "AES-128-cbc".
AES_ENCRYPT(str, key) can no longer be used in persistent virtual columns (and the like).
The function does not support an initialization vector.
is a function for generating good encryption keys for AES_ENCRYPT.
is a key derivation function which is useful if an authentication validation against the value is required without data being able to be decrypted.
This page is licensed: GPLv2, originally from
Uncompress a compressed string. This function uncompresses a binary string compressed by the COMPRESS 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. This function requires MariaDB to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The server system variable indicates whether a compression library is present.
This page is licensed: GPLv2, originally from
SELECT UNCOMPRESS(COMPRESS('a string'));
+----------------------------------+
| UNCOMPRESS(COMPRESS('a string')) |
+----------------------------------+
| a string |
+----------------------------------+
SELECT UNCOMPRESS('a string');
+------------------------+
| UNCOMPRESS('a string') |
+------------------------+
| NULL |
+------------------------+SELECT SHA2('Maria',224);
+----------------------------------------------------------+
| SHA2('Maria',224) |
+----------------------------------------------------------+
| 6cc67add32286412efcab9d0e1675a43a5c2ef3cec8879f81516ff83 |
+----------------------------------------------------------+
SELECT SHA2('Maria',256);
+------------------------------------------------------------------+
| SHA2('Maria',256) |
+------------------------------------------------------------------+
| 9ff18ebe7449349f358e3af0b57cf7a032c1c6b2272cb2656ff85eb112232f16 |
+------------------------------------------------------------------+
SELECT SHA2('Maria',0);
+------------------------------------------------------------------+
| SHA2('Maria',0) |
+------------------------------------------------------------------+
| 9ff18ebe7449349f358e3af0b57cf7a032c1c6b2272cb2656ff85eb112232f16 |
+------------------------------------------------------------------+RANDOM_BYTES(length)SELECT HEX(AES_ENCRYPT('foo', 'bar', '0123456789abcdef', 'aes-256-cbc')) AS x;
+----------------------------------+
| x |
+----------------------------------+
| 42A3EB91E6DFC40A900D278F99E0726E |
+----------------------------------+INSERT INTO t VALUES (AES_ENCRYPT('text',SHA2('password',512)));16 x (trunc(string_length / 16) + 1)Calculate password hash. This function calculates a password hash string for a plaintext password.
PASSWORD(str)The PASSWORD() function is used for hashing passwords for use in authentication by the MariaDB server. It is not intended for use in other applications.
Calculates and returns a hashed password string from the plaintext password str. Returns an empty string if the argument is NULL.
The return value is a nonbinary string in the connection , determined by the values of the and system variables.
This is the function that is used for hashing MariaDB passwords for storage in the Password column of the (see ), usually used with the statement. It is not intended for use in other applications.
The function takes into account the authentication plugin where applicable (a or statement). For example, when used in conjunction with a user authenticated by the , the statement will create a longer hash:
The behavior of this function is affected by the value of the system variable. If this is set to 1 (0 is default), MariaDB reverts to using the by default for newly created users and passwords.
- permits the setting of basic criteria for passwords
- pre-MySQL 4.1 password function
This page is licensed: GPLv2, originally from
CREATE USER edtest@localhost IDENTIFIED VIA ed25519 USING PASSWORD('secret');
CREATE USER edtest2@localhost IDENTIFIED BY 'secret';
SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv
WHERE user LIKE 'edtest%'\G
*************************** 1. row ***************************
CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)): edtest@localhost => {
...
"plugin": "ed25519",
"authentication_string": "ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY",
...
}
*************************** 2. row ***************************
CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)): edtest2@localhost => {
...
"plugin": "mysql_native_password",
"authentication_string": "*14E65567ABDB5135D0CFD9A70B3032C179A49EE7",
...
}SELECT PASSWORD('notagoodpwd');
+-------------------------------------------+
| PASSWORD('notagoodpwd') |
+-------------------------------------------+
| *3A70EE9FC6594F88CE9E959CD51C5A1C002DC937 |
+-------------------------------------------+SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');