| Bug #37163 | V5.1.4 breaking when used with Stored Proc running AES_DECRYPT | ||
|---|---|---|---|
| Submitted: | 3 Jun 2008 16:21 | Modified: | 11 Jun 2008 18:47 |
| Reporter: | Santanu Lahiri | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.1.4 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 5.1.4, AES_ENCRYPT, ODBC, stored proc, windows | ||
[11 Jun 2008 8:26]
Tonci Grgin
Hi Santanu and thanks for your report. Alas, "binary" is used everywhere in MySQL and it's hard for connector to tell whether to treat "binary" as true binary data or should it be UTF8, for example, like in ad-hoc queries. This is a known problem, please search BugsDB. For now, you can either parse out starting "0x" (as your result is returned in form of "0xcorrectresult" or use CAST AS SOMETHING (like CHAR). mysql> SELECT AES_DECRYPT(UserName,'123456'), AES_DECRYPT(UserTitle,'123456') FROM UserTable; Field 1: `AES_DECRYPT(UserName,'123456')` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 100 Max_length: 4 Decimals: 31 Flags: BINARY Field 2: `AES_DECRYPT(UserTitle,'123456')` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 100 Max_length: 6 Decimals: 31 Flags: BINARY Example: SELECT AES_DECRYPT(Val2,"123456") FROM bug37163 SQLExecDirect: In:hstmt = 0x00851FE0, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "AES_DECRYPT(Val2,"123456")" 0xTony << Should have been "Tony" 1 row fetched from 1 column. and workaround SELECT CAST(AES_DECRYPT(Val2,"123456") AS CHAR) FROM bug37163 SQLExecDirect: In:hstmt = 0x00851FE0, szSqlStr = "", cbSqlStr = -3 Return: SQL_SUCCESS=0 Get Data All: "CAST(AES_DECRYPT(Val2,"123456") AS CHAR)" "Tony" 1 row fetched from 1 column. Closing as "Won't fix" as proper patch should be in server.
[11 Jun 2008 18:47]
Santanu Lahiri
Yes, the workaround did it. I changed all occurances of AES_DECRYPT to CAST(AES_DECRYPT(..) AS CHAR) and tried with V5.1.4 again. Of course I did not try every stored proc, but the few that I did try seemed to work correctly, so I am assuming the rest will too. Thank you, Tonci... Santanu
[11 Jul 2008 2:06]
Matthew Wanders
I experienced this same issue using the ODBC Driver v5.1.4 in a classic ASP web application. I'm storing customer information in BLOB columns using AES_ENCRYPT. When I select the data (AES_DECRYPT) out of the database (ADODB) using the 5.1.4 ODBC driver, I get binary data (causing a run-time error in the ASP, since I try to convert it to a string in dispaly it in a web page). Using the old 3.51 driver works perfectly. The work around of casting to a CHAR worked perfectly, thanks for that. It was quite funny the first time it occured to me. Most of the binary-garbage data that came back from the query contained numbers in the 8000-25000 range. When I ran this through the HTMLEncode function, it though it was unicode characters and I suddenly had Chinese/Japanese glyphs in my web page. Was a bit shocking to data in the web page suddenly show in another language without knowing how I did it.
Description: Driver returns empty strings as field values when the stored proc contains the AES_DECRYPT function. When using V3.51, the same code works correctly and returns the right values. How to repeat: Create a table called UserTable, with two columns UserName VARCHAR(100), UserTitle VARCHAR(100) Use AES_ENCRYPT and populate the table with a few rows. Use "123456" as key Create Stored Procs as DELIMITER $$ CREATE PROCEDURE `ReadAESData`() BEGIN SELECT AES_DECRYPT(UserName,'123456'), AES_DECRYPT(UserTitle,'123456') FROM UserTable; END$$ CREATE PROCEDURE `ReadTestData`() BEGIN SELECT UserName, UserTitle FROM UserTable; END$$ DELIMITER ; Create a project where you can call the stored proc ReadAESData using ADO. I used the ADO classes from Carlos Antonelli (CodeProject.Com). Add code to traverse the resultset returned, and print the data values. I used the List control to display returned data. Now run it using V3.51.19 first. The output will show the data values placed in the database earlier. Switch driver to 5.1.4 and run again. Empty strings are returned this time. Lastly, call the ReadTestData proc instead and repeat the above two steps. Both versions display the data from the table, albeit using the encoded values.