PDO and Safe Database Access

PDO is how I talk to databases from PHP: one consistent way to connect, run prepared statements, handle transactions, and get results. My main goal with the setup below is to make the unsafe thing (gluing user input into SQL, or ignoring errors) hard to do by accident.

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 work for values, not for column or table names. If users get to pick a sort column, check it against a fixed list of allowed columns. Never paste the request value into the SQL.

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

LIMIT ... OFFSET is easy, but it gets slow on big tables (the database still reads every skipped row), and pages shift when rows are added. Keyset pagination remembers the last ID you saw and starts from there instead.

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.