Use MySQL encryption functions to protect sensitive Web Site Data

The following articles mainly introduce how to use MySQL encryption functions to protect sensitive data on websites. If you are using a MySQL Web application, it can store passwords or other sensitive information in applications.

Protecting this data from access by hackers or prophers is an important concern, because you cannot allow unauthorized users to use or destroy applications, but also ensure your competitive advantage.

Fortunately, MySQL comes with many designs designed to provide this type of secure MySQL encryption functions. This article outlines some of these functions and explains how to use them and how they provide different levels of security.

Bidirectional encryption

Let's start with the simplest encryption: two-way encryption. Here, a piece of data is encrypted by a key and can only be decrypted by the person who knows the key. MySQL has two functions to support this type of encryption: ENCODE () and DECODE (). 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)  
 
 

Among them, Joe's password is guessme, Which is encrypted by the key abracadabra. Note that the encrypted 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 for restoring to the original string. This key must be passed to the DECODE () function to obtain the original unencrypted password. The following is 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 runs in a Web application -- DECODE () uses a dedicated website key to unbind the password stored in the database when verifying the user's logon, and compare it with the content entered by the user. If you use PHP as your script language, you can query it as follows:

 
 

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

Note: although the ENCODE () and DECODE () functions can meet most requirements, you may want to use more powerful encryption methods. In this case, you can use the AES_ENCRYPT () and AES_DECRYPT () functions. They work in the same way, but the encryption strength is higher.

Unidirectional encryption

One-way encryption is different from two-way encryption. Once data is encrypted, this process cannot be reversed. Therefore, password verification includes re-encryption of the user's input content, and comparing it with the saved ciphertext to see if it matches. A simple one-way encryption method is the MD5 verification code. The MySQL encryption function creates a "fingerprint" for your data and saves it for verification and testing. The following 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 content entered by the user matches the Saved Password by obtaining the MD5 verification code and comparing it with the Saved Password, as shown below:

 
 

  
  
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 system underlying crypt () system call to complete encryption. This MySQL encryption function has two parameters: one is the string to be encrypted, and the other is the "salt" with double or multiple characters ". It then encrypts the string with salt; this salt can then be used to encrypt the user's input again and compare it with 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 users WHERE username='joe' AND password=ENCRYPT('guessme', 'ab');   
  
  
+----------+   
  
  
| COUNT(*) |   
  
  
+----------+   
  
  
| 1 |   
  
  
+----------+   
  
  
1 row in set (0.00 sec) 
 
 

Note: ENCRYPT () can only be used on * NIX systems because it uses the underlying crypt () Library.

Fortunately, the above example shows how to use MySQL to encrypt your data one-way and two-way, I also told you some ideas about how to protect the information security of databases and other sensitive databases. Wish you a pleasant programming!

The above content is an introduction to how to use the MySQL encryption function to protect sensitive data on Web sites. I hope you will gain some benefits.