TopolearnPrepared statements are the standard defence against SQL injection and the base of every PHP ORM.
PDO is PHP's database abstraction layer. The same API talks to MySQL, PostgreSQL, SQLite and others through drivers:
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $pass);Since PHP 8.0 errors throw PDOException by default. Setting PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC makes rows plain associative arrays.
Never build SQL by concatenating input. Use prepared statements with placeholders, and the values travel separately from the SQL, so they can't change its meaning:
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch(); // one row, or falsefetchAll() returns all rows, fetchColumn() one value, and fetch(PDO::FETCH_CLASS) or fetchObject(User::class) hydrate objects. lastInsertId() returns the new id after an insert, and rowCount() the rows affected by an update or delete.
Placeholders work only for values, not table or column names; whitelist those in code.
Wrap related writes in a transaction: beginTransaction(), then commit(), or rollBack() in a catch. PHP 8.4 added driver-specific subclasses such as Pdo\Sqlite and Pdo\Pgsql, created with PDO::connect(), which expose driver-only methods.
Example
<?php
$pdo = new PDO('sqlite::memory:', options: [
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, credits INT)');
$insert = $pdo->prepare('INSERT INTO users (email, credits) VALUES (?, ?)');
foreach (['a@x.io' => 10, 'b@x.io' => 5] as $email => $credits) {
$insert->execute([$email, $credits]);
}
$evil = "x' OR '1'='1";
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $evil]);
var_dump($stmt->fetch()); // bool(false): no injection
try {
$pdo->beginTransaction();
$pdo->exec("UPDATE users SET credits = credits - 3 WHERE id = 1");
$pdo->exec("UPDATE users SET credits = credits + 3 WHERE id = 2");
$pdo->commit();
} catch (PDOException $e) {
$pdo->rollBack();
throw $e;
}
print_r($pdo->query('SELECT credits FROM users')->fetchAll(PDO::FETCH_COLUMN)); // [7, 8]Remember
- Use prepared statements with placeholders for every value
- PDO throws PDOException on errors since PHP 8
- Group related writes in beginTransaction/commit with rollBack on failure
Watch out for
- Interpolating a variable into the SQL string passed to prepare(), which defeats the point.