Migrating Legacy Database Access to PDO

1. Why legacy database access is so dangerous

Legacy database access through mysql_* functions or procedural mysqli with variables embedded directly in SQL strings is one of the most common and most dangerous patterns in old PHP projects. The problem is not just that mysql_* has been completely removed since PHP 7.0, it is that string-concatenated queries without prepared statements practically always mean a SQL injection vulnerability, as soon as user input reaches the query unfiltered.

The typical finding in a legacy audit looks like this: a function that takes a user ID via $_GET['id'] and inserts it directly into a SQL string, without any escaping or binding logic. These patterns have grown over years, often copied by several generations of developers, and are typically scattered across dozens to hundreds of places in the code, which makes a spot fix impractical.

Migrating to PDO solves two problems at once: it establishes the technical prerequisite for a PHP version jump, since mysql_* no longer exists, and it structurally eliminates the SQL injection risk, provided prepared statements are used consistently instead of string concatenation. Both goals require the same migration path, which is why this article focuses on a shared strategy.

2. Inventory: finding every access pattern in the code

Before a single line of code changes, the migration needs a complete inventory of all database access patterns in the project. A simple grep for mysql_query, mysql_connect and mysqli_query provides a first overview, but does not cover the even more dangerous cases where variables are interpolated directly into SQL strings, even when mysqli is already used in an object-oriented style.

The following script systematically searches for SQL strings with direct variable interpolation, one of the most reliable indicators of a SQL injection vulnerability, regardless of which database API is in use.

#!/usr/bin/env bash
# find-sql-injection-risks.sh — locate string-interpolated SQL queries
set -euo pipefail

# Matches SELECT/INSERT/UPDATE/DELETE strings containing a $variable
# directly inside the quotes — a strong signal of unescaped user input
grep -rnE '"(SELECT|INSERT|UPDATE|DELETE)[^"]*\$[a-zA-Z_]' \
  --include='*.php' \
  app/code/ \
  | tee /tmp/sql-injection-candidates.txt

echo "Found $(wc -l < /tmp/sql-injection-candidates.txt) candidate locations"

This list is the starting point for the entire migration. Every hit is assessed for risk, for example whether the interpolated variable comes from user input or an internally controlled source, and put into a prioritized order, which is covered in more depth in section eight of this article.

3. Building an adapter layer as a safe transition

A migration that rebuilds every access point simultaneously is not practical with several hundred locations found. The proven approach is a thin adapter class that encapsulates the new PDO connection and exposes an API similar enough to the old mysql_* usage to migrate existing code with minimal changes, while already relying entirely on prepared statements internally.

<?php

declare(strict_types=1);

// Adapter layer: familiar call shape, safe PDO implementation underneath
final class Database
{
    private \PDO $connection;

    public function __construct(string $dsn, string $user, string $password)
    {
        $this->connection = new \PDO($dsn, $user, $password, [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }

    /**
     * @param array<string, mixed> $params
     * @return array<int, array<string, mixed>>
     */
    public function fetchAll(string $sql, array $params = []): array
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /** @param array<string, mixed> $params */
    public function fetchOne(string $sql, array $params = []): ?array
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch();
        return $row === false ? null : $row;
    }

    /** @param array<string, mixed> $params */
    public function execute(string $sql, array $params = []): int
    {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
}

// Legacy call site — old: mysql_query("SELECT * FROM users WHERE id = $id")
// New: named placeholders replace direct string interpolation entirely
$db = new Database('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'app_user', getenv('DB_PASSWORD') ?: '');
$user = $db->fetchOne('SELECT * FROM users WHERE id = :id', ['id' => $id]);

The decisive advantage of this adapter layer: every method structurally enforces the use of placeholders instead of string concatenation, because $sql and $params are separate parameters. A developer can hardly write an unsafe query through this API anymore without deliberately bypassing the method.

4. Prepared statements instead of string concatenation

The core of the entire migration is the transition from string-concatenated SQL queries to prepared statements with bound parameters. The difference is not merely syntactic, it is fundamental: with a prepared statement, the SQL structure is compiled by the database driver separately, before values are inserted, so a value can never be interpreted as part of the SQL syntax, regardless of its content.

Named placeholders such as :id instead of positional ? placeholders additionally increase readability for queries with many parameters, because the mapping between placeholder and value stays explicitly visible in the code, rather than following from the order of values in an array. When migrating legacy code, it makes sense to systematically use named placeholders, even though positional placeholders are functionally equivalent, because they are less error-prone when the query changes later.

5. Unifying fetch modes and return types

An often overlooked aspect of the migration concerns the consistency of return types. mysql_fetch_array() historically returned both numerically indexed and associative keys in the same array, which in legacy code led to a wild mix of $row[0] and $row['name'] within the same project. PDO allows explicitly and project-wide setting a fetch mode, for example exclusively PDO::FETCH_ASSOC, which makes code more predictable and easier to review.

During migration, it is advisable to set this unified fetch mode as the default already in the connection configuration, as shown in the adapter example in section three, rather than specifying it explicitly for every single call. This reduces the amount of code that must be adjusted during migration and prevents new, not yet migrated callers from accidentally using an inconsistent fetch mode.

6. Retrofitting transactions correctly

Many legacy systems with mysql_* access forgo transactions entirely, because the old API technically supported them but they were rarely used consistently. When migrating to PDO, it makes sense to simultaneously check which multi-step database operations actually need to run atomically, for example an order that creates both a row in the order table and several rows in the line item table.

PDO offers a simple, consistent transaction API across all supported database drivers with beginTransaction(), commit() and rollBack(). A proven pattern is to encapsulate critical multi-step operations in a method that automatically rolls back on failure, instead of spreading transaction logic across the calling code, where it is easily forgotten.

<?php

declare(strict_types=1);

// Multi-step operation wrapped in a transaction with automatic rollback
final class OrderCreator
{
    public function __construct(private readonly \PDO $connection)
    {
    }

    /** @param array<int, array{price: float, quantity: int}> $lines */
    public function create(int $customerId, array $lines): int
    {
        $this->connection->beginTransaction();

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

            $lineStmt = $this->connection->prepare(
                'INSERT INTO order_lines (order_id, price, quantity) VALUES (:order_id, :price, :quantity)'
            );
            foreach ($lines as $line) {
                $lineStmt->execute([
                    'order_id' => $orderId,
                    'price' => $line['price'],
                    'quantity' => $line['quantity'],
                ]);
            }

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

7. Error handling: from return values to exceptions

mysql_query() returned false on error, and the script had to explicitly check every return value afterward to detect errors, a pattern that was regularly forgotten in practice. PDO solves this structurally when PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION, as shown in the adapter example: a database error then automatically throws a PDOException, instead of returning an easily overlooked value.

This change requires reviewing existing code that previously checked explicitly for false return values, because such checks never trigger anymore after migration, yet the error is still thrown if not caught. A central try-catch block at a sensible place in the application, for example in the controller or command handler, catches unexpected PDOException instances and prevents database errors from uncontrollably leaking details like table names or connection data to the user.

<?php

declare(strict_types=1);

// Central boundary: catches PDOException, never leaks internals to users
final class ErrorHandlingController
{
    public function __construct(private readonly OrderCreator $orderCreator)
    {
    }

    public function handle(int $customerId, array $lines): array
    {
        try {
            $orderId = $this->orderCreator->create($customerId, $lines);
            return ['success' => true, 'order_id' => $orderId];
        } catch (\PDOException $e) {
            // Log the real error internally, return a generic message
            error_log('Order creation failed: ' . $e->getMessage());
            return ['success' => false, 'error' => 'Order could not be created'];
        }
    }
}

8. Prioritization: which queries to migrate first

With hundreds of locations found in the inventory from section two, the migration needs an order. The most important factor is not usage frequency, but the origin of the interpolated variable: queries that directly process user input from $_GET, $_POST or $_COOKIE pose an acute security risk and should be migrated ahead of everything else, regardless of their otherwise complexity.

Queries with internally controlled values, for example from configuration files or fixed constants, carry lower immediate risk and can follow in the second prioritization wave. A third category, queries with no variable interpolation at all, does not need urgent migration from a security standpoint, but should still be updated before the next PHP version jump for compatibility reasons, because mysql_* simply no longer exists.

#!/usr/bin/env bash
# categorize-sql-findings.sh — tag each candidate by variable origin
set -euo pipefail

while IFS=: read -r file line content; do
  if echo "$content" | grep -qE '\$_(GET|POST|COOKIE|REQUEST)'; then
    echo "CRITICAL  $file:$line"
  elif echo "$content" | grep -qE '\$_(SESSION|SERVER)'; then
    echo "MEDIUM    $file:$line"
  else
    echo "LOW       $file:$line"
  fi
done < /tmp/sql-injection-candidates.txt | sort

9. Procedural mysql_/mysqli vs. PDO compared

The following table compares the most important differences between the old database access patterns and the PDO target architecture.

The table makes clear that switching to PDO is not merely a formal necessity for newer PHP versions, it brings structural security and maintainability benefits that go far beyond pure compatibility.

10. Summary

Legacy database access through mysql_* or procedural mysqli with string-concatenated SQL queries is one of the most urgent modernization tasks in old PHP projects, because it represents both a technical compatibility problem and an acute security risk. A thin PDO adapter layer that structurally enforces prepared statements enables a gradual migration without a big bang rebuild, by moving existing callers to the new, safe API one at a time.

Prioritization should follow actual risk, not usage frequency: queries with direct user input first, internally controlled values afterward. Unified fetch modes, consistent exception-based error handling and cleanly encapsulated transactions round out the migration and ultimately deliver a database layer that both runs on current PHP versions and is structurally secured against SQL injection.

11. FAQ: Migrating Legacy Database Access to PDO