Dynamic SQL Query

💬 App Building
,
1

I want to build a SQL playground in App mode

  1. A TextInput where user enters SQL query - “sql_query”

  2. A MySQL resource query using {{ sql_query.value }}

  3. A button to trigger it

  4. A table to display results

Is dynamic SQL execution intentionally blocked in MySQL resources in recent Retool versions?
Please advice.
Regards

image
image690×343 25.1 KB

3

Its a big security issue, but you can “Disable converting queries to prepared statements” in the connection settings

3 Likes
4

Hello @Amritya_Singh,

Dynamic SQL is not being intentionally blocked, this error message is due to Retool's default query building that occurs under the hood. There is a layer of query building between the raw SQL you are passing in from the input field and the final SQL statement that is called and executed on the backend.

This is done for safety reasons, to help avoid SQL injections so that a user won't be able to delete data or drop tables.

As @Tobias_Sell stated, you can toggle the checkbox for “Disable converting queries to prepared statements” to allow for the SQL to pass through unedited. But this does carry a risk with it.

If you can tell me more about your use case I can better advise you on options. My first suggestion would be to have only parts of the SQL statement be dynamic. Such as having the text input component be the variable used with a WHERE clause.

Or like in your example, having a table name be dynamic, but the rest of the SQL statement is 'hard-coded' into the query.