Prepared Statements

Why Prepared Statements Matter

Prepared statements separate SQL structure from user data. This prevents SQL injection and makes your queries safer and more predictable.

The Wrong Way (Do Not Do This)

Concatenating user input into SQL is dangerous.

<?php
$q = $_GET["q"] ?? "";

// Dangerous:
$sql = "SELECT id, title FROM items WHERE title LIKE '%" . $q . "%'";
echo $sql;

The Correct Way (Binding Parameters)

Use placeholders and bind values via execute().

<?php
$q = $_GET["q"] ?? "";

$stmt = $pdo->prepare("SELECT id, title FROM items WHERE title LIKE ? ORDER BY id DESC LIMIT 20");
$stmt->execute(["%" . $q . "%"]);

$rows = $stmt->fetchAll();
foreach ($rows as $r) {
  echo $r["id"] . " - " . $r["title"] . PHP_EOL;
}

Named Placeholders

Named placeholders can improve readability.

<?php
$stmt = $pdo->prepare("SELECT id, title FROM items WHERE title LIKE :q ORDER BY id DESC LIMIT 20");
$stmt->execute(["q" => "%" . $q . "%"]);

Whitelist for Identifiers

You cannot bind table names or column names. For ORDER BY and similar identifiers, use a whitelist.

<?php
$sort = $_GET["sort"] ?? "new";
$allowed = [
  "new" => "id DESC",
  "old" => "id ASC",
  "title" => "title ASC",
];

$orderBy = $allowed[$sort] ?? $allowed["new"];

$sql = "SELECT id, title FROM items ORDER BY " . $orderBy . " LIMIT 20";
$rows = $pdo->query($sql)->fetchAll();

Production Tip

Bind all user values, validate types (int casting for IDs), and use allowlists for identifiers. This single habit prevents a huge class of security bugs.