Data Encryption Using AES_ENCRYPT

An article by admin    19 Comments

Having databases contain information on subscribers, customers, clients, and the like is similar to playing russian roulette.  You’re never certain as to when the bullet comes!  Same principles apply to having your customer’s sensitive information yanked from your precious databases!  We all watch the news to see which company will have their customer details compromised next.

Most recently, Sony has now had approximately 100 million accounts compromised, costing them upwards of $171 million dollars.  And even better, the data was acquired by exploiting a simple MySQL injection vulnerability that could’ve easily been prevented (see this post on data sanitizing).

But the purpose of this post is to discuss alternative methods for data security IF you’ve neglected to follow simple steps to sanitize user input and safeguard connection details, so here we go…

AES_ENCRYPT, AES_DECRYPT

The AES_ENCRYPT algorithm is a beautiful thing, and according to the MySQL developer website:

“AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.” [link]

Using AES_ENCRYPT and AES_DECRYPT can be a daunting task if not approached properly, and there are a few things you should be aware of prior to starting use of this function:

The Bad:

• Using Php MyAdmin to edit data rows containing encrypted data (even when not altering the encrypted field) often results in a failure to decrypt at a later time (aka, the encrypted data is no longer properly encrypted, and is thusly not able to be decrypted).
• If your encryption key is misplaced, lost, deleted, or modified- your data will not be decrypted, and will be rendered useless.

The Awesome:

• Assuming that your encryption key has been properly secured, your data is safe.  Your database may still get hacked, but at least nothing useful will ever come out of it, avoiding those sony-style issues down the road.
• It turns a simple email address such as “[email protected]” into: i?Ó?Õ?=$?Bês3|yûkh38s (that is straight copy-paste from a database by the way).  Good luck deciphering that without the key!

Setting Up your AES MySQL Fields

To get started with this heavy duty encryption, you’ll (obviously) need to have you MySQL database already set up and ready to go.  Create your database table in whatever manner you’d generally use (example below of simple table).  This table is for an imaginary credit card storage table (not recommended by the way), and would contain a users first name, last name, encrypted credit card number, and credit card expiration date.

Note that line 9 is highlighted for a reason: fields containing AES data must be varbinary or blob, and the length must be at least 128 characters long.  [Edited and quoted from acheong87]: The reason is simply that the encrypted string is binary data (not meant to be human-readable). Displaying such binary data, will of course, yield special characters

Also note that this table contains an auto-incrementing, primary key field (“id”) so that a unique numeric identifier can be automatically assigned to each table entry (that’s just good form people), however, it’s best to name your auto-incrementing, primary key field something more unique than “id”.

CREATE TABLE `Encryption` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`f_name` varchar(300) DEFAULT NULL,

`l_name` varchar(300) DEFAULT NULL,

`cc_n` varbinary(128) DEFAULT NULL,

`cc_exp` date NOT NULL DEFAULT '0000-00-00',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Inserting AES_ENCRYPT-ed Data Into Your Database

Using AES_ENCRYPT to insert encrypted data into your database is fairly straightforward, with two main parts: the value to be encrypted, and the key with which the data will be encrypted and decrypted.

AES_ENCRYPT(‘value to be encrypted’, ‘key’)

The value to be encrypted should still be passed to your query as an escaped ‘safe’ value using (at minimum) the mysql_real_escape_string function, while the key should be a secured (and preferably a random alphanumeric string).

For the sake of brevity within this article, we’ll pass the key value as a php constant, i.e..

$salt = "th1s1sasaltkey";

At which point our insert statement becomes:

AES_ENCRYPT('value to be encrypted', '$salt');

So, assuming your form directly correlates with the ‘create table’ statement from above, and has matching fields- your mysql_query insert statement would be the following:

$cc_n = mysql_real_escape_string($_POST['cc_n']); //Passing the credit card number as a pre-escaped value

mysql_query( "INSERT INTO `Encryption`

(`f_name`, `l_name`, `cc_n`, `cc_exp`)

VALUES
(
'" . mysql_real_escape_string($_POST['f_name']) . "',
'" . mysql_real_escape_string($_POST['l_name']) . "',
AES_ENCRYPT('$cc_n', '$salt'), //Note that the encryption statement is not encapsulated
'" . mysql_real_escape_string($_POST['cc_exp']) . "'
)";

I recommend simplifying the query (while shortening it) by using this data sanitization function, in which case the values for your insertion statement would become “(‘$data[f_name]’, $data[l_name]’, AES_ENCRYPT(‘$data[cc_n]’, ‘$salt’), ‘$data[cc_exp]’)”, however for the sake of clarity I have omitted the pre-cleaned statement.

Retrieving and Decrypting The Data

To retrieve and decrypt our encrypted data, it’s just the reverse using the AES_DECRYPT function, which also requires a value to be decrypted, and the same key that was used to encrypt the data.  If the same key that was used for encryption is not used for decryption, an empty value (or gibberish in some cases) will be returned.

AES_DECRYPT('value to be decrypted', '$salt');

Since it can be assumed that in this case, you’ll likely need more than just the credit card number to be returned in your query, we pass the query using “*” while specifying the decryption algorithm (including value, key, and what we would like to be able to call the value as for printed/echo’d output).  For readability in the example below, I’ve added a line break between each entry within the ‘while’ loop, as well as a line under each set (in case you have multiple rows of data).

$d_sql = mysql_query("SELECT *, AES_DECRYPT(cc_n, '$salt') AS cc_number FROM Encryption");

while ($row = mysql_fetch_array($d_sql))
{

echo $row['f_name'] . "<br />";
echo $row['l_name']. "<br />";
echo $row['cc_number']. "<br />";
echo $row['cc_exp']. "<hr />";

}

Conclusion

Provided that you properly secure your encrypt/decrypt key generally and during database transactions, your information should be more than properly secured for most applications.  Some tips:

1. Make sure to NEVER put your encryption key into a plain text or html file.  Ever.  Don’t do it.  Just don’t.
2. If using a common included database or other file across an entire site, it’s okay to have this reference the $salt and pass your constant value, however DO NOT locate the actual $salt value in this included file.  Carefully place your encryption key into a file named something like “people_to_know.php” or a dash of alphanumeric randomness within a folder that cannot be accessed directly.  Then within your commonly included file, “include(‘../../../someunaccessablefolder/people_to_know.php’);”

As aforementioned, using encryption does not prevent your database or website from being hacked! It merely decreases the risk of actual information being compromised and disclosed in the event of your database being hacked when properly secured.