(mysql.info) encryption-functions
Info Catalog
(mysql.info) bit-functions
(mysql.info) other-functions
(mysql.info) information-functions
12.9.2 Encryption and Compression Functions
-------------------------------------------
The functions in this section perform encryption and decryption, and
compression and uncompression.
* The encryption and compression functions return binary strings.
For many of these functions, the result might contain arbitrary byte
values. If you want to store these results, use a `BLOB' column rather
than a `CHAR' or (before MySQL 5.0.3) `VARCHAR' column to avoid
potential problems with trailing space removal that would change data
values.
* Exploits for the MD5 and SHA-1 algorithms have become known.
You may wish to consider using one of the other encryption functions
described in this section instead.
* `AES_ENCRYPT(STR,KEY_STR)', `AES_DECRYPT(CRYPT_STR,KEY_STR)'
These functions 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,
but you can extend it up to 256 bits by modifying the source. We
chose 128 bits because it is much faster and it is secure enough
for most purposes.
`AES_ENCRYPT()' encrypts a string and returns a binary string.
`AES_DESCRIPT()' descrypts 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:
16 × (trunc(STRING_LENGTH / 16) + 1)
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.
You can use the AES functions to store data in an encrypted form
by modifying your queries:
INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
`AES_ENCRYPT()' and `AES_DECRYPT()' can be considered the most
cryptographically secure encryption functions currently available
in MySQL.
* `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.
* Non-empty strings are stored as a four-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 `CHAR' or `VARCHAR'
column. (Use of `CHAR' or `VARCHAR' to store compressed
strings is not recommended. It is better to use a `BLOB'
column instead.)
* `DECODE(CRYPT_STR,PASS_STR)'
Decrypts the encrypted string CRYPT_STR using PASS_STR as the
password. CRYPT_STR should be a string returned from `ENCODE()'.
* `ENCODE(STR,PASS_STR)'
Encrypt STR using PASS_STR as the password. To decrypt the result,
use `DECODE()'.
The result is a binary string of the same length as STR.
* `DES_DECRYPT(CRYPT_STR[,KEY_STR])'
Decrypts a string encrypted with `DES_ENCRYPT()'. If an error
occurs, this function returns `NULL'.
Note that this function works only if MySQL has been configured
with SSL support. See secure-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.
* `DES_ENCRYPT(STR[,(KEY_NUM|KEY_STR)])'
Encrypts the string with the given key using the Triple-DES
algorithm.
Note that this function works only if MySQL has been configured
with SSL support. See secure-connections.
The encryption key to use is chosen based on the second argument
to `DES_ENCRYPT()', if one was given:
*Argument* *Description*
No argument The first key from the DES key file is used.
KEY_NUM The given key number (0-9) from the DES key
file is used.
KEY_STR 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.
mysql> SELECT customer_address FROM customer_table
> WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');
* `ENCRYPT(STR[,SALT])'
Encrypts STR using the Unix `crypt()' system call and returns a
binary string. The SALT argument should be a string with at least
two characters. If no SALT argument is given, a random value is
used.
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.
If `crypt()' is not available on your system (as is the case with
Windows), `ENCRYPT()' always returns `NULL'.
* `MD5(STR)'
Calculates an MD5 128-bit checksum for the string. The value is
returned as a binary string of 32 hex digits, or `NULL' if the
argument was `NULL'. The return value can, for example, be used as
a hash key.
mysql> SELECT MD5('testing');
-> 'ae2b1fca515949e5d54fb22b8ed95575'
This is the `RSA Data Security, Inc. MD5 Message-Digest Algorithm.'
If you want to convert the value to uppercase, see the description
of binary string conversion given in the entry for the `BINARY'
operator in cast-functions.
See the note regarding the MD5 algorithm at the beginning this
section.
* `OLD_PASSWORD(STR)'
`OLD_PASSWORD()' was added to MySQL when the implementation of
`PASSWORD()' was changed to improve security. `OLD_PASSWORD()'
returns the value of the old (pre-4.1) implementation of
`PASSWORD()' as a binary string, and is intended to permit you to
reset passwords for any pre-4.1 clients that need to connect to
your version 5.0 MySQL server without locking them out. See
password-hashing.
* `PASSWORD(STR)'
Calculates and returns a password string from the plaintext
password STR and returns a binary string, or `NULL' if the
argument was `NULL'. This is the function that is used for
encrypting MySQL passwords for storage in the `Password' column of
the `user' grant table.
mysql> SELECT PASSWORD('badpwd');
-> '*AAB3E285149C0135D51A520E1940DD3263DC008C'
`PASSWORD()' encryption is one-way (not reversible).
`PASSWORD()' does not perform password encryption in the same way
that Unix passwords are encrypted. See `ENCRYPT()'.
system in MySQL Server; you should _not_ use it in your own
applications. For that purpose, consider `MD5()' or `SHA1()'
instead. Also see RFC 2195 for more information about handling
passwords and authentication securely in your applications.
* `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
binary string of 40 hex digits, or `NULL' if the argument was
`NULL'. One of the possible uses for this function is as a hash
key. You can also use it as a cryptographic function for storing
passwords. `SHA()' is synonymous with `SHA1()'.
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.
* `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
Info Catalog
(mysql.info) bit-functions
(mysql.info) other-functions
(mysql.info) information-functions
automatically generated byinfo2html