[backfill: ClickHouse/clickhouse-js] insert columns parameter does not backtick-quote identifiers with spaces/special chars

[alex-clickhouse]

Target client repo

ClickHouse/clickhouse-js

Severity

sev:1 — visible server-side syntax error (not silent corruption), and a trivial client-side workaround exists: callers can pre-quote the identifier themselves (e.g. columns: ['`test id`', 'name']). The default JSONEachRow insert path that omits the columns parameter is unaffected, since ClickHouse parses column names from the JSON object keys.

Description

When calling client.insert(...) with a columns array whose entries contain spaces (or other characters that require identifier quoting in ClickHouse SQL), the generated INSERT INTO ... (cols) FORMAT ... statement bakes the raw strings into the SQL without backtick-escaping, and the server rejects it with a syntax error.

The relevant code joins user-supplied column names verbatim:

// packages/client-common/src/client.ts (getInsertQuery)
if (Array.isArray(params.columns) && params.columns.length > 0) {
  columnsPart = ` (${params.columns.join(', ')})`
}
// ...
return `INSERT INTO ${params.table.trim()}${columnsPart} FORMAT ${format}`

For columns: ['test id', 'name'] this produces INSERT INTO test.numbers (test id, name) FORMAT JSONEachRow, which fails to parse server-side (Syntax error: failed at position ...: id, name) FORMAT ...). The same applies to the * EXCEPT (...) branch.

ClickHouse server version

Code analysis only; not verified against a running server.

Reproduction

import { createClient } from '@clickhouse/client'

const client = createClient({ url: 'http://localhost:8123' })

await client.command({
  query:
    "CREATE TABLE IF NOT EXISTS test_numbers (`test id` UInt64, name String) ENGINE = Memory",
})

// FAILS: generated SQL is `INSERT INTO test_numbers (test id, name) FORMAT JSONEachRow`
await client.insert({
  table: 'test_numbers',
  values: [{ 'test id': 1, name: 'one' }],
  format: 'JSONEachRow',
  columns: ['test id', 'name'],
})

Expected: insert succeeds (or the client errors with a clearer client-side message about identifier quoting).
Actual: server returns Code: 62. DB::Exception: Syntax error ... Expected one of: token, ClosingRoundBracket, Comma, Dot.

Note: the same insert without the columns parameter works, because ClickHouse extracts column names from the JSONEachRow keys itself. The bug is specific to the columns (and columns.except) parameter path.

Workaround for users today: pre-quote the column names in the array, e.g. columns: ['`test id`', 'name'].

Suggested fix

In getInsertQuery (packages/client-common/src/client.ts), wrap each identifier with backticks and escape any embedded backticks before joining, e.g.:

const quoteIdent = (s: string) => '`' + s.replace(/`/g, '``') + '`'
columnsPart = ` (${params.columns.map(quoteIdent).join(', ')})`
// ...and the same for the `except` branch

This matches how ClickHouse itself quotes identifiers and is safe for already-simple names. (If existing users have been pre-quoting their column names as a workaround, the change would need to detect and skip already-backticked input, or be released as a documented behavior change.)

Source bug

Relayed from ClickHouse/clickhouse-cpp#30