How to encrypt an existing MySql database

One thing I want you to keep in mind as you're reading this, is that this is a very basic guide to quickly get some encryption on your data. It is by no means a failsafe enterprise-level chinese-hacker-proof way of encrypting your data. Furthermore, aside from just encrypting some of your data I recommend at least the following:

• Encrypt the entire disk volume that your database resides on
• Encrypt the entire database instance
• Create firewalls at every levels of your infrastructure
• Use the highest level of encryption that fits the limits of your performance window
• Get a chastity belt and a few Assa Abloy padlocks to protect your server

Now with that said, here's a super simple way of encrypting your stuff.

Let's say you have a table called user that looks like this:

As you can see, nothing in this table is encrypted, because you can obviously read it. Don't worry this is all fake data ;)

Anyway, you probably want to encrypt that email column so if someone gets into your db they can't just steal all your email addresses. Here's the quick and easy way:

UPDATE user SET EMAIL = AES_ENCRYPT(EMAIL, 'password');

The 'password' should probably be a nice long string that you're going to guard with your life going forward. What this does is it encrypts the EMAIL field with the key 'password' and updates it in the database. So now what your result set looks like is this:

Hard to guess what those emails are now.

By now you're probably a bit impressed, but asking yourself, okay great, but how the heck do I use data that looks like this in my own application? Well, I'm glad you asked. Here's how:

SELECT FIRST,LAST,CAST(AES_DECRYPT(EMAIL,'password') AS CHAR) AS EMAIL, WHEN_CREATED FROM user

Hint: If you don't user the CAST( ... AS CHAR), you'll get a blob returned in MySQL which is probably not what you want. MySql needs to know how to interpret the encrypted data once it has decrypted it. Also, If you don't say "AS EMAIL" after the casting, you'll get the whole formula as your column name and it will be pretty nasty, so this is cleaner.

Some more very important stuff to consider:

1. You should consider changing your data type to binary since this is no longer being stored as a varchar
2. You should definitely consider increasing the size of the encrypted (now binary) column because it now takes up more room and you don't want crazy errors you can't debug later.
3. Storing the key in a safe place goes without saying, but try to come up with a pretty cryptic string like 'HDIEdygygde783juinifiaaoeygyqyegyYYHGEYD' and just use that within your application to decrypt. The downside of doing this is that if you lose this key, then you are essentially screwed.

Happy encrypting