Protect sensitive Web site data by encrypting information with MySQL

Securing Web site data stored in MySQL from hackers or snoopers is a critical concern, both to avoid unauthorized use or damage to your application and to retain your competitive advantage. MySQL comes with a variety of encryption functions designed to offer you just this type of security.

If you're running a Web application using MySQL, chances are good that it has passwords or other sensitive data stored somewhere within it. Securing this data from hackers or snoopers is a critical concern, both to avoid unauthorized use or damage to your application and to retain your competitive advantage. Fortunately, MySQL comes with a variety of encryption functions designed to offer you just this type of security. This document outlines some of these functions, with illustrations of how they can be used and the differing levels of security they offer.

This article is also available as a TechRepublic download.

Two-Way encryption

Let's begin with the simplest form of encryption: two way encryption. Here, a piece of data is encrypted using a secret "key" and can only be decrypted by persons who know that key. MySQL comes with two functions to support this form of encryption, named ENCODE() and DECODE(). Here's a simple example illustrating them in action:

mysql> INSERT INTO users (username, password) VALUES ('joe', ENCODE('guessme', 'abracadabra'));
Query OK, 1 row affected (0.14 sec)

Here, Joe's password is guessme and it's been encrypted using the secret key abracadabra. Note that the result of the encryption is a binary string, as shown below:

mysql> SELECT * FROM users WHERE username='joe';
+—————+—————+
| username | password |
+—————+—————+
| joe      | ¡?i??!?  |
+—————+—————+
1 row in set (0.02 sec)

The secret key abracadabra is critical to recovering the original string. This key must be passed to the DECODE() function in order to retrieve the original, unencrypted password. Here's how it works:

mysql> SELECT DECODE(password, 'abracadabra') FROM users WHERE username='joe';
+————————————————-+
| DECODE(password, 'abracadabra') |
+————————————————-+
| guessme                         |
+————————————————-+
1 row in set (0.00 sec)

It should be fairly easy to see how this works in the context of a Web application - when verifying a user login, DECODE() the password stored in the database using the site-specific key and match it against what the user's input. Assuming you're using PHP as your scripting language, your query might look like this:

<?php
$query = "SELECT COUNT(*) FROM users WHERE username='$inputUser' AND DECODE(password, 'abracadabra') = '$inputPass'";
?>

Tip: While the ENCODE() and DECODE() functions are fine for most purposes, there may be situations when you'd prefer stronger encryption. In these situations, use the AES_ENCRYPT() and AES_DECRYPT() functions, which work in the same manner but offer much stronger encryption.

One-Way encryption

One-way encryption differs from two-way encryption in that once the data is encrypted, there's no way of reversing the process. Password verification therefore consists of re-encrypting the user's input and testing it against the stored ciphertext to see if it matches.

A simple form on one-way encryption lies in the use of MD5 checksums. MySQL'sMD5() function creates a "fingerprint" of your data, which can be stored and used in verification tests. Here's a simple example of how it works:

mysql> INSERT INTO users (username, password) VALUES ('joe', MD5('guessme'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM users WHERE username='joe';
+—————+—————————————————+
| username | password                         |
+—————+—————————————————+
| joe      | 81a58e89df1f34c5487568e17327a219 |
+—————+—————————————————+
1 row in set (0.02 sec)

You can now test whether a user's input matches the stored password by obtaining an MD5 checksum of the input password and matching it against the stored password, as follows:

mysql> SELECT COUNT(*) FROM users WHERE username='joe' AND password=MD5('guessme');
+—————+
| COUNT(*) |
+—————+
|        1 |
+—————+
1 row in set (0.00 sec)

Alternatively, consider the ENCRYPT() function, which uses the system's underlying crypt() system call to perform encryption. This function takes two parameters: the string to be encrypted and a two (or more) character "salt". It then encrypts the string using the salt; this salt can then be used to re-encrypt user input at a later date and verify it against the previously-encrypted string. Here's an example illustrating how it works:

mysql> INSERT INTO users (username, password) VALUES ('joe', ENCRYPT('guessme', 'ab'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM users WHERE username='joe';
+—————+———————-+
| username | password      |
+—————+———————-+
| joe      | ab/G8gtZdMwak |
+—————+———————-+
1 row in set (0.00 sec)

Results

mysql> SELECT COUNT(*) FROM users WHERE username='joe' AND password=ENCRYPT('guessme', 'ab');
+—————+
| COUNT(*) |
+—————+
|        1 |
+—————+
1 row in set (0.00 sec)

Tip:ENCRYPT() only works on *NIX systems, as it depends on the underlying crypt() library.

And that's about it. Hopefully, the examples above demonstrated how you can perform both one-way and two-way encryption of your data in MySQL, and gave you some ideas about how you can secure your user databases and, indeed, other sensitive database information. Happy coding!