# 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

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

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

## Safe pattern

~~~php
$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

~~~php
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

~~~php
// 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()`.