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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
With SQL functions (
With PL/pgSQL functions (
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
If parameters should be treated as identifiers, properly sanitize them with one of these tools:
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:
Basics on SQL-injection:
Similar considerations may apply to other server-side languages that allow dynamic SQL.