Prepared statement

In database management systems (DBMS), 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: At first, the application creates the statement template and send it to the DBMS. Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):

   INSERT INTO products (name, price) VALUES (?, ?);

2. Then, the DBMS compiles (parses, optimizes and translates) 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 of the statement template, 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 the above example, it might supply "bike" for the first parameter and "10900" for the second parameter.

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

• The overhead of compiling the statement is incurred only once, although the statement is executed multiple times. However 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]
• Prepared statements are resilient against SQL injection because values which are transmitted later using a different protocol are not compiled like the statement template. If the 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.^[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

Major DBMSs, including MySQL,^[5] Oracle,^[6] DB2,^[7] Microsoft SQL Server^[8] and PostgreSQL.^[9] widely 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.^[10]

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,^[11] Perl's DBI,^[12] PHP's PDO ^[1] and Python's DB-API.^[13] 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.^[14]

Examples

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));
      }
    }
  }
}

<?php
$stmt = null;

try {
    $conn = new PDO("mysql:dbname=mysql", "root");
    $conn->exec("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");
    $stmt = $conn->prepare("INSERT INTO products VALUES (?, ?)");
    $params = array(array("bike", 10900),
                    array("shoes", 7400),
                    array("phone", 29500));
    foreach ($params as $param) {
        $stmt->execute($param);
    }
    $stmt = $conn->prepare("SELECT * FROM products WHERE name = ?");
    $params = array("shoes");
    $stmt->execute($params);
    echo $stmt->fetch()[1];
} finally {
    if ($stmt !== null) {
        $stmt->closeCursor();
    }
}
?>

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

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())
    {
        // ...
    }
}

import mysql.connector

conn = None
cursor = None

try:
    conn = mysql.connector.connect(database="mysql", user="root")
    cursor = conn.cursor(prepared=True)
    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])
finally:
    if cursor is not None:
        cursor.close()
    if conn is not None:
        conn.close()

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

 SetDatabaseString(#Database, 0, "test")  
 If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")    
   ; ...
 EndIf
 

 SetDatabaseString(#Database, 0, "test")  
 If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=$1")    
   ; ...
 EndIf

References