We have an administrative use case where we want to make an admin panel that generates SQL views for our clients.
So I have a query setup as follows:
create view {{viewname}} as {{query}}
Where viewname and query are set as additional scope.
I then pass in the viewname and the query for the view in as additional scope to trigger the query but I get an SQL error - it looks like the viewname and query are wrapped in quotes.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''clientA_service_intervals' as 'select si.id, si.asset_certification_id' at line 1"
In this case, clientA_service_intervals and the select query should not be quoted. i.e the syntax should be
create view clientA_service_intervals as
select si.id, si.asset_certification_id ... from ... etc
I understand quoting would be standard behavior for a query - but in this case I want the my parameters not quoted.
Or perhaps there's a better way to achieve this? I'm trying to generate multiple views for multiple clients at once - so this query would be triggered multiple times for the different view names and query types.
I think the issue is how prepared statements work. You can disable using them on a per/resource basis (would be handy on a per query basis), but be sure you understand the risks, specifically around SQL injection. If you're just creating the views once, maybe you can disable it, generate the views, and then re-enable it. Either way, to do it, find your database resource in the Resources section of retool, click the "Settings" button, and check this box:
For more context, by default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries.
If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

