PHP PDO Prepared Statements
Prepared statements protect you from SQL injection by separating the query template from the data. This snippet shows the canonical PDO pattern: connect, `prepare`, bind parameters by name, `execute`, and fetch. The runnable accordions use an in-memory SQLite database (`sqlite::memory:`) so the test wrapper does not need an external DB; the same code shape works against MySQL or PostgreSQL by changing the DSN.
948 views
7
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Set up a tiny demo schema.
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');
// Insert with named placeholders. Bind values, then execute.
$insert = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert->execute([':name' => 'Ada', ':email' => '[email protected]']);
$insert->execute([':name' => 'Linus', ':email' => '[email protected]']);
// Read back with fetchAll.
$rows = $pdo->query('SELECT id, name, email FROM users ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);PDO is PHP's database-agnostic interface; the DSN string (sqlite::memory: here) tells it which driver to use. Setting ERRMODE_EXCEPTION is essential: without it, PDO silently swallows query errors and you have to check return values everywhere. prepare parses and validates the SQL once; execute runs it with new parameter values, which means repeated inserts share the parsed plan. Named placeholders (:name, :email) are easier to read than positional ? and impervious to argument-order mistakes.
// Continuing from the connection above.
$select = $pdo->prepare('SELECT id, name, email FROM users WHERE name LIKE :q');
$select->execute([':q' => 'A%']);
// fetch() returns one row at a time; fetchAll() returns all of them.
// FETCH_ASSOC gives associative arrays keyed by column name.
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
echo $row['id'], ": ", $row['name'], " <", $row['email'], ">\n";
}
// FETCH_OBJ returns stdClass objects, more ergonomic when columns map to fields.
$select->execute([':q' => '%']);
$objs = $select->fetchAll(PDO::FETCH_OBJ);
foreach ($objs as $o) {
echo "obj: $o->name $o->email\n";
}Use fetch() in a while loop for streaming large result sets; the rows come from the driver one at a time so memory stays bounded. fetchAll() materialises everything at once and is fine for small queries. The fetch mode controls the row shape: FETCH_ASSOC returns [col => value] (most common for JSON output), FETCH_OBJ returns stdClass objects, FETCH_NUM returns positional arrays. Pass parameters in the execute([...]) form rather than concatenating into the SQL; the database treats the values as data, not code, which is what closes the SQL injection vector.
// A transaction makes a group of statements atomic: all commit or none do.
$pdo->beginTransaction();
try {
$update = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
$update->execute([':email' => '[email protected]', ':id' => 1]);
$update->execute([':email' => '[email protected]', ':id' => 2]);
$pdo->commit();
echo "committed\n";
} catch (Throwable $e) {
$pdo->rollBack();
echo "rolled back: ", $e->getMessage(), "\n";
}
$rows = $pdo->query('SELECT id, name, email FROM users')->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);Wrap related writes in beginTransaction / commit so a partial failure rolls the whole batch back via rollBack. The try / catch (Throwable) shape matters: if any prepared statement throws (the ERRMODE_EXCEPTION flag we set earlier), control jumps to the catch block and rollBack() undoes whatever was already executed in the transaction. Without a transaction, a failed second insert would leave the first insert visible to other connections, breaking invariants that depend on both writes succeeding. Always pair beginTransaction with both a commit on the success path AND a rollback in the catch block; orphaned transactions hold locks and break replication.