Week 7: PDO, Prepared Statements & CRUD

1. Connecting with PDO

PDO (PHP Data Objects) is PHP's database abstraction layer — one consistent API works across MySQL, PostgreSQL, SQLite and others. Connect with a DSN (data source name) string and credentials:

db.php

<?php
declare(strict_types=1);

function getConnection(): \PDO
{
    $dsn = 'mysql:host=127.0.0.1;dbname=week06_shop;charset=utf8mb4';

    return new \PDO($dsn, 'root', 'your_password', [
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        \PDO::ATTR_EMULATE_PREPARES => false,
    ]);
}

$pdo = getConnection();
echo "Connected!";

Those three options matter more than they look:

ERRMODE_EXCEPTION — database errors throw PDOException instead of silently returning false, so failures can't slip past unnoticed
FETCH_ASSOC — query results come back as associative arrays (['name' => ...]) rather than PDO's default of numeric-and-associative combined
EMULATE_PREPARES => false — uses the database driver's real prepared statements rather than PDO emulating them client-side, which is both faster and marginally safer

Never hardcode credentials in a committed file

The example above hardcodes a password for clarity. In real code, read credentials from environment variables (getenv('DB_PASSWORD')) or a .env file excluded from version control — a habit worth building now, before Week 9's Laravel projects make it the default convention.

2. Prepared Statements & Bound Parameters

Never build a SQL query by concatenating a variable directly into the string — that's exactly how SQL injection happens (covered in depth in Week 8). A prepared statement separates the SQL structure from the data, sent to the database as two distinct pieces:

never-do-this.php

<?php
// DANGEROUS -- never do this
$email = $_GET['email'];
$pdo->query("SELECT * FROM customers WHERE email = '{$email}'");
// An attacker submitting email = "' OR '1'='1" returns every row

prepared-statements.php

<?php
declare(strict_types=1);

// Named placeholders
$stmt = $pdo->prepare('SELECT * FROM customers WHERE email = :email');
$stmt->execute(['email' => $_GET['email'] ?? '']);
$customer = $stmt->fetch(); // false if no row matched

// Positional placeholders
$stmt = $pdo->prepare('SELECT * FROM products WHERE price <= ? AND stock_count > ?');
$stmt->execute([100.00, 0]);
$products = $stmt->fetchAll();

// Inserting data works the same way
$stmt = $pdo->prepare('INSERT INTO customers (name, email) VALUES (:name, :email)');
$stmt->execute(['name' => 'Ada Lovelace', 'email' => 'ada@example.com']);
$newId = $pdo->lastInsertId();

The database receives the SQL template and the parameter values as two separate things — the value ' OR '1'='1 is bound as a literal string to compare against the email column, never interpreted as SQL syntax. This is what actually prevents SQL injection: not escaping, not sanitizing input, but structurally separating code from data at the protocol level.

3. A CRUD Data-Access Layer

Wrapping raw PDO calls behind a small class keeps SQL out of the rest of your application and gives you one place to fix or extend query logic:

ProductRepository.php

<?php
declare(strict_types=1);

namespace App\Shop;

class ProductRepository
{
    public function __construct(private \PDO $pdo) {}

    /** @return array<array<string, mixed>> */
    public function all(): array
    {
        return $this->pdo->query('SELECT * FROM products ORDER BY name')->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM products WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    public function create(string $name, float $price, int $stockCount): int
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO products (name, price, stock_count) VALUES (:name, :price, :stock)'
        );
        $stmt->execute(['name' => $name, 'price' => $price, 'stock' => $stockCount]);
        return (int) $this->pdo->lastInsertId();
    }

    public function updateStock(int $id, int $newCount): bool
    {
        $stmt = $this->pdo->prepare('UPDATE products SET stock_count = :count WHERE id = :id');
        return $stmt->execute(['count' => $newCount, 'id' => $id]);
    }

    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare('DELETE FROM products WHERE id = :id');
        return $stmt->execute(['id' => $id]);
    }
}

Every calling piece of code depends on this repository's methods, not on raw SQL scattered through the codebase — change how find() fetches a product (add caching, change the query) and every caller benefits without being touched. This is exactly the shape Laravel's Eloquent models automate starting Week 11.

4. Transactions

A transaction groups multiple statements so they all succeed together or all fail together — critical whenever one logical operation touches more than one table:

OrderRepository.php

<?php
declare(strict_types=1);

namespace App\Shop;

class OrderRepository
{
    public function __construct(private \PDO $pdo) {}

    /** @param array<array{product_id: int, quantity: int}> $items */
    public function placeOrder(int $customerId, array $items): int
    {
        $this->pdo->beginTransaction();

        try {
            $stmt = $this->pdo->prepare(
                'INSERT INTO orders (customer_id, status) VALUES (:customer_id, :status)'
            );
            $stmt->execute(['customer_id' => $customerId, 'status' => 'pending']);
            $orderId = (int) $this->pdo->lastInsertId();

            $itemStmt = $this->pdo->prepare(
                'INSERT INTO order_items (order_id, product_id, quantity) VALUES (:order_id, :product_id, :quantity)'
            );
            $stockStmt = $this->pdo->prepare(
                'UPDATE products SET stock_count = stock_count - :qty WHERE id = :id AND stock_count >= :qty'
            );

            foreach ($items as $item) {
                $itemStmt->execute([
                    'order_id' => $orderId,
                    'product_id' => $item['product_id'],
                    'quantity' => $item['quantity'],
                ]);

                $stockStmt->execute(['qty' => $item['quantity'], 'id' => $item['product_id']]);
                if ($stockStmt->rowCount() === 0) {
                    throw new \RuntimeException("Insufficient stock for product {$item['product_id']}");
                }
            }

            $this->pdo->commit();
            return $orderId;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

Without a transaction, a failure partway through (say, the third item is out of stock) would leave the order and the first two items already committed to the database — an order that's silently incomplete. beginTransaction(), commit() and rollBack() guarantee all-or-nothing: if anything throws before commit(), every change since beginTransaction() is undone.

5. Handling Database Errors

With ERRMODE_EXCEPTION set, any database failure — a broken connection, a constraint violation, a syntax error — throws a PDOException you can catch and respond to deliberately:

error-handling.php

<?php
try {
    $stmt = $pdo->prepare('INSERT INTO customers (name, email) VALUES (:name, :email)');
    $stmt->execute(['name' => $name, 'email' => $email]);
} catch (\PDOException $e) {
    if ($e->getCode() === '23000') { // integrity constraint violation (e.g. duplicate email)
        echo "That email address is already registered.";
    } else {
        error_log($e->getMessage());
        echo "Something went wrong. Please try again.";
    }
}

SQLSTATE code 23000 covers integrity constraint violations — including the UNIQUE constraint on customers.email from last week's schema. Branching on it turns a raw database error into a specific, actionable message instead of a generic failure.

6. Hands-on Exercise

Hands-on

Build a full repository layer over last week's schema

Connect PHP to the blog schema from Week 6 and wrap it in a proper data-access layer.

Requirements:

Write a getConnection(): \PDO function with the three connection options from this lesson, reading credentials from environment variables via getenv().
Build a PostRepository class with all(), find(int $id), create(string $title, string $body, int $authorId) and delete(int $id) methods, every query using prepared statements.
Add a attachTag(int $postId, int $tagId) method that inserts into post_tags, wrapped so a duplicate attachment (same post+tag twice) is caught via the 23000 SQLSTATE and reported as a friendly message rather than crashing.
Add a createWithTags(string $title, string $body, int $authorId, array $tagIds) method that inserts the post and every tag attachment inside a single transaction — if any tag attachment fails, the whole post creation rolls back.
Write a small script exercising every method and printing the results.

Hint

Test the rollback path deliberately — attach an intentionally invalid tagId inside createWithTags() and confirm the post itself doesn't end up in the database afterward. If it does, the transaction isn't actually wrapping every statement.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

What actually prevents SQL injection when using a prepared statement — escaping the input, or something else?

Something else — structural separation. The SQL template (with placeholders) is sent to the database first and parsed as code; the parameter values are sent afterward and bound as literal data, never re-parsed as SQL syntax. This is why a malicious string like ' OR '1'='1 ends up compared literally against a column instead of altering the query's logic — it's never given the chance to be interpreted as SQL at all.

What does setting `PDO::ATTR_ERRMODE` to `ERRMODE_EXCEPTION` change?

By default, PDO's older error mode just returns false from a failed operation and silently continues — a failure that's trivially easy to miss if you don't check every return value. ERRMODE_EXCEPTION makes any database error throw a PDOException instead, so a failed query surfaces loudly and can be handled (or at minimum crashes visibly) rather than silently corrupting later logic that assumed the query succeeded.

In `placeOrder()`, why does the code call `rollBack()` inside the `catch` block instead of just letting the exception propagate?

Without an explicit rollBack(), any statements already executed since beginTransaction() (the order row, and any order items inserted before the failure) would remain uncommitted but still hold their locks and pending state — the transaction needs to be explicitly closed one way or the other. Calling rollBack() before re-throwing undoes every change from this transaction cleanly, then lets the caller find out something went wrong.

Why wrap PDO calls in a repository class instead of calling `$pdo->prepare()` directly wherever a query is needed?

It keeps SQL in one place per entity instead of scattered across every controller or script that happens to need product data. Changing how a query works — adding a filter, optimizing it, adding caching — only requires editing the repository method; every caller benefits automatically without being touched. It's the same underlying idea Laravel's Eloquent models formalize starting Week 11.