MySQL Bugs: #12872: AES_Decrypt wrong column type

Bug #12872	AES_Decrypt wrong column type
Submitted:	30 Aug 2005 10:24	Modified:	19 Sep 2006 15:26
Reporter:	Martin MDR	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.14	OS:	Windows (Windows XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Using AES Encryption, on an ASP.NET application, there are some column type problems upon Decryption:

SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename ORDER BY Field_ID

returns the system.byte[] type, while:

SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename

returns the string type. So when adding an ORDER BY the returned column type changes from string to system.byte[]. This also happens with GROUP BY.

I use the following to connect my application to the MySQL server: Public ConnectionString As String = "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=root;database=mydatabase"

As you can see the version of the Connector is 3.51. I use MySQL version 4.1.11.

How to repeat:
In ASP.NET (1.1) try:

SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename ORDER BY Field_ID

You see it returns system.byte[] type. Then try:

SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename

You see it returns string type.

Suggested fix:
No idea how to fix it, but I suggest to change to output type string.

Test case in C - gives the same type for both queries, even with different optimizer plans

Attachment: 12872.c (text/x-csrc), 3.23 KiB.

I tried to repeat this bug on newer 4.1.14 version (sorry, ASP .Net is not available for me). 

I had created the following table:

create table t12872 (id integer primary key, c1 char(100));
insert into t12872 values(1, 'abc');
...

and inserted several rows into it.

My test case (see file attached) gives the following on Linux:

type=253
length=100
decimals=31
type=253
length=100
decimals=31

That is, the type of result is MYSQL_TYPE_VAR_STRING (253), according to the documentation (http://dev.mysql.com/doc/mysql/en/encryption-functions.html):

"The return string is a binary string..."

in both cases. 

The idea was to check, is there any difference in result type depending on index usage. I've got the following plans for the queries:

mysql> explain SELECT AES_DECRYPT(c1, '123') from t12872 order by id limit 1;
+----+-------------+--------+-------+---------------+---------+---------+------+
------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  |
 rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+
------+-------+
|  1 | SIMPLE      | t12872 | index | NULL          | PRIMARY |       4 | NULL |
   15 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+
------+-------+
1 row in set (0,00 sec)

mysql> explain SELECT AES_DECRYPT(c1, '123') from t12872;
+----+-------------+--------+------+---------------+------+---------+------+----
--+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | row
s | Extra |
+----+-------------+--------+------+---------------+------+---------+------+----
--+-------+
|  1 | SIMPLE      | t12872 | ALL  | NULL          | NULL |    NULL | NULL |   1
5 |       |
+----+-------------+--------+------+---------------+------+---------+------+----
--+-------+
1 row in set (0,01 sec)

So, plans are different, but the result type is the same (at least, in C).

Please, try to check if there is the same behaviour on 4.1.14 (available at http://dev.mysql.com/downloads/mysql/4.1.html). Check the plans for your queries (are they different from mine). Add any details that make my test case different from what you are observing.

If you have nothing to add, I'll mark this bug as "Can't repeat".

I tested it again on 4.1.14, but the same results, still different types. I also tested the two different queries in MySQL-Front, also there you see differences where one shows the direct results, the other only blob fields with the results inside.

I created a sample project in ASP.NET with both the database included and a test ASP.NET application showing the result types. Please download it from http://www.mdrcomponents.com/Decrypttest.zip

Inside you find two directories, one containing the database, the other containing the sample application. If you want to have a look at the source, open Test.aspx.vb

I hope this provided a better insight if this is a bug.

I was unable to reproduce this bug under 4.1 & 5.0 (from bk trees)

Has anyone tested this in combination with ASP.NET? Also, did you test it with the database I attached?

I'm very sure a problem exists, and in my opinion it's within MySQL. Please test it with the database I attached before and with the queries from the source and you can see they give different result types.

In case you need more to reproduce the issue, I'd be happy to help.

Jorge,
Did you use ASP.NET in your test?

I was able to repeat this bug using the supplied ASP test-case and the table provided by the user.

This isn't specific to AES_DECRYPT() at all, but many (all?) string functions which return different types depending on whether the optimizer uses a temporary table (and filesort?) for the query. Example:

create table t1 (a int primary key, b blob);
insert into t1 values (1,'abc'),(2,'def');
--enable_metadata
select upper(b) as data from t1 limit 1;
select upper(b) as data from t1 group by a;
--disable_metadata
drop table t1;

I am also seeing this problem under 
MySQL 5.0.19 on SuSE Linux
using mysql-connector-java-3.1.10-bin.jar

If I remove the order by clause from the sql statement - then I get a well formed string back.
If I have the order by then I get bytes back.
It only seems to be a problem with more complex statements.

THIS IS A CRITICAL ONE FOR ME. Do I need to submit this bug again to hopefully raise the priority and mark as a different operating system

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Additional Info:
I have tried the ASP.NET page that MDR provided with 4.1.22 and it produced the following :
Test 1 (with ORDER BY and GROUP BY):

Query:
SELECT Personeel.Personeel_ID, AES_DECRYPT(Personeel.Voornaam, '^P]]YEDWC'), AES_DECRYPT(Personeel.Tussenvoegsel, '__\^XBET'), AES_DECRYPT(Personeel.Achternaam, 'P^___CF') FROM Personeel GROUP BY Personeel.Personeel_ID ORDER BY AES_DECRYPT(Personeel.Achternaam, 'P^___CF')

Results:
Docent - System.String
Ruiter - System.String

Test 2 (without ORDER BY and GROUP BY):

Query:
SELECT Personeel.Personeel_ID, AES_DECRYPT(Personeel.Voornaam, '^P]]YEDWC'), AES_DECRYPT(Personeel.Tussenvoegsel, '__\^XBET'), AES_DECRYPT(Personeel.Achternaam, 'P^___CF') FROM Personeel

Results:
Ruiter - System.String
Docent - System.String

Just a small comment for those who run into this issue with ASP.Net.

Use the cast function to help solve this problem.

Select CAST(AES_DECRYPT(column,'mykey') AS CHAR(#)) AS alias FROM table;

This is because ASP.Net has some issues with BLOB types, so you'll have to convert it into a character string in order for .Net to pick it up.