Blog

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

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.

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.

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.

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 BartelsSeptember 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 HomerDecember 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 McDavidJanuary 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.

dougFebruary 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 SandelFebruary 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

MarkMarch 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.