MySQL Bugs: #10155: PreparedStatement detects question marks inside double quotes

Bug #10155	PreparedStatement detects question marks inside double quotes
Submitted:	25 Apr 2005 18:43	Modified:	2 Aug 2005 18:13
Reporter:	Paul Bonfanti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.8	OS:	Windows (Windows XP)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
If a prepared statement is passed a string that contains literal strings surrounded by double quotes, MySQL will detect question marks in the literal strings and expect parameter values for them when it shouldn't.  The example below used to reproduce the problem is a very simple example.  From looking at the example one would think using the work around should be trivial.  The problem is a customer has an existing application with thousands of prepared statements that use the syntax that is causing the problem.  For them to modify this code is non-trivial.  Note that this problem only occurs with the MySQL JDBC driver.  If the JDBC-ODBC bridge is used with the MySQL ODBC driver then the problem doesn't occur.

How to repeat:
Execute the following java application:

import java.sql.*;

public class prepStat
{
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/test";
	
        // Using the JDBC-ODBC bridge works
	//private static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
	//private static final String url = "jdbc:odbc:MySQLODBC";
	
	private static final String user = "root";
	private static final String pass = "root";

	private static Connection conn;
	private static PreparedStatement pstmt;
	private static Statement stmt;
	private static ResultSet rs;

	public static void main( String[] args )
		throws Exception
	{
		//DriverManager.setLogStream( System.out );

		Class.forName(driver);

		// Get the connection
		conn = DriverManager.getConnection( url, user, pass );

		stmt = conn.createStatement();
		try{
		stmt.execute( "drop table bd_question_mark" );
		}catch( Throwable t ){}
		stmt.close();

		stmt = conn.createStatement();
		stmt.execute( "create table bd_question_mark(c varchar(255)  NULL)" );
		stmt.close();

		// Using this syntax works but requires escaping the single quote
		//pstmt = conn.prepareStatement( "insert into bd_question_mark values( 'Test question mark? Test single quote''' )" );

		// Using this syntax fails with the JDBC driver
		pstmt = conn.prepareStatement( "insert into bd_question_mark values( \"Test question mark? Test single quote'\" )" );

		pstmt.executeUpdate();
		pstmt.close();

		stmt = conn.createStatement();
		rs = stmt.executeQuery( "SELECT c FROM bd_question_mark" );
		while ( rs.next() )
			System.out.println( rs.getString( 1 ) );

		rs.close();
		stmt.close();
		conn.close();
	}
}

Suggested fix:
Have the prepared statement code ignore question marks that are surrounded by double quotes.

In the constructor of the inner class ParseInfo of the class PreparedStatement, I changed the following code:

                if (!inQuotedId) {
                    if ((c == '\'') && (pre1 == '\\') && (pre2 == '\\')) {
                        inQuotes = !inQuotes;
                    } else if ((c == '\'') && (pre1 != '\\')) {
                        inQuotes = !inQuotes;
                    }
                }

to:

                if (!inQuotedId) {
                    if (((c == '\'') || (c == '"')) && (pre1 == '\\') && (pre2 == '\\')) {
                        inQuotes = !inQuotes;
                    } else if (((c == '\'') || (c == '"')) && (pre1 != '\\')) {
                        inQuotes = !inQuotes;
                    }
                }

and this solved the problem.

Updated version of PreparedStatement with fix for this bug.

Attachment: PreparedStatement.java (text/java), 105.21 KiB.

The fix in my last comment didn't take into account a single quote within double quotes and a double quote within single quotes.  I've attached a PreparedStatement.java file to this bug that contains a fix that takes this into account.

Fixed in 3.1.10.