2

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?

codebot
  • 517
  • 8
  • 29
  • 55
  • 1
    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 Jan 25 '18 at 17:15
  • 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 Jan 25 '18 at 17:27
  • Isnt it format+using equal to `quote_literall`,since : (a) the L of format is equivalent to `quote_nullable` ([here](https://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT)) and (b) `quote_nullable` works the same as `quote_literal` ([here](https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)) – codebot Jan 25 '18 at 17:32
  • 1
    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é Jan 25 '18 at 18:43
  • @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 Jan 25 '18 at 19:18
  • that was the Daniel's ponit I believe - no use talking about not safe tires if you ignore brakes and vise versa. I need to take care of one to make the other work – Vao Tsun Jan 26 '18 at 18:46

1 Answers1

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    Yes, I added the links just to show that sometimes prepared statements can introduce risks, especially if you dont know how to syntax them. Also...Isnt it format+using equal to `quote_literall`,since : (a) the L of format is equivalent to `quote_nullable` ([here](https://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT)) and (b) `quote_nullable` works the same as `quote_literal` ([here](https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN))...so I guess format+using = `quote_literall` ? No? Thanks – codebot Jan 25 '18 at 17:34
  • 1
    They are somewhat similar. If you forget to use `quote_*` or use the `%s` format in the wrong place, you are vulnerable. – Laurenz Albe Jan 25 '18 at 19:30
  • The docs confused me again about USING. USING "is much less prone to SQL-injection attacks since there is no need for quoting or escaping". [here](https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) . How USING is safe if there is no quoting/escaping? Am I suppose to conclude that `EXECUTE/format/USING` is translated to a prepared statement by `SPI_prepare` according to [this](https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING) ? Does `SPI_prepare` also applies to dynamic `EXECUTE` queries ? – codebot Jan 29 '18 at 01:10
  • Saying that USING "is much less *prone* to SQL-injection attacks" is like saying that it could get attacked. I dont get if quoting/escaping of `quote_literal` is better or worst that EXECUTE/format/USING that does no quoting/escaping and I have no way to test it myself. I concluded that `SPI_prepare` does not apply for EXECUTE, so I am chasing my tail here. – codebot Jan 29 '18 at 01:14
  • @codebot The query is safe against SQL injections when a) all parameters are escaped (sanitized), b) when parameters are passed by separate way, and cannot to change the query semantic. quote_xxx does @a, `USING` does @b. Usually everything what is possible should be passed by `USING`. What is not possible (SQL identifiers) must be escaped. – Pavel Stehule Jan 29 '18 at 06:08
  • I have added a paragraph that should explain processing in more detail. – Laurenz Albe Jan 29 '18 at 08:36
  • @LaurenzAlbe OK, you say that " The text in the arguments is never parsed as part of the SQL statement, so SQL injection is impossible." I know this is the right behavior for a successful prepared statement. This is what I thought too. – codebot Jan 29 '18 at 12:58
  • @LaurenzAlbe ...Until I read this is in the [doc](https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) "An EXECUTE with a simple constant command string and some USING parameters, as in the first example above, is functionally equivalent to just writing the command directly in PL/pgSQL and allowing replacement of PL/pgSQL variables to happen automatically. " . Does this means that the command and variables are all automatically parsed together? Because if this is true, then there is no prepared statement behavior. – codebot Jan 29 '18 at 13:02
  • @PavelStehule Yes, I guess that EXECUTE+USING is better than `quote_*`,since the first method behaves like prepared statements, separating the SQL command from the variables. With `quote_*` you just escape strings that is not the safest choice. I am a bit confused by the docs, (see my latest comments here), but if parsing everything together is not the case, then EXECUTE+USING are actually safe. – codebot Jan 29 '18 at 13:09
  • 1
    No, it means that `EXECUTE 'SELECT $1' USING myvar;` is the same as `SELECT myvar;`. – Laurenz Albe Jan 29 '18 at 13:10
  • @codebot - If you use escaping correctly, then security is 100%. There can be small performance differences - quoting needs few more operations - conversions from/to string for each quoted parameter and escaping + string concatenation. `USING` uses binary values. – Pavel Stehule Jan 29 '18 at 13:25