pl/pgsql vs prepared statements against sql injection attacks

2
votes

I have more experience with prepared statements and I know they are really good against SQL injection attacks.

I was wondering if the format/USING and quote_literal/quote_nullable of pl/pgsql, are equally efficient, given the fact that prepared statements have some vulnerabilities too (check here and here).

So, is pl/pgsql safety in the same level like prepared statements? Should I consider my self safe and covered with format/USING / quote_literal/quote_nullable or I have to do more, to be more safe?

1
postgresqlpdoprepared-statementsql-injectionplpgsql
format+using is designed with injections in mind - it is as secure as it can be in postgres I would say. your links do not show prepared statement vulnerability - they show how bad design can shoot in a foot despite safe prepared statement. well - format won't save here as well – Vao Tsun
Yes, I added those links to make a point that prepared statements are not safe, especially if you dont know what u doing. Thanks – codebot
Isnt it format+using equal to quote_literall,since : (a) the L of format is equivalent to quote_nullable (here) and (b) quote_nullable works the same as quote_literal (here) – codebot
plpgsql vs prepared statements is a false dichotomy, because plpgsql code must always be called by a query. If that query is subject to an sql injection, it's game over before the plpgsql code is even reached. The question is wrong. It's like asking if in a car, it's safer to have good tires or good brakes. – Daniel Vérité
@DanielVérité but the query that calls the plpgsql maybe safe and the query in the plpgsql may have a syntactical error that makes it vulnerable to injections...A car may have new,good tires and no breaks at all... – codebot

1 Answers

4
votes

EXECUTE with USING in PL/pgSQL is 100% safe from SQL injection. The examples you quote are not relevant.

Quoting is only safe if you do it properly. This is why it is not as good as using parameters.

A statement with placeholders that uses USING is processed as a prepared statement, and the arguments given to USING become the arguments of the prepared statement. The text in the arguments is never parsed as part of the SQL statement, so SQL injection is impossible.