Prepared statement

Quality 0.50 · 2 views · Updated 2 months ago

Database feature

title: "Prepared statement" type: doc version: 1 created: 2026-02-28 author: "Wikipedia contributors" status: active scope: public tags: ["databases", "sql", "articles-with-example-c-sharp-code", "articles-with-example-perl-code", "articles-with-example-php-code", "articles-with-example-python-(programming-language)-code", "articles-with-example-java-code"] description: "Database feature" topic_path: "technology/databases" source: "https://en.wikipedia.org/wiki/Prepared_statement" license: "CC BY-SA 4.0" wikipedia_page_id: 0 wikipedia_revision_id: 0

::summary Database feature ::

In database management systems (DBMS), a prepared statement, parameterized statement, (not to be confused with parameterized query) is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:

  • efficiency, because they can be used repeatedly without re-compiling
  • security, by reducing or eliminating SQL injection attacks

A prepared statement takes the form of a pre-compiled template into which constant values are substituted during each execution, and typically use SQL DML statements such as INSERT, SELECT, or UPDATE.

A common workflow for prepared statements is:

  1. Prepare: The application creates the statement template and sends it to the DBMS. Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):
  2. :
  3. Compile: The DBMS compiles (parses, optimizes and translates) the statement template, and stores the result without executing it.
  4. Execute: The application supplies (or binds) values for the parameters of the statement template, and the DBMS executes the statement (possibly returning a result). The application may request the DBMS to execute the statement many times with different values. In the above example, the application might supply the values "bike" for the first parameter and "10900" for the second parameter, and then later the values "shoes" and "7400".

The alternative to a prepared statement is calling SQL directly from the application source code in a way that combines code and data. The direct equivalent to the above example is: ::code[lang=sql] INSERT INTO products (name, price) VALUES ('bike', '10900');

Not all optimization can be performed at the time the statement template 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.{{cite web|last=Petrunia|first=Sergey|date=28 April 2007|title=MySQL Optimizer and Prepared Statements|url=http://s.petrunia.net/blog/?p=16|work=Sergey Petrunia's blog|accessdate=25 September 2011|archive-url=https://web.archive.org/web/20180205184504/http://s.petrunia.net/blog/?p=16|archive-date=2018-02-05|url-status=dead}}

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.{{cite web|last=Zaitsev|first=Peter|title=MySQL Prepared Statements|url=http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/|work=MySQL Performance Blog|date=2 August 2006|accessdate=25 September 2011}} Implementation limitations may also lead to performance penalties; for example, some versions of MySQL did not cache results of prepared queries.{{cite web|title=7.6.3.1. How the Query Cache Operates|url=http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html|work=MySQL 5.1 Reference Manual|publisher=Oracle|accessdate=26 September 2011}} 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 == Major [[DBMS]]s, including [[SQLite]],{{Cite web|date=18 Oct 2021|title=Prepared Statement Objects|url=http://www.sqlite.org/c3ref/stmt.html|website=[[SQLite]]}} [[MySQL]],{{cite web |last=Oracle |title=20.9.4. C API Prepared Statements |url=https://dev.mysql.com/doc/c-api/8.4/en/c-api-prepared-statement-interface.html |accessdate=27 March 2012 |work=MySQL 5.5 Reference Manual}} [[Oracle Database|Oracle]],{{cite web|title= 13 Oracle Dynamic SQL|url= http://download.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_13dyn.htm|work= Pro*C/C++ Precompiler Programmer's Guide, Release 9.2|publisher= Oracle|accessdate= 25 September 2011}} [[IBM Db2]],{{cite web |title=SQL: Pengertian, Sejarah, Fungsi, dan Jenis Perintah SQL |url=https://www.bubblevy.com/2023/12/sql-pengertian-sejarah-fungsi-dan-jenis.html |accessdate= |publisher=}} [[Microsoft SQL Server]]{{cite web|title= SQL Server 2008 R2: Preparing SQL Statements|url= http://msdn.microsoft.com/en-us/library/ms175528.aspx|work= MSDN Library|publisher= Microsoft|accessdate= 25 September 2011}} and [[PostgreSQL]]{{cite web|title= PREPARE|url= http://www.postgresql.org/docs/9.5/static/sql-prepare.html|work= PostgreSQL 9.5.1 Documentation|publisher= PostgreSQL Global Development Group|accessdate= 27 February 2016}} support prepared statements. 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 prepared statements are also available using a SQL syntax for debugging purposes.{{cite web|last= Oracle|title= 12.6. SQL Syntax for Prepared Statements|url= http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html|work= MySQL 5.5 Reference Manual|accessdate= 27 March 2012}}

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 (programming language)|Java]]'s [[Java Database Connectivity|JDBC]],{{cite web|title= Using Prepared Statements|url= http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html|work= The Java Tutorials|publisher= Oracle|accessdate= 25 September 2011}} [[Perl]]'s [[Perl DBI|DBI]],{{cite web|last= Bunce|first= Tim|title= DBI-1.616 specification|url= https://metacpan.org/module/DBI#prepare|work= CPAN|accessdate= 26 September 2011}} [[PHP]]'s [[PHP Data Objects|PDO]] and [[Python (programming language)|Python]]'s DB-API.{{cite web|title= Python PEP 289: Python Database API Specification v2.0|url= https://www.python.org/dev/peps/pep-0249/ }} 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 | lc = on}} only [[H2 (DBMS)|H2]] supports this feature. {{cite web |url= http://thecodist.com/article/sql-injections-how-not-to-get |title= SQL Injections: How Not To Get Stuck |publisher= The Codist |date= 8 May 2007 |accessdate= February 1, 2010 }}

== Examples ==

=== Go === // Define a BookModel type which wraps a sql.DB connection pool. type BookModel struct { DB *sql.DB }

// This will insert a new book into the database. func (m *BookModel) Insert(title, author string) (int, error) { stmt := "INSERT INTO book (title, author, created) VALUES(?, ?, UTC_TIMESTAMP())"

// The "Exec" function will automatically prepare the statement for you,
// which requires an additional round-trip to the database.
//
// It is possible to avoid prepared statements, if you are sure they are not needed.
// See ExecerContext for details. https://pkg.go.dev/database/sql/driver#ExecerContext
//
// Other functions such as "Query" work the same way,
// and have an equivalent interface.
result, err := m.DB.Exec(stmt, title, author)
if err != nil {
	return 0, err
}

id, err := result.LastInsertId() // Not supported in the Postgres driver -- use RETURNING instead.
if err != nil {
	return 0, err
}

// The ID returned has the type int64, so we convert it to an int type
// before returning.
//
// Keep in mind, on 32-bit machines, this can potentially truncate the value.
// It is usually more safe to return int64 from your function directly,
// which is still supported on 32-bit machines.
return int(id), nil

} ::

The placeholder parameter syntax differs depending on your database. MySQL, SQL Server and SQLite use the ? notation, but PostgreSQL uses the $N notation. For example, if you were using PostgreSQL instead you would write:

::code[lang=go] _, err := m.DB.Exec("INSERT INTO ... VALUES ($1, $2, $3)", ...) ::

Java JDBC

This example uses Java and JDBC:

::code[lang=java] import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;

public class Main {

public static void main(String[] args) throws SQLException {
    MysqlDataSource ds = new MysqlDataSource();
    ds.setDatabaseName("mysql");
    ds.setUser("root");

    try (Connection conn = ds.getConnection()) {
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
        }

        try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO products VALUES (?, ?)")) {
            stmt.setString(1, "bike");
            stmt.setInt(2, 10900);
            stmt.executeUpdate();
            stmt.setString(1, "shoes");
            stmt.setInt(2, 7400);
            stmt.executeUpdate();
            stmt.setString(1, "phone");
            stmt.setInt(2, 29500);
            stmt.executeUpdate();
        }

        try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM products WHERE name = ?")) {
            stmt.setString(1, "shoes");
            ResultSet rs = stmt.executeQuery();
            rs.next();
            System.out.println(rs.getInt(2));
        }
    }
}

} ::

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 PDO:

::code[lang=php]

exec('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)'); // Prepare a query to insert multiple products into the table $statement = $connection->prepare('INSERT INTO products VALUES (?, ?)'); $products = [ ['bike', 10900], ['shoes', 7400], ['phone', 29500], ]; // Iterate through the products in the "products" array, and // execute the prepared statement for each product foreach ($products as $product) { $statement->execute($product); } // Prepare a new statement with a named parameter $statement = $connection->prepare('SELECT * FROM products WHERE name = :name'); $statement->execute([ ':name' => 'shoes', ]); // Use array destructuring to assign the product name and its price // to corresponding variables [ $product, $price ] = $statement->fetch(); // Display the result to the user echo "The price of the product {$product} is \${$price}."; :: ### Perl DBI This example uses [Perl](perl) and [DBI](perl-dbi): ::code[lang=perl] #!/usr/bin/env perl -w use strict; use DBI; my ($db_name, $db_user, $db_password) = ('my_database', 'moi', 'Passw0rD'); my $dbh = DBI->connect("DBI:mysql:database=$db_name", $db_user, $db_password, { RaiseError => 1, AutoCommit => 1}) or die "ERROR (main:DBI->connect) while connecting to database $db_name: " . $DBI::errstr . "\n"; $dbh->do('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)'); my $sth = $dbh->prepare('INSERT INTO products VALUES (?, ?)'); $sth->execute(@$_) foreach ['bike', 10900], ['shoes', 7400], ['phone', 29500]; $sth = $dbh->prepare("SELECT * FROM products WHERE name = ?"); $sth->execute('shoes'); print "$$_[1]\n" foreach $sth->fetchrow_arrayref; $sth->finish; $dbh->disconnect; :: ### C# ADO.NET This example uses [C#](c-sharp-programming-language) and [ADO.NET](ado-net): ::code[lang=csharp] 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 from the database via reflection. The consequence of this is that a different [query plan](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: , 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](python-programming-language) and DB-API: ::code[lang=python] import mysql.connector with mysql.connector.connect(database="mysql", user="root") as conn: with conn.cursor(prepared=True) as cursor: cursor.execute("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)") params = [("bike", 10900), ("shoes", 7400), ("phone", 29500)] cursor.executemany("INSERT INTO products VALUES (%s, %s)", params) params = ("shoes",) cursor.execute("SELECT * FROM products WHERE name = %s", params) print(cursor.fetchall()[0][1]) :: ### Magic Direct SQL This example uses Direct SQL from [Fourth generation language](4gl) like eDeveloper, uniPaaS and magic XPA from [Magic Software Enterprises](magic-software-enterprises) Virtual username Alpha 20 init: 'sister' Virtual password Alpha 20 init: 'yellow' SQL Command: SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2 Input Arguments: 1: username 2: password ### PureBasic [PureBasic](purebasic) (since v5.40 LTS) can manage 7 types of link with the following commands SetDatabase**Blob**, SetDatabase**Double**, SetDatabase**Float**, SetDatabase**Long**, SetDatabase**Null**, SetDatabase**Quad**, SetDatabase**String** There are 2 different methods depending on the type of database For **SQLite**, **ODBC**, **MariaDB/Mysql** use: ? ::code[lang=blitzbasic] SetDatabaseString(#Database, 0, "test") If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?") ; ... EndIf :: For **PostgreSQL** use: $1, $2, $3, ... ::code[lang=blitzbasic] SetDatabaseString(#Database, 0, "Smith") ; -> $1 SetDatabaseString(#Database, 1, "Yes") ; -> $2 SetDatabaseLong (#Database, 2, 50) ; -> $3 If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=$1 AND active=$2 AND years>$3") ; ... EndIf :: ## References ## References 1. The PHP Documentation Group. ["Prepared statements and stored procedures"](http://php.net/manual/en/pdo.prepared-statements.php). *PHP Manual*. 2. Petrunia, Sergey. (28 April 2007). ["MySQL Optimizer and Prepared Statements"](http://s.petrunia.net/blog/?p=16). *Sergey Petrunia's blog*. 3. Zaitsev, Peter. (2 August 2006). ["MySQL Prepared Statements"](http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/). *MySQL Performance Blog*. 4. ["7.6.3.1. How the Query Cache Operates"](http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html). *Oracle*. 5. (18 Oct 2021). ["Prepared Statement Objects"](http://www.sqlite.org/c3ref/stmt.html). 6. Oracle. ["20.9.4. C API Prepared Statements"](https://dev.mysql.com/doc/c-api/8.4/en/c-api-prepared-statement-interface.html). *MySQL 5.5 Reference Manual*. 7. ["13 Oracle Dynamic SQL"](http://download.oracle.com/docs/cd/B10501_01/appdev.920/a97269/pc_13dyn.htm). *Oracle*. 8. ["SQL: Pengertian, Sejarah, Fungsi, dan Jenis Perintah SQL"](https://www.bubblevy.com/2023/12/sql-pengertian-sejarah-fungsi-dan-jenis.html). 9. ["SQL Server 2008 R2: Preparing SQL Statements"](http://msdn.microsoft.com/en-us/library/ms175528.aspx). *Microsoft*. 10. ["PREPARE"](http://www.postgresql.org/docs/9.5/static/sql-prepare.html). *PostgreSQL Global Development Group*. 11. Oracle. ["12.6. SQL Syntax for Prepared Statements"](http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html). *MySQL 5.5 Reference Manual*. 12. ["Using Prepared Statements"](http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html). *Oracle*. 13. Bunce, Tim. ["DBI-1.616 specification"](https://metacpan.org/module/DBI#prepare). *CPAN*. 14. ["Python PEP 289: Python Database API Specification v2.0"](https://www.python.org/dev/peps/pep-0249/). 15. ["PHP manual entry for PDO::prepare()"](https://www.php.net/manual/en/pdo.prepare.php). ::callout[type=info title="Wikipedia Source"] This article was imported from [Wikipedia](https://en.wikipedia.org/wiki/Prepared_statement) and is available under the [Creative Commons Attribution-ShareAlike 4.0 License](https://creativecommons.org/licenses/by-sa/4.0/). Content has been adapted to SurfDoc format. Original contributors can be found on the [article history page](https://en.wikipedia.org/wiki/Prepared_statement?action=history). ::
databasessqlarticles-with-example-c-sharp-codearticles-with-example-perl-codearticles-with-example-php-codearticles-with-example-python-(programming-language)-codearticles-with-example-java-code