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:
Prepare: The statement template is created by the application and sent to
the database management system. Certain values are left unspecified, called
parameters, placeholders or bind variables:
INSERT INTO PRODUCT VALUES The DBMS parses, compiles, and performs
query optimization on the statement template, and stores the result without
executing it. Execute: At a later time, the
application supplies values for the parameters, and the DBMS executes the
statement. 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: The overhead of compiling and optimizing
the statement is incurred only once, although the statement is executed
multiple times. Not all optimization can be performed at the time the prepared
statement is compiled, for two reasons: the best plan may depend on the specific
values of the parameters, and the best plan may change as tables and indexes
change over time. Prepared statements are resilient
against SQL injection, because parameter values, which are transmitted later
using a different protocol, need not be correctly escaped. If the original
statement template is not derived from external input, SQL injection cannot
occur. 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. Implementation limitations may also lead
to performance penalties: some versions of MySQL did not cache results of
prepared queries, and some DBMSs such as PostgreSQL do not perform additional
query optimization during execution. 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, Oracle,
DB2, Microsoft SQL Server, and PostgreSQL. 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.
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, Perl's DBI, PHP's
PDO and Python's DB-API. 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. Examples
= Java JDBC = This example uses Java and the JDBC API:
Java PreparedStatement provides "setters", setString(String),
setDouble(double), etc.) for all major built-in data types.
= PHP PDO = This example uses PHP and PHP Data
Objects: Alternately:
= Perl DBI = This example uses Perl and DBI:
= C# ADO.NET = This example uses C# and ADO.NET:
ADO.NET SqlCommand will accept any type for the value parameter of AddWithValue,
and type conversion occurs automatically. Note the use of "named
parameters" 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':
References ^ a b The PHP Documentation Group.
"Prepared statements and stored procedures". PHP Manual. Retrieved 25
September 2011. ^ Petrunia, Sergey. "MySQL Optimizer and
Prepared Statements". Sergey Petrunia's blog. Retrieved 25 September 2011.
^ Zaitsev, Peter. "MySQL Prepared Statements". MySQL Performance Blog.
Retrieved 25 September 2011. ^ "7.6.3.1. How the Query Cache
Operates". MySQL 5.1 Reference Manual. Oracle. Retrieved 26 September 2011.
^ a b "PREPARE". PostgreSQL 9.0.5 Documentation. PostgreSQL Global
Development Group. Retrieved 26 September 2011.
^ Smith, Lukas Kahwe. "Prepared statement gotchas". Poo-tee-weet.
Retrieved 26 September 2011. ^ Oracle. "20.9.4. C API Prepared
Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
^ "13 Oracle Dynamic SQL". Pro*C/C++ Precompiler Programmer's Guide, Release
9.2. Oracle. Retrieved 25 September 2011.
^ "Using the PREPARE and EXECUTE statements". i5/OS Information Center,
Version 5 Release 4. IBM. Retrieved 25 September 2011.
^ "SQL Server 2008 R2: Preparing SQL Statements". MSDN Library. Microsoft.
Retrieved 25 September 2011. ^ Oracle. "12.6. SQL Syntax for Prepared
Statements". MySQL 5.5 Reference Manual. Retrieved 27 March 2012.
^ "Using Prepared Statements". The Java Tutorials. Oracle. Retrieved 25
September 2011. ^ Bunce, Tim. "DBI-1.616 specification".
CPAN. Retrieved 26 September 2011. ^ "Python PEP 289: Python Database API
Specification v2.0". ^ "SQL Injections: How Not To Get
Stuck". The Codist. 8 May 2007. Retrieved February 1, 2010.