Use the MySQL encryption function to protect the sharing of sensitive data on the Web website

If you are running an Web application that USES MySQL, there is a good chance that it will save passwords or other sensitive information in the application. Protecting this data from hackers or snoops is an important concern, because you can't allow unauthorized people to use or damage the application while still maintaining your competitive edge. Fortunately, MySQL comes with a number of encryption functions designed to provide this type of security. This article gives an overview of some of these functions and explains how to use them and the different levels of security they can provide.

The bidirectional encryption

Let's start with the simplest encryption: two-way encryption. Here, a piece of data is encrypted with a key that can only be decrypted by someone who knows the key. MySQL has two functions to support this type of encryption, called ENCODE() and DECODE(). Here is a simple example:

mysql > INSERT INTO users (username, password) VALUES ('joe', ENCODE('guessme', 'abracadabra'));

Query OK, 1 row affected (0.14 sec)

Where, the password of Joe is guessme, which is encrypted by the key abracadabra. Note that the result 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 abracadabra key is critical to restoring the original string. This key must be passed to the DECODE() function to obtain the original, unencrypted password. Here's how to use it:

mysql > SELECT DECODE(password, 'abracadabra') FROM users WHERE username='joe';

+---------------------------------+

| | DECODE (password, 'abracadabra')

+---------------------------------+

| guessme |

+---------------------------------+

1 row in set (0.00 sec)

It should be easy to see how it works in the Web application -- to verify that a user is logged in, DECODE() unlocks the password stored in the database using a website-specific key and compares it to what the user enters. Assuming you're using PHP as your own scripting language, you can query it like this:

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

Tip: while the functions ENCODE() and DECODE() satisfy most requirements, there are times when you want to use stronger encryption. In this case, you can use the AES_ENCRYPT() and AES_DECRYPT() functions, which work in the same way but with higher encryption strength.

One-way encryption

One-way encryption is different from two-way encryption. Once the data is encrypted, there is no way to reverse the process. So password verification involves re-encrypting the user's input and comparing it to the saved ciphertext to see if it matches. One simple one-way encryption method is the MD5 check code. MySQL's MD5() function creates a "fingerprint" for your data and saves it for validation tests. Here's a simple example of how to use it:

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 |

+----------+----------------------------------+

81 a58e89df1f34c5487568e17327a219 | joe | |

+----------+----------------------------------+

1 row in set (0.02 sec)

You can now test whether what the user enters matches the saved password by taking the MD5 check code for the password the user entered and comparing it with the saved 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 1 using the ENCRYPT() function, which USES the underlying crypt() system call to complete the encryption. This function takes two arguments: one for the string to be encrypted and one for the double (or multiple) character "salt." It then encrypts the string with salt; This salt can then be used to encrypt the user's input again and compare it to the previously encrypted string. Here's an example of how to use it:

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)

As a result,

mysql > SELECT COUNT(*) FROM users WHERE username='joe' AND password=ENCRYPT('guessme', 'ab');

+----------+

| | COUNT (*)

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

Tip: ENCRYPT() can only be used on the *NIX system, as it requires the underlying crypt() library.