Not 100% sure if this is safe

Question

Given the following PostgreSQL code, I think that this code is not vulnerable to SQL injection:

_filter 'day'
_start 1
_end 10
_sort 'article_name'
_order 'ASC'

RETURN QUERY EXECUTE '
SELECT *
FROM articles a
WHERE article_name ILIKE ''%' || $1 || '%''
ORDER BY $4 ' || $5 || '
LIMIT ($3-$2) OFFSET $2'
USING _filter, _start, _end, _sort, _order;

But I don't find that much documentation on the USING clause in combination with strings. I did find this nice article (accepted answer) on DBA: https://dba.stackexchange.com/questions/159424/how-to-use-function-parameters-in-dynamic-sql-with-execute which I think I've implemented correctly

Laurenz Albe · Accepted Answer · 2019-10-08T07:30:33.237

2

This piece of code won't do what you want.

$1 will be replaced with the first function argument, not with _filter.

Also, this is open to SQL injection.

This is what you want:

RETURN QUERY EXECUTE
   format(
      E'SELECT *\n'
      'FROM articles a\n'
      'WHERE article_name ILIKE %L\n'
      'ORDER BY %I %s\n'
      'LIMIT %s OFFSET %s',
      '%' || _filter || '%',
      _sort,
      _order,
      _end - _start,
      _start);

edited Oct 08 '19 at 07:30

answered Oct 07 '19 at 12:29

Laurenz Albe

209,280
17
206
263

mhmm... $3 doesn't "reacts" to sort, see this sample code: CREATE OR REPLACE FUNCTION get_all_articles(_filter VARCHAR, _start INTEGER, _end INTEGER, _sort VARCHAR, _order VARCHAR) RETURNS SETOF aarticles LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY EXECUTE ' SELECT a.article_id as id, a.article_name FROM aarticles a WHERE article_name ILIKE ''%'' || $1 || ''%'' ORDER BY $3 ' || _order || ' LIMIT (' || (_end - _start) || ') OFFSET $2' USING _filter, _start, _sort; END; $$; SELECT * FROM get_all_articles('c', 0, 10, 'article_id', 'DESC'); kinda lost... – Paul Oct 07 '19 at 14:49
And the table statement... CREATE TABLE aarticles (article_id INT, article_name VARCHAR); INSERT INTO aarticles(article_id, article_name) VALUES (1, 'bike'), (2, 'car'), (3, 'aircraft'); – Paul Oct 07 '19 at 14:49
I don't understand. Maybe you could open a second question with the new code and a description of your problem. – Laurenz Albe Oct 07 '19 at 15:12
I've replaced the ORDER BY with this: ORDER BY ' || quote_ident(_sort) || ' ' || _order || ' and now it work's as expected. (https://stackoverflow.com/questions/8139618/postgresql-parameterized-order-by-limit-in-table-function/8146245#8146245) For now I've withdrawn the accepted answer, I think that the correction is fair? – Paul Oct 08 '19 at 07:17
Yes. I have fixed the answer. – Laurenz Albe Oct 08 '19 at 07:30

Not 100% sure if this is safe

1 Answers1