A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...
s, 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
SQL
SQL is a programming language designed for managing data in relational database management systems ....
statements such as queries or updates, the prepared statement takes the form of a template
Template processor
A template processor is software or a software component that is designed to combine one or moretemplates with a data model to produceone or more result documents...
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 (DMBS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):
Query optimization is a function of many relational database management systems in which multiple query plans for satisfying a query are examined and a good query plan is identified. This may or not be the absolute best strategy because there are many ways of doing plans. There is a trade-off...
on the statement template, and stores the result without executing it.
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:
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
SQL injection
A SQL injection is often used to attack the security of a website by inputting SQL statements in a web form to get a badly designed website in order to dump the database content to the attacker. SQL injection is a code injection technique that exploits a security vulnerability in a website's software...
, 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 is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...
, 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
MySQL
MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
SQL Server may refer to:* Any database server that implements the Structured Query Language* Microsoft SQL Server, a relational database server from Microsoft* Sybase SQL Server, a relational database server developed by Sybase...
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
. 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
Java (programming language)
Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...
Java DataBase Connectivity, commonly referred to as JDBC, is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases...
Perl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier. Since then, it has undergone many changes and revisions and become widely popular...
In computing, the Perl DBI offers a standardized way for programmers using the Perl programming language to embed database communication within their programs. The latest DBI module for Perl from CPAN can run on a range of operating systems....
PHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...
's PDO. 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
H2 (DBMS)
H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode. The disk footprint is about 1 MB....
Java is a programming language originally developed by James Gosling at Sun Microsystems and released in 1995 as a core component of Sun Microsystems' Java platform. The language derives much of its syntax from C and C++ but has a simpler object model and fewer low-level facilities...
Java DataBase Connectivity, commonly referred to as JDBC, is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases...
API:
java.sql.PreparedStatement stmt = connection.prepareStatement(
"SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
stmt.setString(1, username);
stmt.setString(2, password);
stmt.executeQuery;
PHP is a general-purpose server-side scripting language originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document...
and PHP Data Objects (PDO):
$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
$stmt->execute(array($username, $password)))
Perl is a high-level, general-purpose, interpreted, dynamic programming language. Perl was originally developed by Larry Wall in 1987 as a general-purpose Unix scripting language to make report processing easier. Since then, it has undergone many changes and revisions and become widely popular...
In computing, the Perl DBI offers a standardized way for programmers using the Perl programming language to embed database communication within their programs. The latest DBI module for Perl from CPAN can run on a range of operating systems....
:
my $stmt = $dbh->prepare('SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?');
$stmt->execute($username, $password);
The source of this article is wikipedia, the free encyclopedia. The text of this article is licensed under the GFDL.
