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

Here's the scenario. You are building a custom member login area to a website. You need to store the user's name, email address and a password. The name and email can be stored in 'plain text', but for added security, you want to store the password in an encrypted format (in case someone steals the database somehow, or just for your users' peace of mind).

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 password, then decrypt the password whenever necessary. This is helpful if you ever want to display the password to the user in an email, or if you're encrypting other 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 the 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 the password

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

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

$password = mysql_fetch_row(mysql_query("SELECT AES_DECRYPT(user_password,'$key') FROM users WHERE user_id = 4"));
echo $password[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')

Your comment has been posted....