PostgreSQL. Is such function vulnerable to SQL injection or is it safe?

Question

Functions that looks problematic

I'm exploring postgresql database and I see a recurring pattern:

CREATE OR REPLACE FUNCTION paginated_class(_orderby text DEFAULT NULL, _limit int DEFAULT 10, _offset int DEFAULT 0)
RETURNS SETOF pg_class
LANGUAGE PLPGSQL
AS $$
BEGIN
        return  query  execute'
            select * from pg_class
        order by '|| coalesce (_orderby, 'relname ASC') ||'
        limit $1 offset $1
        '
        USING _limit, _offset;
END;
$$;

Sample usage:

SELECT * FROM paginated_class(_orderby:='reltype DESC, relowner ASC ')

Repeating is:

• _orderby is passed as text. It could be any combination of fields of returned SETOF type. E.g. 'relname ASC, reltype DESC'
• _orderby parameter is not sanitized or checked in any way
• _limit and _offset are integers

DB Fiddle for that: https://www.db-fiddle.com/f/vF6bCN37yDrjBiTEsdEwX6/1

Question: is such function vulnerable to SQL injection or not?

By external signs it's possible to suspect that such function is vulnerable to sql injection.

But all my attempts to find combination of params failed.

E.g.

CREATE TABLE T(id int);
SELECT * FROM paginated_class(_orderby:='reltype; DROP TABLE T; SELECT * FROM pg_class');

will return "Query Error: error: cannot open multi-query plan as cursor".

I did not found a way to exploit vulnerability if it exists with UPDATE/INSERT/DELETE.

So can we conclude that such function is actually safe?

If so: then why?

UPDATE. Possible plan for attack

Maybe I was not clear: I'm not asking about general guidelines but for experimental exploit of vulnerability or proof that such exploit is not possible.

DB Fiddle for this: https://www.db-fiddle.com/f/vF6bCN37yDrjBiTEsdEwX6/4 (or you can provide other of course)

My conclusions so far

A. Such attack could be possible if _orderby will have parts:

1. sql code that suppresses output of first SELECT
2. do something harmful
3. select * from pg_class so that it satisfies RETURNS SETOF pg_class

E.g.

SELECT * FROM paginated_class(_orderby:='relname; DELETE FROM my_table; SELECT * FROM pg_class')

It's easy for 2 and 3. I don't know a way to do 1st part.

This will generate: "error: cannot open multi-query plan as cursor"

B. If it's not possible to suppress first SELECT

Then:

• every postgresql function works in separate transaction
• because of error this transaction will be rollbacked
• there is no autonomous transactions as in Oracle
• for non-transactional ops: I know only about sequence-related ops
• everything else be that DML or DDL is transactional

So? Can we conclude that such function is actually safe?

Or I'm missing something?

UPDATE 2. Attack using prepared function

From answer https://stackoverflow.com/a/69189090/1168212

A. It's possible to implement Denial-of-service attack putting expensive calculation

B. Side-effects:

If you put a function with side effects into the ORDER BY clause, you may also be able to modify data.

Let's try the latter:

CREATE FUNCTION harmful_fn()
RETURNS bool
LANGUAGE SQL
AS '
DELETE FROM my_table;
SELECT true;
';

SELECT * FROM paginated_class(_orderby:='harmful_fn()', _limit:=1);

https://www.db-fiddle.com/f/vF6bCN37yDrjBiTEsdEwX6/8

Yes.

So if an attacker has right to create functions: non-DOS attack is possible too.

I accept Laurenz Albe answer but: is it possible to do non-DOS attack without function?

Ideas?

Answer 1

No, that is not safe. An attacker could put any code into your ORDER BY clause via the _orderby parameter.

For example, you can pass an arbitrary subquery, as long as it returns only a single row: (SELECT max(i) FROM generate_series(1, 100000000000000) AS i). That can easily be used for a denial-of-service attack, if the query is expensive enough. Or, like with this example, you can cause a (brief) out-of-space condition with temporary files.

If you put a function with side effects into the ORDER BY clause, you may also be able to modify data.