Support ClickHouse's query parameters

[niyue]

Use Case

ClickHouse natively supports Query Parameters feature [1][2], which allow users to write generic SQL templates using typed placeholders. This avoids manual string interpolation, improves readability, and reduces the risk of SQL injection.

Sample usage

import chdb

df = chdb.query(
    "SELECT toDate({base_date:String}) + number AS date "
    "FROM numbers({total_days:UInt64}) "
    "LIMIT {items_per_page:UInt64}",
    "DataFrame",
    params={"base_date": "2025-01-01", "total_days": 10, "items_per_page": 2},
)
print(df)

# expected output:
#         date
# 0 2025-01-01
# 1 2025-01-02

This is standard ClickHouse syntax, but chdb does not yet support passing query parameters.

Describe the solution you'd like

Enable query parameters in chdb by wiring through ClickHouse’s native setQueryParameters C++ API.
Expose this capability across chdb’s language bindings (Python, Node.js, etc.) so end users can easily supply a params dictionary when executing a query.

Additional context

This would bring chdb to parity with clickhouse client CLI and clickhouse-connect Python library

• ClickHouse client CLI supports this feature [2], e.g.

clickhouse-client --param_message='hello' --query="SELECT {message: String}"
hello

• clickhouse-connect, the official ClickHouse Python client library, supports similar features in its QueryContext API [3]

client.create_query_context(query='SELECT value1, value2 FROM data_table WHERE key = {k:Int32}',
                            parameters={'k': 2},
                            column_oriented=True)
result = client.query(context=qc)

References

[1] ClickHouse SQL syntax: defining and using query parameters, https://clickhouse.com/docs/sql-reference/syntax#defining-and-using-query-parameters
[2] How to Use Query Parameters in ClickHouse, https://clickhouse.com/videos/how-to-use-query-parameters-in-clickhouse
[3] clickhouse-connect client library's support for query parameters, https://clickhouse.com/docs/integrations/language-clients/python/advanced-querying#querycontexts

[niyue]

I’ve submitted PR #437 to address this issue for the Python binding.

[cyrusmsk]

What are the benefits of this approach compared to handling parameter incorporation on the language side?
Using f-strings or t-strings in Python let's say

[niyue]

@cyrusmsk

What are the benefits of this approach compared to handling parameter incorporation on the language side?

ClickHouse’s documentation doesn’t elaborate much on the advantages. It mainly states that Query parameters allow you to write generic queries that contain abstract placeholders instead of concrete identifiers. [1]

I can think of several benefits:

1. Typed parameters

Query parameters carry type information, whereas language-level string interpolation treats everything as a string, providing no SQL-type validation.

--- https://fiddle.clickhouse.com/2c69452e-28a8-491d-9279-a9b022329815
SET param_v1 = -1;
SELECT {v1:UInt32};

Received exception from server (version 25.11.2):
Code: 457. DB::Exception: Received from localhost:9000. DB::Exception: Value -1 cannot be parsed as UInt32 for query parameter 'v1' because it isn't parsed completely: only 0 of 2 bytes was parsed: . (BAD_QUERY_PARAMETER)
(query: SELECT {v1:UInt32};)

You could try to emulate this using the language’s own string interpolation, but it won’t necessarily map cleanly to SQL types, and the quality and capbility of interpolation features vary across languages.

2. Safe and correct SQL construction is hard with string interpolation

When formatting SQL manually, you must properly escape quoting characters and carefully handle edge cases to avoid SQL injection vulnerabilities. It is much easier to get it right if using query parameters.

--- https://fiddle.clickhouse.com/d0ea5522-972b-4662-a9b5-432836367df2

SET param_message = '''; DROP TABLE users; --';
SELECT {message:String};

--- \'; DROP TABLE users; --

message = "'; DROP TABLE users; --"
df = chdb.query(
    "SELECT {message:String} ",
    "DataFrame",
    params={"message": message}  # <== no need to worry about special char escaping or SQL injection
)

3. Better integration with ClickHouse

Since query parameters is a native ClickHouse feature, it provides a more consistent way to integrate with the ClickHouse ecosystem. The same parameterized query template can be reused across various tools, such as:

• clickhouse-connect (the official Python client)
• The ClickHouse CLI
  • clickhouse client --param_size=100 --query="SELECT * FROM numbers({size:UInt32})"
• The ClickHouse REST API
  • curl http://localhost:8123/?param_size=100 -d "SELECT * FROM numbers({size:UInt32})"

This also means that different languages can share the exact same query template. Copying and reusing queries across languages/tools becomes much simpler.

Some tools, such as the ClickHouse Cloud UI, even offer dedicated form-based support for parameterized queries (see video [2], starting from 4:02):

[1] https://clickhouse.com/docs/sql-reference/syntax#defining-and-using-query-parameters
[2] https://clickhouse.com/videos/how-to-use-query-parameters-in-clickhouse

---

UPDATE:
I found a ClickHouse's official documentation stating this:

While ClickHouse doesn't have traditional "prepared statements" in the RDBMS sense, it provides query parameters that serve the same purpose: safe, parameterized queries that prevent SQL injection.
...
Query parameters can be used for:

• Preventing SQL injection
• Parameterized queries with type safety
• Dynamic filtering in applications
• Reusable query templates