Skip to main content

Using Prepared Statements

Prepared statements keep user-supplied values separate from SQL instructions. Use them whenever a value comes from a form, URL, session or uploaded file.

Unsafe pattern

$sql = "SELECT * FROM activities WHERE location = '" . $_GET["location"] . "'";

Concatenating input into SQL can allow SQL injection and can break on punctuation.

Safe pattern

$location = trim($_GET["location"] ?? "");

$stmt = $pdo->prepare(
    "SELECT activity_id, activity_name, location, capacity
     FROM activities
     WHERE location = :location"
);
$stmt->execute(["location" => $location]);
$activities = $stmt->fetchAll();

The placeholder is part of the SQL. The value is supplied separately.

Validate before querying

if ($location === "" || mb_strlen($location) > 100) {
    exit("Choose a valid location.");
}

Prepared statements address SQL injection; validation checks whether the data is acceptable for the application. Use both.

Common operations

// One placeholder
$stmt = $pdo->prepare("SELECT * FROM activities WHERE activity_id = :id");
$stmt->execute(["id" => $activityId]);

// Several placeholders
$stmt = $pdo->prepare(
    "UPDATE activities SET capacity = :capacity WHERE activity_id = :id"
);
$stmt->execute(["capacity" => $capacity, "id" => $activityId]);

Check

  • No user value is concatenated into SQL.
  • Placeholder names are meaningful.
  • Every placeholder receives a value.
  • Input is validated before execution.
  • Displayed output is escaped with htmlspecialchars().
Back to top