Description:
When ClientPreparedQuery.asSql() method is called for prepared statements with string parameters which contain a single quote the resulting query has not escaped single quote and this breaks submitting this query to the DB. This relates to new implementations of StringValueEncoder.getString(...) method which does not encode quotes.
This behavior is present in all versions since 8.0.29
Our application has huge amount of prepared statements which are used to generate a long sql script which is submitted in one go to the db server
How to repeat:
Please consider the test case below:
// ------------------ class App --------------------------
package escaping_bug;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.mysql.cj.ClientPreparedQuery;
import com.mysql.cj.jdbc.MysqlDataSource;
import com.mysql.cj.jdbc.JdbcStatement;
/*
create table SOME_TABLE (
id bigint(20) NOT NULL AUTO_INCREMENT,
col1 varchar(255) not null,
col2 varchar(255) not null,
PRIMARY KEY (id)
)
*/
public class App
{
private String dbURL;
private String user;
private String password;
private String addRecordQuery = "INSERT INTO SOME_TABLE (col1, col2) values (?,?)";
private MysqlDataSource dataSource;
private Connection c;
private PreparedStatement s;
public static void main( String[] args )
{
System.out.println( "Hello World!" );
}
public App () {
}
public void init() throws SQLException {
dataSource = new MysqlDataSource();
dataSource.setUrl(dbURL);
dataSource.setUser(user);
dataSource.setPassword(password);
dataSource.setAllowMultiQueries(true);
dataSource.setRewriteBatchedStatements(true);
c = dataSource.getConnection();
s = c.prepareStatement(addRecordQuery);
}
public void close() throws SQLException {
if(s != null) {
s.close();
}
if(c != null) {
c.close();
c = null;
}
if(dataSource != null) {
dataSource = null;
}
}
public void clearBatch() throws SQLException {
if(s != null)
s.clearBatch();
}
public void addBatch(String col1, String col2) throws SQLException {
if(s == null)
init();
s.setString(1, col1);
s.setString(2, col2);
s.addBatch();
}
public String extractBatchedCommands() throws SQLException{
StringBuilder result = new StringBuilder();
JdbcStatement mysqlSt = s.unwrap(JdbcStatement.class);
ClientPreparedQuery q = (ClientPreparedQuery) mysqlSt.getQuery();
int numInTheBatch = q.getBatchedArgs() == null ? 0 : q.getBatchedArgs().size();
for(int i = 0; i<numInTheBatch; i++) {
q.setBatchCommandIndex(i);
String currentBatchCommand = q.asSql();
result.append(currentBatchCommand);
result.append(";");
}
return result.toString();
}
public void submitSqlScript(String script) throws SQLException {
s.execute(script);
}
public void setDbURL(String dbURL) {
this.dbURL = dbURL;
}
public void setUser(String user) {
this.user = user;
}
public void setPassword(String password) {
this.password = password;
}
}
// ------------------ class AppTest
package escaping_bug;
import org.junit.jupiter.api.Test;
import java.sql.SQLException;
/**
* Unit test for simple App.
*/
public class AppTest {
private App app = new App();
private void initialise() throws SQLException {
app.setDbURL("jdbc:mysql://localhost:3306/dev?useUnicode=yes&characterEncoding=UTF-8&enabledTLSProtocols=TLSv1.2&autocommit=0");
app.setUser("user");
app.setPassword("pwd");
app.init();
}
@Test
public void saveEntities() throws SQLException {
initialise();
try {
app.addBatch("string with \' single quote", "another string");
app.addBatch("one more string with \' single quote", "another string");
String batchCommands = app.extractBatchedCommands();
System.out.println("Generated Script: "+batchCommands);
app.submitSqlScript(batchCommands);
}catch(SQLException ex) {
ex.printStackTrace();
throw ex;
}
}
}
The test fails with an exception:java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'single quote','another string');INSERT INTO SOME_TABLE (col1, col2) values ('one' at line 1
Suggested fix:
A fix should be made here https://github.com/mysql/mysql-connector-j/commit/4c98c11eb93dd56c01b9f5945f0e16137c585a39
In class StringValueEncoder method getString(...) at line 191 value 'x' should be escaped as it is done in method getBytes(...)
Description: When ClientPreparedQuery.asSql() method is called for prepared statements with string parameters which contain a single quote the resulting query has not escaped single quote and this breaks submitting this query to the DB. This relates to new implementations of StringValueEncoder.getString(...) method which does not encode quotes. This behavior is present in all versions since 8.0.29 Our application has huge amount of prepared statements which are used to generate a long sql script which is submitted in one go to the db server How to repeat: Please consider the test case below: // ------------------ class App -------------------------- package escaping_bug; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import com.mysql.cj.ClientPreparedQuery; import com.mysql.cj.jdbc.MysqlDataSource; import com.mysql.cj.jdbc.JdbcStatement; /* create table SOME_TABLE ( id bigint(20) NOT NULL AUTO_INCREMENT, col1 varchar(255) not null, col2 varchar(255) not null, PRIMARY KEY (id) ) */ public class App { private String dbURL; private String user; private String password; private String addRecordQuery = "INSERT INTO SOME_TABLE (col1, col2) values (?,?)"; private MysqlDataSource dataSource; private Connection c; private PreparedStatement s; public static void main( String[] args ) { System.out.println( "Hello World!" ); } public App () { } public void init() throws SQLException { dataSource = new MysqlDataSource(); dataSource.setUrl(dbURL); dataSource.setUser(user); dataSource.setPassword(password); dataSource.setAllowMultiQueries(true); dataSource.setRewriteBatchedStatements(true); c = dataSource.getConnection(); s = c.prepareStatement(addRecordQuery); } public void close() throws SQLException { if(s != null) { s.close(); } if(c != null) { c.close(); c = null; } if(dataSource != null) { dataSource = null; } } public void clearBatch() throws SQLException { if(s != null) s.clearBatch(); } public void addBatch(String col1, String col2) throws SQLException { if(s == null) init(); s.setString(1, col1); s.setString(2, col2); s.addBatch(); } public String extractBatchedCommands() throws SQLException{ StringBuilder result = new StringBuilder(); JdbcStatement mysqlSt = s.unwrap(JdbcStatement.class); ClientPreparedQuery q = (ClientPreparedQuery) mysqlSt.getQuery(); int numInTheBatch = q.getBatchedArgs() == null ? 0 : q.getBatchedArgs().size(); for(int i = 0; i<numInTheBatch; i++) { q.setBatchCommandIndex(i); String currentBatchCommand = q.asSql(); result.append(currentBatchCommand); result.append(";"); } return result.toString(); } public void submitSqlScript(String script) throws SQLException { s.execute(script); } public void setDbURL(String dbURL) { this.dbURL = dbURL; } public void setUser(String user) { this.user = user; } public void setPassword(String password) { this.password = password; } } // ------------------ class AppTest package escaping_bug; import org.junit.jupiter.api.Test; import java.sql.SQLException; /** * Unit test for simple App. */ public class AppTest { private App app = new App(); private void initialise() throws SQLException { app.setDbURL("jdbc:mysql://localhost:3306/dev?useUnicode=yes&characterEncoding=UTF-8&enabledTLSProtocols=TLSv1.2&autocommit=0"); app.setUser("user"); app.setPassword("pwd"); app.init(); } @Test public void saveEntities() throws SQLException { initialise(); try { app.addBatch("string with \' single quote", "another string"); app.addBatch("one more string with \' single quote", "another string"); String batchCommands = app.extractBatchedCommands(); System.out.println("Generated Script: "+batchCommands); app.submitSqlScript(batchCommands); }catch(SQLException ex) { ex.printStackTrace(); throw ex; } } } The test fails with an exception:java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'single quote','another string');INSERT INTO SOME_TABLE (col1, col2) values ('one' at line 1 Suggested fix: A fix should be made here https://github.com/mysql/mysql-connector-j/commit/4c98c11eb93dd56c01b9f5945f0e16137c585a39 In class StringValueEncoder method getString(...) at line 191 value 'x' should be escaped as it is done in method getBytes(...)