Reputation: 1894
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
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
Reputation: 1270411
Here is explicit logic for solving this:
where (op1 = '=' and value1 = ?) or
(op1 = '<' and value < ?) or
. . .
Upvotes: 1
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