Two Way Encryption Using MySQL 5 & The Advanced Encryption Standard (AES)

Follow Me on Pinterest

MySQL EncryptionRecently I was asked to implement two way encryption security for data that we gathered on a campaign, which we stored in a MySQL table running on one of our servers.

Lately this has been an extremely important issue as recently the Sony Playstation Network has been the victim of a successful hacking attempt which has caused embarrassment and major headaches for the company.

I initially approached the project with PHP in mind as a way of encrypting the data but changed my mind after it involved configuring and updating the PHP installation on the server. Instead I started to look into the possibility of MySQL handling the encryption for me and the following functions came to the rescue!

AES (or Advanced Encryption Standard) is currently one of the most secure and popular methods for securing data and is even used by the US government to secure its top secret documents.

This blog post was written to help you implement this using MySQL. This is currently written using MYSQL version 5.

We start off by making sure that the fields that we wish to store our data have a field collation setup to accept binary characters. If you are using PHPMyAdmin you just do this by selecting binary from the collation menu (see below):

Setting the Collation using PHPMyAdmin

If you are using the command line then you just need to run the following query making sure that you add the correct amount of characters (but make sure you allow for any padding that gets added when the field is decrypted).


Now we have setup our field to accept encrypted data we can now create a key that will be used to encrypt and decrypt the data. You have to be careful that you responsibly store this key. A good way to ensure that a user trying to view pages that are running on your server is to make it as difficult as possible for them to be able to access the file in which the key resides. I would suggest saving your key outside of the root of the website. In order to make your encryption file available outside of the document root you may need to set up the location as an allowed path in your apache httpd.include file.

php_admin_value open_basedir "/path/to/my/website/root/folder:/path/to/the/location/of/the/encryption/file:/tmp"

To create our key file we setup a constant so that it can be made available through all our applications.

I would suggest generating a large password and then md5 hashing the password. You can then use this as your encryption key.

//key to encrypt data

Once we have our encryption key and we have also include the file into our scripts we can now generate queries for MySQL that will allow us to encrypt the data we are passing in.

$sql = "INSERT INTO tablename (status, name, email) VALUES (700, AES_ENCRYPT('Neil Keith Young', ENCRYPTION_KEY), AES_ENCRYPT('', ENCRYPTION_KEY))";

If you are using PHPMyAdmin to view your tables you will have something similar to below:

MySQL Encrypted Row in PHPMyAdmin

MySQL has now taken our data, encrypted it and is now storing our data in binary format.

We can now retrieve this data by decrypting the fields when we return our row:

$sql = "SELECT AES_DECRYPT(name,'1234567890') AS name, AES_DECRYPT(email,'1234567890') AS email FROM tablename";

This has now decrypted the data and you should get the following:

Data Decrypted From MySQL

Thats it!

This will ensure that if you are storing any data that this can only be accessed using your encryption key. If you also make sure that your application has the correct security measures in place and your server hardware security is locked down then this deters potential hackers from being able to access your data and you avoid such a situation that can not only be extremely embarrassing but will cost you money in the long run.

Filed Under: Blog, Security
Tags: , , , , , .
Bookmark: permalink.

Let me know what you think:

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>