Choosing Between PDO Prepared Statements and an ORM for Safe, Scalable PHP Data Access

Decision and Constraints

When building a PHP application that talks to a relational database you must balance three concerns:

1. SQL injection safety – user‑supplied values must never be interpreted as SQL.
2. Pagination performance on large tables – OFFSET/LIMIT becomes costly as the table grows; keyset (seek) pagination scales better.
3. Maintainability in a long‑lived codebase – the data‑access layer should be easy to test, extend, and keep performant without hidden N+1 queries.

Both PDO prepared statements and a modern ORM/query builder (e.g., Doctrine DBAL or QueryBuilder) can satisfy these constraints, but they do so with different trade‑offs. The following guide helps you pick the approach that matches your project’s priorities.

Comparison Table

Aspect	PDO Prepared Statements	ORM / Query Builder
Injection safety	Baseline: bound parameters are sent separately to the driver; emulation can be disabled for native prepares.	Safe when using the builder’s parameter binding; raw expression APIs can re‑introduce risk if misused.
Control over SQL	Full control – you write the exact statement, tune indexes, and see what is executed.	SQL is generated; you can inspect it via logging, but complex tweaks may require dropping to raw SQL.
Pagination	You implement keyset pagination manually (e.g., WHERE id > ? ORDER BY id LIMIT ?). OFFSET works but scales poorly.	Many ORMs provide pagination helpers; keyset support varies – you may need to write custom DQL or use extensions.
Boilerplate	Requires a thin repository layer to map rows to DTOs/entities.	Reduces boilerplate via entity mapping and repository patterns; however, entity hydration adds overhead.
Learning curve	Low to moderate – familiarity with PDO and prepared statements is common.	Higher – you must learn the ORM’s API, annotation/xml configuration, and lazy‑loading pitfalls.
Portability	Driver‑specific features (e.g., LIMIT placeholder support) must be checked per database.	ORMs abstract differences, making it easier to switch databases (though performance characteristics may change).

Trade‑off Summary

• Choose PDO when you need maximum performance, fine‑grained SQL tuning, or are working on a micro‑service where the query set is relatively simple and stable.
• Choose an ORM/query builder when query complexity, rapid feature development, or database portability outweigh the overhead of generated SQL, and you are willing to invest in query logging and eager‑loading strategies to avoid N+1 problems.
• Middle ground – a small repository wrapper around PDO gives you testability, explicit SQL, and injection safety while keeping the ability to swap the underlying driver later.

Concrete Implementation: PDO‑Based Repository with Keyset Pagination

The example below shows a thin repository for a users table. It uses PDO with exceptions enabled, disables emulated prepares to let MySQL handle parsing, and implements keyset pagination using the primary key id.

1. PDO Connection Setup

<?php
// config/database.php
$dsn = 'mysql:host=db.example.com;dbname=app;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false, // native prepares
];
$pdo = new PDO($dsn, $dbUser, $dbPass, $options);

Run this script on the application bootstrap (e.g., in a front controller or DI container). No special privileges are required beyond the database user’s ability to connect and run SELECT queries.

2. Repository Class

<?php
// src/Repository/UserRepository.php
class UserRepository
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Fetch a page of users using keyset pagination.
     * @param int|null $afterId  Exclusive cursor; null means start from the beginning.
     * @param int $pageSize      Number of rows to return.
     * @return array<array<string,mixed>>
     */
    public function findPage(?int $afterId, int $pageSize): array
    {
        $sql = 'SELECT id, email, created_at FROM users '
             . 'WHERE (:afterId IS NULL OR id > :afterId) '
             . 'ORDER BY id ASC '
             . 'LIMIT :limit';
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':afterId', $afterId, PDO::PARAM_INT);
        $stmt->bindValue(':limit',   $pageSize, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Example of a safe search that binds user input.
     */
    public function findByEmail(string $email): ?array
    {
        $sql = 'SELECT id, email FROM users WHERE email = :email';
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':email', $email, PDO::PARAM_STR);
        $stmt->execute();
        return $stmt->fetch() ?: null;
    }
}

Place this file in your autoloaded source directory (e.g., PSR‑4). The repository receives the PDO instance via constructor injection, making it easy to mock in unit tests.

3. Usage Example

<?php
require 'vendor/autoload.php';
$repo = new UserRepository($pdo);

// First page
$page = $repo->findPage(null, 50);

// Subsequent pages – use the last id from the previous set
$lastId = end($page)['id'] ?? null;
$nextPage = $repo->findPage($lastId, 50);

This approach avoids OFFSET, so the database only scans rows after the cursor, which remains efficient even on tables with millions of rows.

Validation and Safety Checks

To confirm that the chosen method defends against SQL injection and that pagination behaves as expected, run the following diagnostic steps in a staging environment.

3.1 Injection Safety Test

<?php
// Try to inject via the email parameter
$malicious = "' OR '1'='1";
$result = $repo->findByEmail($malicious);
// Expected: $result is null because the value is treated as a literal string.
var_dump($result);

Run this script from the command line with the same PHP user that runs your application. No special permissions are needed. If the query returns a row, the binding is not working – check that PDO::ATTR_ERRMODE is set to EXCEPTION and that emulated prepares are disabled.

3.2 Pagination Performance Check

Execute the generated SQL manually in your MySQL/MariaDB client to compare row examination:

EXPLAIN SELECT id, email FROM users WHERE id > 123450 ORDER BY id LIMIT 50;
EXPLAIN SELECT id, email FROM users LIMIT 123450, 50;

Look at the rows column in the output. The keyset version should show a low number (roughly the limit + a few rows for the index scan), while the OFFSET version will show a value close to the offset + limit, indicating that MySQL had to skip many rows.

3.3 Verify Native Prepare Behaviour for LIMIT

If you are using MySQL 5.6 or earlier, native prepares do not accept a placeholder for LIMIT. Test as follows:

<?php
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare('SELECT id FROM users LIMIT ?');
$stmt->bindValue(1, 10, PDO::PARAM_INT);
try {
    $stmt->execute();
    echo 'Limit placeholder succeeded';\
} catch (PDOException $e) {
    echo 'Failed: ' . $e->getMessage();
}

If the execute throws an exception, you must either enable emulation for LIMIT clauses or construct the query string with a sanitized integer (whitelisted range) before preparing.

Limitations and Practical Verification

• The repository shown assumes an auto‑increment integer primary key for keyset pagination. If your table uses a UUID or composite key, adapt the WHERE clause accordingly.
• Disabling emulated prepares removes the ability to use client‑side side‑effects like LIMIT placeholder emulation on older drivers; verify your target MySQL/MariaDB version supports native prepares for the clauses you need.
• While the repository prevents injection via bound parameters, any dynamic column names, ordering directions, or table names must be whitelisted or validated separately.
• To verify that no N+1 queries appear when you later extend the repository to fetch related data, enable the PDO statement logger or a database‑level general log and count statements per request during a test scenario.

When to Re‑evaluate

If your application’s query complexity grows beyond simple selects and filters (e.g., heavy use of conditional joins, complex aggregations, or database‑specific features), consider migrating to an ORM/query builder while keeping the repository pattern. Conversely, if you find the ORM generating inefficient queries despite eager‑loading efforts, revert to a PDO‑based repository for the hot paths.