ClickHouse DB::Exception: Unknown query parameter (Code: 456)

The "DB::Exception: Unknown query parameter" error in ClickHouse occurs when a query contains a parameter placeholder that the server cannot resolve. The UNKNOWN_QUERY_PARAMETER error code (code 456) is raised when a {name:Type} placeholder is used in a query but no corresponding parameter value is provided.

Impact

The query is rejected at parse time and no data is processed. This affects applications that use parameterized queries but fail to supply all the required parameter values. The error is straightforward to fix once the missing parameter is identified.

Common Causes

1. A parameterized query uses a {param_name:Type} placeholder but the parameter was not included in the request
2. A typo in the parameter name — the name in the query does not match the name provided in the parameters
3. The HTTP request omits the param_ prefix required for parameter names in the query string
4. A client library does not properly pass parameters to the ClickHouse server
5. Using the placeholder syntax in a context where parameterized queries are not supported
6. Template queries in dashboards or tools where variable substitution failed before the query was sent

Troubleshooting and Resolution Steps

1. Identify the missing parameter from the error message. The text reports the unset substitution by name:

   DB::Exception: Substitution `start_date` is not set
   

2. For HTTP interface queries, parameters must be passed with the param_ prefix:

   curl -G 'http://localhost:8123/' \
     --data-urlencode "query=SELECT * FROM events WHERE date >= {start_date:Date}" \
     --data-urlencode "param_start_date=2024-01-01"
   

3. For the clickhouse-client command line, use the --param_ flag:

   clickhouse-client \
     --param_start_date='2024-01-01' \
     --query="SELECT * FROM events WHERE date >= {start_date:Date}"
   

4. Verify the parameter name matches exactly (case-sensitive) between the placeholder and the supplied value:

   -- Placeholder uses 'startDate'
   SELECT * FROM events WHERE date >= {startDate:Date}
   -- Parameter must match: --param_startDate='2024-01-01'
   

5. If using a client library, ensure parameters are passed correctly. For example, in Python with clickhouse-connect:

   client.query(
       "SELECT * FROM events WHERE date >= {start_date:Date}",
       parameters={"start_date": "2024-01-01"}
   )
   

6. Check that the type in the placeholder matches the value being supplied. Valid syntax is {name:Type}:

   SELECT * FROM events
   WHERE date >= {start_date:Date}
     AND count > {min_count:UInt64};
   

Best Practices

• Always verify that every {param:Type} placeholder in a query has a corresponding parameter value in the request.
• Use consistent naming conventions for parameters to reduce typo-related errors.
• Test parameterized queries manually with clickhouse-client --param_ before deploying them in applications.
• Document the expected parameters for each query template in your application.
• Use client libraries that support parameterized queries natively for type safety and SQL injection prevention.

Frequently Asked Questions

Q: What is the syntax for parameterized queries in ClickHouse?
A: Use {parameter_name:DataType} as a placeholder in the query. Supply the value via param_parameter_name in the HTTP query string, or --param_parameter_name in the CLI.

Q: Can I use parameterized queries for table or column names?
A: No. Parameterized queries only support value substitution. Table names, column names, and other identifiers cannot be parameterized. Use string substitution on the client side for dynamic identifiers.

Q: Do parameterized queries prevent SQL injection?
A: Yes. ClickHouse parameterized queries handle escaping and type validation server-side, making them safe against SQL injection. Always prefer parameterized queries over string concatenation.