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.
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.
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 (`).
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.
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.
Last updated
Was this helpful?