Prepared statement

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.

The typical workflow of using a prepared statement is as follows:

  1. Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):
    • INSERT INTO PRODUCT (name, price) VALUES (?, ?)
  2. The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
  3. Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.

As compared to executing SQL statements directly, prepared statements offer two main advantages:[1]

On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server.[3] Implementation limitations may also lead to performance penalties: some versions of MySQL did not cache results of prepared queries,[4] and some DBMSs such as PostgreSQL do not perform additional query optimization during execution.[5][6]

A stored procedure, which is also precompiled and stored on the server for later execution, has similar advantages. Unlike a stored procedure, a prepared statement is not normally written in a procedural language and cannot use or modify variables or use control flow structures, relying instead on the declarative database query language. Due to their simplicity and client-side emulation, prepared statements are more portable across vendors.

Software support

Prepared statements are widely supported by major DBMSs, including MySQL,[7] Oracle,[8] DB2,[9] Microsoft SQL Server,[10] and PostgreSQL.[5] Prepared statements are normally executed through a non-SQL binary protocol, for efficiency and protection from SQL injection, but with some DBMSs such as MySQL are also available using a SQL syntax for debugging purposes.[11]

A number of programming languages support prepared statements in their standard libraries and will emulate them on the client side even if the underlying DBMS does not support them, including Java's JDBC,[12] Perl's DBI,[13] PHP's PDO [1] and Python's DB-API.[14] Client-side emulation can be faster for queries which are executed only once, by reducing the number of round trips to the server, but is usually slower for queries executed many times. It resists SQL injection attacks equally effectively.

Many types of SQL injection attacks can be eliminated by disabling literals, effectively requiring the use of prepared statements; as of 2007 only H2 supports this feature.[15]

Examples

Java JDBC

This example uses Java and the JDBC API:

java.sql.PreparedStatement stmt = connection.prepareStatement(
               "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?");
stmt.setString(1, username);
stmt.setInt(2, roomNumber);
stmt.executeQuery();

Java PreparedStatement provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types.

PHP PDO

This example uses PHP and PHP Data Objects (PDO):

$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
$stmt->execute(array($username, $pass));

Alternately:

$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME=:username AND PASSWORD=:pass");
$stmt->execute(array('username' => $username, 'pass' => $pass));

PERL DBI

This example uses Perl and DBI:

my $stmt = $dbh->prepare('SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?');
$stmt->execute($username, $password);

C# ADO.NET

This example uses C# and ADO.NET:

using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";
 
    command.Parameters.AddWithValue("@username", username);
    command.Parameters.AddWithValue("@room", room);
 
    using (SqlDataReader dataReader = command.ExecuteReader())
    {
        // ...
    }
}

ADO.NET SqlCommand will accept any type for the value parameter of AddWithValue, and type conversion occurs automatically. Note the use of "named parameters" (i.e. "@username") rather than "?" - this allows you to use a parameter multiple times and in any arbitrary order within the query command text.

However, the AddWithValue method should not be used with variable length data types, like varchar and nvarchar. This is because .NET assumes the length of the parameter to be the length of the given value, rather than getting the actual length of from the database via reflection. The consequence of this is that a different query plan is compiled and stored for each different length. In general, the maximum number of 'duplicate' plans is the product of the lengths of the variable length columns as specified in the database. For this reason, it is important to use the standard Add method for variable length columns:

command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, where ParamLength is the length as specified in the database.

Since the standard Add method needs to be used for variable length data types, it is a good habit to use it for all parameter types.

Python DB-API

This example uses Python DB-API with SQLite and paramstyle='qmark':

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
 
_users = [('mother', 'red'),
          ('father', 'green'),
          ('me', 'blue')]
c.executemany('INSERT INTO users VALUES (?,?)', _users)
 
params = ('sister', 'yellow')
c.execute('SELECT * FROM users WHERE username=? AND room=?', params)
c.fetchone()

References

  1. ↑ 1.0 1.1 The PHP Documentation Group. "Prepared statements and stored procedures". PHP Manual. Retrieved 25 September 2011.
  2. ↑ Petrunia, Sergey (28 April 2007). "MySQL Optimizer and Prepared Statements". Sergey Petrunia's blog. Retrieved 25 September 2011.
  3. ↑ Zaitsev, Peter (2 August 2006). "MySQL Prepared Statements". MySQL Performance Blog. Retrieved 25 September 2011.
  4. ↑ "7.6.3.1. How the Query Cache Operates". MySQL 5.1 Reference Manual. Oracle. Retrieved 26 September 2011.
  5. ↑ 5.0 5.1 "PREPARE". PostgreSQL 9.0.5 Documentation. PostgreSQL Global Development Group. Retrieved 26 September 2011.
  6. ↑ Smith, Lukas Kahwe (14 May 2008). "Prepared statement gotchas". Poo-tee-weet. Retrieved 26 September 2011.
  7. ↑ Oracle. "20.9.4. C API Prepared Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
  8. ↑ "13 Oracle Dynamic SQL". Pro*C/C++ Precompiler Programmer's Guide, Release 9.2. Oracle. Retrieved 25 September 2011.
  9. ↑ "Using the PREPARE and EXECUTE statements". i5/OS Information Center, Version 5 Release 4. IBM. Retrieved 25 September 2011.
  10. ↑ "SQL Server 2008 R2: Preparing SQL Statements". MSDN Library. Microsoft. Retrieved 25 September 2011.
  11. ↑ Oracle. "12.6. SQL Syntax for Prepared Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
  12. ↑ "Using Prepared Statements". The Java Tutorials. Oracle. Retrieved 25 September 2011.
  13. ↑ Bunce, Tim. "DBI-1.616 specification". CPAN. Retrieved 26 September 2011.
  14. ↑ "Python PEP 289: Python Database API Specification v2.0".
  15. ↑ "SQL Injections: How Not To Get Stuck". The Codist. 8 May 2007. Retrieved February 1, 2010.