Snowflake Load from S3

Workflows
, , , ,
1

I have the following SQL Query running in a workflow and when I copy and paste the code directly in Snowflake it works without a problem, but it does not work in the workflow. Here is the code that is run in Snowflake and in Retool:

Snowflake:

copy into lppingpost ("pingedContractID", "contractID", "campaignID", "entity", "entityID", "accepted", "response_code", "total_time", "priceToken", "sessionIDToken", "messageToken", "response", "createdOnDateTime", "affiliateID", "advertiserID", "verticalID", "campaignName", "affiliateName", "contractName", "advertiserName", "verticalName")
  FROM @snow_simple/misc/retool/pingPostLog/pingPostLog_2023-10-23_0.csv
  file_format = (format_name = PINGPOST);

Retool:

copy into lppingpost ("pingedContractID", "contractID", "campaignID", "entity", "entityID", "accepted", "response_code", "total_time", "priceToken", "sessionIDToken", "messageToken", "response", "createdOnDateTime", "affiliateID", "advertiserID", "verticalID", "campaignName", "affiliateName", "contractName", "advertiserName", "verticalName")
  FROM @snow_simple/misc/retool/pingPostLog/pingPostLog_{{query1.data['0'].LPDATE}}_{{query1.data['0'].PINGPOSTPAGE}}.csv
  file_format = (format_name = PINGPOST);

I've tried putting @snow_simple/misc/retool/pingPostLog/pingPostLog_{{query1.data['0'].LPDATE}}_{{query1.data['0'].PINGPOSTPAGE}}.csv in single quotes and double quotes but that too doesn't work.

2

Hi @kirkholmes I know we discussed in office hours, but let me know if the issue is still unresolved! :crossed_fingers:

For anyone following along, we're thinking that the error is 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.

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 prepared statements are needed, we 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.

3

@Tess You beat me to the punch!

I did create a new resource and disabled converting queries to prepared statements and now this is working as intended!

So now anytime I need to run an SQL query that includes query variables, I just use that new resource, nothing else has to change with all of my apps or workflows!!!

1 Like
4

Ah yay! Glad it's working!