Using AES Encryption in MySQL and PHP

An easy-to-follow-to-using MySQL ' s new Aes_encrypt and Aes_decrypt functions to ENCRYPT and DECRYPT data using a Sal T with PHP.

According to MySQL, AES encryption (Advanced Encryption Standard) are the best method available for providing reversible en Cryption and decryption in SQL.

Formerly known as Rijndael, the Aes_encrypt and Aes_decrypt functions is now built-in to MySQL so can take user data, Encrypt it with a salt, the store it in your database, then extract it again later and decrypt it.

Define your salt

You'll need to an apply a salt to the data so you encrypt. This is a special code, the encryption algorithm uses which works a bit like a key.

You'll need to provide the exact same key back to decrypt the data, and if a attacker should gain access to your database , they won ' t is able to decipher it without knowing the salt.

If you define your salt in PHP like this, you'll be able to pull the constant into your SQL statements more easily.

if (!define (' SALT '))
Define (' SALT ', ' 897sdn9j98u98jk ');

Encrypting data with Aes_encrypt

To insert data into your MySQL database and encrypt the sensitive information, you'll need to issue a command like this, a Long with your salt.

INSERT into your_table (username,email,shoesize)
VALUES (' $username ',
Aes_encrypt (' $email ', ' ". SALT. "'),
Aes_encrypt (' $shoesize ', ' ". SALT. "'));

This would insert the username in plain text, as it's non-sensitive, but encrypt the user's email and shoesize, to prevent them from being viewed without access to the salt.

Decrypting data with Aes_decrypt

At some point, you ' re going to need to access some of the data you stored in its encrypted form, and you can do this very Easily using the Aes_decrypt function of MySQL and the same salt you used when you encrypted the data and inserted it.

Select Username, aes_decrypt (' email ', ' ". SALT. "') As email,
Aes_decrypt (' shoesize ', ' ". SALT. "')
As Shoesize from your_table WHERE username = ' Fred ';

If you SELECT the encrypted data without running it through aes_decrypt or with the wrong or no salt, you'll get an ugly, Unreadable string of odd characters. This means if a attacker manages to access your database, but does not has access to your server to view the salt, they Won ' t is able to read any of the data you ' ve stored. At least, not without going to great lengths to try and decrypt the data.

Updating Encrypted data with Aes_encrypt

Updating encrypted records is very similar to insertion. Basically, just apply the same salt and re-issue the Aes_encrypt command to re-encrypt the data again and lock it away Safely.

UPDATE your_table SET email = aes_encrypt (' $email ', ' ". SALT. "'), Shoesize = Aes_encrypt (' $shoesize ', '". SALT. "') WHERE username= ' Fred ';

Searching encrypted data using both Aes_encrypt and Aes_decrypt

Things get a little bit more complicated if you need to search for data this ' s encrypted and then display it in its Unen crypted form.

Say wanted to search for a user using their e-mail address, but you ' d encrypted the database. First, you're need to encrypt the e-mail address you want to search for with aes_encrypt and your salt, and then you ' d need To use Aes_decrypt to ensure that MySQL decrypted it, returning it in a readable format.

Can achieve this, using the code a bit like this:

SELECT User_username,
Aes_decrypt (email, ' ".) SALT. "') As email,
Aes_decrypt (Shoesize, ' ". SALT. "') As Shoesize
From Your_table WHERE
(email = aes_encrypt (' $q ', ' ".) SALT. "'));

That's pretty much all there are to it. You can find out more about the AES encryption functions on the MySQL website.

