Prepared Statement

An SQL query or "statement" that is pre-defined on the database server, typically with parameters that can be passed arguments when the statement is executed. Unlike typical SQL query execution where a query string is executed immediately, PreparedStatements separate definition from execution. A PreparedStatement is defined using SQL -- including indicated parameters and (frequently, but not always) parameter types -- in one step, and can be executed (repeatedly, if necessary) along with specified parameter arguments in a separate step.

Use of PreparedStatements provides two main benefits over equivalent DynamicSql:

• If a given query needs to be repeatedly re-executed -- particularly if the query needs to be re-run with varying parameter arguments -- re-executing a PreparedStatement requires less CPU resource (i.e., runs faster and/or consumes less energy) than re-executing DynamicSql. This is because dynamically-generated SQL needs to be created, parsed, planned and executed every time. A given PreparedStatement is created, parsed and planned only once; (re-)execution only runs (or re-runs) the predefined query plan. In rare circumstances, this can result in a suboptimal query plan for particular parameter arguments, with slower than expected execution.

• As long as care is taken to never incorporate external data in PreparedStatement definitions, classic SqlInjection is effectively eliminated without having to rely on quote-escaping, keyword scanning, or other filtering mechanisms. This is because parameter arguments are only bound to a pre-existing query plan, and thus cannot be used to construct a new -- possibly malicious -- query.

Let's look at a simple example in PHP using PostgreSQL. Assume we want to select all customers from a customers table with a specified name, which has been obtained from the user and stored in a variable $userInput.

If we don't use prepared statements and naïvely use DynamicSql, the simplest (and most dangerous) case could be this:

 <?php
 // Connect to a database named "mydb"
 $dbconn = pg_connect("dbname=mydb");

 // Create a query for execution
 $qry = "SELECT * FROM customers WHERE name = '" + $userInput + "'";

 // Execute the query.
 $result = pg_query($dbconn, $qry);
 ?>

If $userInput is, say, "'; delete from customers; --", we're going to be in trouble. This dangerously simplistic example invites SQL injection.

If we're going to create dynamic SQL from user input, we must sanitise the user input, as follows:

 <?php
 // Connect to a database named "mydb"
 $dbconn = pg_connect("dbname=mydb");

 // Create a query for execution
 $qry = "SELECT * FROM customers WHERE name = '" + pg_escape_string($userInput) + "'";

 // Execute the query.
 $result = pg_query($dbconn, $qry);
 ?>

This requires discipline and care to ensure that pg_escape_string() (or some appropriate equivalent) is consistently used, and we need assurance that pg_escape_string() is reliable and complete enough to ensure sanitisation against any possible attack. That adds complexity and risk: we have to trust the filters to be correct, and we have to ensure that they're used consistently and correctly.

Using prepared statements, the above would be:

 <?php
 // Connect to a database named "mydb"
 $dbconn = pg_connect("dbname=mydb");

 // Prepare a query for execution
 $result = pg_prepare($dbconn, "myquery", 'SELECT * FROM customers WHERE name = $1');

 // Execute the prepared query.
 $result = pg_execute($dbconn, "myquery", array($userInput));
 ?>

The pg_prepare function sends the query string to the DBMS for compilation, and identifies the parametric query with the name "myquery". It is now prepared, i.e., compiled, for execution.

The pg_execute function invokes "myquery", passing only the data for the parameter identified by '$1'. It does not construct a new query string from "myquery" + $1 for parsing, planning and execution. It merely passes the argument to 'myquery' -- which has already been parsed and planned (compiled) via pg_prepare -- in the same manner as passing an argument to a pre-existing stored procedure. If "userInput" consists of "'; delete from customers; --", all it will do is harmlessly try to select a customer whose name is "'; delete from customers; --".

---

The idea of PreparedStatement is straightforward enough, but how is one forced to interface with them in modern DBMSs? Are PreparedStatements typically associated directly with the DBMS, or do DBMSs allow session-created PreparedStatements? It seems to me the latter would be at least as efficient but far more flexible: it'd reduce how much you're tied to the Database (since the regular SQL could still be executed, and applications wouldn't all be tied to a single version of the statement) and a little caching would effectively allow one to avoid recompiling the PreparedStatement when many instances of the same application-version connect.

Anyone know of a good reference on these things?

References tend to be product specific. See, for example, http://www.postgresql.org/docs/current/static/sql-prepare.html or http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

---

See TopOnPreparedStatements