(09-16-2016, 08:13 PM)kilishan Wrote: A lot of people bring up prepared statements as the reason to use. A number of the non-PDO drivers also support that feature so that's kind of a wash.
I'm writing this from PostgreSQL's perspective.
The most important aspect of query parameterization is that if done properly, it prevents SQL injection type of attacks without requiring the developers to reinvent data-sanitizing procedures based on quoting and escaping (either client-side and therefore error-prone or server-side and therefore adding more round trips).
In most DB drivers, query parameterization is implemented either by using server-side prepared statements or by emulating them at client-side by replacing placeholders with their corresponding values. The former is suboptimal most of the time (one command is sent to prepare a statement, one to execute it, and sometimes - like in PDO - another one to deallocate the prepared statement). The latter is just unsafe.
The official Postgres client library, libpq, provides a convenient function PQexecParams(). It issues only one command to a server, sending a query string with placeholders and values separately, so the values will never have a chance to affect how the query is interpreted by the server.
In the reference PHP implementation, PQexecParams() is used internally under the hood of pg_query_params() (unconditionally) and PDOStatement::execute() (conditionally). The condition for PDOStatement::execute() to use PQexecParams() is that the option [PDO::PGSQL_ATTR_DISABLE_PREPARES => true] has to be passed either to PDO::__construct() (making it a per-handle setting) or to PDOStatement::prepare() (making it a one-time setting). By default PGSQL_ATTR_DISABLE_PREPARES is set to false, so PDO will use server-side prepared statements, which is a great feature but only when many executions of the same query with variable parameters are done. Using server-side prepared statements for single query execution is just crippling performance.
By not using pg_query_params() for query parameterization CodeIgniter leaves its users PDO as the only way of having easy, safe and performant protection from SQL injection type of attacks in a CodeIgniter-based application. Which is unfortunate, because PDO itself has the sad restriction that the question mark character is always interpreted as a placeholder, so it's impossible to use it in a query string literally as anything else than a placeholder (as in "SELECT 'a=>1,b=>2'::hstore ?| ARRAY['b','c']", for example). The restriction can be worked around, it probably can be even fixed in PDO, but for the time being, pg_query_params() would be my preference for query parameterization implementation.
To make things worse, when _protect_identifiers is true (the default), CI_DB_query_builder as of version 3 will try to escape string parameters with pg_escape_literal() regardless of whether it's actually needed or not. This adds another unnecessary round trip whenever PQexecParams() is used, because strings can be safely passed verbatim in that case.
