MySQL Bugs: #60144: Pound symbol encrption and decryption problem

Bug #60144	Pound symbol encrption and decryption problem
Submitted:	16 Feb 2011 15:17	Modified:	29 Apr 2013 12:38
Reporter:	Vsetar Prajin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.0.8	OS:	Windows (XP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	charset, Pound symbol

Description:
I need to save password in a table by encrypting it.PAssword contains pound symbol.It stored in db correctly.But when retrieving it there comes a problem.
rs.getString("aes_decrypt(col1,'key1')") decrypts password in a wrong manner.So comparison of password that user entered and the one stored in db fails eventhough the entered password is correct.I could see that when I convert that decrypted password to bytes which incorrectly shows -17,-65,-67,-17,-65,-67 as byte values instead of -62,-93 which is the corresponding byte value for pound sign.

Am I missing something?

PS : I set character sets of server,client,connection,results,database to utf8.

How to repeat:
Test program :

<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>  
<%@ page import="java.sql.*"%>
		
		<% 
		String url = "jdbc:mysql://localhost:3306/adsm";
        String driverName = "org.gjt.mm.mysql.Driver";
        String userName = "root";
        String passWord = null;
		try
        {
			Class.forName(driverName);
			Connection conn = DriverManager.getConnection(url, userName, passWord);
			String q = "select aes_decrypt(col1,'key1') from test" ;
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next())
			{
				String pw = rs.getString("aes_decrypt(col1,'key1')");
				byte[] by = new byte[20];
				by = pw.getBytes(); 
				for( int i=0 ; i<by.length;i++)
				System.out.println("i : "+i+".. by[i] : "+by[i]);
				System.out.println("Pw : "+pw);
			}
			stmt.close();
			conn.close();
        }
		catch(Exception e)
        {
	System.out.println("Error in connecting database" + e.getMessage());
                e.printStackTrace();
        }
		%>

Are you sure this is a server problem, not a problem of your JDBC driver? Had you tried to repeat with recent versions of server (4.1.x is no longer supported anyway, so nobody is going to fix any bug in it) and MySQL Connector/J (5.1.7)?

Tested in mhsql 5.0.36 also..Got wrong result only :(

Had you used recent version of Connector/J, as I've suggested? If you had, please, add the following code to your test case:

      this.rs = this.stmt.executeQuery("SHOW VARIABLES LIKE 'chara%'");
      while (this.rs.next()) {
        String name = this.rs.getString("Variable_name");
        String val = this.rs.getString("Value");
        System.out.println(name + ":\t" + val);
      }

before executing your query, and send the output.

Many thanks for your concern..

I don't know what is the connector version.Can you tel me how to find that?

One more thing.I've edited my.ini file to use default charset as utf8.

When I use  " show variables like 'char%'" command in command line client,it shows all values for character_set_client,connection,results as utf8. So there is no issues with charset.

Output of your test code : (character_Set_results doesn't show anything here but it displays 'utf8' when viewing through command line client)

character_set_client:	utf8

character_set_connection:	utf8

character_set_database:	utf8

character_set_results:	

character_set_server:	utf8

character_set_system:	utf8

You can get the driver version details this way:

    DatabaseMetaData dbmd = conn.getMetaData();
    System.out.println("getDriverVerision       :" + dbmd.getDriverVersion());
    System.out.println("getDriverMajorVerision  :" + dbmd.getDriverMajorVersion());
    System.out.println("getDriverMinorVerision  :" + dbmd.getDriverMinorVersion());
    System.out.println("getDriverName           :" + dbmd.getDriverName());

Also, please, modify your test case to use the following select:

select aes_decrypt(col1,'key1') as a, hex(aes_decrypt(col1,'key1')) as b from test

and print values for columns a and b of the result, byte by byte, as you did.

For 1st column,the result was 'valï¿½ï¿½'

And the byte values were

i : 0.. by[i] : 118

i : 1.. by[i] : 97

i : 2.. by[i] : 108

i : 3.. by[i] : -17

i : 4.. by[i] : -65

i : 5.. by[i] : -67

i : 6.. by[i] : -17

i : 7.. by[i] : -65

i : 8.. by[i] : -67

For 2nd column,the result was 76616CC2A3.

And the byte values were

i : 0.. by[i] : 55

i : 1.. by[i] : 54

i : 2.. by[i] : 54

i : 3.. by[i] : 49

i : 4.. by[i] : 54

i : 5.. by[i] : 67

i : 6.. by[i] : 67

i : 7.. by[i] : 50

i : 8.. by[i] : 65

i : 9.. by[i] : 51

Tested with connector /J version 5.0.8 too..Issue still persists :(

I used the below query to insert data.

update test set col1 = aes_encrypt('"+value+"','key1');  

where value is a string that contains £ symbol.

Hello Kravchuk,

Please help me out from this problem.This has been wasting my time for past 2 days.If this is a bug,then announce it here soon !

Thank you for the feedback.

Please provide output of SHOW CREATE TABLE test, insert into this table value aes_encrypt('£','key1') using your Java application (as you usually do it) and send us output of select hex(aes_encrypt('£','key1'));

I am sorry, we need output of select hex(col1) from test for row where you inserted aes_encrypt('£','key1')

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi Vsetar,

There is no feedback since Mar 2011, so I close this report as "Can't repeat".
Please, feel free to reopen it if the problem still exists with current driver version.