Encryption and Decryption of MySQL data.

CREATE  TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`first_name` VARBINARY(100) NULL ,
`address` VARBINARY(200) NOT NULL ,
PRIMARY KEY (`id`)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

Why we used VARBINARY data type instead of VARCHAR:
Because AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string. AES is a block-level algorithm. So when data encrypted it is padded. So you can calculate the length of the result string using this formula:

1 16 × (trunc(string_length / 16) + 1)
So if your address field structure is = VARCHAR(100) ; //100 length of varchar
Then before your encryption it should be converted
= 16 * (trunc(100/ 16) + 1)
= 16 * (6.25 + 1)
= 16 * 7.25
= 116

So VARCHAR(100) should be converted to  VARBINARY(116) minimum. I suggest use little more like VARBINARY(150) in this case.

Now look how I insert data using AES_ENCRYPT, where I used key ‘usa2010′ :

INSERT into user (first_name, address) VALUES (AES_ENCRYPT('Obama', 'usa2010'),AES_ENCRYPT ('Obama', 'usa2010'));

Now look how I decrypt data using AES_DECRYPT:

SELECT AES_DECRYPT(first_name, 'usa2010'), AES_DECRYPT(address, 'usa2010') from user;