Bug #47585 Bug in AES Decrypt function since mysql-connector-java-5.0.8
Submitted: 24 Sep 2009 9:11 Modified: 25 Sep 2009 9:58
Reporter: Martin Pe Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:v5.0.8 to v5.1.10 OS:Any
Assigned to: CPU Architecture:Any

[24 Sep 2009 9:11] Martin Pe
Description:
I'm using de function AES_ENCRYPT / AES_DECRYPT in my java program to save private user information in a table.

Currently i'm using the conector "mysql-connector-java-5.0.7-bin.jar" and runs well. But since v5.0.8 including the new version 5.1.10 the function doesn't run.

I can encrypt with both version (5.0.7 and 5.1.10) but only 5.0.7 version decrypts well the data. Upper versions show corrupted decrypted information.

If i use mysql console, i can decrrypt well the information.

A simple example of my sentence:

SELECT AES_DECRYPT(Column,'abcdfeg') FROM users WHERE NUser='user'

This sentence decrypts well the information in java connector 5.0.7 and mysql console (all versions i test), but not on java conector 5.0.8 and uppper.

How to repeat:
All the times, I can't update the connector in my program.
[24 Sep 2009 9:17] Martin Pe
A example of corrupted decrypted  information thats return the connector: [B@121f1d
[24 Sep 2009 13:29] Mark Matthews
We'd need to see the steps your code uses to attempt to encrypt/decrypt values. The handling of binary data has changed to be more compliant with both the JDBC and SQL specifications in later versions of the driver.

Depending on how one passes either the key, or processes the value, data might not be encrypted or decrypted correctly.

Given that we don't know how your program processes the data, we can not begin to find the issue.
[24 Sep 2009 17:53] Mark Matthews
Notice that the AES decrypt function tells the driver that what it's returning is BINARY (or VARBINARY), hence the result of calling ResultSet.getString() is something like "[B@121f1d", which means "an array of bytes, at memory location 121f1d in the JVM".

If the application needs a string, it first needs to know what encoding was used when it was sent to be encrypted, and then one does something like:

new String(rs.getBytes(...), "encodingName");

The way it worked with earlier versions of the driver was pure luck, in that your encoding matched what was sent, and the driver assumed that it could go from byte[] -> char, which isn't a valid assumption.
[25 Sep 2009 7:57] Martin Pe
Thank you for your comemnts, you opened my eyes. I solved the problem.

In my program i get the information with a generic function that returns objects:

/*Returns a one row of table data*/
public Object[] getRowData () throws SQLException {
        Object[] objects = new Object[getNumColumnas()];
        for(int i=0;i<getNumColumnas();i++){
            objects[i]=resultadoSql.getObject(i+1);
            System.out.println(objects[i].getClass());
        }
        return objects;
}

"System.out.println(objects[i].getClass());" with java conector v5.0.7 returns java.lang.String.

With java conector 5.0.8 and upper returns: "class [B". I think that means binary.

Then this was the problem.
So, i don't use the object directly, i convert it in String:

String s = new String((byte[])dataRow[0], "utf8");

Thanks for all.
[25 Sep 2009 9:51] Tonci Grgin
Martin, glad problem is solved.
[25 Sep 2009 9:58] Martin Pe
Solved
[10 Aug 2015 6:48] Harita Mahajan
Hi,

Is there any better way to resolve this issue. As same issue has been faced. But need a generic solution,because there are various queries which are impacted due to sql connector version upgrade. 

So do we have any mysql server side configuration settings for such varbinary columns which should be returning String in resultset. Or Code changes should be minimal rather than managing from every query resultset.

Thanks
Harita Mahajan
[2 Oct 2015 15:08] Filipe Silva
Harita,

I'm sorry but there's really nothing we can do about it. As Mark explained AES_DECRYPT returns BINARY data and so we have to treat it as so. However, if you call ResultSet.getString(int) you will actually get the string representation of the returned Bytes and not an array of Bytes as Mark mentioned. Mind your character encodings, thought. I think Mark meant to say ResultSet.getObject() instead.

As I see it you may have a couple choices. Either change your code to deal with the binary data or change your queries to cast this field to CHAR before returning. If changing your queries is too cumbersome you could also implement a StatementInterceptor that injects the CAST function where needed for you.

Hope this helps.