SQL parameterization
Check out the Parameterize SQL tutorial for more hands-on examples!
For security, we handle all parameterized SQL queries as prepared statements for both performance and security reasons. Prepared statements are a way to template queries and substitute values where desired during execution. Not only is there some performance benefit when using prepared statements, they are also robust against SQL injection.
Watch a quick intro for how to parameterize your queries here —
Using variables in queries
You can parameterize your queries with both Input parameters and Python variables! We use Jinja templating syntax to indicate the parameters which should be substituted into your query. See the screenshot at the bottom of the page for an example of how to use Jinja's if blocks and for loops in your queries.
To pass a Python variable into your query you'll need to wrap it in double curly braces, {{ }}. For multi-index variables (e.g. lists, arrays), you'll also need to declare that you're passing in an array-type variable (e.g. {{list_variable | array}})
Individual databases have limits on how many parameters can be bound to a single query. For example, PostgreSQL allows 32767 variables, however Snowflake's maximum is 16384.
By default, prepared statements cannot accept query attributes as parameters and only allow for substituting in values. For example, the phrase WHERE column = {{value}} is allowed by default while WHERE {{column}} = value is not. You can force the literal parameterization of a query attribute by passing | sqlsafe as an additional flag with your parameter. e.g. WHERE {{column | sqlsafe}} = value.
If you use the sqlsafe flag to force a parameterization you are removing the protection that prepared statements offer against sql injection!
