MySQL Bugs: #31457: JDBC SQL Escape processing {fn ... confused by escaped quotes

Bug #31457	JDBC SQL Escape processing {fn ... confused by escaped quotes
Submitted:	8 Oct 2007 15:46	Modified:	2 Mar 2009 18:40
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	Jdbc-5.0.7	OS:	Any (atleast Windows and Linux)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Java: String s = "insert into temp values ('\\'b\\'') ,( '{}') ,('{f')";
used in a JDBC executeUpdate(s) yields a "Not a valid escape sequence" exception.

The JDBC code in com.mysql.jdbc.EscapeProcessor is looking for a matching "}" to the "{f", but should not be looking inside quoted strings.

Note:
 There has to be a pair of {} in the test case else the
/** Short circuit this code if we don't have a matching pair of "{}". -
		 * Suggested by Ryan Gustafason
*/
does not try to parse the sql text.
(This shortcut does not handle quoted strings, but it is just a short cut).

Experiment shows that it is the pair of escaped quotes that is causing the lightweight parser to be confused.

Specifically the problem is in EscapeTokenizer handling the sequence backslash-quote-quote while inQuotes=true. The backslash is passed-thru and the quote-quote is interpreted as a doubled-up quote, so it continues to believe it is inQuotes.

How to repeat:
package test;
import java.sql.*;
public class JDBCbug_2 {
    static String testSQL = "insert into temp values ('\\'b\\'') ,( '{}') ,('{f')" ;
    // note that testSQL contains a java escaped singlequote (the extra backslash does not get through to JDBC)
    
    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();
        try {
            stmt.executeUpdate(testSQL);
            System.out.println("TEST SUCCEEDED! "+testSQL);
        } catch (SQLException ex) {
            System.out.println("TEST FAILED! "+testSQL);
            System.out.println("TEST FAILED! "+ex.getMessage());
        }                
    }
}

Suggested fix:
Handle backslash correctly in EscapeTokenizer 
For example
change line 180 from
} else {

to

			} else if(c == '\\' & this.inQuotes) {
                            tokenBuf.append(c); // passthru  the backslash ...
                            if (this.pos + 1 < this.sourceLength) {
			       tokenBuf.append(this.source.charAt(this.pos + 1)); // and the escaped character
    			       this.pos++;
                            }
                            c=0; // prevent look-back seeing the backslash
                        }    else {

Hi Dave and thanks for your report. Verified as described on WinXP Pro SP2 using latest c/J sources.

Connected to 5.0.50-pb1046-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler

Time: 0,313
There was 1 error:
1) testBug17402(testsuite.simple.TestBug31457)java.sql.SQLException: Not a valid escape sequence: {f')
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:914)

Test case

Attachment: TestBug31457.java (text/java), 1.70 KiB.

This is a duplicate of bug#42253. Will be fixed in Connector/J 5.1.

Probably still present in 5.1.18

Using a test with randomly generated noisy strings, an INSERT of 

('string.3', 19, 2, 'S+K9#*I8N4aDhG\'FBE1K_b\'_QJAq&{&^ZzBCXIGBoOsbb&L<KO', NULL)

...among a long sequence of records preceding it, yields:

"Not a valid escape sequence: {&^ZzBCXIGBoOsbb&L<KO'"

But see Bug#4279, where they say:

  In any case, '{' is a _reserved_ character in JDBC unless is is _inside_ a
  '' sequence,  so you need to escape it with '\', or alternatively turn off
  escape processing altogether by calling .setEscapeProcessing(false) on the
  statement in question.

I may check that if more time.

David, please do check as problem reported in bug#42253 is fixed and regression test added. Also, do check your SQL_MODE for NO_BACKSLASH_ESCAPE (NO_BACKSLASH_ESCAPES enables an SQL standard compatibility mode, where backslash is not considered a special character. The result will be that queries will fail.).