On Fri, Apr 20, 2018 at 09:33:22AM -0700, David G. Johnston wrote:
> On Fri, Apr 20, 2018 at 9:22 AM, W. Trevor King wrote:
> > format('
> > WITH RECURSIVE _ancestors(id, ancestors) AS (
> > SELECT
> > item.id AS id,
> > ARRAY[item.ancestor_id] AS ancestors
> > FROM items AS item
> > %s
> > [...]
> >
> > ', condition);
> >
> > SELECT * FROM ancestors('WHERE item.id = 62324721');
>
> Just keep in mind that this opens up a huge SQL-injection hole in
> your database. Depending on how its called you might want to
> validation the input text for both whitelist and blacklist items
> before executing it.
I'm not calling it on user-supplied conditions, but yeah, if I were it
would certainly need some guards. Unfortunately, neither format [1]
nor USING [2,3] seem to have auto-quoting for “make sure this is just
a WHERE condition [4] without side-effects” ;). I think we'd need a
WHERE-condition data type to support that, just like we'd need a
WHERE-condition data type (or a function data type) to support my
initial idea [5]:
CREATE OR REPLACE FUNCTION ancestors(condition WHERE-condition-type)
RETURNS TABLE(id integer, ancestors integer[]) AS
$$
WITH RECURSIVE _ancestors(id, ancestors) AS (
SELECT
item.id AS id,
ARRAY[item.ancestor_id] AS ancestors
FROM items AS item
WHERE condition -- or, with a function type, condition(item)
UNION ALL
…
)
…
$$ LANGUAGE SQL;
And even if you had a WHERE-condition data type, enforcing the
no-side-effects constraint would be tricky.
Things like blacklisting condition text with semicolons, etc. might
help against unintentional typos, although they seem too easily
avoided to be relied on against potentially malicious user input.
Parsing the condition text as WHERE-clause SQL to look for dangerous
constructs might be strong enough, but seems like a lot of work and
something I'm likely to get wrong if I tried ;). For now, I'm just
making sure I'm not allowing untrusted users to provide condition
text.
Thanks,
Trevor
[1]: https://www.postgresql.org/docs/10/static/functions-string.html#FUNCTIONS-STRING-FORMAT
[2]: https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#id-1.8.8.8.3.4
[3]: https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
[4]: https://www.postgresql.org/docs/10/static/sql-select.html#SQL-WHERE
[5]: https://www.postgresql.org/message-id/20180420000055.GL27577%40valgrind.us
--
This email may be signed or encrypted with GnuPG (http://www.gnupg.org).
For more information, see http://en.wikipedia.org/wiki/Pretty_Good_Privacy
