How to utilise {{data}} in a SQL Query?

App Building
1

How do I format this so it resolves EXAMPLE_NUM ?

SELECT * FROM project.dataset.table_name_{{example_num.data.EXAMPLE_NUM}}

2

Hey @Forde_Smith!

Basically, you're looking to have a dynamic table name? If so, I think your first step would be to disable prepared statements in the resource setup page. :slight_smile:

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.

3

Hi @Victoria - so for the use case I raised here (dynamically setting a table name), disabling prepared statements works. However, it stops all? other uses of {{variables}} working in SQL statements (e.g. where ID = {{data.ID}} ). So it seems that this is not a fix.

4

Hey @Forde_Smith! This should work actually :thinking: Is there anything I can do to try replicating your issue?

5

Hi @victoria

Thanks for the post.

When I disable prepared statements to dynamically set the table name, all of the rest of my SQL queries break. I'll need to experiment further to see why or if I can get them working again.

6

That's so odd :frowning:

I can step in to take a look if you'd like! The name of your app and/or query names would be helpful.

And in the meantime, you can create separate resources (one with prepared statements disabled and one with prepared statements enabled) to use in different queries. It's not a great workaround, but it's a workaround :sweat_smile:

7

@victoria
same thing happened for me.
All other queries that contain JS vars are failing now.
Example of where the queries fail:

"WHERE table.column1 = {{UITable.service}}"
If {{UITable.service}} evaluates to "tech" then I get the error "column "tech" does not exist"

8

Hey @Zainab_Salameh! Would you mind sharing a screenshot of your entire query?

9

Hello,

Prepared statements feature essentially put quotes around your {{ }}

Context
image
image

Before disable prepared statements
The SQL is essentially be like this when it runs in SQL server

SELECT *
FROM tasks
WHERE ticket_id = 'A1'

After disabled prepared statements

SELECT *
FROM tasks
WHERE ticket_id = A1

Note of no quotes around A1 value. Because of no quotes for string value, sql server treats it as column name, hence the error you saw. To fix the issue you have to put quotes around {{ }}
image

Note: quotes is mainly for string value. Numeric values you don't have to add quotes.

2 Likes
10

CleanShot 2023-04-27 at 10.15.11
CleanShot 2023-04-27 at 10.15.11729×164 13.4 KB

11

thank you @lamh_bytecode.io for all the details.
I decided to go with a workaround for having dynamic columns to solve my problem rather than disabling Prepared Statements. I have many queries in my app and I don't want to go over all of them to update.

12

Sounds good, its one of the feature that can catch developers off guard.

13