SQL injection in Postgres functions vs prepared queries

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?

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 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 the4se guidelines:

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

If you concatenate values in the SQL string, use:

• format() with format specifier %L. Example.
• quote_literal() or quote_nullable(). Example.

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:

• format() with format specifier %I. Example.
• quote_ident(). Example.
• a cast to a registered type – regclass for table names: _tbl::regclass. (Only works for existing objects!) Example.

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

Related:

• Refactor a PL/pgSQL function to return the output of various SELECT queries
• Define table and column names as arguments in a plpgsql function?

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

More about performance implications in this related answer:

• Function Performance

Basics on SQL-injection:

• http://bobby-tables.com/

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