If you are using a mysql database, you can store passwords or other sensitive and important information in your application. 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 encryption functions designed to provide this type of security. 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:
Copy codeThe Code is as follows:
Mysql> insert into users (username, password)
VALUES ('Joe ', ENCODE ('essme', 'abracadaba '));
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:
Copy codeThe Code is as follows:
Mysql> SELECT * FROM users WHERE username = 'job ';
+ ---------- +
| 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, 'abracadaba ')
FROM users WHERE username = 'job ';
+ --------------------------------- +
| DECODE (password, 'abracadaba') |
+ --------------------------------- +
| 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:
Copy codeThe Code is as follows:
<? Php
$ Query = "select count (*) FROM users WHERE
Username = '$ inputuser' and decode (password,
'Abracadaba') = '$ 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.