PHP Prepared Statements

A prepared statement is like a form letter with blanks that you fill in later. Because the question and the answers are kept apart, sneaky visitors cannot trick your database.

In this page:

What are Prepared Statements?
Binding Parameters
Executing Statements
Fetching Results Safely
SQL Injection Protection

Syntax

php

$stmt = $pdo->prepare("SELECT * FROM table_name WHERE column = :name");
$stmt->execute(["name" => $value]);

$stmt = $mysqli->prepare("SELECT * FROM table_name WHERE column = ?");
$stmt->bind_param("s", $value);   // s = string, i = integer
$stmt->execute();

What are Prepared Statements?

A prepared statement separates a SQL query's fixed structure from the variable data it operates on, sending the query template to the database first and the actual values afterward as parameters, rather than building one combined string.

Example: What are Prepared Statements?

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (id, name) VALUES (:id, :name)")`
$stmt = $db->prepare("INSERT INTO users (id, name) VALUES (:id, :name)");
// Print "Template prepared before any values are sent" to the output
echo "Template prepared before any values are sent";
?>

Binding Parameters

Because the database treats bound parameters strictly as data — never as executable SQL syntax — prepared statements eliminate SQL injection for the values they cover, which is why they're considered the standard defense against it.

Example: Binding Parameters

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (id, name) VALUES (:id, :name)")`
$stmt = $db->prepare("INSERT INTO users (id, name) VALUES (:id, :name)");
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$stmt->bindValue(':name', "Robert'); DROP TABLE users;", SQLITE3_TEXT);
$stmt->execute();
// Print "Malicious input treated as plain data, not SQL" to the output
echo "Malicious input treated as plain data, not SQL";
?>

Executing Statements

In mysqli, you prepare a statement with placeholders (?), bind values with bind_param() specifying each value's type, then call execute() — a slightly more verbose flow than PDO's.

Example: Executing Statements

php

<?php
// mysqli style: $stmt = $conn->prepare("INSERT INTO users (name) VALUES (?)");
// $stmt->bind_param("s", $name); $stmt->execute();
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->bindValue(1, "Alice", SQLITE3_TEXT);
$stmt->execute();
echo "Executed via positional placeholder";
?>

Fetching Results Safely

In PDO, you prepare a statement with named or positional placeholders and pass an array of values directly to execute($values), which many developers find more concise than mysqli's separate bind_param() step.

Example: Fetching Results Safely

php

<?php
// Create a new `PDO` instance with 'sqlite::memory:', stored in `$pdo`
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (name TEXT)");
// Declare `$stmt`, set to `$pdo->prepare("INSERT INTO users (name) VALUES (?)")`
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute(["Alice"]);
// Print "PDO lets you pass values directly to execute()" to the output
echo "PDO lets you pass values directly to execute()";
?>

SQL Injection Protection

A prepared statement can be executed multiple times with different bound values without re-parsing the SQL each time, which is also a meaningful performance win when running the same query repeatedly, like inserting many rows in a loop.

Example: SQL Injection Protection

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (name) VALUES (:name)")`
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
// Loop over `["Alice", "Bob", "Carol"]`, binding each item to `$name`
foreach (["Alice", "Bob", "Carol"] as $name) {
    $stmt->bindValue(':name', $name, SQLITE3_TEXT);
    $stmt->execute();
}
// Print "Same prepared statement reused for each insert" to the output
echo "Same prepared statement reused for each insert";
?>

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP CRUD Operations Next Lesson → PHP Stored Procedures