Bug #54856 Passsword decryption doesnt work-returns a BLOB object
Submitted: 28 Jun 2010 8:18 Modified: 28 Jun 2010 10:41
Reporter: Abhilash Shah Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.77, 5.5.6-m3 OS:Linux (4.0)
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[28 Jun 2010 8:18] Abhilash Shah
Description:
Environment:
OS- RHEL 4 , Mysql InnoDB 5.0.77

Problem:

Encryption takes place but decryption is always returned as a BLOB
Steps:-
update V2RCPCAN.RCPUser set PWD=ees_encrypt('test123','spam') where userid = 'joe';

--> 1 rows affected

SELECT aes_decrypt(PWD, 'spam')) FROM RCPUser WHERE userid = 'joe';
--> BLOB

How to repeat:
Steps:- run the below queries in any test user table

update V2RCPCAN.RCPUser set PWD=ees_encrypt('test123','spam') where userid = 'joe';

--> 1 rows affected

SELECT aes_decrypt(PWD, 'spam')) FROM RCPUser WHERE userid = 'joe';
--> BLOB
[28 Jun 2010 8:18] Abhilash Shah
Steps:-
update V2RCPCAN.RCPUser set PWD=ees_encrypt('test123','spam') where userid = 'joe';

--> 1 rows affected

SELECT aes_decrypt(PWD, 'spam')) FROM RCPUser WHERE userid = 'joe';
--> BLOB
[28 Jun 2010 8:21] Tonci Grgin
This should be a duplicate of Bug#54684
[28 Jun 2010 9:00] Valeriy Kravchuk
Verified with mysql-trunk also:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot --column-type test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT aes_decrypt(aes_encrypt('test','spam'), 'spam');
Field   1:  `aes_decrypt(aes_encrypt('test','spam'), 'spam')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     16
Max_length: 4
Decimals:   31
Flags:      BINARY 

+-------------------------------------------------+
| aes_decrypt(aes_encrypt('test','spam'), 'spam') |
+-------------------------------------------------+
| test                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

Manual (http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_aes-decrypt) does NOT say that aes_decrypt() always return a binary string, so I'd call this a bug.
[28 Jun 2010 10:41] Abhilash Shah
Ok, somehow it got working thanks to all please close this thread. But it still doesn't work in mysql wb.