Nikša Baldun
Nikša Baldun

Reputation: 1894

WHERE clause operator based on column value

I'd like to have a table which contains values to compare against and operator to use (=, !=, ~, !~ etc). For example:

CREATE TABLE rule (
    value1 varchar NOT NULL,
    op1 varchar NOT NULL,
    value2 varchar NOT NULL,
    op2 varchar NOT NULL,
    ...
);

What I want can be described with this pseudo code:

SELECT * FROM rule WHERE value1 op1 ?;

In the example, I've stored the operator in a separate column, but I am open to other solutions.

Upvotes: 1

Views: 118

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

SQL is static language that does not allow to parameterize anything but values. You'd need some form of dynamic SQL - which burns down to concatenating a query string that is executed in turn.

You could make your whole SELECT statement dynamic with a server-side PL/pgSQL function. Or with any client-side logic, which entails an additional round-trip to the server, though.

Or just make the evaluation dynamic by encapsulating that part in a function:

CREATE TABLE the_rule (
   value1 text NOT NULL
 , op1    text NOT NULL
 , value2 text NOT NULL
 , op2    text NOT NULL
);

INSERT INTO the_rule VALUES
   ('foo','=','bar','<')
 , ('baz','<','bam','>');


CREATE FUNCTION rule_eval(_val text, _opr text, _arg text
                        , OUT _pass bool) AS
$func$
BEGIN
   EXECUTE format('SELECT %L %s %L', _val, _opr, _arg)
   INTO _pass;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM the_rule
WHERE  rule_eval(value1, op1, 'foo')
AND    rule_eval(value2, op2, 'aaa');

db<>fiddle here

However, this kind of obfuscation largely prohibits performance-optimized execution plans. Such functions are black boxes to the the Postgres query planner, indexes cannot be used, for example.

And you are open to SQL injection. _val and _arg are properly quoted in the example above, thereby making SQL injection impossible. But the operator cannot be quoted. You could use the object identifier type regoperator to guarantee valid operators - and cast to regoper and combine with the OPERATOR() construct to get valid syntax. Like:

CREATE TABLE the_rule (
   value1 text NOT NULL
 , op1    regoperator NOT NULL
 , value2 text NOT NULL
 , op2    regoperator NOT NULL
);

INSERT INTO the_rule VALUES
   ('foo', '=(text,text)', 'bar', '<(text,text)')
 , ('baz', '<(text,text)', 'bam', '>(text,text)');

CREATE FUNCTION rule_eval(_val text, _opr regoperator, _arg text
                        , OUT _pass bool) AS
$func$
BEGIN
   EXECUTE format('SELECT %L OPERATOR(%s) %L', _val, _opr::regoper, _arg)
   INTO _pass;
END
$func$  LANGUAGE plpgsql;

-- Same query as above

db<>fiddle here

Now, SQL injection is impossible. But we have introduced more complexity, yet. And I am not sure reoperator stays valid across dump / restore cycles or major version upgrades. (Probably better to persist the text representation after all.)

Or if you only allow a predetermined set of operators - with a FK constraint to a secure lookup table or an enum type or just a plain CHECK constraints for a hand full of allowed operators. Like:

CREATE TABLE the_rule (
   value1 text NOT NULL
 , op1    text NOT NULL CHECK (op1 = ANY ('{>,>=,=,<=,<}'))
 , value2 text NOT NULL
 , op2    text NOT NULL CHECK (op2 = ANY ('{>,>=,=,<=,<}'))
);

INSERT INTO the_rule VALUES
   ('foo', '=', 'bar', '<')
 , ('baz', '<', 'bam', '>');

CREATE FUNCTION rule_eval(_val text, _opr text, _arg text
                        , OUT _pass bool) AS
$func$
BEGIN
   EXECUTE format('SELECT %L %s %L', _val, _opr, _arg)
   INTO _pass;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM the_rule
WHERE  rule_eval(value1, op1, 'foo')
AND    rule_eval(value2, op2, 'aaa');

db<>fiddle here

Input from the table is secure but the function itself is an entry point for SQL injection now.

And we haven't even touched complications with different data types, yet.

In short: possible, but you need to know exactly what you are doing to deal with various kinds of possible complications. Typically, there is a simpler way to implement your requirements.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270411

Here is explicit logic for solving this:

where (op1 = '=' and value1 = ?) or
      (op1 = '<' and value < ?) or
      . . .

Upvotes: 1

D-Shih
D-Shih

Reputation: 46239

You can try to use OR and AND in condition.

SELECT * 
FROM rule 
WHERE 
    (op1 = '=' AND value1 = ?) 
OR
    (op1 = '!=' AND value1 != ?)

Upvotes: 1

Related Questions