Databases with PDO

One API for many databases

PDO (PHP Data Objects) is PHP's database abstraction layer, with drivers for MySQL/MariaDB, PostgreSQL, SQLite, SQL Server and others. Connect with a DSN string, and configure it properly: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION (throw on errors; the default since PHP 8.0), PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, and with MySQL consider PDO::ATTR_EMULATE_PREPARES => false to use native prepared statements and real integer types. Always use prepared statements with placeholders (:id or ?) and pass values separately, which prevents SQL injection; identifiers such as column names cannot be bound, so whitelist them instead. Wrap multi-statement changes in transactions (beginTransaction, commit, rollBack). Fetch large result sets row by row or with generators rather than loading everything into memory. Many applications use a query builder or ORM on top of PDO, such as Laravel's Eloquent, Doctrine ORM or Doctrine DBAL, but understanding PDO makes those tools far less mysterious.

Prepared statements

The query structure and the data travel separately, so data can never change the SQL.

Safe queries and a transaction with PDO

Prepared statements everywhere; the transfer is all-or-nothing.

<?php
declare(strict_types=1);

$pdo = new PDO(
    'pgsql:host=localhost;port=5432;dbname=shop',
    getenv('DB_USER') ?: 'shop',
    getenv('DB_PASSWORD') ?: '',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ],
);

$stmt = $pdo->prepare('SELECT id, title, price_paise FROM products WHERE category = :cat AND price_paise <= :max');
$stmt->execute(['cat' => $category, 'max' => $maxPaise]);
$products = $stmt->fetchAll();

$sortable = ['title', 'price_paise'];
$sort = in_array($_GET['sort'] ?? '', $sortable, true) ? $_GET['sort'] : 'title';   // whitelist identifiers

$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE wallets SET balance_paise = balance_paise - ? WHERE user_id = ? AND balance_paise >= ?')
        ->execute([$amount, $fromUser, $amount]);
    $pdo->prepare('UPDATE wallets SET balance_paise = balance_paise + ? WHERE user_id = ?')
        ->execute([$amount, $toUser]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Check affected rows for conditional updates

In the transfer above, the debit only happens if the balance is sufficient. Check rowCount() after the debit; if it is 0, roll back and report insufficient funds instead of crediting the other wallet.

त्वरित जाँच: Why do prepared statements prevent SQL injection?

• They encrypt the SQL
• They remove quotes from input
• The SQL structure and the data are sent separately, so data is never interpreted as SQL
• They only work with integers