(mysql.info.gz) Encryption functions
Info Catalog
(mysql.info.gz) Bit functions
(mysql.info.gz) Other Functions
(mysql.info.gz) Information functions
12.8.2 Encryption Functions
---------------------------
The functions in this section encrypt and decrypt data values. If you
want to store results from an encryption function that might contain
arbitrary byte values, use a `BLOB' column rather than a `CHAR' or
`VARCHAR' column to avoid potential problems with trailing space
removal that would change data values.
`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 usually secure
enough.
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 as `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'));
You can get even more security by not transferring the key over the
connection for each query, which can be accomplished by storing it
in a server-side variable at connection time. For example:
SELECT @password:='my password';
INSERT INTO t VALUES (1,AES_ENCRYPT('text',@password));
`AES_ENCRYPT()' and `AES_DECRYPT()' were added in MySQL 4.0.2, and
can be considered the most cryptographically secure encryption
functions currently available in MySQL.
`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. If you
want to save it in a column, use a `BLOB' column type.
`DES_DECRYPT(CRYPT_STR[,KEY_STR])'
Decrypts a string encrypted with `DES_ENCRYPT()'. On error, this
function returns `NULL'.
Note that this function works only if MySQL has been configured
with SSL support. 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 doesn't look like an encrypted string,
MySQL will return the given CRYPT_STR.
`DES_DECRYPT()' was added in MySQL 4.0.1.
`DES_ENCRYPT(STR[,(KEY_NUM|KEY_STR)])'
Encrypts the string with the given key using the Triple-DES
algorithm. On error, this function returns `NULL'.
Note that this function works only if MySQL has been configured
with SSL support. 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)'.
The 128 is added to make it easier to recognize an encrypted key.
If you use a string key, KEY_NUM will be 127.
The string length for the result will be `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 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
will be used to encrypt the message. Between the number and the
key there should be at least one space. The first key is the
default key that is used if you don't 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');
`DES_ENCRYPT()' was added in MySQL 4.0.1.
`ENCRYPT(STR[,SALT])'
Encrypt STR using the Unix `crypt()' system call. The SALT
argument should be a string with two characters. (As of MySQL
3.22.16, SALT may be longer than 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, `ENCRYPT()' always
returns `NULL'. Because of this, we recommend that you use `MD5()'
or `SHA1()' instead, because those two functions exist on all
platforms.
`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.
`MD5()' was added in MySQL 3.23.2.
`OLD_PASSWORD(STR)'
`OLD_PASSWORD()' is available as of MySQL 4.1, when the
implementation of `PASSWORD()' was changed to improve security.
`OLD_PASSWORD()' returns the value of the pre-4.1 implementation
of `PASSWORD()'. Password hashing.
`PASSWORD(STR)'
Calculates and returns a password string from the plaintext
password STR, 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');
-> '7f84554057dd964b'
`PASSWORD()' encryption is one-way (not reversible).
`PASSWORD()' does not perform password encryption in the same way
that Unix passwords are encrypted. See `ENCRYPT()'.
* The `PASSWORD()' function is used by the authentication
system in MySQL Server, you should _not_ use it in your own
applications. For that purpose, use `MD5()' or `SHA1()' instead.
Also see RFC 2195 for more information about handling passwords
and authentication securely in your application.
`SHA1(STR)'
`SHA(STR)'
Calculates an SHA1 160-bit checksum for the string, as described in
RFC 3174 (Secure Hash Algorithm). The value is returned as a
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 cryptographically safe function for storing
passwords.
mysql> SELECT SHA1('abc');
-> 'a9993e364706816aba3e25717850c26c9cd0d89d'
`SHA1()' was added in MySQL 4.0.2, and can be considered a
cryptographically more secure equivalent of `MD5()'. `SHA()' is
synonym for `SHA1()'.
Info Catalog
(mysql.info.gz) Bit functions
(mysql.info.gz) Other Functions
(mysql.info.gz) Information functions
automatically generated byinfo2html