PHP Prepared Statements

In this page:

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

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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
$stmt = $db->prepare("INSERT INTO users (id, name) VALUES (:id, :name)");
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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
$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();
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
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (name TEXT)");
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute(["Alice"]);
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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
foreach (["Alice", "Bob", "Carol"] as $name) {
    $stmt->bindValue(':name', $name, SQLITE3_TEXT);
    $stmt->execute();
}
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