Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

Reversible AES Encryption in MySQL

Keeping sensitive information for any organization is a must in this day and age, and encrypting this data is one of your many lines of defense. Below are simple one liners on how to use AES encryption and decryption using MySQL as your database. When you call the AES_ENCRYPT function, MySQL will automatically return the encrypted value in a binary form, I’ll show you how to convert this into a hexadecimal value which you will need to do if you ever backup your database to SQL files. I was so happy to find that MySQL had this function built in, I can’t tell you how many types I ran through the PHP api looking for something that could meet my needs, I never even bothered to do a search for it on MySQL.

Note: Do not encrypt passwords!
Yeah, you read that correctly. Not everything needs to be decrypted. Imagine if you had a rouge system administrator, dba, or even worse, a hacker. Passwords need to be and should be hashed. Think about it for a minute, the only reason you would want to encrypt a password is if you want to be able to decrypt it at a later time. There’s no reason to do that, if a user forgets his or her password, it should be reset, and then they should be forced to change it on their next login.

In this example, I am going to encrypt MyUberSecretPassW0rd using a really short "salt" to aid in my encyption: 32fn32tn6asfv, if you don’t know what a salt is, read my MD5 and SHA1 Hashing article.

1
2
3
4
5
6
7
mysql> SELECT AES_ENCRYPT( 'MyUberSecretPassW0rd', '32fn32tn6asfv' );
+--------------------------------------------------------+
| AES_ENCRYPT( 'MyUberSecretPassW0rd', '32fn32tn6asfv' ) |
+--------------------------------------------------------+
| ¡áøì               ¥?                       |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Of course, the above string looks a little ugly so we can convert the binary values to hex using a HEX function.

1
2
3
4
5
6
7
mysql> SELECT HEX(AES_ENCRYPT( 'MyUberSecretPassW0rd', '32fn32tn6asfv' ));
+------------------------------------------------------------------+
| HEX(AES_ENCRYPT( 'MyUberSecretPassW0rd', '32fn32tn6asfv' ))      |
+------------------------------------------------------------------+
| 869AA18EE1F817EC84F0EDE9FE64FC15AD7B1FC16BFA45C26DB98DA5063C223F |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

Simple enough, now we have all of our data encrypted, but now we need to decrypt, and yes, it’s exactly as it easy as you thought it would be…

1
mysql> SELECT AES_DECRYPT(UNHEX('869AA18EE1F817EC84F0EDE9FE64FC15AD7B1FC16BFA45C26DB98DA5063C223F'), '32fn32tn6asfv');

2 comments to Reversible AES Encryption in MySQL

Leave a Reply