jdbc-v2: JDBC escape processing rewrites bound String values — "{fn " in data corrupts the statement

[alex-clickhouse]

Description

jdbc-v2 applies JDBC escape-sequence processing to the fully parameter-inlined statement text, using regexes that have no notion of string literals. Because PreparedStatementImpl inlines every bound parameter into the SQL text before the statement is sent, the contents of bound String values get re-scanned as SQL syntax.

Any bound String value containing the four characters {fn is therefore rewritten: the {fn is deleted along with the next } anywhere in the statement.

jdbc-v2/src/main/java/com/clickhouse/jdbc/StatementImpl.java:87-98

public static String escapedSQLToNative(String sql) {
    ...
    // Replace function escape syntax {fn <function>} (e.g., {fn UCASE(name)})
    sql = sql.replaceAll("\\{fn ([^\\}]*)\\}", "$1");

[^\}]* crosses string-literal boundaries freely, so the } that gets deleted is usually not in the same value — or even the same row — as the {fn that triggered the match.

Call path (all defaults, nothing opt-in):

1. PreparedStatementImpl.setObject(...) inlines the value to SQL text immediately (PreparedStatementImpl.java:288-291 → encodeObject)
2. executeBatch() → executeInsertBatch() concatenates all rows into one INSERT … VALUES (…),(…),… string (PreparedStatementImpl.java:356-363)
3. StatementImpl.executeUpdateImpl → parseJdbcEscapeSyntax(sql) (StatementImpl.java:256)
4. escapeProcessingEnabled defaults to true (StatementImpl.java:69)

The value encoders themselves are correct — SQLUtils.escapeSingleQuotes properly escapes \ then '. The defect is that a later pass re-reads the finished literal as syntax. { and } are legal inside a ClickHouse string literal and cannot be escaped away.

Steps to reproduce

1. Create a table with a Map(String, String) column (any type whose literal syntax contains } will do).
2. INSERT via PreparedStatement, binding a java.util.Map whose value contains the substring {fn — e.g. Z!F3{fn .
3. executeBatch() fails with a server-side SYNTAX_ERROR, because the map literal's closing } was deleted.

Error Log or Exception StackTrace

java.sql.SQLException: Code: 62. DB::Exception: Cannot parse expression of type Map(String, String) here:
{'user': 'Z!F3','session': '8t+.5Eh &DdC','region': 'AIh<ju)&J','tier': 'ent',('/|c2t7',13446)),(61806,...
: While executing WaitForAsyncInsert. (SYNTAX_ERROR) (version 26.2.1.558 (official build))

	at com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:63)
	at com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:40)
	at com.clickhouse.jdbc.StatementImpl.executeUpdateImpl(StatementImpl.java:265)
	at com.clickhouse.jdbc.PreparedStatementImpl.executeInsertBatch(PreparedStatementImpl.java:365)
	at com.clickhouse.jdbc.PreparedStatementImpl.executeBatchImpl(PreparedStatementImpl.java:339)
	at com.clickhouse.jdbc.PreparedStatementImpl.executeBatch(PreparedStatementImpl.java:327)
Caused by: com.clickhouse.client.api.ServerException: Code: 62. DB::Exception: Cannot parse expression of type Map(String, String) here: ...
	at com.clickhouse.client.api.internal.HttpAPIClientHelper.readClickHouseError(HttpAPIClientHelper.java:514)

In that statement props['user'] was the 8-character value Z!F3{fn . Escape processing deleted {fn (4 chars) plus the map's closing } — exactly 5 characters — leaving 'Z!F3' and an unterminated map literal.

Expected Behaviour

Escape processing must not rewrite text inside string literals. The statement above should insert the value Z!F3{fn verbatim.

Reproducing test

Pure unit test, no server required — escapedSQLToNative is public static. Save as jdbc-v2/src/test/java/com/clickhouse/jdbc/EscapeProcessingStringLiteralTest.java:

package com.clickhouse.jdbc;

import org.testng.annotations.Test;

import static org.testng.Assert.assertEquals;

public class EscapeProcessingStringLiteralTest {

    /**
     * A bound String value whose content happens to contain the four characters
     * "{fn ". The \{fn ([^\}]*)\} rule deletes those four characters and the next
     * '}' anywhere in the statement -- here the Map literal's own closing brace --
     * producing SQL the server cannot parse.
     */
    @Test
    public void fnEscapeMustNotBeAppliedInsideStringLiteral() {
        String sql = "INSERT INTO t VALUES ({'user': 'Z!F3{fn ','tier': 'ent'},('n',1))";
        assertEquals(StatementImpl.escapedSQLToNative(sql), sql);
    }

    /** Real escape sequences, appearing as syntax rather than inside a literal, must still be rewritten. */
    @Test
    public void realEscapeSequencesAreStillRewritten() {
        assertEquals(StatementImpl.escapedSQLToNative("SELECT {fn UCASE(name)} FROM t"), "SELECT UCASE(name) FROM t");
        assertEquals(StatementImpl.escapedSQLToNative("SELECT {d '2024-01-02'}"), "SELECT toDate('2024-01-02')");
        assertEquals(StatementImpl.escapedSQLToNative("SELECT {ts '2024-01-02 02:01:01'}"),
                "SELECT timestamp('2024-01-02 02:01:01')");
    }
}

Run with mvn -pl jdbc-v2 surefire:test -Dtest=EscapeProcessingStringLiteralTest. On main @ 3db04e09:

Running com.clickhouse.jdbc.EscapeProcessingStringLiteralTest
Tests run: 2, Failures: 1, Errors: 0, Skipped: 0

com.clickhouse.jdbc.EscapeProcessingStringLiteralTest.fnEscapeMustNotBeAppliedInsideStringLiteral <<< FAILURE!
java.lang.AssertionError:
  expected [INSERT INTO t VALUES ({'user': 'Z!F3{fn ','tier': 'ent'},('n',1))]
  but found [INSERT INTO t VALUES ({'user': 'Z!F3','tier': 'ent',('n',1))]
	at com.clickhouse.jdbc.EscapeProcessingStringLiteralTest.fnEscapeMustNotBeAppliedInsideStringLiteral(EscapeProcessingStringLiteralTest.java:24)

realEscapeSequencesAreStillRewritten passes, and should keep passing after a fix.

Code Example

End-to-end via PreparedStatement:

try (Connection conn = DriverManager.getConnection(url, props)) {
    try (Statement s = conn.createStatement()) {
        s.execute("CREATE TABLE IF NOT EXISTS t (id UInt64, props Map(String, String)) "
                + "ENGINE = MergeTree ORDER BY id");
    }

    Map<String, String> value = new LinkedHashMap<>();
    value.put("user", "Z!F3{fn ");   // 8 chars of ordinary printable ASCII
    value.put("tier", "ent");

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO t VALUES (?,?)")) {
        ps.setObject(1, 1L);
        ps.setObject(2, value);
        ps.addBatch();
        ps.executeBatch();          // SQLException: Code: 62 ... SYNTAX_ERROR
    }
}

Workaround: statement.setEscapeProcessing(false).

Why this is easy to miss

It is data-dependent and rare, so it presents as a flaky server error rather than a client bug. We hit it in a benchmark inserting randomPrintableASCII payloads: 29 four-character windows per row gives 29/95⁴ ≈ 1 failure per 2.8M rows, i.e. ~16% of a 500k-row run — so identical code passed two days running and failed on the third.

Two aspects worth flagging:

• It can corrupt data silently. The failure above is loud only because the deleted } happened to be structural. When the deleted } is itself inside another string literal, the statement stays valid and the wrong values are inserted, with no error.
• {fn is the practically reachable rule. The {d '…'} and {ts '…'} rules at StatementImpl.java:92-95 share the same literal-blindness, but I could not construct a data-driven trigger for them: a ' inside a value is escaped to \', so only a literal's own delimiter can complete {d ', and the pattern then needs a '} reachable without crossing another quote. Worth fixing together regardless, since a fix should be structural.

Suggested fix

Make escape processing literal-aware — skip regions inside '…', "…", backticks and comments — rather than running String.replaceAll over the whole statement. Alternatives: apply escape processing to the statement template before parameters are inlined, or (best for inserts) avoid inlining altogether and use the binary insert path.

Side note: the three replaceAll passes currently run over the entire inlined batch statement, which for a 100k-row batch is multiple MB of text per executeBatch() — so making this literal-aware may also be a throughput win.

Configuration

Environment

• Cloud (also reproduced against a local server)
• Client version: main @ 3db04e09 (reported as 0.10.0-rc1). The {fn rule reached its current form in fe6ca21d (2025-08-04); escapedSQLToNative dates to 31f03ea4.
• Language version: OpenJDK 17
• OS: Linux x86_64

ClickHouse Server

• ClickHouse Server version: 26.2.1.558 (Cloud); also reproduced on 26.6.1.1193
• ClickHouse Server non-default settings, if any: none relevant (async_insert was on in the Cloud case; not required to reproduce)
• CREATE TABLE statements for tables involved:

CREATE TABLE t (id UInt64, props Map(String, String)) ENGINE = MergeTree ORDER BY id;

• Sample data: id = 1, props = {'user': 'Z!F3{fn ', 'tier': 'ent'}