[jdbc-v2] setURL / setBlob(Blob) / setClob(Clob) / setNClob / setSQLXML put the value into the SQL without quotes, so the statement fails

Start in jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java, especially encodeObject and the listed setter methods; check JdbcUtils.java:159 for the DATALINK mapping. Trace how the SQL-text path handles strings, numbers, streams and unsupported objects, then run the relevant JDBC tests. Done when the affected setters produce valid SQL using their data, numeric values and existing string and stream behavior remain intact, and unsupported values fail with SQLException.

Description

On the default SQL-text path (beta.row_binary_for_simple_insert off), PreparedStatementImpl writes some bound values into the SQL as bare text, without quotes. The server then rejects the statement with SYNTAX_ERROR (Code 62). These setters cannot work on this path:

• setURL(int, URL), and setObject with a java.net.URL (also with Types.DATALINK / JDBCType.DATALINK).
• setBlob(int, Blob), setClob(int, Clob), setNClob(int, NClob), setSQLXML(int, SQLXML), and setObject with a Blob / Clob. For these, the SQL gets the toString() of the object (for example javax.sql.rowset.serial.SerialBlob@73a3540), not its data.

The same code path also affects other non-numeric setObject values: Character, enum constants, OffsetTime, java.util.Date, Calendar. If the text is a valid identifier, the server reads it as a column reference and the statement does not fail. Example: setObject(1, 'a') in SELECT count() FROM t WHERE a <> ? runs WHERE a <> a and returns 0 instead of 2.

setString with the same text works. The InputStream / Reader overloads of setBlob / setClob also work. INSERT and SELECT are both affected.

Found by automated analysis of the client while working on a related jdbc-v2 setter report (#3334 covers the same setters on the RowBinary writer path; this issue is the SQL-text path). Verified against a live server, not by inspection.

Steps to reproduce

1. CREATE TABLE t (id Int32, v String) ENGINE = MergeTree ORDER BY id
2. Open a jdbc-v2 connection with default properties and prepare INSERT INTO t (id, v) VALUES (?, ?).
3. setInt(1, 1), setURL(2, new URL("http://example.com/a?b=c")), executeUpdate().

Error Log or Exception StackTrace

setURL:           Code: 62. DB::Exception: Cannot parse expression of type String here: http://example.com/a?b=c): ... (SYNTAX_ERROR) (version 26.9.1.1629 (official build))
setBlob(Blob):    Code: 62. DB::Exception: Expected ')' after the value of column `v` of type Nullable(String) here: javax.sql.rowset.serial.SerialBlob@73a3540): ... (SYNTAX_ERROR)
setClob(Clob):    Code: 62. DB::Exception: Expected ')' after the value of column `v` of type Nullable(String) here: javax.sql.rowset.serial.SerialClob@73a3540): ... (SYNTAX_ERROR)
setObject('a'):   Code: 47. DB::Exception: Unknown expression identifier `a`. In scope a ... (UNKNOWN_IDENTIFIER)

For SELECT ? AS v, system.query_log shows the SQL text the driver sent:

setURL(1, url)                      -> SELECT http://example.com/a?b=c AS v      exception_code 62
setString(1, url.toString())        -> SELECT 'http://example.com/a?b=c' AS v    exception_code 0

Results on main (one new table per row, columns (id Int32, v <type>, tail Float64 DEFAULT 1.5)):

Setter	Column type	Result
setString(url.toString())	String	OK, http://example.com/a?b=c
setURL	String, Nullable(String), LowCardinality(String), String DEFAULT 'x'	Code 62
setObject(URL), setObject(URL, Types.DATALINK), setObject(URL, JDBCType.DATALINK)	String	Code 62
setBlob(SerialBlob), setObject(SerialBlob)	Nullable(String)	Code 62, SQL has SerialBlob@…
setClob(SerialClob), setObject(SerialClob)	Nullable(String)	Code 62, SQL has SerialClob@…
setNClob(NClob)	Nullable(String)	Code 62, SQL has the toString() of the NClob
setSQLXML(SQLXML)	Nullable(String)	Code 62, SQL has the toString() of the SQLXML
setBlob(InputStream), setClob(Reader)	Nullable(String)	OK, abc
setObject(Character 'a'), setObject(enum RED)	String / Enum8('RED' = 1)	Code 47 UNKNOWN_IDENTIFIER
setObject(OffsetTime)	String	Code 62 (07:30Z)
setObject(java.util.Date), setObject(Calendar)	DateTime	Code 62 (Thu Jan 01 00:00:00 UTC 1970, java.util.GregorianCalendar[...])
setObject(BigInteger) / setObject(Long) / setObject(Float)	UInt64 / Int64 / Float32	OK

Expected Behaviour

The value is sent as a quoted, escaped string literal, as setString does, so the server stores the text. The server accepts only the quoted form:

INSERT INTO t (id, v) VALUES (1, http://example.com/a?b=c)    -> Code: 62. DB::Exception: Cannot parse expression of type String here: http://example.com/a?b=c)
INSERT INTO t (id, v) VALUES (2, 'http://example.com/a?b=c')  -> OK; SELECT returns http://example.com/a?b=c

For Blob / Clob / NClob / SQLXML, the data of the object is sent, not toString(). JDBC maps URL to DATALINK, and the driver already maps JDBCType.DATALINK to java.net.URL (jdbc-v2/.../internal/JdbcUtils.java:159).

Root cause

PreparedStatementImpl.encodeObject(Object, Long) (jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java:764) has one instanceof branch for each supported type. A value that matches no branch gets to the fallback at line 862:

return SQLUtils.escapeSingleQuotes(x.toString()); //Escape single quotes

This is correct for numbers (Integer, Long, BigInteger, BigDecimal, Float, …), which must stay unquoted. For all other types, it writes toString() into the SQL as raw text: it escapes the text but does not add quotes. setURL (:511), setBlob(int, Blob) (:400), setClob(int, Clob) (:406), setNClob(int, NClob) (:550), setSQLXML (:574) and the setObject overloads (:269–:303) all call encodeObject(x) with such a value. The same code is in v0.11.1.

Suggested fix

Make the fallback in encodeObject type-aware:

• Keep the unquoted form only for Number values.
• URL: a quoted string literal of toString(), as for String.
• Clob / NClob: encode getCharacterStream() like the Reader branch. Blob: encode getBinaryStream() like the InputStream branch. SQLXML: getString() as a quoted string.
• Character / Enum: a quoted string (Enum.name() for an enum).
• Any other type: throw SQLException (type not supported) instead of sending SQL that the server cannot parse, or reads as an identifier. Temporal types such as java.util.Date, Calendar and OffsetTime need a real conversion, not toString().

These must keep their current behavior: numbers stay unquoted (setObject(BigInteger) into UInt64, setObject(Long), setObject(Float)), setString with the same text, setBlob(InputStream) / setClob(Reader).

Code Example

Properties p = new Properties();
p.setProperty("user", "default");
p.setProperty("password", "<password>");
try (Connection conn = DriverManager.getConnection("jdbc:clickhouse://localhost:8123/default", p);
     Statement st = conn.createStatement()) {
    st.execute("CREATE TABLE t (id Int32, v Nullable(String)) ENGINE = MergeTree ORDER BY id");
    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO t (id, v) VALUES (?, ?)")) {
        ps.setInt(1, 1);
        ps.setURL(2, new URL("http://example.com/a?b=c"));        // Code 62
        // ps.setBlob(2, new SerialBlob("abc".getBytes()));       // Code 62, SQL has "SerialBlob@..."
        // ps.setClob(2, new SerialClob("abc".toCharArray()));    // Code 62, SQL has "SerialClob@..."
        // ps.setString(2, "http://example.com/a?b=c");           // OK
        ps.executeUpdate();
    }
}

Configuration

Client Configuration

// default jdbc-v2 connection: only user and password; beta.row_binary_for_simple_insert is not set (off)

Environment

• Cloud
• Client version: jdbc-v2 main at f9586aba0 (0.12.0-rc1-SNAPSHOT); the same code is in v0.11.1
• Language version: Java 17 (Temurin 17.0.21)
• OS: Linux (Docker)

ClickHouse Server

• ClickHouse Server version: 26.9.1.1629
• ClickHouse Server non-default settings, if any: the repository's CI test-server config; nothing related to this issue
• CREATE TABLE statements for tables involved: CREATE TABLE t (id Int32, v String) ENGINE = MergeTree ORDER BY id (same result with Nullable(String), LowCardinality(String), String DEFAULT 'x')
• Sample data for all these tables: not needed