MySQL AES_Encrypt and AES_Decrypt. Awesome!

Posted by santosj on July 21, 2006

I know for a fact that this works for MySQL 5 and greater. I believe it was added in MySQL 4.1, but I would have to check on that to be sure. If you are using MySQL 5, then I would definitely use this.

Don’t Use MySQL Password() or MD5()

You shouldn’t use PASSWORD or MD5 for passwords as they have been known to be crackable. MySQL added AES_ENCRYPT AND AES_DECRYPT, along with DES_ENCRYPT and DES_DECRYPT. The syntax is pretty easy, you provide the string first and the key next.

Validation

if(ctype_alnum($_POST['username']) and ctype_alnum($_POST['password'])) {

    $exists = mysql_num_rows(mysql_query($sql));

}

Encryption

[mysql]
SELECT NULL
FROM users
WHERE
username=’{username}’
AND
users.password=AES_ENCRYPT(‘{password}’, ‘ThisIsAVeryLongStringB1t0h3s’);
[/mysql]

I found that you can use NULL as a select for queries that you don’t need any information from. It is useful for when you only want the amount of rows and don’t need any fields later. Most will use ‘*’, but that is still returning all of the fields and wasn’t resources. I don’t need any fields, so no thank you.

You then replace {username} and {password} with their respective variables after you have done testing to make sure they are ‘safe’ to be used in the query. Security first, and it would be pointless to use extra MySQL security precautions only to not have validation to check that the strings are what you require in the first place.

Decryption

[mysql]
SELECT AES_DECRYPT(‘{password}’, ‘ThisIsAVeryLongStringB1t0h3s’)
AS recover
FROM users
WHERE username=’{username}’;
[/mysql]

You have to use the same key string as you used to encrypt the password, so it is completely possible to encrypt and decrypt information using different keys, including ones that are session based.

This is heavily based on MySQL SQL usage, so it won’t work in SQLite or another database type. It would probably make more sense to use PHP encryption. If you are only going to use MySQL for your project, then I would look this up. It is very easy to use and is better encryption than PASSWORD.

Possibly Related Posts:


General

4 Comments.

  1. Robert Colton May 28, 2007 at 4:43 pm

    Hi

    I am a MySQL noob but getting there. I was looking up the Encryption functions so we can store CC numbers securely until they are used and deleted. Was looking at the AEX_Encrypt/Decrypt and looks just what we need. But one thing. If I have the secure key in the script (ColdFusion) what generates the encrypted field, is it not a simple matter to find that key and decrypt the values if I am a hacker?

  2. Jacob Santos May 29, 2007 at 5:39 pm

    I’m not aware about how coldfusion works.

    It might if the key is displayed, but if it can do mysql, then there should be some way to hide the coldfusion. If not then I think using another method like keeping the key in the sql statement might be better and just copying it for each one.

  3. Rob May 29, 2007 at 6:36 pm

    Thanks for the reply.

    Cold Fusion simply uses plain text SQL statements

    e.g.

    select *
    from table
    where name = ‘Fred’

    Since the script is unencrypted the key used in AES_Encrypt would be visible if someone was determined enough to poke around and find it. However, is it possible to store the SQL that saves the CC numbers and passwords as stored procedures amd hide them within the MySQL

    How would you hide the key in PHP?

  4. Jacob Santos May 29, 2007 at 6:58 pm 
    < ?php
$key = 'key';
?>

    It is possible to build a procedure if you have the right mysql version that allows it and it is enabled.