ClickHouse / ClickHouse/clickhouse-js

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

Open
#945 0 comments 0 reactions 1 assignee Claimed by @polyglotAI-bot View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.