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
Triage: Triaged: D2 (Serious)

[3 Mar 2010 22:07] Stephane Giron
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);
[8 Apr 2010 7:34] Tonci Grgin
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.
[8 Apr 2010 7:36] Tonci Grgin
Escalated to Mark.
[11 Apr 2010 15:58] Stephane Giron
Hi Tonci,

Thanks for the update.
Cheers

Stephane
[7 May 2010 16:10] Mark Matthews
Fixed for 5.1.13.
[13 May 2010 14:44] Tony Bedford
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.