Put Data from a JS transformer to a SQL CTE

Queries and Resources
,
1

Hi,

I have a string output from a JS transformer in the form of:

( 
     ( 15, 37, 1, 1059, 100  ),  
     ( 15, 36, 1, 1059, 100  ), 
     ( 15, 14,  1, 1059, 18  )  ,  ... 

)

I would like to put this data into a SQL CTE like this:

WITH updated(a, b, c, d, e) AS (VALUES  

{{JS_transformer_updated_values.value}} 
                                                                                                                   
) Select * from updated

I keep getting a syntax error where I called the JS transformer on my SQL script.

Could you guys help me resolve this problem?

Regards,
Mo

1 Like
2

By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names, arrays of separate values, 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 inputs.

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.

1440×502 45.5 KB

1 Like