Mike Lopez

Development

How to Build Secure Dynamic Filters in PHP and MySQL With PDO

By Published

An editor selecting allowed fields on a paper form before producing orderly results

Secure dynamic filters in PHP and MySQL require two different defenses. Bind every data value through PDO, and map every dynamic identifier, such as a sort column or direction, through a server-owned allowlist.

Placeholders cannot represent table names, column names, keywords, or sort directions. Trying to bind those parts leads either to broken SQL or to unsafe string interpolation. The query shape must come from trusted application code.

This example builds an authenticated administrative article search with status, category, date range, sorting, a matching total, and bounded pagination. It assumes PHP 8.2 or newer with mbstring and pdo_mysql, MySQL 8.0 or newer, and an InnoDB articles table containing id, account_id, title, excerpt, status, category_id, and published_at.

Configure PDO with exceptions, associative fetches, and PDO::ATTR_EMULATE_PREPARES => false. Native prepares expose duplicate-placeholder mistakes and let MySQL prepare the statements itself.

Authorization is part of the query, not a filter selected by the requester. Adapt this boundary to the application's real session or identity service:

<?php

declare(strict_types=1);

/**
 * Returns the server-owned account scope for an authorized article search.
 *
 * @param array<string, mixed> $session
 */
function requireArticleSearchAccount(array $session): int
{
    if (($session['can_search_articles'] ?? false) !== true) {
        throw new DomainException('Article search is not allowed.');
    }

    $accountId = filter_var(
        $session['account_id'] ?? null,
        FILTER_VALIDATE_INT,
        ['options' => ['min_range' => 1]]
    );

    if ($accountId === false) {
        throw new DomainException('Article search scope is unavailable.');
    }

    return $accountId;
}

The account identifier comes from trusted server-side identity state. It is never accepted from the query string. Because this is an authorized administrative search, the status allowlist below may include unpublished states; a public endpoint should instead enforce published in its server-owned predicate.

Define the accepted input

Start with an explicit request model instead of passing $_GET throughout the application.

<?php

declare(strict_types=1);

final readonly class ArticleFilters
{
    /**
     * Stores validated article filter values.
     */
    public function __construct(
        public ?string $search,
        public ?string $status,
        public ?int $categoryId,
        public ?string $publishedFrom,
        public ?string $publishedTo,
        public string $sort,
        public string $direction,
        public int $page,
        public int $perPage
    ) {
    }
}

/**
 * Parses an ISO date and returns its database representation.
 */
function parseDate(mixed $value): ?string
{
    if (!is_string($value) || $value === '') {
        return null;
    }

    $date = DateTimeImmutable::createFromFormat('!Y-m-d', $value);
    $errors = DateTimeImmutable::getLastErrors();

    if (
        $date === false
        || ($errors !== false && ($errors['warning_count'] > 0 || $errors['error_count'] > 0))
    ) {
        throw new InvalidArgumentException('Invalid date filter.');
    }

    return $date->format('Y-m-d');
}

/**
 * Reads and validates the supported query-string filters.
 */
function readArticleFilters(array $query): ArticleFilters
{
    $statuses = ['draft', 'published', 'archived'];
    $sorts = ['published_at', 'title', 'id'];
    $directions = ['asc', 'desc'];
    $search = isset($query['q']) && is_string($query['q'])
        ? trim($query['q'])
        : null;
    $status = isset($query['status']) && is_string($query['status'])
        ? strtolower($query['status'])
        : null;
    $categoryId = filter_var(
        $query['category_id'] ?? null,
        FILTER_VALIDATE_INT,
        ['options' => ['min_range' => 1]]
    );
    $page = filter_var(
        $query['page'] ?? 1,
        FILTER_VALIDATE_INT,
        ['options' => ['min_range' => 1, 'max_range' => 100000]]
    );
    $perPage = filter_var(
        $query['per_page'] ?? 25,
        FILTER_VALIDATE_INT,
        ['options' => ['min_range' => 1, 'max_range' => 100]]
    );
    $sort = isset($query['sort']) && is_string($query['sort'])
        ? strtolower($query['sort'])
        : 'published_at';
    $direction = isset($query['direction']) && is_string($query['direction'])
        ? strtolower($query['direction'])
        : 'desc';

    if ($search !== null && mb_strlen($search) > 100) {
        throw new InvalidArgumentException('Search is too long.');
    }

    if ($status !== null && !in_array($status, $statuses, true)) {
        throw new InvalidArgumentException('Invalid status filter.');
    }

    if (!in_array($sort, $sorts, true) || !in_array($direction, $directions, true)) {
        throw new InvalidArgumentException('Invalid sort option.');
    }

    return new ArticleFilters(
        $search === '' ? null : $search,
        $status,
        $categoryId === false ? null : $categoryId,
        parseDate($query['published_from'] ?? null),
        parseDate($query['published_to'] ?? null),
        $sort,
        $direction,
        $page === false ? 1 : $page,
        $perPage === false ? 25 : $perPage
    );
}

The limits are product choices, not universal constants. Choose them deliberately to protect response time and database work.

Build the predicate once

The data query and count query must use exactly the same filter logic. Duplicating condition assembly is a common source of mismatched totals.

<?php

declare(strict_types=1);

final readonly class SqlPredicate
{
    /**
     * Stores a SQL predicate and its bound values.
     *
     * @param array<string, int|string> $parameters
     */
    public function __construct(
        public string $sql,
        public array $parameters
    ) {
    }
}

/**
 * Builds the shared WHERE predicate for article searches.
 */
function buildArticlePredicate(ArticleFilters $filters, int $accountId): SqlPredicate
{
    $conditions = ['a.account_id = :account_id'];
    $parameters = [':account_id' => $accountId];

    if ($filters->search !== null) {
        $search = '%' . escapeLike($filters->search) . '%';
        $conditions[] = '(a.title LIKE :search_title OR a.excerpt LIKE :search_excerpt)';
        $parameters[':search_title'] = $search;
        $parameters[':search_excerpt'] = $search;
    }

    if ($filters->status !== null) {
        $conditions[] = 'a.status = :status';
        $parameters[':status'] = $filters->status;
    }

    if ($filters->categoryId !== null) {
        $conditions[] = 'a.category_id = :category_id';
        $parameters[':category_id'] = $filters->categoryId;
    }

    if ($filters->publishedFrom !== null) {
        $conditions[] = 'a.published_at >= :published_from';
        $parameters[':published_from'] = $filters->publishedFrom . ' 00:00:00';
    }

    if ($filters->publishedTo !== null) {
        $conditions[] = 'a.published_at < :published_before';
        $parameters[':published_before'] = (new DateTimeImmutable($filters->publishedTo))
            ->modify('+1 day')
            ->format('Y-m-d 00:00:00');
    }

    return new SqlPredicate(
        implode(' AND ', $conditions),
        $parameters
    );
}

/**
 * Escapes wildcard characters for a LIKE value using backslash escaping.
 */
function escapeLike(string $value): string
{
    return addcslashes($value, '\\%_');
}

Use ESCAPE '\\' explicitly if your MySQL SQL mode or portability requirements make the escape behavior ambiguous. Also remember that %term% searches generally cannot use a normal B-tree index efficiently. For large text collections, evaluate MySQL full-text search or a dedicated search system.

Map identifiers through allowlists

PDO parameters represent complete data literals, as the PDO::prepare() documentation explains. The application must map user choices to trusted SQL fragments.

<?php

declare(strict_types=1);

/**
 * Returns a trusted ORDER BY expression for validated filter choices.
 */
function articleOrderBy(ArticleFilters $filters): string
{
    $columns = [
        'published_at' => 'a.published_at',
        'title' => 'a.title',
        'id' => 'a.id',
    ];
    $directions = [
        'asc' => 'ASC',
        'desc' => 'DESC',
    ];

    return $columns[$filters->sort]
        . ' ' . $directions[$filters->direction]
        . ', a.id ' . $directions[$filters->direction];
}

The final id tie-breaker makes the order stable when titles or timestamps match. Without it, rows can move unpredictably between pages even when no data changes.

Execute both queries with one binder

Bind values based on their PHP type. Keep the binding helper shared by the count and data statements.

<?php

declare(strict_types=1);

/**
 * Binds validated scalar values to a prepared statement.
 *
 * @param array<string, int|string> $parameters
 */
function bindParameters(PDOStatement $statement, array $parameters): void
{
    foreach ($parameters as $name => $value) {
        $statement->bindValue(
            $name,
            $value,
            is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
        );
    }
}

/**
 * Returns one filtered article page and its matching total.
 *
 * @return array{items:list<array<string,mixed>>, total:int, page:int, per_page:int}
 */
function searchArticles(PDO $pdo, ArticleFilters $filters, int $accountId): array
{
    $predicate = buildArticlePredicate($filters, $accountId);
    $countStatement = $pdo->prepare(
        'SELECT COUNT(*) FROM articles a WHERE ' . $predicate->sql
    );
    bindParameters($countStatement, $predicate->parameters);
    $countStatement->execute();
    $total = (int) $countStatement->fetchColumn();

    $dataStatement = $pdo->prepare(
        'SELECT a.id, a.title, a.excerpt, a.status, a.published_at
         FROM articles a
         WHERE ' . $predicate->sql . '
         ORDER BY ' . articleOrderBy($filters) . '
         LIMIT :limit OFFSET :offset'
    );
    bindParameters($dataStatement, $predicate->parameters);
    $dataStatement->bindValue(':limit', $filters->perPage, PDO::PARAM_INT);
    $dataStatement->bindValue(
        ':offset',
        ($filters->page - 1) * $filters->perPage,
        PDO::PARAM_INT
    );
    $dataStatement->execute();

    return [
        'items' => $dataStatement->fetchAll(PDO::FETCH_ASSOC),
        'total' => $total,
        'page' => $filters->page,
        'per_page' => $filters->perPage,
    ];
}

At the request boundary, resolve authorization before accepting filters or executing SQL:

$accountId = requireArticleSearchAccount($_SESSION);
$filters = readArticleFilters($_GET);
$result = searchArticles($pdo, $filters, $accountId);

The only concatenated fragments come from application-owned predicate and order builders. All request data remains bound. This follows the primary defenses in the OWASP SQL Injection Prevention Cheat Sheet: prepared statements for values and allowlist validation where binding is not possible.

Test the combinations that break builders

Dynamic query builders fail at intersections, not only at individual filters. Add integration tests against the real supported MySQL version for:

  • No filters
  • Each filter alone
  • Search plus status and category
  • Both date boundaries
  • Duplicate sort values across page boundaries
  • An empty result
  • The maximum page size
  • Rejected status, sort, direction, date, and identifier values
  • Search containing %, _, quotes, backslashes, and multibyte text
  • Search through both title and excerpt with native prepares enabled
  • Count and data queries returning the same logical set
  • The server-owned account constraint combined with every filter

Authorization must remain in the shared predicate. The example's server-owned account_id constraint therefore applies identically to the count and data queries and cannot be replaced by a query-string value.

Pagination is a separate design choice

The example uses OFFSET because it supports numbered pages. On large or frequently changing result sets, cursor pagination may be more stable and efficient. The filters, allowlisted sorting, and authorization rules stay the same, but the cursor must include every ordering key.

First make the query construction safe and reusable. Then select OFFSET or cursor navigation based on interface requirements and measured query plans. Security and pagination performance are related, but they are not the same problem.