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!