I'm trying to run a query where the column value comes from a drop-down list. The value output is a string, so my query looks like this:
select * from accounts where {{dropDownList.value}} ilike {{'%' + account_search_textInput.value + '%'}}
When the query runs, it is as follows:
select * from accounts where "first_name" ilike '%Adam%';
The SQL prepared statement shows double quotes around the column name first_name which I think is causing an issue but I don't think I can remove them. Is there any other way to successfully run the query where first_name can represent the column name rather than a string value?
When I run the query with the quotes around "first_name" (using the dynamic value generated from the drop-down list) the query results in 0 returned rows, whereas when directly hard-coding the first_name column name (without quotes), it returns 1 record. (The correct query should return 1 record)
Hello! Looks like you’re using a dynamic column name, which means you’ll need to disable prepared statements in your resource. 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.
