Aug
1
2011

Data Encryption Using AES_ENCRYPT

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 “admin@admin.com” 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.

Related Posts

17 Comments + Add Comment

  • Do i need to change table fields to BLOB Type ?

    • Correct- the field type needs to be “blob” or “varbinary” to accommodate the characters generated by AES

  • Why do you not recommend your table structure? Is the table structure and the matching of data within it, e.g. the combination of names with credit numbers, insecure?

    • Ah, my non-recommendation was referring to storing user credit card information; not the actual table structure. Since any site ‘can’ be hacked, it’s usually ‘best’ to keep the money stuff elsewhere (stripe, paypal, authorize.net, etc…).

  • Works great, many thanks. Now I use this code in my programs

  • this code gives error with me and don’t know why ??

    • What is the code you’re using?

  • Very nice!
    Thanks!

  • awesome…outstanding explanation…thanks…:)

  • You mention not storing the key in plain text anywhere, but what about mysql logs – general query log, slow query log, binary logs? Won’t the key be visible from these logs?

    • You are correct, logs are usually purged regularly (but as with many things, it varies based on host), so this is not 100%, but it’s a good start.

      There are alternatives such as emulating same-type functionality using PHP (See: http://www.prioninteractive.com/2011/02/exploring-data-encryption-with-mysql/), however, I haven’t fully explored the possibilities associated with PHP-based encryption to replicate the AES_ functionality.

      By the same token, assuming AES encryption usage, SSL is certainly a good way to go as well to fill the gap between browser and server.

  • There is some misinformation in this article.

    “[…] fields containing AES data must be varbinary or blob, and the length must be at least 128 characters long.”

    This is not true. The resulting size of an encrypted string is 16*(floor(original_string_len/16)+1). For example, encrypting a 50-character string will result in 16*(floor(50/16)+1), or 64, characters after encryption.

    “[…] full of special characters (also known as arbitrary byte values) is the reason for the varbinary field type […]”

    This comment is misleading. The reason for the type isn’t that the post-encryption string is “full of special characters.” 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, by incidence, but the special characters are in no way “the reason” for the type. I’m sure you know this and the distinction seems pedantic, but IMO this statement is too counter-factual to be considered figurative.

    I think the How’s in this article are great, but not the Why’s.

    • You are absolutely correct, the resulting size of the encrypted string is calculated, however, I’ve yet (aside from temporary credit card storage) to use AES for anything where the string length was guaranteed to be an exact length, and I’ll update my article to reflect your point on the binary data!

  • Thank you for the useful information….

  • I’ve been reading out many of your articles and i can claim pretty good stuff. I will make sure to bookmark your blog.

  • This article was very well written, I have found this blog on Yahoo. I will bookmark this site and come back often, thank you for all your time and effort, cheers.

  • I learned a lot from this post, much appreciated! :)

Leave a comment