# Databases with PDO — PHP

Source: https://www.skillbyai.com/en/php/d-pdo

> Query databases safely with PDO prepared statements and transactions.

## 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.

![Two parallel arrows into a database cylinder: one carrying a template with blank slots, the other carrying values that fill the slots.](assets/figures/php/section-6-map.svg) — Figure 6.1 — Query template and bound parameters sent separately.

## Safe queries and a transaction with PDO

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

```php
<?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.

**Quiz:** Why do prepared statements prevent SQL injection?

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

*Answer:* The SQL structure and the data are sent separately, so data is never interpreted as SQL. Bound parameters are always treated as values, never as SQL syntax.