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
