Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
possible duplicate of Do stored procedures prevent SQL injection? – Colin 't Hart Jul 19 '15 at 21:06
@Colin'tHart: The question you refer to is old and has accumulated lots of votes. But the leading answers are mostly off topic - addressing SQL Server despite the postgres tag. Some general definitions, truisms and the inevitable links to Bobby Tables. No Postgres-specific information. The important default procedural language plpgsql is not even mentioned. These answers are misleading at best. Do not make this a duplicate. Maybe the other way round? Or retag the other question, since the answers are clearly not concerned with Postgres at all. – Erwin Brandstetter Jul 20 '15 at 18:33
up vote 24 down vote accepted

It depends.

With SQL functions (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 within and pass parameters.

With PL/pgSQL functions (LANGUAGE plpgsql), the answer is normally yes.

However, PL/pgSQL allows for dynamic SQL where passed parameters (or parts) can be treated as identifiers or code, which makes SQL injection possible. You cannot tell from outside whether the function body deals with that properly. Tools are provided. Basically:

If parameters should be treated as values or plain text in dynamic SQL with EXECUTE, use

If parameters should be treated as identifiers, properly sanitize them with one of these tools:

Code examples in related answers on SO here or here:

Never just build a string from user input and execute. (This includes identifiers, which have to be treated like user input when building dynamic SQL!)

More about implications on performance in this related answer:
PostgreSQL Stored Procedure Performance

Basics on SQL-injection:

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

share|improve this answer
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


By posting your answer, you agree to the privacy policy and terms of service.

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