Additional languages
MySQL 5.0 Reference Manual :: 12 Functions and Operators :: 12.10 Other Functions :: 12.10.2 Encryption and Compression Functions
« 12.10.1 Bit Functions
12.10.3 Information Functions »
Section Navigation      [Toggle]

12.10.2. Encryption and Compression Functions

Name Description
AES_DECRYPT() Decrypt using AES
AES_ENCRYPT() Encrypt using AES
COMPRESS() Return result as a binary string
DECODE() Decodes a string encrypted using ENCODE()
DES_DECRYPT() Decrypt a string
DES_ENCRYPT() Decrypt a string
ENCODE() Encode a string
ENCRYPT() Encrypt a string
MD5() Calculate MD5 checksum
OLD_PASSWORD() Return the value of the old (pre-4.1) implementation of PASSWORD
PASSWORD() Calculate and return a password string
SHA1(), SHA() Calculate an SHA-1 160-bit checksum
UNCOMPRESS() Uncompress a string compressed
UNCOMPRESSED_LENGTH() Return the length of a string before compression

The functions in this section perform encryption and decryption, and compression and uncompression:

Compression or encryption Uncompression or decryption
AES_ENCRYT() AES_DECRYPT()
COMPRESS() UNCOMPRESS()
ENCODE() DECODE()
DES_ENCRYPT() DES_DECRYPT()
ENCRYPT() Not available
MD5() Not available
OLD_PASSWORD() Not available
PASSWORD() Not available
SHA() or SHA1() Not available
Not available UNCOMPRESSED_LENGTH()

Note: 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.

Note: 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_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: 

    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.

    The strength of the encryption is based on how good the random generator is. It should suffice for short strings.

  • 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 Section 5.8.7, “Using 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 Section 5.8.7, “Using 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.

    The use of ENCYPT() with multi-byte character sets other than utf8 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.

  • 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 Section 12.9, “Cast Functions and Operators”.

    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 Section 5.7.9, “Password Hashing as of MySQL 4.1”.

  • 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().

    Note: The PASSWORD() function is used by the authentication 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, section 2 (Challenge-Response Authentication Mechanism (CRAM)), 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

User Comments

Posted by Rizwan Omer on June 1 2004 12:05am[Delete] [Edit]

The md5() function is very useful for Password encryption. Keep in mind that we can not Decrypt it.
The most simplest method to use md5() function of MySQL with PHP is as follows (PHP Code):
Insert the record into the MySQL Database using a query like:

$query = "INSERT INTO user VALUES ('DummyUser',md5('DummyPassword'))";

And then for matching the password use:

$password = md5($password);
$query = "SELECT * FROM user WHERE username='DummyUser' AND password='DummyPassword'";

In the above code you can use your Variables instead of DummyUser & DummyPassword. The length of the Password field in my DB is 60 char.

Hope this helps!! :)

Posted by Mike Chirico on June 1 2004 12:07am[Delete] [Edit]

./configure --with-openssl

This will enable des_decrypt and des_encrypt:

mysql> select des_decrypt(des_encrypt('sample','mykey1'),'mykey1');

+------------------------------------------------------+
| des_decrypt(des_encrypt('sample','mykey1'),'mykey1') |
+------------------------------------------------------+
| sample                                               |
+------------------------------------------------------+
1 row in set (0.00 sec)


Posted by Alberto on May 16 2004 6:23pm[Delete] [Edit]

Using aes_encrypt and aes_decrypt with PHP

Insert:
mysql_db_query("table","insert into userdata (user,password,other) values ('$user',aes_encrypt('$password','text_key'),'$other')");

Read:
mysql_db_query("table","select *,AES_DECRYPT(password,'text_key') as password from userdata");

Modify:
mysql_db_query("table","Update userdata Set password=aes_encrypt('$password','text_key')");

Posted by Mike Chirico on April 22 2004 8:15am[Delete] [Edit]

Complicated but interesting...


This assumes MySQL has been configured with SSL support [ ./configure --with-openssl ]

CREATE TABLE password (
p_id int(11) NOT NULL auto_increment,
id varchar(20),
valid enum('y','n'),
password BLOB,
timeEnter timestamp,
PRIMARY KEY (p_id)
);


insert into password (id,password)
values ('bob',des_encrypt('secret','somekey'));
insert into password (id,password)
values ('tom',des_encrypt('password','somekey'));
insert into password (id,password)
values ('kate',des_encrypt('desmark','somekey'));
insert into password (id,password)
values ('tim',des_encrypt('tim','somekey'));
insert into password (id,password)
values ('sue',des_encrypt('SUM','somekey'));
insert into password (id,password)
values ('john',des_encrypt('dotgo86','somekey'));
insert into password (id)
values ('randal');




mysql> select id,des_decrypt(password,'somekey') from password;

+--------+---------------------------------+
| id | des_decrypt(password,'somekey') |
+--------+---------------------------------+
| bob | secret |
| tom | password |
| kate | desmark |
| tim | tim |
| sue | SUM |
| john | dotgo86 |
| randal | NULL |
+--------+---------------------------------+
7 rows in set (0.00 sec)



Note the bad passwords in the file ('secret','password', and
password is the same as the id.


The following update statement will fill in the
value for valid:


update password set valid =
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
),

substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
)
)
,'n');


Which gives the following:

mysql> select id,valid from password;
select id,valid from password;
+--------+-------+
| id | valid |
+--------+-------+
| bob | n |
| tom | n |
| kate | y |
| tim | n |
| sue | y |
| john | y |
| randal | n |
+--------+-------+
7 rows in set (0.00 sec)





To understand it, try taking the following select apart:

select
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
),

substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
)
)
,'n')
as valid
from password;

Reference:
http://osdn.dl.sourceforge.net/sourceforge/souptonuts/README_mysql.txt

Regards,

Mike Chirico

Posted by Louis Green on December 8 2004 10:48pm[Delete] [Edit]

Are you having trouble decrypting your information?

If so, be sure that your field sizes are properly set for the type of encryption algorithim you've chosen. Otherwise, the encrypted data will be truncated, and of course, decryption will fail.

For instance, you'll need to have a field size of at least 40 characters to successfully store a string encoded with SHA1.

Posted by Ralf Hauser on January 14 2005 12:24pm[Delete] [Edit]

before storing an AES key to the server, please investigate whether it will be swapped out to disk on that server or not (http://bugs.mysql.com/bug.php?id=7846)

Posted by Patrick Denny on January 20 2005 8:32pm[Delete] [Edit]

Note,
ENCODE and DECODE don't seem to accept a row name as the second argument. so the following WILL FAIL:

select * from `table_name` where `encrypted_row` = ENCODE('passed_value',`salt_row`)

however, the first argument can be a row name, as follows:

select * from `table_name` where `encrypted_row` = ENCODE(`salt_row`,'passed_value')


Posted by Stewart Smith on February 11 2005 12:32am[Delete] [Edit]

The first example of using md5 to store passwords to a web app is not ideal. It is vulnerable to dictonary attacks.

For a start, the users password may pass over the network (if your web app and mysql server are on different machines). If you're not using SSL to mysql, then this will be in plain text. PHP has an md5 function, it may be better to use that (especially if this is a secure web app running over SSL).

If I were to get a dump of your password table, and I had a list of pre-computed md5 sums for possible passwords, i could quite easily do a compare to see if any user has a password in my list.

The way the UNIX password file (now) does it is to add some 'salt' to the password. You add an extra field to your password table, 'salt'. This is a random string (generated each time the user changes their password). This salt is stored in plain text. When you are computing the md5 of the password, you prepend (or append, it doesn't matter - as long as you're consistent) the salt to the password. e.g. md5($salt . $password). When they try to log in, you do the same thing md5($salt . $entered_password). If that equals the value of the password field in the database, you allow access!

this means that if an attacker gets a dump of your password table, they are going to have to get their list of passwords and md5 sum every single one with every single salt value (in your table) to do a dictionary attack.

Instantly you now have better security!

Posted by [name withheld] on April 28 2005 5:15am[Delete] [Edit]

If I understand correctly that the keys to all these algorithms are sent in plain text to the SQL server so that it can perform the crypto then using any of these is insanely dangerous.

Most likely your keys will end up in update logs, packet sniffer logs, replication logs, error logs....who knows.

Do the crypto in your application before inserting and after selecting. THERE SHOULD BE A BIG WARNING ABOUT THIS.

Posted by Tim Bellefy on May 4 2005 10:43am[Delete] [Edit]

I am using this method to verify a client's login with PHP:

*Their email is their username

SELECT clientID, AES_DECRYPT(email,'key_str1') as email, AES_DECRYPT('pass','key_str2') as pass FROM $table WHERE pass=AES_ENCRYPT('$pass_value','key_str2') AND email=AES_ENCRYPT('$user_value','key_str1')

Works great. (so far)

Posted by Patrik Mancuso on August 29 2005 10:00am[Delete] [Edit]

<?
//la classe $db si trova in phpbb o in phpnuke
$sql = "INSERT INTO t SET fr_user='utente', fr_data=AES_ENCRYPT('text','password')";
$ret = $db->sql_query($sql);
?>

info tabella mysql5:

CREATE TABLE t (
FR int(11) NOT NULL auto_increment,
fr_user varchar(10) character set utf8 collate utf8_bin NOT NULL,
fr_data blob NOT NULL,
fr_ftp varchar(15) NOT NULL,
PRIMARY KEY (FR)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

importante: deve essere blob


Posted by Michael Babcock on December 14 2005 5:06pm[Delete] [Edit]

To simplify the password example above slightly:

The following will insert a user record with a hash of the password given by "$PASSWORD"

INSERT INTO user (username, password)
VALUES ('User', sha1($PASSWORD))";

The following will see if a matching username/password combo is found:

SELECT ID from user where username = $USERNAME
and password = sha1($PASSWORD);

If there is no result-set, "invalid username or password" can be returned.

Posted by Rolf Martin-Hoster on March 10 2006 7:51pm[Delete] [Edit]

AES_ENCRYPT: if strlen(str) % 16 == 0 then AES_ENCRYPT will add an axtra block of chr(16). This is particularly useful to know when trying to use PHP's mcrypt.

Posted by Mark Hedges on April 7 2006 12:33am[Delete] [Edit]

That's totally correct about the use of SQL encryption functions. If you don't use a localhost or SSL connection to your database server, the plaintext and key string are sent in the clear and encryption does not protect any data from attackers along that path.

One way to protect more sensitive information, for instance, credit card numbers, is to use GnuPG to encrypt the data with the public part of a key whose private counterpart only lives on a very secure machine that runs the batch transaction, and requires a passphrase to load it into memory in your charging program. The encrypted block is stored in a text field, the plaintext never crosses the network, and an attack on the web server or database cannot compromise the data.

The slightly stronger exception might be the use of DES_ENCRYPT, which if you have configured your keyring on your server, does not need to transmit the locking key with the plaintext data. (Though it still transmits the plaintext in the clear.)

Also, regarding the mentioned exploit of sha1, there are stronger versions like sha256, sha384, sha512 etc. but mysql does not implement them; they would have to be implemented in code.

Posted by picolobo on September 21 2006 8:50pm[Delete] [Edit]


I had problems with ENCRYPT MySQL function when i tried to compare with the encrypted password (with ENCRYPT).

Another solution i read from "UNIX Advanced programming" where i found about the UNIX system call "crypt()":

Password="tB" //The two first letters of encrypted password

SELECT password from users where Password=ENCRYPT('".$_POST['password']."',Password)


mysql> select password from users where password=encrypt('pasword','tB');

+---------------+
| password      |
+---------------+
| tBY8OVuabSiTU |
+---------------+
1 row in set (0.01 sec)

Bye.

Answer from: http://es2.php.net/manual/es/function.crypt.php

> topace at lightbox dot org
> 22-Sep-2005 06:34
>
> To authenticate against a stored crypt in MySQL, simply use:
>
> SELECT ................
> AND Password=ENCRYPT('".$_POST['password']."',Password)

Add your own comment.