Bug #53672 Curly braces and single quote escaping error: SQLException
Submitted: 15 May 2010 17:41 Modified: 20 May 2010 10:50
Reporter: Manuel Francesco Cash Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.12 OS:Windows
Assigned to: CPU Architecture:Any
Tags: braces, curly, escape, quote, SqlException

[15 May 2010 17:41] Manuel Francesco Cash 
Description:
The following statement raises a:
java.sql.SQLException: Not a valid escape sequence: {')

"INSERT INTO t(string) VALUES ('{'), ('}'), ('\\\\\\''), ('{')"

This is only true if all rows are present in the statement.

How to repeat:
Use this code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class MySQLTest {

	public static void main(String[] args) throws Exception {

		Class.forName("com.mysql.jdbc.Driver");

		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "admin");
		Statement statement = connection.createStatement();

        statement.execute("DROP TABLE IF EXISTS t");

        statement.execute("CREATE TABLE t(string VARCHAR(32))");

        statement.execute("INSERT INTO t(string) VALUES ('{'), ('}'), ('\\\\\\''), ('{')");

		statement.close();
		connection.close();

	}

}

Suggested fix:
Workaround:

Escape "'" using "''" instead of "\\'":

"INSERT INTO t(string) VALUES ('{'), ('}'), ('\\\\'''), ('{')"
[20 May 2010 9:27] Tonci Grgin 
Hi Manuel and thanks for your report.

Can you please test with NO_BACKSLASH_ESCAPES SQL_MODE and notify me of result. If test still fails, please attach complete test case together with connection string and SQL_MODE settings.
[20 May 2010 9:35] Tonci Grgin 
Manuel, this is a duplicate of Bug#51313 which is already in process of fixing.

Bug#51527, There is a profound catch when inserting blob values using escapes together with sjis, cp932 and big5. These three charsets can have dangerous things in the second byte of a two-byte character: \ and '

So, using ' is out of question although it works for you.
[20 May 2010 10:50] Manuel Francesco Cash 
Sorry, I don't understand. How can I escape "'" without using "\"? With "''"? Using NO_BACKSLASH_ESCAPES SQL_MODE with the same statement, gives me the same error...
[20 May 2010 10:58] Tonci Grgin 
Manuel, you got me wrong... This happens sometimes when none's using their native language.

Anyways, your report is a duplicate of Bug#51313. Please monitor progress there.