# Escape and Dynamic 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.

```sql
-- 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.

```sql
-- 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 (\`).

```sql
-- 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.

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

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

{% hint style="warning" %}
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**.
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.codatum.com/data-exploration/parameter-overview/escape-and-dynamic-reference.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.