Replace table names with a temporary variable value

I have many queries on the retool and all query runs a BigQuery SQL and returns data. These all queries contains a single table name, but when I wanted to change a table name I have to do this in all queries which is a bit repetitive process.

Is there any way when I have put my table name in a temporary variable and put that variable at place of table names in SQL?


Hey @flintrider! Definitely. In order to do this, you’ll need to disable prepared statements for your resource. 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.



I think a good feature would be to be able to disable prepared statements on a per query basis, not just globally. Even better per parameter. This would really my modules dynamic and much more reusable!

Is this something we could add as a feature request?

Definitely. Per query basis would be so great. I’ve filed it and will report back!


Just to add some context, we've held off on this so far because we want admins/resource creators to have some control on if prepared statements are allowed. For anyone else running into this or interested in this functionality, please leave your +1s in the thread to help us properly triage this request!


Hi @victoria - would there be a way to do this via JavaScript? Can you suggest any other alternatives? To clarify, I'm trying to:


Is it possible to use a prepared statement like this:

SET @TableName = {{}}

SELECT * FROM @TableName

Hey @Quinlan_Eddy! Just to clarify, have you disabled prepared statements on your resource?

1 Like

I'm also trying to do something similar. I want to use dynamic table names and fields, but due to the parameterization they are wrapped in strings. Having a way to not parameterize the values would be helpful.

Turning off prepared statements is not an option, as it is too blanketed and creates security holes elsewhere.

I know this is a difficult situation, as I am asking for prepared statements and non-prepared statements at the same time! :sweat_smile:

Understandable. Just requested this internally!

As a note, perhaps you could use a Query JSON with SQL query:

Though this wouldn't help with the need for dynamic table names :frowning:

1 Like

I was able to make it work with the below Dynamic BigQuery SQL. This works without changing the settings for prepared statements.

DECLARE table_name STRING;
SET table_name = {{text1.value}};
  SELECT <col_1>,<col_2>   
  FROM `test_dataset`.%s  
""", table_name);

That's awesome, thank you for sharing @bshah! I'm sure this will help other users.


I had a win with the following (dynamically populated from a select component):+1::muscle:

SET @table_name = {{Select_MySQLTable.value}};
SET @query = CONCAT('SELECT * FROM <<resource_name>>.', @table_name);
PREPARE stmt FROM @query;