Prepared Statements

Prepared Statements are a useful tool for preventing SQL Injection. Instead of building an SQL string to be evaluated by the database, a database statement is prepared first. This statement contains the query string but with placeholders for any dynamic data. It is similar to defining a function with arguments for inputs. When the data is added to the statement, it must go into a pre-defined spot and must match the data type specified (string, integer, etc.). The effect is to maintain a strict separation between the SQL control and the SQL data. This makes it impossible for an attacker to get “control code” out of the data area into the statement.

An example using prepared statements in PHP.

<?php
  // prepare the statement with ? placeholder
  $sql = "SELECT name FROM products WHERE name=?";
  $stmt = mysqli_prepare($connection, $sql);

  // Bind the value to the placeholder
  // The type declaration is "s" for string.
  $name = 'Blue shirt';
  mysqli_stmt_bind_param($stmt, "s", $name);

  // Execute the statement
  mysqli_stmt_execute($stmt);

  // Bind the result column to a variable
  mysqli_stmt_bind_result($stmt, $product_name);

  // Fetch and work with each row; encode output for HTML context
  while(mysqli_stmt_fetch($stmt)) {
    echo htmlspecialchars($product_name, ENT_QUOTES, 'UTF-8');
  }
?>

---

An object-oriented example of prepared statements in PHP.

<?php
  // create the object
  $mysqli = new mysqli("localhost", "user", "pwd", "db");

  // prepare the statement with ? placeholder
  $sql = "SELECT name FROM products WHERE name=?";
  $stmt = $mysqli->prepare($sql);

  // Bind the value to the placeholder
  // The type declaration is "s" for string.
  $name = 'Blue shirt';
  $stmt->bind_param("s", $name);

  // Execute the statement
  $stmt->execute();

  // Bind the result column to a variable
  $stmt->bind_result($product_name);

  // Fetch and work with each row; encode output for HTML context
  while($stmt->fetch()) {
    echo htmlspecialchars($product_name, ENT_QUOTES, 'UTF-8');
  }
?>

---

PDO prepared statements

PHP Data Objects (PDO) is a database-agnostic API — the same code works against MySQL, PostgreSQL, SQLite, and other supported drivers. The OWASP Query Parameterization Cheat Sheet uses PDO for its PHP example, and it is the recommended choice when portability or a consistent object-oriented interface matters. PDO supports both positional (?) and named (:name) placeholders.

<?php
  // Connect. The charset=utf8mb4 clause ensures the connection speaks full
  // UTF-8. Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION is the
  // recommended configuration — the driver throws PDOException on any error
  // instead of returning false, so you do not need to check every return
  // value. PDO::ATTR_EMULATE_PREPARES => false tells the MySQL driver to use
  // native prepared statements rather than the client-side emulation it uses
  // by default; the parsed SQL and the parameter values are then sent to
  // the server separately.
  $dsn = 'mysql:host=localhost;dbname=db;charset=utf8mb4';
  $pdo = new PDO($dsn, 'user', 'pwd', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  ]);

  // Prepare the statement with a named placeholder.
  $stmt = $pdo->prepare("SELECT name FROM products WHERE name = :name");

  // Execute, passing bound values in the array argument to execute().
  $name = 'Blue shirt';
  $stmt->execute([':name' => $name]);

  // Iterate; each row is an associative array because of ATTR_DEFAULT_FETCH_MODE.
  foreach($stmt as $row) {
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8');
  }
?>

For statements that run repeatedly with different values (for example, inserting many rows in a loop), prepare once and call execute() per iteration. With native prepared statements (as configured above), the server can parse the SQL on the prepare() call and reuse the plan across executions; with the default client-side emulation the driver re-substitutes the parameters into the SQL each time, so the practical speedup varies by driver and configuration.

<?php
  $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (:name, :price)");
  foreach($rows as $row) {
    $stmt->execute([':name' => $row['name'], ':price' => $row['price']]);
  }
?>

---

What placeholders cannot cover

Prepared-statement placeholders substitute for values only — literal strings, numbers, dates, booleans, and NULLs. They cannot stand in for SQL identifiers (table names, column names) or keywords (sort direction, LIMIT/OFFSET in some drivers). The OWASP SQL Injection Prevention Cheat Sheet states that when parts of a query “can’t use bind variables, such as table names, column names, or sort order indicators (ASC or DESC), input validation or query redesign is the most appropriate defense.” Concretely, that means comparing the input against a fixed allowlist of legal values and using the allowlisted value in the query — never the raw input.

<?php
  // Sort direction. The only input that maps to DESC is the literal 'desc';
  // anything else — missing, misspelled, or attacker-supplied — falls back
  // to the safe default ASC, so the value going into the query is always
  // one of two known-good tokens.
  $sortDir = ($_GET['dir'] ?? '') === 'desc' ? 'DESC' : 'ASC';

  // Sort column. Map the request to an allowlist of legal columns.
  $allowedColumns = ['name' => 'name', 'price' => 'price', 'created' => 'created_at'];
  $sortCol = $allowedColumns[$_GET['col'] ?? ''] ?? 'name';

  // Compose the query using only allowlisted tokens for the non-value parts;
  // continue to use placeholders for the value parts.
  $sql  = "SELECT name, price FROM products WHERE category = :cat ";
  $sql .= "ORDER BY {$sortCol} {$sortDir}";
  $stmt = $pdo->prepare($sql);
  $stmt->execute([':cat' => $_GET['category'] ?? '']);
?>

Never build identifier or keyword parts of a query from concatenation with unvalidated input, even with escaping applied — the escaping functions are designed for string literals, not for identifiers or SQL keywords.