PHP Prepared Statements

How it works

Prepare a fixed statement with placeholders, then pass the values through the driver. A placeholder represents a value, not a table name, column name or arbitrary ORDER BY expression. Allowlist identifiers when a requirement genuinely needs a variable structural choice. Parameters do not replace validation or record-level authorisation, and a prepared query can still expose data the caller should not see.

Run it locally

Run this complete source locally with PHP 8.2 or newer. Save it using the filename shown and run the command below. PHP is not currently available in Bhylu’s browser playground. Apache and MySQL are not needed for an ordinary CLI example.

php -l prepared-statements.php
php prepared-statements.php

Download this source as a local PHP lab file. Keep lab files separate from the Bhylu project and production data.

Example

<?php
declare(strict_types=1);

$db = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$db->exec('CREATE TABLE loan (id INTEGER PRIMARY KEY, item TEXT NOT NULL, quantity INTEGER NOT NULL CHECK(quantity > 0), returned INTEGER NOT NULL DEFAULT 0)');
$db->exec("INSERT INTO loan (id, item, quantity) VALUES (1, 'Spade', 2), (2, 'Drill', 1), (3, 'Spade', 3)");
$query = $db->prepare("SELECT SUM(quantity) FROM loan WHERE item = :item");
$query->execute(["item"=>"Spade"]);
echo $query->fetchColumn(), " units\n";
$query->execute(["item"=>"Spade' OR 1=1 --"]);
echo $query->fetchColumn() === null ? "No matching value\n" : "Unexpected match\n";

Expected result

5 units
No matching value

The first bound value matches two Spade records. The second value contains SQL-looking punctuation but is still treated as one data value, not a replacement for the WHERE clause. SUM returns null when there are no matching input rows in this query; that missing aggregate result needs an explicit presentation policy.