Two-way encryption of MySQL database

If you are running a Web application that uses MySQL, you have a great chance of keeping passwords or other sensitive information in your application. Protecting this data from or from prying eyes is an important concern because you can neither allow unauthorized people to use or disrupt applications, but also ensure your competitive advantage. Fortunately,

If you are running a Web application that uses MySQL, you have a great chance of keeping passwords or other sensitive information in your application. Protecting this data from or from prying eyes is an important concern because you can neither allow unauthorized people to use or disrupt applications, but also ensure your competitive advantage. Fortunately, MySQL comes with a number of 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: two-way encryption. Here, a piece of data is encrypted by a key that can only be decrypted by the person 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:
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, which is encrypted through the key Abracadabra. Note that the result of the encryption is a binary string, 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 reverting 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 the site-specific key to unlock the stored password and compares it to the user's input when verifying that the user is logged in. Assuming that you use PHP as your own scripting language, you can query as follows:
Undefined undefined
$query = "Select COUNT (*) from the users WHERE
Username= $inputUser and DECODE (password,
Abracadabra) = $inputPass ";? >
Note: Although the Encode () and decode () functions meet most requirements, there are times when you want to use a higher-intensity 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.

Two-way encryption of MySQL database