Mysql bidirectional encryption Decryption method usage detailed _mysql

If you're using a MySQL database, you'll have a great chance of keeping your password 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:

Copy Code code as follows:

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:

Copy Code code 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 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:

Copy Code code as follows:

<?php
$query = "Select COUNT (*) from users WHERE
Username= ' $inputUser ' and DECODE (password,
' Abracadabra ') = ' $inputPass ';
?>

Note: While the Encode () and decode () functions meet most of the requirements, you sometimes want to use more intense encryption. In this case, you can use the Aes_encrypt () and Aes_decrypt () functions, which work the same way, but with higher encryption strength.