Skip to main content

Prepared statements

Learn how Retool uses prepared statements for SQL queries.

Retool converts SQL queries into prepared statements. This makes queries run more efficiently and prevents SQL injection attacks. Since most databases do not support dynamic column or table names in prepared statements, you generally cannot use {{ }} embedded expressions to dynamically specify columns or tables.

How it works​

A prepared statement separates the query code from the values it references. This allows the query to be reused with different values without the database needing to recompile the query each time. Instead, the database can use the same pre-compiled query and use different values whenever it's run.

In addition, this prevents any code, malicious or accidental, from performing destructive actions, such as DROP TABLE.

Dynamic values​

The following example uses a {{ }} embedded expression to reference a value when retrieving a list of users. As a prepared statement, the query successfully runs with the dynamic value.

SELECT id, name, subscription
FROM sample_users
WHERE expiration < {{ date1.value }}

Dynamic names or statements​

Unlike dynamic values, you cannot use {{ }} embedded expression to dynamically specify names or actions (e.g., a column name or SELECT).

The following example attempts to retrieve all records from a specified table. Since PostgreSQL does not support dynamic table names in prepared statements, the query does not work.

select * from {{ select1.value }}

Instead, you would write separate queries for each table and reference those.

getUsers
select * from users
getPayments
select * from payments

Disable prepared statements​

Some Retool integrations allow you to disable prepared statements in the resource's settings. If you do, Retool no longer uses prepared statements and you can use dynamic names or statements. However, this is not recommended as it also prevents SQL injection, exposing you to potential risk.

If you disable prepared statements, you may need to update queries which use {{ }} embedded expressions. Some databases expect prepared statement placeholders without quotes or perform type conversions within prepared statements.

select id, first_name, last_name
from users u
where u.id = {{ numberInput1.value }};

Consult your database documentation on prepared statements to confirm expected usage.