Conditional WHERE expression in dynamic query

Question

I have the following function

CREATE OR REPLACE FUNCTION match_custom_filter(filters text[], id text)
        RETURNS boolean LANGUAGE plpgsql as $$
        DECLARE
            r boolean;
        BEGIN
            execute format(
                'SELECT 1 FROM trackings t LEFT JOIN visitors v ON v.id = t.visitor_id
                WHERE v.id = ''%s'' AND %s',
                id,
                array_to_string(filters, ') AND ('))
            into r;
            RETURN r;
        END $$;

select v.*, array_agg(g.name) as groups from visitors v join groups g on match_custom_filter(g.formatted_custom_filters, v.id)
where v.id = 'cov4pisw00000sjctfyvwq126'
group by v.id

This works fine when the filters are not empty. But it is also possible that a filter is empty, in which case I will have an dangling AND with no right hand side.

Error:

ERROR:  syntax error at end of input
LINE 2: ...               WHERE v.id = 'cov4pisw00000sjctfyvwq126' AND 
                                                                       ^
QUERY:  SELECT 1 FROM trackings t LEFT JOIN visitors v ON v.id = t.visitor_id
                WHERE v.id = 'cov4pisw00000sjctfyvwq126' AND 
CONTEXT:  PL/pgSQL function match_custom_filter(text[],text) line 5 at EXECUTE statement

What's the best way to handle this?

UPDATE:

Example of how I generate the array of string filters based off JSONB array of filter objects

def build_condition(%{"filter" => filter, "field" => field, "value" => value}) when field in @default_values do
    case filter do
      "greater_than"      -> "#{field} > #{value}"
      "less_than"         -> "#{field} < #{value}"
      "is"                -> "#{field} = '#{value}'"
      "is_not"            -> "#{field} <> '#{value}'" 
      ..

Answer 1

First, a warning. What you are doing here gives you in-stored-proc sql injection. I highly recommend you reconsider so you can properly parameterize.

Now, having said this, the obvious option is to declare a text variable and then pre-process it.

In your DECLARE block you add:

 filterstring text;

then in your body, you add:

 filterstring := array_to_string(filters, ') AND ('))
 IF filterstring = '' or filterstring is null THEN
     filterstring := 'TRUE';
 END IF;

Then you use filterstring in place of the array_to_string call in the format() call.

Note that any time you assemble a query anywhere by string interpolation you have the possibility of sql injection.

To protect against SQL injection you will need to rethink your approach a little bit. Your best option is not to use format() for your query to the extent possible. So:

execute 'SELECT 1 FROM trackings t 
      LEFT JOIN visitors v ON v.id = t.visitor_id
          WHERE v.id = $1'
   USING id;

That causes planning and filling in the value to happen on two different points. That works well in the case of a simple parameter. However it doesn't work well in the case of the dynamic filters.

Instead of passing a one-dimensional array in, you could pass a two dimensional (nx3 array) with three elements per line. These would be column name, operator, and value. You can sanitize the column name by passing it through quote_ident and the value by passing it through quote_literal but sanitizing the operators is likely to be a problem so my recommendation would be to whitelist these and throw an exception if the operator is not found. Something like:

  DECLARE 
          ...
          op TEXT;
          allowed_ops TEXT[] := ARRAY['=', '<=', '>='];
  BEGIN
       ...
       IF not(op = ANY(allowed_ops)) THEN
           RAISE EXCEPTION 'Illegal operator in function, %', op;
       END IF;
       ...
  END;

This is not going to be easy but it is doable.