Description:
select '{"','}'
fails with
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}'' at line 1
when run through the JBDC driver version 3.1.11. It works fine using the MySQL console.
(using MySQL 5.0.15-nt-max-log)
note that the select is
quote curlybra doublequote quote comma quote curlyket quote
The bug goes away if:
1) any character within the quotes is changed. (the bug is specific to curly brackets! changing one or both to a letter or to square brackets eliminates the bug).
2) the doublequote is escaped (changed to backslash doublequote).
3) remove quote comma quote.
The bug continues to show with more complicated selects that involve culry brackets, non-escaped double quote, and single quotes. (see the program below). (and fails with the very complicated real-life select that I distilled this bug from).
This bug did not show with earlier driver versions (3.0.9).
How to repeat:
package test;
import java.sql.*;
public class JDBCbug {
static String testSQL = "select '{\"','}'" ;
// note that testSQL contains a java escaped doublequote (the backslash does not get through to JDBC)
static String[] testSQLfails = new String[] {
"select '{\"','}'",
"select '{','\"','}'",
"select '{','\"','A','}'",
"select '{','x\"'||'A','}'",
"select '{','x\"'||('A'),'}'",
"select '{','x\"'||('A'||'z'),'}'",
"select '{','x\"'||('A'='z'),'}'",
"select '{','x\"'||('A'='z'||'x'),'}'",
"select '{','x\"'||('A'='z'||'x'||'y'),'}'" ,
"select '{','x\"'||if('A'='z','x','y'),'}'" };
static String[] testSQLOK = new String[] {
"select 'A\"','}'",
"select '[\"','}'",
"select '{\"',']'" ,
"select '{\"}'",
"select '{','\\\"','}'",
"select '{','\"','A'",
"select 'A','\"','}'",
};
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection con = java.sql.DriverManager.getConnection("jdbc:mysql://127.0.0.1/test", "tester",null);
Statement stmt = con.createStatement();
ResultSet r;
try {
r = stmt.executeQuery(testSQL);
System.out.println("TEST SUCCEEDED! "+testSQL);
} catch (SQLException ex) {
System.out.println("TEST FAILED! "+testSQL);
System.out.println("TEST FAILED! "+ex.getMessage());
}
}
}
Description: select '{"','}' fails with You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}'' at line 1 when run through the JBDC driver version 3.1.11. It works fine using the MySQL console. (using MySQL 5.0.15-nt-max-log) note that the select is quote curlybra doublequote quote comma quote curlyket quote The bug goes away if: 1) any character within the quotes is changed. (the bug is specific to curly brackets! changing one or both to a letter or to square brackets eliminates the bug). 2) the doublequote is escaped (changed to backslash doublequote). 3) remove quote comma quote. The bug continues to show with more complicated selects that involve culry brackets, non-escaped double quote, and single quotes. (see the program below). (and fails with the very complicated real-life select that I distilled this bug from). This bug did not show with earlier driver versions (3.0.9). How to repeat: package test; import java.sql.*; public class JDBCbug { static String testSQL = "select '{\"','}'" ; // note that testSQL contains a java escaped doublequote (the backslash does not get through to JDBC) static String[] testSQLfails = new String[] { "select '{\"','}'", "select '{','\"','}'", "select '{','\"','A','}'", "select '{','x\"'||'A','}'", "select '{','x\"'||('A'),'}'", "select '{','x\"'||('A'||'z'),'}'", "select '{','x\"'||('A'='z'),'}'", "select '{','x\"'||('A'='z'||'x'),'}'", "select '{','x\"'||('A'='z'||'x'||'y'),'}'" , "select '{','x\"'||if('A'='z','x','y'),'}'" }; static String[] testSQLOK = new String[] { "select 'A\"','}'", "select '[\"','}'", "select '{\"',']'" , "select '{\"}'", "select '{','\\\"','}'", "select '{','\"','A'", "select 'A','\"','}'", }; public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver").newInstance(); java.sql.Connection con = java.sql.DriverManager.getConnection("jdbc:mysql://127.0.0.1/test", "tester",null); Statement stmt = con.createStatement(); ResultSet r; try { r = stmt.executeQuery(testSQL); System.out.println("TEST SUCCEEDED! "+testSQL); } catch (SQLException ex) { System.out.println("TEST FAILED! "+testSQL); System.out.println("TEST FAILED! "+ex.getMessage()); } } }