ClickHouse / ClickHouse/clickhouse-go
PrepareBatch: FORMAT/VALUES stripping is not quote-aware — column list silently dropped, quoted values corrupted, or query made invalid
- Dominant language
- Go
- Stars
- 3.3k
- Forks
- 680
- Avg merge
- 2d 3h
- Merged PRs (30d)
- 14
Description
## Observed
`extractInsertQueryComponents` in `batch.go` strips the `FORMAT` clause and the `VALUES`
suffix with two plain regexes that run over the raw query text:
```go
// batch.go:10-11
var truncateFormat = regexp.MustCompile(`(?i)\sFORMAT\s+[^\s]+`)
var truncateValues = regexp.MustCompile(`\sVALUES\s.*$`)
```
Both keywords are *structural* SQL positions, but the regexes match any occurrence,
including one inside a quoted identifier or a string literal. Three distinct failures
follow, all reproduced on `main` (06550b5) against server 26.7.3.19, on the native and
the HTTP protocol:
1. **A column list is silently discarded, so values are written to the wrong columns.**
With a column identifier that contains ` VALUES `, `truncateValues` cuts the query at
that point, the column list disappears, and the batch falls back to the table's full
column list in table order:
```
CREATE TABLE lead_swap (c1 UInt32, `a VALUES b` UInt32) ENGINE Memory
PrepareBatch("INSERT INTO lead_swap (`a VALUES b`, c1)")
Append(111, 222) // caller means `a VALUES b`=111, c1=222
-> stored c1=111, `a VALUES b`=222 // swapped, no error
```
The equivalent with ` FORMAT ` mangles the identifier instead:
`INSERT INTO t ("a FORMAT JSON b", col2)` is normalized to
`INSERT INTO t ("a b", col2) FORMAT Native`.
2. **A quoted setting value is silently altered.** `FORMAT ` is deleted from inside
the literal:
```
PrepareBatch("INSERT INTO lead_lc SETTINGS log_comment='a FORMAT JSON b'")
```
`system.query_log` records `log_comment = 'a b'` — the value the caller asked for was
never sent.
3. **The query becomes syntactically invalid.** A `VALUES` token inside a quoted setting
value truncates the query mid-literal:
```
PrepareBatch("INSERT INTO lead_lc SETTINGS log_comment='x VALUES y'")
```
The client sends `INSERT INTO lead_lc SETTINGS log_comment='x FORMAT Native` and the
server rejects it:
- native: `code: 62, message: Single quoted string is not closed: Syntax error: failed at position 41 ('x FORMAT Native)`
- HTTP: `failed to init block for HTTP batch: failed to determine columns for HTTP insert: sendQuery: [HTTP 400] code: 62 ... (SYNTAX_ERROR)`
The same query with `log_comment='xy'` succeeds, so the token inside the literal is the
only difference.
`log_comment` is a normal setting that carries free text, so a value holding the word
`VALUES` or `FORMAT` is a realistic input rather than a synthetic one.
Note: cases 2 and 3 are visible only when the query has **no** column list. With a column
list, everything after it is dropped by `normalizeInsertQueryMatch` anyway — that is the
separate, already-reported #1919.
## Expected behaviour
`FORMAT` and `VALUES` should be recognized only at a top-level structural position. A
keyword inside a single-quoted string, a double-quoted identifier, or a backtick-quoted
identifier is data and must be left untouched:
- the column list must survive a column name containing ` VALUES ` / ` FORMAT `;
- a `SETTINGS` value must reach the server unchanged;
- a valid INSERT must never be turned into a query the server cannot parse.
## Code example
```go
package main
import (
"context"
"fmt"
"github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
ctx := context.Background()
conn, _ := clickhouse.Open(&clickhouse.Options{
Addr: []string{"localhost:9000"},
Protocol: clickhouse.Native,
Auth: clickhouse.Auth{Database: "default", Username: "default"},
})
defer conn.Close()
_ = conn.Exec(ctx, "DROP TABLE IF EXISTS lead_swap")
_ = conn.Exec(ctx, "CREATE TABLE lead_swap (c1 UInt32, `a VALUES b` UInt32) ENGINE Memory")
// 1. column list silently dropped -> positional mapping -> swapped values
b, err := conn.PrepareBatch(ctx, "INSERT INTO lead_swap (`a VALUES b`, c1)")
if err != nil {
panic(err)
}
_ = b.Append(uint32(111), uint32(222))
_ = b.Send()
var c1, av uint32
_ = conn.QueryRow(ctx, "SELECT c1, `a VALUES b` FROM lead_swap").Scan(&c1, &av)
fmt.Printf("c1=%d `a VALUES b`=%d\n", c1, av) // c1=111 `a VALUES b`=222 -- swapped
// 2. FORMAT deleted from inside a quoted setting value
_ = conn.Exec(ctx, "DROP TABLE IF EXISTS lead_lc")
_ = conn.Exec(ctx, "CREATE TABLE lead_lc (c1 UInt32) ENGINE Memory")
b2, _ := conn.PrepareBatch(ctx, "INSERT INTO lead_lc SETTINGS log_comment='a FORMAT JSON b'")
_ = b2.Append(uint32(1))
_ = b2.Send()
_ = conn.Exec(ctx, "SYSTEM FLUSH LOGS")
var lc string
_ = conn.QueryRow(ctx, "SELECT log_comment FROM system.query_log WHERE query LIKE '%lead_lc%' AND type='QueryFinish' ORDER BY event_time DESC LIMIT 1").Scan(&lc)
fmt.Printf("log_comment=%q\n", lc) // "a b" -- not "a FORMAT JSON b"
// 3. VALUES inside a quoted setting value -> invalid query
_, err = conn.PrepareBatch(ctx, "INSERT INTO lead_lc SETTINGS log_comment='x VALUES y'")
fmt.Println("err:", err) // code: 62, Single quoted string is not closed
}
```
## Error log
```log
code: 62, message: Single quoted string is not closed: Syntax error: failed at position 41 ('x FORMAT Native): 'x FORMAT Native.
```
## Details
Root cause: `batch.go:26-27` applies `truncateFormat` and `truncateValues` to the whole
query string before any parsing, so neither knows about quoting.
Suggested fix: replace the two regexes with a single left-to-right byte scan that tracks
single-quote, double-quote and backtick quoting (plus backslash escapes and `--` / `#`
comments) and reports the offset of the first `FORMAT` / `VALUES` keyword found **outside**
quotes; truncate there. Contrast cases that must keep their current behavior:
`INSERT INTO t (a, b) VALUES (1, 2)`, `INSERT INTO t (a, b) FORMAT JSONEachRow`, and a
lowercase / mixed-case `format` clause.
Related but distinct — same family of "the INSERT parser is a regex, not a parser":
#1950 (comments), #1952 (`TABLE` keyword and table functions), #1827 and #1401 (backticks
when splitting the column list), #1919 (`SETTINGS` after a column list).
Provenance: found by automated analysis of `batch.go` while working on an adjacent change,
then verified against a live server on both protocols — not reported from inspection alone.
### Environment
* [x] `clickhouse-go` version: `main` @ 06550b5
* [x] Interface: ClickHouse API (native and HTTP; both affected)
* [x] Go version: go1.25.11
* [x] Operating system: Linux
* [x] ClickHouse version: 26.7.3.19
* [x] Is it a ClickHouse Cloud? No
* [x] ClickHouse Server non-default settings, if any: none
* [x] `CREATE TABLE` statements for tables involved:
```sql
CREATE TABLE lead_swap (c1 UInt32, `a VALUES b` UInt32) ENGINE Memory;
CREATE TABLE lead_lc (c1 UInt32) ENGINE Memory;
```
* [x] Sample data for all these tables: see the snippet
Contributor guide
Research direction
Start in batch.go at extractInsertQueryComponents and the truncateFormat/truncateValues handling around lines 26-27. Add focused tests for quoted identifiers, quoted setting values, escapes, comments, and the existing structural FORMAT/VALUES cases, then run the Go test suite. Done means structural keywords are truncated while quoted data remains unchanged on both affected protocols.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 76/100