Encrypt MySQL data using AES techniques

the data will be encrypted and stored in the database for providing an additional level of security for the data. In MySQL AES(Advanced Encryption Standard) algorithm can be used to encrypt and decrypt the data being inserted and retrieved.

•  AES_ENCRYPT(data,encryption_key) - the method that can be used to encrypt the data being inserted. 

        e.g:- AES_ENCRYPT('chathuranga','abc123');

• AES_DECRYPT(encrypted_data,encryptyed_key) 

suppose you need to encrypt and store the username and email of the every user in the database. these encrypted values are stored in the database as binary strings. therefore you must give suitable data types for the columns in the table to accept and hold binary string inputs. therefore we must use varbinary instead of varchar. create the following table in the database.

create table user_table(

user_id int auto_increment primary key,

username varbinary(100),

email varbinary(100));

then insert following data into the table created. you can see that  am using AES_ENCRYPT function with cha123 as the key.

insert into user_table values('',AES_ENCRYPT('chathuranga','cha123'),AES_ENCRYPT('chathuranga.t@gmail.com','cha123'));



insert into user_table values('',AES_ENCRYPT('darshana','cha123'),AES_ENCRYPT('chathurangat@lankacom.net','cha123'));

you can see that the data has been stored in a encrypted format. see below screen dump.

 the result of the select query is displayed in the encrypted format. if you need to see the original values (decrypted values) you have to use the AES_DECRYPT function to decrypt the values stored.  the command is as follows.

select AES_DECRYPT(username,'cha123') As username_origianal , AES_DECRYPT(email,'cha123') As email_origianal from user_table;

refer the below screen shot.

 finally try out  the following select statement to understand how to use where clause with an encrypted columns.

select AES_DECRYPT(username,'cha123') As username_origianal , 

AES_DECRYPT(email,'cha123') As email_origianal 

from user_table 

where username = AES_ENCRYPT('chathuranga','cha123');

Hope this will helpful for you!!!

Thanks and Regards,
Chathuranga Tennakoon
chathuranga.t@gmail.com