NightFox logo NightFox personal tech cave

PDO and Safe Database Access

PDO provides a consistent interface for database connections, prepared statements, transactions, and result handling. The main goal is to make unsafe SQL and inconsistent error handling difficult.

Create One Well-Configured Connection

<?php
declare(strict_types=1);

final class Database
{
    public static function connect(): PDO
    {
        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
            $_ENV['DB_HOST'],
            (int) ($_ENV['DB_PORT'] ?? 3306),
            $_ENV['DB_NAME']
        );

        return new PDO($dsn, $_ENV['DB_USER'], $_ENV['DB_PASSWORD'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }
}

Prepared Statements

<?php
declare(strict_types=1);

$stmt = $pdo->prepare(
    'SELECT id, name, email
       FROM customers
      WHERE status = :status
        AND created_at >= :created_after
      ORDER BY name'
);
$stmt->execute([
    'status' => 'active',
    'created_after' => '2026-01-01 00:00:00',
]);

$customers = $stmt->fetchAll();

Placeholders bind values, not SQL identifiers. Whitelist dynamic column names and sort directions instead of inserting raw request data.

Safe Dynamic Sorting

<?php
declare(strict_types=1);

$allowedSorts = [
    'name' => 'name',
    'created' => 'created_at',
    'status' => 'status',
];

$sortKey = $_GET['sort'] ?? 'name';
$sortColumn = $allowedSorts[$sortKey] ?? $allowedSorts['name'];
$direction = ($_GET['direction'] ?? 'asc') === 'desc' ? 'DESC' : 'ASC';

$sql = "SELECT id, name, status FROM customers ORDER BY {$sortColumn} {$direction}";
$rows = $pdo->query($sql)->fetchAll();

Transactions

<?php
declare(strict_types=1);

$pdo->beginTransaction();

try {
    $invoice = $pdo->prepare(
        'INSERT INTO invoices (customer_id, status, created_at)
         VALUES (:customer_id, :status, UTC_TIMESTAMP())'
    );
    $invoice->execute(['customer_id' => 42, 'status' => 'draft']);
    $invoiceId = (int) $pdo->lastInsertId();

    $line = $pdo->prepare(
        'INSERT INTO invoice_lines
             (invoice_id, description, quantity, unit_price_cents)
         VALUES
             (:invoice_id, :description, :quantity, :unit_price_cents)'
    );
    $line->execute([
        'invoice_id' => $invoiceId,
        'description' => 'Service charge',
        'quantity' => 1,
        'unit_price_cents' => 12500,
    ]);

    $pdo->commit();
} catch (Throwable $exception) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $exception;
}

Pagination

Offset pagination is easy but becomes slower and less stable on large changing data sets. Keyset pagination uses the last seen indexed value.

SELECT id, created_at, subject
FROM tickets
WHERE (created_at, id) < (:created_at, :id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Database Access Rules

  • Use a least-privilege database account for the application.
  • Never expose raw database errors to users.
  • Keep SQL close to the repository or query object that owns it.
  • Use transactions for multi-statement state changes.
  • Log slow operations with identifiers and duration, not secrets.
  • Design indexes around actual query patterns.

Related Reading