MySQL encryption function protects web site sensitive data

If you're running a Web application that uses MySQL, it's a great opportunity to keep passwords or other sensitive information in your application. Protecting this data from hackers or prying eyes is an important concern because you cannot allow unauthorized people to use or destroy applications, but also to ensure your competitive advantage. Luckily, MySQL has many cryptographic functions designed to provide this type of security. This article outlines some of these functions and explains how to use them, as well as the different levels of security they can provide.

Bidirectional encryption

Let's start with the simplest encryption: bidirectional encryption. Here, a piece of data is encrypted by 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 () respectively. The following is a simple example:

Mysql> INSERT into users (username, password) VALUES (' Joe ', ENCODE (' guessme ', ' Abracadabra ')); Query OK, 1 row affected (0.14 sec)

Where Joe's password is guessme, it is encrypted through the key Abracadabra. Note that the result of the encryption is a binary string, as follows:

Mysql> SELECT * from users WHERE username= ' Joe '; +----------+----------+ | username | password | +----------+----------+ | Joe |¡?i??!? | +----------+----------+ 1 row in Set (0.02 sec)

Abracadabra This key is critical for restoring to the original string. This key must be passed to the Decode () function to obtain the original, unencrypted password. Here's how it's used:

mysql> SELECT DECODE (password, ' Abracadabra ') from the 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 a Web application--DECODE () uses a Web site-specific key to unlock the passwords stored in the database and compare them with what the user has entered. Suppose you use PHP as your own scripting language, you can query as follows:

Tip: Although encode () and decode () can meet most of the requirements, sometimes you want to use a more intensive encryption method. In this case, you can use the Aes_encrypt () and Aes_decrypt () functions, which work the same way, but with higher encryption strength.

One-way encryption

One-way encryption is different from bidirectional encryption, and once the data is encrypted there is no way to reverse the process. Therefore, the authentication of the password includes the rekeying of the user's input, and compare it with the saved cipher text to see if it matches. A simple one-way encryption method is the MD5 check code. The MySQL MD5 () function creates a "fingerprint" of your data and saves it for verification testing. Here is 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 | +----------+----------------------------------+ | joe | 81a58e89df1f34c5487568e17327a219 | +----------+----------------------------------+ 1 row in Set (0.02 sec)

Now you can test whether the user's input matches the saved password by getting the MD5 check code for the user's password and matching it to 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 using the Encrypt () function, which uses the crypt () system call at the bottom of the system to complete the encryption. This function has two parameters: one is the string to be encrypted, and the other is the "salt" of the two (or more) characters. It then encrypts the string with a salt, and the salt can then be used to encrypt the user's input again and compare it to the previously encrypted string. The following example shows 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)

The result is

Mysql> SELECT COUNT (*) from the users WHERE Username= ' Joe ' and Password=encrypt (' guessme ', ' ab '); +----------+ | COUNT (*) | +----------+ | 1 | +----------+ 1 row in Set (0.00 sec)

Hint: ENCRYPT () can only be used on the *nix system, because it needs to use the underlying crypt () library.

Fortunately, the above example illustrates how you can use MySQL to encrypt your data in one-way and bidirectional, and tells you some ideas about how to secure your database and other sensitive database information.