Escape and Dynamic Reference

Parameter escaping and dynamic table/column reference

The parameter system handles SQL injection protection and value escaping based on the parameter's context. This mechanism not only provides security but also enables dynamic table/column reference.

Parameter Escaping

1. String Parameters in Regular Context

• Parameters are escaped as a string in SQL.

• The escape is performed according to the SQL syntax of the selected connection.

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

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

2. String Parameters in Quoted Context

• If a parameter is placed within quotes (such as single or double quotes), it will be properly escaped to preserve the original SQL syntax and prevent breaking the quoted context.

• The escape is performed according to the SQL syntax of the selected connection.

-- Below is an example using a BigQuery connection

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

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

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

3. Date Parameters Special Processing

• Regular context: Dates are formatted as 'YYYY-MM-DD'

• However, when using a BigQuery connection, dates are formatted as YYYYMMDD (without hyphens) within backticks (`).

-- Below is an example using a BigQuery connection

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

-- Date in 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: Table name generation in BigQuery
SELECT * FROM `table_{{date_param}}`    -> SELECT * FROM `table_20241002`

4. Other Parameters

• Number and boolean parameters are not escaped and are expanded as is.

Dynamic Identifier Generation

• By placing parameters within quotes, you can safely generate SQL identifiers like 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"

Escaping is applied based on the surrounding quotes to ensure the SQL syntax remains valid. However, since string parameters can accept arbitrary values, depending on how the SQL is written, it's possible for unintended tables or columns to be referenced. When generating identifiers dynamically, consider applying safeguards such as fixing prefixes or suffixes, or writing the SQL in a way that it results in an error when unexpected values are provided.