Bug #33302 Escaped character is escaped in PreparedStatement

• Submitted: 17 Dec 2007 23:27
• Modified: 18 Dec 2007 1:50
• Reporter: James Casey
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S2 (Serious)
• Version: MySQL-Connector for Java 5.0.7
• OS: Linux
• CPU Architecture: Any

[17 Dec 2007 23:27] James Casey

Description:
After calling the setString(int, String) method on a PreparedStatement and passing it a string that contains an escaped character, "\'", the string that is passed to MySQL for processing looks like this: "\\\'".  The result is that the backslash character (\) gets stored in the database.

This is a particular problem with file names, since the backslash char is not allowed in file names in NTFS.

Note: Attempting to turn off escape processing prior to setting the string on the PreparedStatement has no effect.

How to repeat:
STEP 1: Create the table, "testTable, with one VARCHAR column, "FileName"

------------

STEP 2: Write a sort Java program that executes the following JDBC code:

String escString = "funny\'FileName";

PreparedStatement pstmt = conn.prepareStatment("INSERT INTO testTable (FileName) VALUES (?)");

pstmt.setString(1, escString);

int retCode = pstmt.executeUpdate();

STEP 3: Inspect the row inserted by the JDBC code, and note that the value of FileName = "funny\'FileName".

Step 4:  If you execute this program using a debugger, and inspect the value of pstmt after the setString() method is called, you will see the INSERT statement looks like this:  "INSERT INTO testTable (FileName) VALUES (funny\\\'FileName)"

[18 Dec 2007 1:50] Mark Matthews

PreparedStatement.setString() is not expecting to see "mysql escaped" characters, just "plain" Java Strings as per the JDBC specification (and ODBC specification which is was inspired by, and the SQL CLI, etc), so the rules for Java escaping apply, not the ones for MySQL. 

PreparedStatements do any (necessary) mysql-style escaping on your behalf before sending it to the backend.