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 ('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:
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:
Undefined
$ 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.