Hi everyone, I need help regarding a sql query.
I am currently leveraging JavaScript to dynamically format an insert statement.
And then I am using a sql query component to execute this.
And it compiles correctly in the format that I want, however, I get the following error message:
I am not sure what is wrong or where the error is coming from? I would appreciate your help. Thanks!
Hi @Lumos_Angel
Dynamic SQL queries like this one are blocked by our prepared statements settings.
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 choose to disable prepared statements at the resource level, but I would recommend not doing this, and instead changing your query to be a GUI mode query where you can pass in a dynamic array of key/value pairs to insert.
If you choose to disable this setting in the resource setup, 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.
Thank you @Tess for the recommendation.
I was able to actually find the work around. I leveraged the snowflake json parsing and lateral flatten built-in functions to create an upsert script.
Glad to hear you were able to move forward!
