Parameterized Queries

This feature was introduced in Hydrolix version 5.11.

Prevent SQL injection and make query templates reusable by using parameterized queries. This practice separates SQL logic from data values. Instead of interpolating values directly into SQL strings, define {name:Type} placeholders in the query and pass values as param_-prefixed HTTP parameters. Hydrolix substitutes the values as typed literals before execution.

Parameterized queries work on both the HTTP Query API (/query) and the ClickHouse HTTP interface (port 8088).

Usage

Define {name:Type} placeholders in the SQL query. For each placeholder, include a corresponding param_name=value HTTP parameter. Hydrolix strips the param_ prefix, matches the remaining name to the placeholder, validates the value against the declared type, and substitutes it as a literal value.

The Type in the placeholder is any ClickHouse data type like UInt64, String, Date, DateTime, or DateTime64(3).

Parameters can appear in the URL query string, the POST body, or both. When the same parameter name appears in both locations, POST body values overwrite URL values.

GET examples

Use --data-urlencode with -G to pass param_ parameters in the URL query string.

curl -G \
  --header "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "query=SELECT * FROM hydro.logs WHERE user_id = {user_id:UInt64} AND status = {status:String}" \
  --data-urlencode "param_user_id=12345" \
  --data-urlencode "param_status=active" \
  "https://hostname.hydrolix.live/query"

Alternatively, pass everything directly in the URL.

curl --header "Authorization: Bearer ${HDX_TOKEN}" \
  "https://hostname.hydrolix.live/query?param_user_id=12345&param_status=active&query=SELECT%20*%20FROM%20hydro.logs%20WHERE%20user_id%20%3D%20%7Buser_id%3AUInt64%7D%20AND%20status%20%3D%20%7Bstatus%3AString%7D"

POST examples

Pass param_ parameters in the URL query string and the SQL as the POST body.

curl -X POST \
  --header "Authorization: Bearer ${HDX_TOKEN}" \
  "https://hostname.hydrolix.live/query?param_user_id=12345" \
  -d "SELECT * FROM hydro.logs WHERE user_id = {user_id:UInt64}"

Alternatively, pass both the parameters and the query in the POST body using --data-urlencode.

curl -X POST \
  --header "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "param_user_id=12345" \
  --data-urlencode "param_start_date=2024-01-01" \
  --data-urlencode "param_status=active" \
  --data-urlencode "query=SELECT * FROM hydro.logs WHERE user_id = {user_id:UInt64} AND created_date >= {start_date:Date} AND status = {status:String}" \
  "https://hostname.hydrolix.live/query"

Combine parameters with query options

Parameterized query values (param_-prefixed) and query options (such as hdx_query_debug or max_execution_time) don't interfere with each other. Include both in the same request.

curl -X POST \
  --header "Authorization: Bearer ${HDX_TOKEN}" \
  --data-urlencode "param_id=123" \
  --data-urlencode "query=SELECT * FROM hydro.logs WHERE id = {id:UInt64}" \
  "https://hostname.hydrolix.live/query?max_execution_time=60"

Error handling

If a query contains a {name:Type} placeholder but the corresponding param_name parameter is missing, the query returns a ClickHouse error:

Code: 456. DB::Exception: Substitution `user_id` is not set. (UNKNOWN_QUERY_PARAMETER)

If the supplied value doesn't match the declared type, ClickHouse returns a type conversion error.