Bug #1149 AES_ENCRYPT & AES_DECRYPT Bug
Submitted: 27 Aug 2003 6:35 Modified: 28 Aug 2003 9:59
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.14 & 4.1.0 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[27 Aug 2003 6:35] [ name withheld ] 
Description:
With some combinations of string and passwords used in aes_encrypt the output generated contains characters that aes_decrypt cannot handle (probably a space in the beginning of the field).
On the attached example the select query returns a null value instead of the previously inserted "Libero"

How to repeat:

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `string` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO test(string) VALUES(AES_ENCRYPT('Libero','eKqvqhjXKvrNt3aBjyVXg1'));

SELECT id,AES_DECRYPT(string,'eKqvqhjXKvrNt3aBjyVXg1') as string FROM test;
[28 Aug 2003 9:59] Indrek Siitan 
The problem is that the AES-encrypted string might contain 0x00 value,
which VARCHAR treats as end-of-string, so VARCHAR isn't suitable for
storing AES_ENCRYPT() results.

Change the field to TEXT or BLOB, that handles binary data, and the
problem is solved:

mysql> CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `string` text NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test(string)
    -> VALUES(AES_ENCRYPT('Libero','eKqvqhjXKvrNt3aBjyVXg1'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id,AES_DECRYPT(string,'eKqvqhjXKvrNt3aBjyVXg1')
    -> as string FROM test;
+----+--------+
| id | string |
+----+--------+
|  2 | Libero |
+----+--------+
1 row in set (0.00 sec)
[22 Oct 2003 1:44] Ralf Hauser 
please mention on http://www.mysql.com/doc/en/Miscellaneous_functions.html that Blobs are fine too!

One interesting observation: sometimes we used the standard sun.jdbc.rowset.CachedRowSet to receive the Blob streams.
Normally this worked fine, but after AES_DECRYPT, we receive some complaint about incompatible data types...