Prepared statement

From Wikipedia, the free encyclopedia
Jump to navigation Jump to search

In database management systems (DBMS), a prepared statement or parameterized statement is a feature used to pre-compile SQL code, separating it from data. Benefits of prepared statements are:[1]

  • 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):
    INSERT INTO products (name, price) VALUES (?, ?);
  2. Compile: The DBMS compiles (parses, optimizes and translates) the statement template, and stores the result without executing it.
  3. 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:

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.[2]

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; for example, some versions of MySQL did not cache results of prepared queries.[4] 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[edit]

Major DBMSs, including SQLite,[5] MySQL,[6] Oracle,[7] IBM Db2,[8] Microsoft SQL Server[9] and PostgreSQL[10] 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.[11]




Java JDBC[edit]

This example uses Java and JDBC:

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 {

        MysqlDataSource ds = new MysqlDataSource();

        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.setString(1, "shoes");
                stmt.setInt(2, 7400);
                stmt.setString(1, "phone");
                stmt.setInt(2, 29500);

            try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM products WHERE name = ?")) {
                stmt.setString(1, "shoes");
                ResultSet rs = stmt.executeQuery();


PHP PDO[edit]

This example uses PHP and PDO:


try {
    // Connect to a database named "mysql", with the password "root"
    $connection = new PDO('mysql:dbname=mysql', 'root');

    // Execute a request on the connection, which will create
    // a table "products" with two columns, "name" and "price"
    $connection->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) {

    // Prepare a new statement with a named parameter
    $statement = $connection->prepare('SELECT * FROM products WHERE name = :name');
        ':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}.";

    // Close the cursor so `fetch` can eventually be used again
} catch (\Exception $e) {
    echo 'An error has occurred: ' . $e->getMessage();

Perl DBI[edit]

This example uses Perl and DBI:

#!/usr/bin/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 = ?");
print "$$_[1]\n" foreach $sth->fetchrow_arrayref;


C# ADO.NET[edit]

This example uses C# and ADO.NET:

using (SqlCommand command = connection.CreateCommand())
    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 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:



Python DB-API[edit]

This example uses Python and DB-API:

import mysql.connector

with mysql.connector.connect(database="mysql", user="root") as conn:
    with conn.cursor(prepared=True) as cursor:
        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)

Magic Direct SQL[edit]

This example uses Direct SQL from Fourth generation language like eDeveloper, uniPaaS and magic XPA from Magic Software Enterprises

Virtual username  Alpha 20   init: 'sister'
Virtual password  Alpha 20   init: 'yellow'


Input Arguments: 
1:  username
2:  password


PureBasic (since v5.40 LTS) can manage 7 types of link with the following commands

SetDatabaseBlob, SetDatabaseDouble, SetDatabaseFloat, SetDatabaseLong, SetDatabaseNull, SetDatabaseQuad, SetDatabaseString

There are 2 different methods depending on the type of database

For SQLite, ODBC, MariaDB/Mysql use: ?

  SetDatabaseString(#Database, 0, "test")  
    ; ...

For PostgreSQL use: $1, $2, $3, ...

  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")    
    ; ...


  5. ^ "Prepared Statement Objects". SQLite. 18 Oct 2021.{{cite web}}: CS1 maint: url-status (link)
  8. ^ "Using the PREPARE and EXECUTE statements". i5/OS Information Center, Version 5 Release 4. IBM. Retrieved 25 September 2011.[permanent dead link]
  10. ^ "PREPARE". PostgreSQL 9.5.1 Documentation. PostgreSQL Global Development Group. Retrieved 27 February 2016.