Data Encryption using AES (Advanced Encryption Standard) in MySql

This example is tested in MySql Version : 5.0.80-enterprise-nt-log. to know your version please fire below query.

SHOW VARIABLES LIKE 'version';


Table Script :

CREATE TABLE `test` (
  `id` bigint(6) unsigned NOT NULL auto_increment,
  `simple_name` varchar(100) default NULL,
  `encrypted_name` varbinary(112) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


Why VARBINARY or BLOB ?


This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).


How to calculate encrypted text length ?

function : 16 * (trunc(string_length / 16) + 1)

Example : 
       lets consider string_length = 100.
       similar mysql query : SELECT (16 * (TRUNCATE(100 / 16, 0) + 1)) encrypted_length;
       ans : 112 (simple multiplication of 16).


simple_name : Procrastination is the art of keeping up with yesterday
key : SHA1('Lorem ipsum dolor sit amet')

Why (SHA / SHA1 / SHA2) in key generation ?

For a key length of 128 bits, the most secure way to pass a key to the key argument is to create a truly random 128-bit value and pass it as a binary value.


AES Encrypt :

Syntax : AES_ENCRYPT(str,key)

INSERT INTO test(simple_name, encrypted_name)
VALUES('Procrastination is the art of keeping up with yesterday',
AES_ENCRYPT('Procrastination is the art of keeping up with yesterday', SHA1('Lorem ipsum dolor sit amet')));

AES Decrypt :

Syntax : AES_DECRYPT(crypt_str,key)

SELECT AES_DECRYPT(encrypted_name, SHA1('Lorem ipsum dolor sit amet')) encrypted_name FROM test;


source : https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html