[jdbc-v2] DatabaseMetaData getSchemas / getPrimaryKeys / getFunctions put the pattern into SQL without escaping

[polyglotAI-bot]

Description

DatabaseMetaDataImpl (jdbc-v2) puts the caller's argument into the SQL text of three metadata queries without escaping. Each one builds ... LIKE '" + pattern + "':

• getSchemas(String catalog, String schemaPattern): DatabaseMetaDataImpl.java:1801
• getPrimaryKeys(String catalog, String schema, String table): DatabaseMetaDataImpl.java:1253-1254
• getFunctions(String catalog, String schemaPattern, String functionNamePattern): DatabaseMetaDataImpl.java:1852

A single quote in the argument closes the string literal too early. This causes two problems:

1. A legal name that contains ' fails with Code: 62 ... SYNTAX_ERROR. ClickHouse accepts such names, for example CREATE DATABASE `a'b` .
2. The argument text becomes part of the SQL. getSchemas(null, "nomatch' OR '1'='1") returns all databases instead of none. getSchemas(null, "x' UNION ALL SELECT name, '' FROM system.users -- ") returns rows from system.users.

The sibling methods are correct. getTables binds the pattern as a PreparedStatement parameter (LIKE ?, line 1025), and getColumns uses SQLUtils.enquoteLiteral. With the same names, both return the correct rows.

Steps to reproduce

1. Create a database and a table that have a single quote in the name:

   CREATE DATABASE `lead_a'b`;
   CREATE TABLE `lead_a'b`.`t'1` (id Int32, v String) ENGINE MergeTree ORDER BY id;

2. Call conn.getMetaData().getSchemas(null, "lead_a'b").
3. Call getPrimaryKeys(null, "lead_a'b", "t'1") and getFunctions(null, null, "to'Int").

Results from jdbc-v2 main against ClickHouse 26.9.1.1629:

Call	Expected	Actual
getSchemas(null, "lead_a'b")	1 row: lead_a'b	SQLException, Code 62
getSchemas(null, "nomatch' OR '1'='1")	0 rows	8 rows (all databases)
getSchemas(null, "x' UNION ALL SELECT name, '' FROM system.users -- ")	0 rows	user names from system.users
getPrimaryKeys(null, "lead_a'b", "t'1")	1 row: id	SQLException, Code 62
getPrimaryKeys(null, "nomatch' OR '1'='1", null)	0 rows	206 rows from all databases
getFunctions(null, null, "to'Int")	0 rows	SQLException, Code 62
getFunctions(null, null, "nomatch' OR '1'='1")	0 rows	1918 rows (all functions)
getTables(null, "lead_a'b", "%", null) (contrast)	1 row	1 row (correct)
getColumns(null, "lead_a'b", "t'1", "%") (contrast)	2 rows	2 rows (correct)

Error Log or Exception StackTrace

getSchemas(null, "lead_a'b"):
java.sql.SQLException: Code: 62. DB::Exception: Single quoted string is not closed: Syntax error: failed at position 96 ('): '. . (SYNTAX_ERROR) (version 26.9.1.1629 (official build))

getPrimaryKeys(null, "lead_a'b", "t'1"):
java.sql.SQLException: Code: 62. DB::Exception: Single quoted string is not closed: Syntax error: failed at position 397 (b' AND system.tables.name ILIKE 't'1' ORDER BY COLUMN_NAME): b' AND system.tables.name ILIKE 't'1' ORDER BY COLUMN_NAME. . (SYNTAX_ERROR) (version 26.9.1.1629 (official build))

getFunctions(null, null, "to'Int"):
java.sql.SQLException: Code: 62. DB::Exception: Single quoted string is not closed: Syntax error: failed at position 307 ('): '. . (SYNTAX_ERROR) (version 26.9.1.1629 (official build))

Expected Behaviour

The methods must use the argument as a pattern value, not as SQL text. The server gives these results when the quote is escaped correctly (same WHERE clauses that the driver builds):

SELECT name FROM system.databases WHERE name LIKE 'lead_a''b'
-> lead_a'b

SELECT count() FROM system.databases WHERE name LIKE 'nomatch'' OR ''1''=''1'
-> 0

SELECT database, name, primary_key FROM system.tables
WHERE primary_key <> '' AND database ILIKE 'lead_a''b' AND name ILIKE 't''1'
-> lead_a'b  t'1  id

SELECT count() FROM system.functions WHERE name LIKE 'to''Int'
-> 0

Root cause

The three methods use plain string concatenation, for example (line 1801):

"WHERE name LIKE '" + (schemaPattern == null ? "%" : schemaPattern) + "'"

No part of the argument is escaped, so ' ends the literal and the rest of the argument is parsed as SQL.

Suggested fix

• Bind each argument as a PreparedStatement parameter, as getTables does (prepareStatement(sql) with LIKE ? / ILIKE ?). Keep the current LIKE / ILIKE operators and the null → % default.
• Prefer binding to SQLUtils.enquoteLiteral: enquoteLiteral doubles ' but does not escape \ ([client-v2, jdbc-v2] SQLUtils.enquoteLiteral / enquoteIdentifier do not escape backslashes, corrupting or breaking SQL #3063, open PR Escape backslashes in SQLUtils.enquoteLiteral and enquoteIdentifier #3146), so an argument that contains \' would still break the literal.
• Behavior that must not change: the % and _ wildcards, and the JDBC search escape (getSearchStringEscape() returns \). Today getSchemas(null, "lead\\_plain") correctly returns lead_plain.
• A test: an integration test in DatabaseMetaDataTest with a @DataProvider over the three methods, using a name that contains ' (must return the object) and the pattern nomatch' OR '1'='1 (must return 0 rows).

Note: open PR #3168 makes getSchemas use SHOW DATABASES by default, but it keeps this system.databases query for jdbc_metadata_use_show_statements=false without changes. It does not touch getPrimaryKeys or getFunctions.

Code Example

try (Connection conn = DriverManager.getConnection("jdbc:clickhouse://localhost:8123/", "default", "");
     Statement st = conn.createStatement()) {
    st.execute("CREATE DATABASE `lead_a'b`");
    st.execute("CREATE TABLE `lead_a'b`.`t'1` (id Int32, v String) ENGINE MergeTree ORDER BY id");

    DatabaseMetaData md = conn.getMetaData();
    md.getTables(null, "lead_a'b", "%", null);          // OK: 1 row
    md.getSchemas(null, "lead_a'b");                     // SQLException: Code 62 SYNTAX_ERROR
    md.getPrimaryKeys(null, "lead_a'b", "t'1");          // SQLException: Code 62 SYNTAX_ERROR
    md.getFunctions(null, null, "to'Int");               // SQLException: Code 62 SYNTAX_ERROR

    try (ResultSet rs = md.getSchemas(null, "nomatch' OR '1'='1")) {
        int n = 0;
        while (rs.next()) n++;
        System.out.println(n);                           // prints 8 (all databases), expected 0
    }
}

Configuration

Client Configuration

// Default JDBC properties. The run used compress=false only to avoid #3105
// (client-v2 cannot read ZSTD responses from server 26.9). It is not related to this issue.
Properties props = new Properties();
props.setProperty("compress", "false");

Environment

• Cloud
• Client version: jdbc-v2 0.11.0-rc1-SNAPSHOT (main at 9481c40)
• Language version: Java 17.0.20
• OS: Linux (Docker)

ClickHouse Server

• ClickHouse Server version: 26.9.1.1629
• ClickHouse Server non-default settings, if any: none
• CREATE TABLE statements for tables involved: see "Steps to reproduce"
• Sample data for all these tables: not needed (empty tables)

---

Found by automated analysis (Polyglot AI) while working on #3168. Verified with an integration test through Connection.getMetaData() against a live ClickHouse 26.9.1.1629 server, not only by code inspection.