PHP PDO Explained — Secure Database Queries, Prepared Statements & Real Patterns

<?php
declare(strict_types=1);

/**
 * Returns a configured PDO instance connected to the MySQL shop database.
 * This function is the single place in the app where DB credentials live.
 */
function createDatabaseConnection(): PDO
{
    // DSN components kept as variables so they're easy to pull from .env later
    $dbHost     = 'localhost';
    $dbName     = 'shop';         // the MySQL database name
    $dbUser     = 'app_user';     // a limited-privilege DB user — never root!
    $dbPassword = 'str0ngP@ssw0rd';

    // charset=utf8mb4 handles full Unicode including emoji (utf8 in MySQL is NOT full Unicode)
    $dsn = "mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4";

    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,   // throw exceptions on failure
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,         // return rows as associative arrays
        PDO::ATTR_EMULATE_PREPARES   => false,                    // use REAL server-side prepared statements
    ];

    try {
        $pdo = new PDO($dsn, $dbUser, $dbPassword, $options);
        return $pdo;
    } catch (PDOException $connectionError) {
        // Log the real error privately (never expose DSN/credentials to users)
        error_log('DB connection failed: ' . $connectionError->getMessage());

        // Show a safe message to the browser
        throw new RuntimeException('Database temporarily unavailable. Please try again later.');
    }
}

// --- Usage ---
try {
    $db = createDatabaseConnection();
    echo "Connection established successfully.\n";
} catch (RuntimeException $e) {
    echo $e->getMessage() . "\n";
}

<?php
declare(strict_types=1);

require_once 'database_connection.php';

/**
 * Looks up a user by email address safely using a prepared statement.
 * Returns the user row as an associative array, or null if not found.
 */
function findUserByEmail(PDO $db, string $emailAddress): ?array
{
    // Phase 1: send SQL structure to the server — the '?' is a placeholder, not data
    $statement = $db->prepare(
        'SELECT user_id, username, password_hash, created_at
         FROM users
         WHERE email = ?
         LIMIT 1'
    );

    // Phase 2: send the actual data — the server slots it in as a string literal, never as SQL
    $statement->execute([$emailAddress]);

    // fetch() returns one row as an associative array, or false if nothing matched
    $userRow = $statement->fetch();

    return $userRow !== false ? $userRow : null;
}

/**
 * Registers a new user — demonstrates named placeholders for clarity with multiple columns.
 */
function registerUser(PDO $db, string $username, string $email, string $plaintextPassword): int
{
    // password_hash() uses bcrypt — NEVER store plaintext passwords
    $hashedPassword = password_hash($plaintextPassword, PASSWORD_BCRYPT);

    // Named placeholders (:username, :email, :hash) make the intent obvious
    $statement = $db->prepare(
        'INSERT INTO users (username, email, password_hash, created_at)
         VALUES (:username, :email, :password_hash, NOW())'
    );

    $statement->execute([
        ':username'      => $username,
        ':email'         => $email,
        ':password_hash' => $hashedPassword,
    ]);

    // lastInsertId() returns the auto-incremented primary key of the new row
    return (int) $db->lastInsertId();
}

// --- Demonstrating why injection fails ---
$db = createDatabaseConnection();

// An attacker tries a classic injection payload as the email
$maliciousInput = "' OR '1'='1' --";
$user = findUserByEmail($db, $maliciousInput);

if ($user === null) {
    echo "No user found for input: {$maliciousInput}\n";
    echo "Injection attempt failed — the payload was treated as a plain string.\n";
} else {
    echo "User found: {$user['username']}\n";
}

// Registering a real user
$newUserId = registerUser($db, 'alice', 'alice@example.com', 'correctHorseBatteryStaple');
echo "New user created with ID: {$newUserId}\n";

<?php
declare(strict_types=1);

require_once 'database_connection.php';

$db = createDatabaseConnection();

// --- FETCH_ASSOC: get all products in a category ---
function getProductsByCategory(PDO $db, int $categoryId): array
{
    $statement = $db->prepare(
        'SELECT product_id, name, price, stock_quantity
         FROM products
         WHERE category_id = :category_id
           AND is_active = 1
         ORDER BY name ASC'
    );
    $statement->execute([':category_id' => $categoryId]);

    // fetchAll() returns every matching row as an array of associative arrays
    return $statement->fetchAll();
}

// --- FETCH_COLUMN: just give me the IDs as a flat list ---
function getActiveProductIds(PDO $db): array
{
    $statement = $db->query('SELECT product_id FROM products WHERE is_active = 1');

    // PDO::FETCH_COLUMN + column index 0 = one flat array of the first column's values
    return $statement->fetchAll(PDO::FETCH_COLUMN, 0);
}

// --- Transactions: move stock between warehouses atomically ---
function transferStock(PDO $db, int $productId, int $fromWarehouse, int $toWarehouse, int $quantity): void
{
    try {
        $db->beginTransaction(); // start the atomic block

        // Step 1: deduct from source warehouse
        $deductStatement = $db->prepare(
            'UPDATE warehouse_stock
             SET quantity = quantity - :qty
             WHERE product_id = :pid AND warehouse_id = :wid AND quantity >= :qty'
        );
        $deductStatement->execute([
            ':qty' => $quantity,
            ':pid' => $productId,
            ':wid' => $fromWarehouse,
        ]);

        // rowCount() tells us how many rows were actually changed
        if ($deductStatement->rowCount() === 0) {
            // Not enough stock — abort everything; nothing has been committed yet
            throw new RuntimeException("Insufficient stock in warehouse {$fromWarehouse}.");
        }

        // Step 2: add to destination warehouse
        $addStatement = $db->prepare(
            'UPDATE warehouse_stock
             SET quantity = quantity + :qty
             WHERE product_id = :pid AND warehouse_id = :wid'
        );
        $addStatement->execute([
            ':qty' => $quantity,
            ':pid' => $productId,
            ':wid' => $toWarehouse,
        ]);

        // Both steps succeeded — make the changes permanent
        $db->commit();
        echo "Stock transfer of {$quantity} units committed successfully.\n";

    } catch (Exception $e) {
        // Something went wrong — undo every change made since beginTransaction()
        $db->rollBack();
        echo "Transfer rolled back: " . $e->getMessage() . "\n";
    }
}

// --- Usage ---
$electronics = getProductsByCategory($db, 3);
echo "Found " . count($electronics) . " electronics products.\n";

foreach ($electronics as $product) {
    // Each $product is an associative array: ['product_id'=>1, 'name'=>'...', ...]
    echo "  [{$product['product_id']}] {$product['name']} — £{$product['price']}\n";
}

$allIds = getActiveProductIds($db);
echo "Active product IDs: " . implode(', ', $allIds) . "\n";

transferStock($db, productId: 7, fromWarehouse: 1, toWarehouse: 2, quantity: 50);

<?php
declare(strict_types=1);

require_once 'database_connection.php';

// SQLSTATE codes we want to handle explicitly
const SQLSTATE_DUPLICATE_ENTRY = '23000'; // unique constraint violation

/**
 * Safely inserts a new customer email subscription.
 * Returns a descriptive result message without leaking DB internals.
 */
function subscribeEmail(PDO $db, string $emailAddress): string
{
    try {
        $statement = $db->prepare(
            'INSERT INTO email_subscriptions (email, subscribed_at)
             VALUES (:email, NOW())'
        );
        $statement->execute([':email' => $emailAddress]);

        return "Successfully subscribed {$emailAddress}.";

    } catch (PDOException $dbError) {

        // getCode() returns the 5-char SQLSTATE string
        if ($dbError->getCode() === SQLSTATE_DUPLICATE_ENTRY) {
            // Safe to show the user — doesn't reveal anything about our schema
            return "{$emailAddress} is already subscribed.";
        }

        // For all other errors: log privately, respond generically
        error_log(
            sprintf(
                '[%s] DB error in subscribeEmail — SQLSTATE: %s — Message: %s',
                date('Y-m-d H:i:s'),
                $dbError->getCode(),
                $dbError->getMessage()  // full detail goes to the log, NOT the response
            )
        );

        // The user (and any attacker) sees nothing useful
        return 'Something went wrong. Please try again later.';
    }
}

// --- Simulating different outcomes ---
$db = createDatabaseConnection();

// First subscription attempt
echo subscribeEmail($db, 'bob@example.com') . "\n";

// Second attempt with the same email — triggers duplicate entry
echo subscribeEmail($db, 'bob@example.com') . "\n";

// Simulating a successful second user
echo subscribeEmail($db, 'carol@example.com') . "\n";