Bug #111762 ClientPreparedQuery.asSql method works improperly with params containing quotes
Submitted: 14 Jul 16:08 Modified: 21 Nov 10:27
Reporter: Kyrylo Stolyetnyev Email Updates:
Status: Verified Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[14 Jul 16:08] Kyrylo Stolyetnyev
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();
        c = dataSource.getConnection();
	    s = c.prepareStatement(addRecordQuery);
    public void close() throws SQLException {
    	if(s != null) {
    	if(c != null) {
    		c = null;
    	if(dataSource != null) {
    		dataSource = null;
    public void clearBatch() throws SQLException {
    	if(s != null)
    public void addBatch(String col1, String col2) throws SQLException {
    	if(s == null)
    	s.setString(1, col1);
    	s.setString(2, col2);
    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++) {
            String currentBatchCommand = q.asSql();
        return result.toString();
    public void submitSqlScript(String script) throws SQLException {

	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 {

    public void saveEntities() throws SQLException {
		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);
		}catch(SQLException ex) {
			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(...)
[17 Nov 23:57] Filipe Silva
Thanks Kyrylo Stolyetnyev for your interest in MySQL Connector/J and for filing this report.

Besides the fact that you are right and strings aren't being escaped, why are you doing  this at all? This is exactly what "rewriteBatchedStatements=true" is for.

Also note that PreparedQuery.asSql() is intended mainly for internal consumption, usually for logging or profiling, and the API can change at any moment without any notice. The rendered SQL statements aren't expected to be syntactically correct all times.

I'm verifying this bug only because there's a little bit more we can do to improve this but there are obvious limitations and no one should use this in the way you are trying to do.
[21 Nov 10:27] Kyrylo Stolyetnyev
Thank you for looking into this problem. I know that the way I use this code is not an official way. The reason why I implemented my code in such way is the following. We have a huge number of business objects of different types, multiple prepared statements and no unlimited memory. So I'm concatenating all rendered sql instructions in a buffer and send them to the DB as soon as the buffer is full. Sometimes the size of resulting sql script sent to the DB is longer then max StringBuffer size 2GB. So I decided to control this sensitive part of my code myself. Sometimes I also have a need to dump this huge concatenated sql script into a file.