Unable to AES_DECRYPT after AES_ENCRYPT in mysql

Question

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.

Answer 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.

Answer 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.