Blog

Back 9 Comments

How to Use MySQL's AES_ENCRYPT and AES_DECRYPT to Store Information in a Database


 

UPDATE (March 2013) - Here's a better solution to storing passwords in a database using PHP/MySQL.

 
This mini-tutorial assumes you already know how to connect to your database and work with php/mysql.  

The benefit of using AES_ENCRYPT and AES_DECRYPT is that you can both encrypt the information, then decrypt whenever necessary.  This is helpful if you're encrypting account information that you need to display. 

View the code here.

1:  The Key
For this to work, you must define a "key" to use when encrypting and decrypting information from the database.  It would be best to store this key somewhere on your server outside of the main directory in which you're working.  This key can be whatever you want it to be, but you must also reference the same key during encrypting and decryption.

$key = 'ASKSDFNSDFKEISDJAHDLDSDF1235UUUiidfsdf';

2:  Encrypt

mysql_query("INSERT INTO users (user_first, user_last, user_password) VALUES ('".$_POST['first']."','".$_POST['last']."',AES_ENCRYPT($_POST['password'],$key))");

3:  Decrypt
Now, to display the decrypted information, you'll need a query similar to the one below:

$field = mysql_fetch_row(mysql_query("SELECT AES_DECRYPT(user_info,'$key') FROM users WHERE user_id = 4"));
echo $field[0];

So, using AES_ENCRYPT and AES_DECRYPT can be very useful when you need to store encrypted information in a database as well as display the original, unencrypted information.  Remember, you must use a 'key' in order to "unlock" and display the encrypted information.

 

Robin Baker
December 2, 2010 3:22pm
please explain:
SELECT AES_Encrypt('SECRET DATA','KEY')
UNION
SELECT AES_Decrypt('60316eb379ad9beae9aa057af49bdff5','KEY')
UNION
SELECT AES_Decrypt(AES_Encrypt('SECRET DATA','KEY'),'KEY')
Gavin Towey
August 28, 2012 7:58pm
If you're storing customer passwords, you should hash them with sha1 or md5, not encrypt them. There's really no valid use case for decrypting customer passwords.
Josh Bartels
September 13, 2012 8:59am
Gavin,

Instead of decrypting you could also compare the encrypted string within the database versus the encrypted results of a mysql which would never utilize the decrypt function.
James Homer
December 30, 2012 2:38pm
Don't listen to Gavin. Hashing passwords has caused enough industry damage this year alone - photobucket for one. Josh's advise is better. Run the strongest and fastest encryption available and simply compare encrypted strings.
David McDavid
January 17, 2013 12:27pm
I'm with Gavin, I see no reason to decrypt a user's password. If they forget and need a reminder email, just send them a method for completely resetting their password rather than emailing them their current.

And photobucket may have had some industry damage, but I'd rather do like Google and be unable to recover user passwords. Just send a reset link and call it a day.
doug
February 12, 2013 4:31pm
I did InfoSec for 9 years. I guarantee you that if someone can decrypt customer passwords then someone absolutely will. unless you're the only developer in the joint, it'll happen.

But thanks for the lesson!
Paul Sandel
February 13, 2013 5:32pm
Thanks for the article. While I prefer a one way hash for passwords, this is useful for those times that you have to have a two-way
Mark
March 4, 2013 8:42am
never use two way encryption for storing passwords, if the hacker gets your key as well then your screwed. Always use a one one hashing function such as bcrypt
Dirk W.
March 19, 2013 10:14am
You're taking two POST variables and do 1:1 query with it? Please read https://www.owasp.org/index.php/SQL_Injection before you advise others to put vulnerable applications online.


Your comment has been posted....