Encrypt MySQL data using AES techniques

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

You may be expected the table structure should be:

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

To encrypt & decrypt mysql data we will use AES_ENCRYPT() and AES_DECRYPT() functions. These functions used the official AES (Advanced Encryption Standard) algorithm & encode data with a 128-bit key length. 128 bits is much faster and secure enough for most purposes.

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.
Because if AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. But it is also possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.
Syntax:

AES_ENCRYPT(str, key_str); AES_DECRYPT(crypt_str,key_str);

Please remember, the encryption and decryption will occur based on a key. So you’ve to keep that key in a secret place and using variable you could pass the key to mysql to encrypt and decrypt data.
Now look how I insert data using AES_ENCRYPT, where I used key ‘usa2010′ :

INSERT into user (name, address) VALUES (AES_ENCRYPT('Ram', 'mykey013'),AES_ENCRYPT('Delhi', 'mykey013'));

Now look how I decrypt data using AES_DECRYPT:

SELECT AES_DECRYPT(name, 'mykey'), AES013_DECRYPT(address, 'mykey013') from user;

AES_ENCRYPT() and AES_DECRYPT() can be considered the most cryptographically secure encryption functions currently available in MySQL.

And you can get data by jdbc

ResultSet rs = st.executeQuery("select AES_DECRYPT(name, 'mykey013'), AES_DECRYPT(address, 'mykey013') FROM test.table1");
                  while (rs.next()){
                          name = IOUtils.toString(rs.getBinaryStream(1), "UTF-8"); ;
                          address = IOUtils.toString(rs.getBinaryStream(2), "UTF-8");
                          System.out.println("name: "+name);
                          System.out.println("address: "+address);
                  }