ClickHouse / ClickHouse/clickhouse-js
[backfill: ClickHouse/clickhouse-js] insert columns parameter does not backtick-quote identifiers with spaces/special chars
- Dominant language
- TypeScript
- Stars
- 331
- Forks
- 74
- PR merge metrics
- No merged PRs in 30d
Description
## 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:
```ts
// 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
```ts
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.:
```ts
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 https://github.com/ClickHouse/clickhouse-cpp/issues/30
Contributor guide
Assessment
This issue has not been assessed yet.