current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
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. It's 100% free, no registration required.
up vote 8 down vote favorite
6

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

1 Answer 1

up vote 12 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:
http://bobby-tables.com/

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

 
discard

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.