MySQL encryption and decryption method usage

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 can neither allow unauthorized people to use or disrupt applications, but also keep your competitive edge. 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.

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 (' Fantasy ', ENCODE (' 123456 ', ' ABCD '));

Query OK, 1 row affected (0.14 sec)

Where fantasy's password is 123456, it is encrypted via the key ABCD (the key can be customized here). Note that the result of the encryption is a binary string, as follows:

It should be easy to see how it works in a Web application--DECODE () uses the site-specific key to unlock the password stored in the database when authenticating the user's login, and compares it to what the user has entered. Assuming that you use PHP as your own scripting language, you can query as follows:

$query = "Select COUNT (*) from the users WHERE username= ' $username ' and DECODE (password, ' abcd ') = ' $password '";

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 a higher encryption strength.

MySQL encryption and decryption method usage