47

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?

0

1 Answer 1

58

It depends.

Prepared statements

A.k.a. "prepared queries". These only take values when executed with EXECUTE. No SQL-injection possible at this stage. But you have to defend against SQL-injection while generating / concatenating the statement to be fed to PREPARE in the first place. Prepared statements are very much equivalent to stored SQL functions / procedures in this regard.

SQL functions / procedures

With LANGUAGE sql they are generally safe.

Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from the body and pass parameters.

PL/pgSQL functions /procedures

With LANGUAGE plpgsql they are normally safe.

However, PL/pgSQL allows for dynamic SQL where passed parameters (or parts) are concatenated to a query string and executed with EXECUTE. This can convert user input to SQL code and make SQL injection possible. Tools are there to do it safely. You cannot tell from outside whether the function body handles it properly, you have to look at the code.

Plain SQL statements using parameters as values are safe against SQL injection, just like SQL functions. Only use dynamic SQL where needed and follow these guidelines:

Preferably pass values as values to EXECUTE with the USING clause. Makes SQL injection impossible on principal. Example.

Never just build strings from user input and execute. This includes identifiers, directly passed by a user or fetched from a system catalog.

Related:

More about performance implications in this related answer:

Basics on SQL-injection:

Similar considerations apply to other server-side languages that allow dynamic SQL.

4
  • So, in summary: If 1) I use only language sql, I'm safe, 2) if I use plpgslq but not execute, I'm safe, 3) if I use plpgsql and execute but no identifiers and %s or %L as appropriate I'm safe, or 4) if I use plpgsql and execute and identifiers but %I or quote_ident as appropriate I'm safe. Correct?
    – user4930
    Sep 12, 2013 at 17:28
  • @mickeyf: Basically yes. Plus, use the USING clause for passing values to EXECUTE whenever possible. You could call a PL/pgSQL function from within an SQL function and pass parameters. So, to be absolutely correct, you are safe as long as you don't call any unsafe functions directly or indirectly. If all your functions are done properly, that cannot happen. Sep 12, 2013 at 21:13
  • Would "Concatenating other parts of the SQL code" be safe if done in FORMAT with %s?
    – Rafs
    Nov 30, 2023 at 12:52
  • @Rafs Generally no. There are exceptions, where SQLi is not possible a priori. Nov 30, 2023 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.