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?

up vote 34 down vote accepted

It depends.

SQL functions

With LANGUAGE sql, the answer is generally yes.

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

With LANGUAGE plpgsql, the answer is normally yes.

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 makes SQL injection possible. You cannot tell from outside whether the function body handles it properly. Tools are provided.

Only use dynamic SQL where you need it. Plain SQL statements using parameters as values are safe against SQL injection like SQL functions.

For dynamic SQL, preferably pass values as values with:

Makes SQL injection impossible on principal.

If you concatenate values in the SQL string, use:

Wraps strings in single-quotes safely, thereby avoiding syntax errors and SQL injection.

Process parameters that shall be treated as identifiers in the SQL string with:

Encloses strings in double-quotes safely where required, thereby avoiding syntax errors and SQL injection.

Related:

Never just build a string from user input and execute. This includes identifiers, directly passed by a user or fetched from a system catalog. All have to be treated like user input and quoted safely when building dynamic SQL!

More about performance implications in this related answer:

Basics on SQL-injection:

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

  • 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? – mickeyf Sep 12 '13 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. – Erwin Brandstetter Sep 12 '13 at 21:13

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.