PHP From ZeroModern PHP course

databases storage and caching

Prepared Statements

Prepared statements separate the SQL command from the values being supplied to it. They are one of the most important habits in PHP database work because they prevent user input from being treated as SQL syntax.

Use prepared statements for values from forms, URLs, JSON bodies, cookies, sessions, queues, CSV imports, and third-party APIs. Even internal values should usually be bound as parameters because it keeps SQL consistent and reviewable.

Basic Example

PHP example
<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)');
$pdo->exec("INSERT INTO users (email) VALUES ('sam@example.com')");

$email = 'sam@example.com';

$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

echo $user['email'] . PHP_EOL;

// Prints:
// sam@example.com

The SQL contains :email. The value is passed separately to execute().

Named And Positional Placeholders

PDO supports named placeholders and positional placeholders.

PHP example
<?php

declare(strict_types=1);

$namedSql = 'SELECT id FROM users WHERE email = :email';
$positionalSql = 'SELECT id FROM users WHERE email = ?';

echo $namedSql . PHP_EOL;
echo $positionalSql . PHP_EOL;

// Prints:
// SELECT id FROM users WHERE email = :email
// SELECT id FROM users WHERE email = ?

Named placeholders are often easier to read in application code.

Dynamic Filters

Prepared statements do not mean every query must be fixed. You can build allowed SQL fragments while still binding values.

PHP example
<?php

declare(strict_types=1);

function userListSql(array $filters): array
{
    $where = [];
    $parameters = [];

    if (isset($filters['status'])) {
        $where[] = 'status = :status';
        $parameters['status'] = $filters['status'];
    }

    if (isset($filters['email_search'])) {
        $where[] = 'email LIKE :email';
        $parameters['email'] = '%' . $filters['email_search'] . '%';
    }

    $sql = 'SELECT id, email, status FROM users';

    if ($where !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    return ['sql' => $sql, 'parameters' => $parameters];
}

print_r(userListSql(['status' => 'active', 'email_search' => 'example.com']));

// Prints:
// SELECT id, email, status FROM users WHERE status = :status AND email LIKE :email
// [email] => %example.com%

The column names and operators are chosen by the application. The values are still bound.

What Cannot Be Parameterised

Placeholders are for values, not SQL structure. You cannot safely bind a table name, column name, sort direction, or SQL keyword as a normal parameter.

For dynamic sorting, use an allow-list.

PHP example
<?php

declare(strict_types=1);

function orderByClause(string $requestedSort): string
{
    return match ($requestedSort) {
        'email' => 'ORDER BY email ASC',
        'newest' => 'ORDER BY created_at DESC',
        default => 'ORDER BY id ASC',
    };
}

echo orderByClause('newest') . PHP_EOL;
echo orderByClause('anything unsafe') . PHP_EOL;

// Prints:
// ORDER BY created_at DESC
// ORDER BY id ASC

Never paste a raw request value into ORDER BY, table names, or column names.

Binding Types

execute(['id' => 123]) is enough for many cases. Sometimes you need explicit types, especially for LIMIT, booleans, or driver-specific behaviour.

PHP example
<?php

declare(strict_types=1);

$sql = 'SELECT id, email FROM users ORDER BY id LIMIT :limit';
$limit = 20;

echo $sql . PHP_EOL;
echo 'Bind :limit as integer: ' . $limit . PHP_EOL;

// Prints:
// SELECT id, email FROM users ORDER BY id LIMIT :limit
// Bind :limit as integer: 20

In real PDO code, use $statement->bindValue(':limit', $limit, PDO::PARAM_INT) before executing.

What To Check

Before moving on, make sure you can:

  • Explain why prepared statements prevent SQL injection.
  • Use named placeholders with prepare() and execute().
  • Build dynamic filters while binding values.
  • Use allow-lists for dynamic SQL structure.
  • Handle LIKE patterns deliberately.
  • Know when explicit parameter types are useful.

Practice

Practice: Prepared Search Query

Write a PHP function that builds and runs a prepared user search query.

Requirements

  • Use SQLite in memory so the example is runnable.
  • Insert sample users.
  • Search by optional status and email text.
  • Use placeholders for all values.
  • Use an allow-list for sort options.
  • Print the matching emails for at least two searches.
Show solution

This solution binds values and uses an allow-list for the sort clause.

PHP example
<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL, status TEXT NOT NULL)');
$pdo->exec("INSERT INTO users (email, status) VALUES
    ('sam@example.com', 'active'),
    ('lee@example.com', 'disabled'),
    ('alex@test.com', 'active')
");

function sortClause(string $sort): string
{
    return match ($sort) {
        'email' => 'ORDER BY email ASC',
        'newest' => 'ORDER BY id DESC',
        default => 'ORDER BY id ASC',
    };
}

function searchUsers(PDO $pdo, array $filters, string $sort = 'id'): array
{
    $where = [];
    $parameters = [];

    if (isset($filters['status'])) {
        $where[] = 'status = :status';
        $parameters['status'] = $filters['status'];
    }

    if (isset($filters['email'])) {
        $where[] = 'email LIKE :email';
        $parameters['email'] = '%' . $filters['email'] . '%';
    }

    $sql = 'SELECT email FROM users';

    if ($where !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    $sql .= ' ' . sortClause($sort);

    $statement = $pdo->prepare($sql);
    $statement->execute($parameters);

    return $statement->fetchAll(PDO::FETCH_COLUMN);
}

foreach (searchUsers($pdo, ['status' => 'active'], 'email') as $email) {
    echo $email . PHP_EOL;
}

foreach (searchUsers($pdo, ['email' => 'example.com'], 'newest') as $email) {
    echo $email . PHP_EOL;
}

// Prints:
// alex@test.com
// sam@example.com
// lee@example.com
// sam@example.com

The sort value changes only between known SQL fragments. The status and email values are passed as parameters.