Query erroring even thought the string it generates looks right

Hi, The query that I am trying to run looks fine but it throws a random error saying : is found even though there is no ":".
The string it generates runs on snowflake.

Am I using the values from the dropdown list incorrectly? If I use the values on queries where I don't do a <schema_name>.<table_name>, it works fine. Any help here is much appreciated. Thanks.

Hi @prashanthi

This looks like an issue related to prepared statements.

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.

One option to try to resolve the error, is to 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.

Another option, is to create different queries for each of the different combinations of schema names & table names.


Here's a similar example:

1) Prepared statements default setting is enabled:

2). The same query without a dynamic table name:

3). Prepared statements setting is disabled:

Thank you Tess. I resolved it by creating Stored procedure and sending in these values as parameters. Is there a way to expose the return string of stored procedure so that users can get message like 'Table created successfully' ?

Hi @prashanthi! It should be returned under queryName.data. Is your stored procedure query data null?