Unable to AES_DECRYPT after AES_ENCRYPT in mysql

I created user table

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;

I inserted one row:

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

To select this row i used:

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

I am getting the following result.What i need to do see my data.No data is visible for me.

Solution 1:

According to the Manual:

AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.

• MySQL 5.1 Doc: AES_ENCRYPT() / AES_DECRYPT()

I don't know why it is still returning a binary string in your case. Anyway, try this:

SELECT *, 
       CAST(AES_DECRYPT(first_name, 'usa2010') AS CHAR(50)) first_name_decrypt 
FROM   user

And use first_name_decrypt instead of first_name.

Solution 2:

From mysql command line client there is no need to use CAST:

mysql> SELECT AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc');

+-----------------------------------------------+
| AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') |
+-----------------------------------------------+
| admin                                         |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') AS CHAR (50));

+------------------------------------------------------------------+
| CAST(AES_DECRYPT(AES_ENCRYPT('admin','abc'),'abc') AS CHAR (50)) |
+------------------------------------------------------------------+
| admin                                                            |
+------------------------------------------------------------------+
1 row in set (0.02 sec)

As you can see using cast in command line is little bit slower. But I have noticed that if you use some tools like phpmyadmin, then you need to use CAST, otherwise result will be wrong.