Is PG::Connection#exec_params identical to using a prepared statement when it comes to protect against SQL Injections?

Question

It is admitted that prepared statements provide a good protection against SQL injections and that manually injecting variables into a sql query using PG::Connection#exec is evil and should never be done.

But what about PG::Connection#exec_params ? Does it provide the same level of protection against sql injections than a prepared statement ?

If it does provide protection against sql injections, is it always true or is it only true if you explicitly bind your parameters ?

Answer 1

TL;DR yes.

I don't know the internal specifics of the Ruby driver. I assume that there are however three strategies it could use. Only one would be theoretically possible to top-level SQL-injection attacks (more on this below) but all could be vulnerable to lower-level SQL-injection attacks, as would prepared statements.

To understand this, you need to understand how PostgreSQL executes a query:

1. The query is parsed into a parse tree
2. Parameters are used to plan the query
3. The plan is executed
4. Data is presented for return.

In prepared statements the plan is saved but that's not what makes them safe. What makes them safe regarding top-level attacks is that the parameters are sent separately from the query to be parsed.

The first strategy could be server-side prepared statements. In this case plans would be saved and potentially reused which may have undesirable (or desirable) performance implications.

The second is that the PostgreSQL protocol allows for queries and parameters to be sent separately even when not using prepared statements. This has the same security benefits but does not allow plan reuse. Looking at the code, this looks like the preferred way this method works.

The third is that you could have client-side escaping. That's still safer than anything you will do yourself since it is probably centrally located etc. I know Perl's DBD::Pg can fall back to this but almost never does. I didn't see a fallback but maybe there is one I missed.

So generally, I would say yes, this does offer comparable benefits even if the worst method is used.

Note I keep talking about top-level attacks. It is possible if you call a function somewhere (from a trigger for example) that you can have SQL injection there too. This happens where dynamic SQL is involved but is not usually a problem. None of the methods above protect against this because the planning stage would happen as a sub-part of the execution stage above and the parameters have always been filled in.

This is why good coding practices on all levels of an application are important.