PHP Prepared Statements

This is the single most important security chapter in the course.

The problem

<?php
  // NEVER DO THIS
  $id = $_GET['id'];
  $sql = "SELECT * FROM users WHERE id = $id";
  $pdo->query($sql);

Visit ?id=1 OR 1=1 and the query returns every user. Visit ?id=1; DROP TABLE users-- and you may not have a users table anymore. This is SQL injection, and it has been the number-one web vulnerability for two decades.

Escaping functions are not a fix — they are easy to apply inconsistently, and one missed spot is enough.

The solution

Send the SQL and the data separately. The database then treats the data purely as a value, never as code, no matter what it contains.

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$_GET['id']]);
$user = $stmt->fetch();

Now 1 OR 1=1 is looked up as a literal id. No match, no damage.

Named placeholders

Easier to read once you have several parameters:

<?php
$stmt = $pdo->prepare(
    'SELECT * FROM books WHERE author = :author AND price <= :max'
);
$stmt->execute([
    'author' => $author,
    'max'    => $maxPrice,
]);

What placeholders cannot do

Placeholders work for values only — not for table names, column names, or keywords like ASC/DESC. For those, use a whitelist:

<?php
$allowedSort = ['title', 'price', 'created_at'];
$sort = in_array($_GET['sort'] ?? '', $allowedSort, true)
    ? $_GET['sort']
    : 'title';

$dir = ($_GET['dir'] ?? '') === 'desc' ? 'DESC' : 'ASC';

$sql = "SELECT * FROM books ORDER BY $sort $dir";  // safe: values came from OUR list

IN clauses

You need one placeholder per value:

<?php
$ids = [3, 7, 11];
$placeholders = implode(',', array_fill(0, count($ids), '?'));

$stmt = $pdo->prepare("SELECT * FROM books WHERE id IN ($placeholders)");
$stmt->execute($ids);

LIKE searches

The wildcards go in the value, not the SQL:

<?php
$stmt = $pdo->prepare('SELECT * FROM books WHERE title LIKE ?');
$stmt->execute(['%' . $search . '%']);

The habit

Every single value that came from outside your code — a form, a URL, a cookie, an API, a file — goes through a placeholder. No exceptions, not even "it's just an integer". Make it automatic and this entire class of vulnerability disappears.