How to use MySQL encryption function to protect sensitive data of web site

Source: Internet
Author: User
Tags count crypt functions insert key md5 mysql query

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:

The following are the referenced contents:
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:

The following are the referenced contents:
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:

The following are the referenced contents:
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:

The following are the referenced contents:
<?php
$query = "Select COUNT (*) from the users WHERE username= ' $inputUser ' and DECODE (password, ' abracadabra ') = ' $inputPass '";? >

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:

The following are the referenced contents:

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:

The following are the referenced contents:
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:

The following are the referenced contents:

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

The following are the referenced contents:
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. Wish you a happy programming!



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.