Escaping and dynamic references

How a parameter is escaped depends on where it's placed within the SQL. This mechanism helps prevent SQL injection, and can also be used to dynamically generate identifiers such as table or column names.

String parameters in a normal context

Parameters are expanded escaped as strings. The escaping follows the SQL syntax of the selected connection.

-- If the parameter value is user's "data"
WHERE name = {{str_param}}         -> WHERE name = 'user\'s \"data\"'

-- If the parameter value is O'Reilly's "Book"
SELECT name = {{company}}          -> SELECT name = 'O\'Reilly\'s \"Book\"'

String parameters within a quoted context

If a parameter is placed inside single quotes, double quotes, backticks, or similar, only the inner content is escaped so the outer quote isn't broken. Escaping follows the connection's SQL syntax.

The following is an example for a BigQuery connection.

-- Inside double quotes (escapes both " and ')
-- If the parameter value is user"s 'data'
SELECT "{{str_param}}" as alias    -> SELECT "user\"s \'data\'" as alias

-- Inside single quotes (escapes both " and ')
-- If the parameter value is user"s 'data'
SELECT '{{str_param}}' as alias    -> SELECT 'user\"s \'data\'' as alias

-- Inside backticks (escapes only `)
-- If the parameter value is user"s 'data`
SELECT `{{str_param}}` as alias    -> SELECT `user"s 'data\`` as alias

Special handling for date parameters

• In a normal context, a date expands as a string in YYYY-MM-DD format.
• For BigQuery connections, a date placed inside backticks expands as YYYYMMDD (no hyphens).

-- BigQuery connection example

-- Normal date parameter
SELECT {{date_param}} as normal         -> SELECT '2024-10-02' as normal

-- A date inside quotes follows the same escaping rules
SELECT '{{date_param}}' as single_quote -> SELECT '2024-10-02' as single_quote
SELECT "{{date_param}}" as double_quote -> SELECT "2024-10-02" as double_quote

-- Special case: generating a table name in BigQuery
SELECT * FROM `table_{{date_param}}`    -> SELECT * FROM `table_20241002`

Numeric and boolean parameters

Numeric and boolean parameters aren't escaped; they're expanded as-is.

Generating dynamic references

Placing a parameter inside quotes lets you dynamically generate identifiers such as table or column names.

-- Dynamic table name
SELECT * FROM `table_{{table_suffix}}` -> SELECT * FROM `table_sales_2024`

-- Dynamic field selection
SELECT "column_{{field_type}}"         -> SELECT "column_revenue"

WARNING

Escaping is applied so the syntax doesn't break based on the surrounding quotes. However, a text parameter can contain any string. Depending on how the SQL is written, this could unintentionally reference an unexpected table or column. For dynamic identifier generation, consider mitigations such as fixing a prefix or suffix, or writing the SQL so it fails for unexpected values.