WHERE clause with a dynamic list of boolean columns

Queries and Resources
1

Hi there -- I can't find anything exactly like this on the forum, so hopefully it's not a repeat!

I'm using a Snowflake table and the table has a number of boolean columns for filtering records.

CREATE TABLE my_table (
    id INT,
    name VARCHAR,
    is_red BOOLEAN,
    is_big BOOLEAN,
    is_happy BOOLEAN
);

(There are actually many boolean columns, but kept it simple for the example.)

I have a set of filters on my page that can be switched on and off by different user actions. It collects a list of columns, like: ["is_happy","is_red"].

Whenever this list changes, I trigger the query to refresh the data based on the filters.

Expected result is:

select
  a.*
from
  prj_jynarque_ps_dev.jyn_tableau_dashboard_pivot a
where
	**"IS_HAPPY" or "IS_RED"**

The above is tested ok when run as a query directly on Snowflake, so I know it is supposed to work.

I tried a number of approaches, but the simplest is this one:

    {{
      I2A1.filtered_insights.map(
        (x) => {
          return(`"${x}"`)
        }
      ).join(" or ")
    }}

This results in this: ""is_happy" or "is_red"". Notice the extra double quotes.

Other things I tried, like converting the boolean columns to one json column and using syntax like:

 {{
      I2A1.filtered_insights.map(
        (x) => {
          return(`get(insight_flags, '${x}')::boolean = true`)
        }
      ).join(" or ")
    }}

failed because of the same extra quotes issue.

I understand that the Query Builder can be used in some way to get around this issue, but my interface doesn't use that component, so I'm at an impasse. I can just hard code all of the fields in the sql, but I'm hoping to not do that because I expect these flags to grow and I'd rather have one less place to hard code things.

Thanks for any suggestions!

2

Hi @sammyw,


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.

If you do not want to disable this setting, you would likely need to do some hardcoding in the sql query.