MySQL Bugs: #51704: Statement.executeBatch() does not honor setEscapeProcessing(false)

Bug #51704	Statement.executeBatch() does not honor setEscapeProcessing(false)
Submitted:	3 Mar 2010 22:07	Modified:	13 May 2010 14:44
Reporter:	Stephane Giron	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Any
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
When allowMultiQueries URL option is set to true, a call to Statement.executeBatch() will scan  the query for escape codes, even if setEscapeProcessing(false) was previously called.

This will eventually trigger errors if the escape processing fails parsing correctly (http://bugs.mysql.com/bug.php?id=51313).

How to repeat:
// Get a connection
conn = DriverManager.getConnection("jdbc:mysql://.../..."
                    + "user=myUser&password=myPass&allowMultiQueries=true");

// Create test table 
stmt = conn.createStatement();
stmt.executeUpdate("DROP TABLE IF EXISTS testData");
stmt.executeUpdate("create table testData (i integer primary key, val varchar(200),val2 varchar(200) )");
stmt.close();

// Run the executeBatch
stmt = conn.createStatement();
stmt.addBatch("insert into testData values (50, 'abcd \\\\\\' abcd {abcd \\\\\\' abcd {\\\\\\' abcd \\\\\\' } } abcd ', '')");
stmt.addBatch("insert into testData values (56, 'This is \\\\\\\' { going to -- break escape } processing ', '')");
stmt.addBatch("insert into testData values (57, 'This is \\\\\\'''', '{ going to break escape -- } processing ')");
stmt.setEscapeProcessing(false);
stmt.executeBatch();

When running this example, I get the following error message :

SQLException: Not a valid escape sequence: {abcd \\\' abcd {\\\' abcd \\\' } } abcd ', '');insert into testData values (56, 'This is \\\' { going to -- break escape } processing ', '');insert into testData values (57, 'This is \\\'''', '{ going to break escape -- } processing ');

whereas I don't expect escape processing to occur at all.

Suggested fix:
In method executeBatchUsingMultiQueries of StatementImpl class, the doEscapeProcessing property should be forwarded to the internal statement (batchStmt) which is then used to run the query.

Following code could be replaced :

if (this.doEscapeProcessing) {
	escapeAdjust = 2; /* We assume packet _could_ grow by this amount, as we're not sure how big statement will end up after escape processing */
}

By :

if (this.doEscapeProcessing) {
	escapeAdjust = 2; /* We assume packet _could_ grow by this amount, as we're not sure how big statement will end up after escape processing */
}
else
	batchStmt.setEscapeProcessing(false);

Stephane, my bad, I forgot of this report due to Bug#51313...

Verified as described by looking into sources. Correct patch provided.

Sorry again and thank you.

Escalated to Mark.

Hi Tonci,

Thanks for the update.
Cheers

Stephane

Fixed for 5.1.13.

An entry has been added to the 5.1.13 changelog:

When the allowMultiQueries connection string option was set to true, a call to Statement.executeBatch() scanned the query for escape codes, even though setEscapeProcessing(false) had been called previously.