Monday, March 11, 2013

MySql Encrypt and Decrypt Function

       Mysql supports many compression and encryption functions. One such technique is AES(Advanced Encryption Standard) algorithm. Using this, one can encrypt or decrypt the datas in table. General Syntax for Encrypt/Decrypt are
  
   AES_ENCRYPT(str,key_str)
   AES_DECRYPT(crypt_str,key_str)
 
      AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string. The input arguments may be any length and can contain alphanumeric characters. Basic Insert Query with and without Encrytion are as follows.
 
INSERT INTO `myTable`(myName,myPass) VALUES('Jimmy',AES_ENCRYPT('password1','key:test'));
INSERT INTO `myTable`(myName,myPass) VALUES('David','password2');
 
 
myName myPass
Jimmy ÙjˆçésŸ·
David password2
 
     To update plain text to Encrypted value, execute
 
UPDATE myTable SET myPass = AES_ENCRYPT('password2','key:test') WHERE myName='David';

myNamemyPass
David¦ã bf;× ÀäiO W

    For updating password column to encrypted value in an existing table you can either use the query directly as   

UPDATE myTable SET mypass = AES_ENCRYPT(mypass,'key:test');
 
  or can export data files alone and reinsert by using the below query.

 LOAD DATA INFILE 'C:/Sample.csv' INTO TABLE myTable(myname,@mypass) SET mypass=AES_ENCRYPT(@mypass,'key:test');
   
     Finally, to view the table datas, use decrypt query by passing the key value. Remeber that, if the given key value does not match with that of the key used while insertion, then it will return NULL value.
 
SELECT AES_DECRYPT(mypass,'key:test') FROM myTable;
myNamemyPass
Jimmypassword1
Davidpassword2