Parametrized queries

Learn how to write parametrized SQL queries in Firebolt using $1,$2 placeholders.

Firebolt supports parametrized SQL queries, allowing you to write query templates with placeholders whose values are supplied separately at execution time. This separates query logic from data, preventing SQL injection and making queries easier to reuse.

Placeholder syntax

Use $1, $2, $3, … as positional placeholders anywhere a value expression is valid in a SQL statement:

SELECT * FROM orders WHERE customer_id = $1 AND status = $2;

Parameter values are passed alongside the query via the query_parameters request property. Firebolt substitutes the values server-side before executing the query.

Specifying parameters

In the SQL Workspace

Use the SET statement to define parameters before running the query:

SET query_parameters = [{ "name": "$1", "value": 42 }, { "name": "$2", "value": "shipped" }];

SELECT * FROM orders WHERE customer_id = $1 AND status = $2;

A single parameter can be passed as a JSON object instead of an array:

SET query_parameters = { "name": "$1", "value": 42 };

Via the REST API

Pass query_parameters as a URL query string parameter when calling the query endpoint:

curl --location \
  'https://<engine-url>?database=my_db&query_parameters=[{"name":"$1","value":42},{"name":"$2","value":"shipped"}]' \
  --header 'Authorization: Bearer <access_token>' \
  --data 'SELECT * FROM orders WHERE customer_id = $1 AND status = $2'

Parameter value types

A parameter’s SQL type comes from the JSON type of its value. An integer becomes INT/BIGINT, a JSON number becomes a floating-point value, a boolean becomes BOOLEAN, a string becomes TEXT, and null becomes SQL NULL. These are used directly, without a cast:

SET query_parameters = [{ "name": "$1", "value": 42 }, { "name": "$2", "value": true }];

SELECT * FROM orders WHERE quantity > $1 AND is_paid = $2;

A value that JSON cannot represent directly (DATE, TIMESTAMP, NUMERIC/DECIMAL, BYTEA, JSON, and the BigQuery DATETIME/BYTES/NUMERIC spellings) is passed as a string and cast in the query:

SET query_parameters = [{ "name": "$1", "value": "2024-01-15" }];

SELECT * FROM orders WHERE created_at >= CAST($1 AS DATE);

There is no array- or struct-typed parameter. Pass a delimited string and split it, or a JSON string and cast it to JSON.

Using parametrized queries from an SDK

When connecting via an SDK or driver, parameters are set through the SDK’s prepared statement API rather than via SET. Each SDK uses the same $1, $2, … placeholder syntax in the query string.

For implementation details across all supported SDKs and drivers, see Parametrized queries.