mysql bidirectional encryption and decryption method usage details

If you are using an mysql database, you have a good chance of keeping passwords or other sensitive and important information in your application. Protecting this data from hackers or snoopers is an important concern, because you can't let unauthorized people use or break the application, while still maintaining your competitive advantage. Fortunately, MySQL comes with a number of encryption functions designed to provide this type of security. This article Outlines some of these functions and explains how they are used and the different levels of security they can provide.

The bidirectional encryption

Let's start with the simplest encryption: two-way encryption. Here, a piece of data is encrypted using 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(). Here is a simple example:

mysql> INSERT INTO users (username, password) 
VALUES ('joe', ENCODE('guessme', 'abracadabra')); 
Query OK, 1 row affected (0.14 sec) 

The password for Joe is guessme, which is encrypted by the key abracadabra. Note that the result of the encryption is a binary string, as shown below:

mysql> SELECT * FROM users WHERE username='joe'; 
+----------+----------+ 
| username | password | 
+----------+----------+ 
| joe | ??i??!? | 
+----------+----------+ 
1 row in set (0.02 sec) 
abracadabra This key is essential to restoring the original string. This key must be passed to DECODE() Function to get the original, unencrypted password. Here's how it works:  
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 the Web application -- when authenticating a user's login, DECODE() USES a web-specific key to unlock the password stored in the database and compare it to what the user has entered. Assuming you use PHP as your own scripting language, you can query like this:

<?php 
$query = "SELECT COUNT(*) FROM users WHERE 
username='$inputUser' AND DECODE(password, 
'abracadabra') = '$inputPass'"; 
?> 

Note: While ENCODE() and DECODE() will do most of the work, there are times when you want to use stronger encryption. In this case, you can use the AES_ENCRYPT() and AES_DECRYPT() functions, which work the same way but with higher encryption strength.