| Bug #48820 | Inconsistent behavior of getString() for rs <-> cached rs | ||
|---|---|---|---|
| Submitted: | 16 Nov 2009 21:26 | Modified: | 18 Dec 2015 18:22 |
| Reporter: | John Mikle | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | Connector / J | Severity: | S1 (Critical) |
| Version: | 5.1.10 | OS: | MacOS (10.6.2) |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
| Tags: | cachedrowset, Connector/J, password function | ||
[17 Nov 2009 11:45]
Tonci Grgin
Hi John and thanks for your report.
You will need to be more exact as I can't repeat this using 5.1.31 and latest sources from c/J repo:
Server version: 5.1.31-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT PASSWORD ('SOMETHING');
+-------------------------------------------+
| PASSWORD ('SOMETHING') |
+-------------------------------------------+
| *7AFEFD08B6B720E781FB000CAA418F54FA662626 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like "%old%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| key_cache_age_threshold | 300 |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | OFF |
+-------------------------+-------+
this.rs = this.stmt.executeQuery("SELECT PASSWORD ('SOMETHING')");
this.rs.first();
assertEquals("*7AFEFD08B6B720E781FB000CAA418F54FA662626", this.rs.getString(1));
c/J appears to be just passing whatever comes from server (which is correct thing to do) so I'll need better test case with connection string, server variables that might influence result, JDK version and so on.
[17 Nov 2009 11:46]
Tonci Grgin
java.vm.version : 1.5.0_17-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_17-b04 os.name : Windows Server 2008 os.version : null sun.management.compiler : HotSpot Client Compiler &autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useUnicode=true&characterSetResults=utf8&useInformationSchema=true&useServerPrepStmts=true&cacheResultSetMetadata=true&useAffectedRows=false&traceProtocol=true&useColumnNamesInFindColumn=true
[17 Nov 2009 12:06]
Tonci Grgin
Can't repeat with 5.4.3beta Winx64 either...
Server version: 5.4.3-beta-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT PASSWORD ('SOMETHING');
+-------------------------------------------+
| PASSWORD ('SOMETHING') |
+-------------------------------------------+
| *7AFEFD08B6B720E781FB000CAA418F54FA662626 |
+-------------------------------------------+
1 row in set (0.00 sec)
Time: 0,385
OK (1 test)
[17 Nov 2009 16:34]
John Mikle
Upon rearranging Java code to provide a better way to replicate the error, I discovered that Connector/J does return the correct hash value by retrieving the string from the ResultSet. However, if I populate a CachedRowSetImpl with the ResultSet I get the erroneous value. Here is the result:
2009-11-17 09:30:00,180 DEBUG (com.sldc.data.LoginDO;67) Will perform password () using : select PASSWORD('SOMETHING') as pwd
2009-11-17 09:30:00,187 DEBUG (com.sldc.data.LoginDO;85) Retrieved hash value from rs: *7AFEFD08B6B720E781FB000CAA418F54FA662626
2009-11-17 09:30:00,188 DEBUG (com.sldc.data.LoginDO;92) Retrieved hash value from crs: [B@4941ca7
... and here is the code:
String sql = "select PASSWORD('" + password + "') as pwd";
String hashValue = null;
Connection con = null;
CachedRowSetImpl crs;
AppParams.getLogger().debug("Will perform password () using : " + sql);
try {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost/sldc?user=jmikle&password=corky");
}
catch (ClassNotFoundException e) {
AppParams.getLogger().error("Driver class not found");
}
catch (Exception e) {
e.printStackTrace();
AppParams.getLogger().error("Error Trace in getConnection() : " + e.getMessage());
}
if (con != null) {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
rs.first();
hashValue = rs.getString("pwd");
AppParams.getLogger().debug("Retrieved hash value from rs: " + hashValue);
// Load the CachedRowSet with the ResultSet
rs.beforeFirst();
crs = new CachedRowSetImpl();
crs.populate(rs);
crs.first();
hashValue = crs.getString("pwd");
AppParams.getLogger().debug("Retrieved hash value from crs: " + hashValue);
stmt.close();
con.close();
}
else {
AppParams.getLogger().error("Connection returned null");
}
}
catch (SQLException e) {
e.printStackTrace();
}
All other db access in my application uses a ResultSet populated into a CachedRowSetImpl and everything works fine except the select password ().
[18 Nov 2009 7:29]
Tonci Grgin
John, I do not think there is a bug here. Problem is you are fetching byte-array as a String. Check your return value, it's marked binary:
2009-11-17 09:30:00,188 DEBUG (com.sldc.data.LoginDO;92) Retrieved hash value from crs: [B@4941ca7 <<<<
and this is why:
mysql> SELECT PASSWORD("password") AS PWD;
Field 1: `PWD`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63) <<<<
Length: 41
Max_length: 41
Decimals: 31
Flags: NOT_NULL BINARY <<<<
+-------------------------------------------+
| PWD |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set (0.00 sec)
Same will happen for CONCAT in most cases:
mysql> SELECT CONCAT(1, "password") AS PWD1;
Field 1: `PWD1`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: binary (63)
Length: 9
Max_length: 9
Decimals: 31
Flags: NOT_NULL BINARY
+-----------+
| PWD1 |
+-----------+
| 1password |
+-----------+
1 row in set (0.00 sec)
This is all well documented under functions reference in manual.
[18 Nov 2009 7:35]
Tonci Grgin
Flags and ad-hoc queries: Bug#10491
[18 Nov 2009 15:22]
John Mikle
Tonci: Hello and good morning (my TZ). Thanks for working through this with me. I still think there is a potential issue here. As documented in http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password, a select PASSWORD('str') should return the 41 byte hash value. I believe that something changed from Connector/J 5.0.5 to 5.1.10, and it involves the population of the ResultSet into a CachedRowSet. I have reproduced the testing code (with minor changes and shown below with line numbers). Immediately below are the testing results with nothing changed but the version of Connector/J. The results using 5.0.5 are: 2009-11-18 07:27:50,744 DEBUG (com.sldc.data.LoginDO;87) Will perform password () using : select PASSWORD('corky') as pwd 2009-11-18 07:27:50,753 DEBUG (com.sldc.data.LoginDO;105) Retrieved hash value from resultSet: *D2E253457610AA3FA48DC8ED6D8AA9A6BC622A75 2009-11-18 07:27:50,755 DEBUG (com.sldc.data.LoginDO;113) Retrieved hash value from crs: *D2E253457610AA3FA48DC8ED6D8AA9A6BC622A75 The results using 5.1.10 are: 2009-11-18 07:26:11,283 DEBUG (com.sldc.data.LoginDO;87) Will perform password () using : select PASSWORD('corky') as pwd 2009-11-18 07:26:11,289 DEBUG (com.sldc.data.LoginDO;105) Retrieved hash value from resultSet: *D2E253457610AA3FA48DC8ED6D8AA9A6BC622A75 2009-11-18 07:26:11,290 DEBUG (com.sldc.data.LoginDO;113) Retrieved hash value from crs: [B@75151a72 Also of interest is that successive queries using 5.1.10 yield different return values from the CachedRowSet; thus: 2009-11-18 08:05:08,784 DEBUG (com.sldc.data.LoginDO;87) Will perform password () using : select PASSWORD('corky') as pwd 2009-11-18 08:05:08,793 DEBUG (com.sldc.data.LoginDO;105) Retrieved hash value from resultSet: *D2E253457610AA3FA48DC8ED6D8AA9A6BC622A75 2009-11-18 08:05:08,794 DEBUG (com.sldc.data.LoginDO;113) Retrieved hash value from crs: [B@4723646 I appreciate your help with this. It is not a show-stopper since I have altered by production code to only use the ResultSet when executing a password function. Everything else in 5.1.10 seems to work fine. As a note: my use of the CachedRowSet is to allow every query to obtain a somewhat persistent ResultSet but to allow the connection and statement to close. Here is the code I tested: 84 Connection con = null; 85 CachedRowSetImpl crs; 86 String hash; 87 AppParams.getLogger().debug("Will perform password () using : " + sql); 88 try { 89 try { 90 Class.forName("com.mysql.jdbc.Driver").newInstance(); 91 con = DriverManager.getConnection("jdbc:mysql://localhost/sldc?user=jmikle&password=corky"); 92 } 93 catch (ClassNotFoundException e) { 94 AppParams.getLogger().error("Driver class not found"); 95 } 96 catch (Exception e) { 97 e.printStackTrace(); 98 AppParams.getLogger().error("Error Trace in getConnection() : " + e.getMessage()); 99 } 100 if (con != null) { 101 Statement stmt = con.createStatement(); 102 ResultSet resultSet = stmt.executeQuery(sql); 103 resultSet.first(); 104 hash = resultSet.getString("pwd"); 105 AppParams.getLogger().debug("Retrieved hash value from resultSet: " + hash); 106 // @fix the following does not work with Connector/J 5.1.10 -- the above does work 107 // Load the CachedRowSet with the ResultSet 108 resultSet.beforeFirst(); 109 crs = new CachedRowSetImpl(); 110 crs.populate(resultSet); 111 crs.first(); 112 hash = crs.getString("pwd"); 113 AppParams.getLogger().debug("Retrieved hash value from crs: " + hash); 114 stmt.close(); 115 con.close(); 116 } 117 else { 118 AppParams.getLogger().error("Connection returned null"); 119 } 120 } 121 catch (SQLException e) { 122 e.printStackTrace(); 123 }
[21 Nov 2009 3:27]
John Mikle
Tonci -- I do not know if this is a problem with the PASSWORD function or a problem with this particular type of query being populated into a CachedRowSet. In either case I believe there is a problem.
[23 Nov 2009 9:01]
Tonci Grgin
John, the problem is in this (and I repeat):
You can *not* read this kind of value Retrieved hash value from crs:[B@4723646
with
crs.getString("pwd");
You need to treat it as ByteArray.
Now, why would "normal" resultset allow for getString while cached rowset would not is a real question here. I'll consult.
[23 Nov 2009 10:15]
Tonci Grgin
John, after running few more tests, with your permission I'd like to change synopsis to: "Inconsistent behavior res <-> cached rs" and verify this report. Do you agree? Causes for change: Current synopsis does not describe problem well as it has nothing to do with particular function (PASSWORD) and can be repeated on many more ways.
[23 Nov 2009 17:01]
Tonci Grgin
Ok, changing synopsis on my own so I can mark this as verified and start fixing procedure. getString(x) on cached resultset does not function for ByteArray while for resultest from which cached rs is derived, it does. Workaround is acceptable, use getBytes if in such problem. Attaching test case. Remark: It might not be possible to do our "magic" with BINARY flags on cached resultset...
[23 Nov 2009 17:04]
Tonci Grgin
Test case, JVM and c-string inside. Tested with JDK 1.5
Attachment: TestBug48820.java (text/x-java), 2.82 KiB.
[23 Nov 2009 19:09]
John Mikle
Hello Tonci -- I absolutely agree that a new [change] in synopsis is required. Here is a new issue I found with Connector/J 5.1.10. Perhaps related or not, but it involves use of a CachedRowSet in 5.1.10. Using an alias for a column name that has an embedded space works in all cases using the ResultSet. However trying to retrieve the value in a CachedRowSet generates an error. This does not generate an error in C/J 5.0.5. Both results below are with no other changes than C/J.
I am using JVM 1.6.0_15-b03-219 on Mac OS X 10.6.2.
Let me know if I need to start a new one.
Thanks, John
Here is the test code:
137 public void testCrs () {
138 String insert = "insert into testing set firstName ='John';";
139 String newSql = "select firstName as 'first person' from testing";
140 try {
141 AppParams.getLogger().debug("insert a new row into testing: " + insert);
142 executeAnyStatement(insert);
143 AppParams.getLogger().debug("retrieve from ResultSet: " + newSql);
144 ResultSet rSet = getResultSet(newSql);
145 rSet.first();
146 AppParams.getLogger().debug("newSql using ResultSet: " + rSet.getString("first person"));
147 rSet.close();
148
149 rSet = getResultSet(newSql); // re-get the ResultSet
150 AppParams.getLogger().debug("retrieve from CachedRowSet: " + newSql);
151 CachedRowSetImpl testCrs = new CachedRowSetImpl();
152 testCrs.populate(rSet);
153 testCrs.first();
154 AppParams.getLogger().debug("newSql using CachedRowSet: " + testCrs.getString("first person"));
155 rSet.close();
156 }
157 catch (SQLException e) {
158 e.printStackTrace();
159 AppParams.getLogger().error("Error : " + e.getMessage());
160 }
161 }
Here is the table creation for testing:
CREATE TABLE `sldc`.`testing` (
`testingID` INT NOT NULL AUTO_INCREMENT,
`firstName` TEXT NOT NULL,
PRIMARY KEY (`testingID`)
)
CHARACTER SET utf8;
Here is the result using C/J 5.0.5:
2009-11-23 11:23:16,368 DEBUG (com.sldc.data.LoginDO;141) insert a new row into testing: insert into testing set firstName ='John';
2009-11-23 11:23:16,377 DEBUG (com.sldc.data.LoginDO;143) retrieve from ResultSet: select firstName as 'first person' from testing
2009-11-23 11:23:16,402 DEBUG (com.sldc.data.LoginDO;146) newSql using ResultSet: John
2009-11-23 11:23:16,415 DEBUG (com.sldc.data.LoginDO;150) retrieve from CachedRowSet: select firstName as 'first person' from testing
2009-11-23 11:23:16,418 DEBUG (com.sldc.data.LoginDO;154) newSql using CachedRowSet: John
Here is the result using C/J 5.1.10:
2009-11-23 11:21:53,930 DEBUG (com.sldc.data.LoginDO;141) insert a new row into testing: insert into testing set firstName ='John';
2009-11-23 11:21:53,938 DEBUG (com.sldc.data.LoginDO;143) retrieve from ResultSet: select firstName as 'first person' from testing
2009-11-23 11:21:53,947 DEBUG (com.sldc.data.LoginDO;146) newSql using ResultSet: John
2009-11-23 11:21:53,953 DEBUG (com.sldc.data.LoginDO;150) retrieve from CachedRowSet: select firstName as 'first person' from testing
2009-11-23 11:21:53,957 ERROR (com.sldc.data.LoginDO;159) Error : Invalid column name
Here is the stack trace (relevant portion):
java.sql.SQLException: Invalid column name
at com.sun.rowset.CachedRowSetImpl.getColIdxByName(CachedRowSetImpl.java:1631)
at com.sun.rowset.CachedRowSetImpl.getString(CachedRowSetImpl.java:2473)
at com.sldc.data.LoginDO.testCrs(LoginDO.java:154)
[4 Dec 2009 23:41]
John Mikle
I believe this issue is critical -- thus changed its severity.
[7 Dec 2009 7:53]
Tonci Grgin
John, it would be nice to move last post to new bug report. We do not endorse reporting two problems in one bug report.
[7 Dec 2009 17:08]
John Mikle
Tonci -- suggestion taken. New bug report #49516 submitted today. Thanks.
[7 May 2010 18:44]
Mark Matthews
This is one of those cases where one needs to use "functionsNeverReturnBlobs=true", as the fixes I've tried to make this transparent break all of the other use cases where the server is slightly misleading about the actual underlying type. This can't be really fixed in the JDBC driver until such time as type fidelity is maintained by mysqld, which is why I'm marking it "to be fixed later", although there is the workaround listed in the previous paragraph.
[18 Dec 2015 18:21]
Filipe Silva
Posted by developer: Closing this issue as it is not verifiable when running against current GA servers. It is still verifiable when running with MySQL 5.1, but in this case the result of PASSWORD() is returned as binary. While our ResultSet implmentation performs an automatic conversion between String and Byte array, the CachedRowSetImpl does not. This means that this field must be obtained using the method getBytes() instead. Please reopen or file a new bug if is still a problem for you.
Description: Using MySQL CE Ver mysql-5.4.3-beta-osx10.5-x86_64. The SELECT PASSWORD ('SOMETHING') returns a random, not 41 byte hash value. Performing the same select with mysql or QueryBrowser does return the correct hash value. Connector/J version 5.0.x performs correctly. How to repeat: // get a connection Connection con = openMySqlConnection(); if (con != null) { try { // retrieve the data for this DO // get a statement object stmt = getStmt(con); // Execute the sql // Do some timing to isolate long running queries timeStart = System.currentTimeMillis(); ResultSet rs = stmt.executeQuery("SELECT PASSWORD ('SOMETHING');"); ...