API Security

Implement Pagination, Filtering and Sorting in a PHP API

Build predictable collection endpoints with bounded page sizes, allow-listed filters and stable ordering.

Overview

Collection endpoints become difficult to use when they return every row or accept arbitrary query parameters. Pagination limits response size; filtering narrows the result set; sorting makes results useful. These features should be designed as an explicit API contract.

Bound the page size

Accept a requested page size, validate it as an integer and clamp it to a documented maximum. Never concatenate a user-provided value into SQL.

$limit = filter_input(INPUT_GET, 'limit', FILTER_VALIDATE_INT);
$limit = ($limit === false || $limit === null) ? 20 : $limit;
$limit = max(1, min($limit, 100));

$page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
$page = max(1, $page ?: 1);
$offset = ($page - 1) * $limit;

Bind numeric values as integers with PDO. For large datasets, consider cursor pagination instead of a growing OFFSET.

Allow-list sorting

Column names cannot normally be bound as SQL parameters. Map public sort names to known database columns:

$sortMap = [
    'created' => 'created_at',
    'title'   => 'title',
];
$sortKey = $_GET['sort'] ?? 'created';
$sortColumn = $sortMap[$sortKey] ?? 'created_at';

$direction = ($_GET['direction'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';
$sql = "SELECT id, title, created_at
        FROM articles
        ORDER BY {$sortColumn} {$direction}, id DESC
        LIMIT :limit OFFSET :offset";

The SQL fragments are safe because they come only from fixed allow-lists. Bind all data values.

Keep ordering stable

A sort column may contain duplicate values. Add a unique tie-breaker such as id so records do not jump unpredictably between pages. Cursor pagination should encode the last seen sort value and unique key, and validate that cursor before use.

Filtering rules

Expose only documented filters. Validate date ranges, enum values and search lengths. Combine conditions with parameterized SQL. Avoid silently accepting unknown filter names; either ignore them consistently or return a clear 400 response.

Response design

Return the data plus pagination metadata such as current page, page size and whether another page exists. Avoid expensive total-count queries on every request when the client does not need an exact total.

Checklist

  • Set a maximum page size.
  • Bind LIMIT and OFFSET as integers.
  • Allow-list sort columns and directions.
  • Add a deterministic tie-breaker.
  • Validate every filter and parameterize values.
  • Consider cursor pagination for high-volume tables.

References

PHP PDO · OWASP SQL Injection Prevention

Share this page Help other developers discover this resource.