AskMrPHP.com

The go-to resource for learning PHP, Laravel, Symfony, and your dependencies.

Upgrading Your PHP Database Libraries to PDO or MySQLi

phpdatabasepdomysqlisecuritymysqlbest-practices

In the late 1990s and early 2000s, PHP developers relied on the mysql extension as their primary interface to MySQL databases. Functions like mysql_connect() and mysql_query() became ubiquitous in PHP applications. However, as web applications grew more complex and security threats evolved, the limitations of this extension became increasingly apparent. By 2010, PHP 5.5 deprecated the extension entirely, and with PHP 7.0’s release in 2015, the mysql extension was removed from the language altogether.

If you’re maintaining a legacy PHP codebase today, you will encounter database code that uses these now-vanished functions. The reason for their removal goes beyond mere obsolescence: the mysql extension’s fundamental design made it dangerously easy to introduce SQL injection vulnerabilities. Continuing to use equivalent deprecated patterns exposes your application to significant security risks.

In this guide, we’ll walk through why the old mysql extension fell out of favor and—more importantly—how you can upgrade your existing applications to use the modern, secure alternatives: PDO and MySQLi. We’ll examine the underlying security principles, walk through concrete migration examples, and help you decide which approach best fits your project’s needs.

Why the mysql_* Extension is a Security Risk

The biggest problem with the old mysql_* functions—though they served us well in their time—is that they provide no built-in mechanism to prevent SQL injection attacks. When you interpolate user input directly into SQL strings, you’re trusting that input will always be well-formed and harmless. A malicious user, of course, has other ideas.

An attacker can use carefully crafted input in web forms to manipulate your database queries, potentially leading to data theft, modification, or complete database deletion. The vulnerability is not theoretical; SQL injection has been responsible for some of the most damaging data breaches in history.

Consider this common, vulnerable code snippet that we might encounter in a legacy application:

// The OLD, INSECURE way
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysql_query($sql);

Here, we’re directly inserting user-supplied values from $_POST into the SQL string. The database server parses and executes the entire string as a single SQL statement. If a malicious user enters ' OR '1'='1 as the username, the resulting SQL query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...'

You also may notice the subtle but critical detail: the quotes around the username value are now mismatched. The database interprets this as: “select users where the username is an empty string, OR where the constant expression ‘1’ equals ‘1’ (which is always true)”. The AND password = '...' part still applies, but it’s connected to the empty username check, not to the always-true condition. This query will always return at least one row—typically the first user in your database, often an administrator—effectively granting unauthorized access without a valid password.

Beyond security, the old extension also lacks support for modern MySQL features and has been completely removed as of PHP 7.0. It’s not just bad practice to use it; it’s incompatible with modern servers.

The Modern Solution: Prepared Statements

The most effective defense against SQL injection is to use prepared statements (also called parameterized queries). The core idea is straightforward: you send the SQL command structure to the database first, with placeholders marking where data will go. The database parses and compiles this structure without knowing what data will be supplied. You then send the actual user values separately, and the database inserts them into the pre-compiled query structure.

This separation is crucial because the database knows that the placeholders contain data—not SQL code—so it treats everything you provide as literal values, never as executable commands. Think of it like giving someone a form to fill out: you provide the questions (the SQL structure) and they provide the answers (the data). No matter what they write in the answer fields, they can’t change the questions themselves.

Both PDO and MySQLi provide excellent support for prepared statements, though they implement them with slightly different syntax and philosophies, as we’ll see.

Meet the Replacements: PDO and MySQLi

When the mysql extension was deprecated, PHP’s developers created two successor extensions, each with a different philosophical approach. Understanding their design intentions helps clarify when to choose one over the other.

MySQLi (MySQL Improved)

As the name suggests, MySQLi is a direct, improved replacement for the old mysql extension. It was designed specifically for MySQL and provides an interface that feels familiar to developers transitioning from the old mysql_* functions. MySQLi offers both a procedural style (using functions like mysqli_connect() and mysqli_query()) and an object-oriented style (using classes like mysqli and mysqli_stmt). This dual approach can make migration somewhat easier, though the object-oriented style is generally preferred for new development.

PDO (PHP Data Objects)

PDO takes a fundamentally different approach. It’s a database abstraction layer designed from the ground up to provide a consistent, object-oriented interface across many different database systems. PDO stands for “PHP Data Objects”—it’s not tied to any single vendor. Whether you’re connecting to MySQL, PostgreSQL, SQLite, Oracle, or SQL Server, you use the same PDO classes and methods. The only change required is the connection string (DSN). This abstraction comes at a small cost: PDO can’t use every database-specific feature, as those features may not have equivalents in other systems.

How to Upgrade: Code Examples

Let’s refactor the insecure login example from earlier using both MySQLi and PDO. But before we dive into the code, let’s take a moment to compare our options systematically—this will help you decide which approach to adopt for your own projects.

PDO vs MySQLi: Which Should You Choose?

There are three major approaches to modern database access in PHP. Here’s how they compare, based on your project’s needs and constraints:

First, MySQLi’s procedural style: This approach uses function calls reminiscent of the old mysql_* functions, like mysqli_connect() and mysqli_prepare(). This is my preferred migration path for legacy codebases where developers are already comfortable with procedural PHP and you’re certain you’ll only ever use MySQL. The procedural style can make the transition feel less disruptive, especially for older code.

Second, MySQLi’s object-oriented style: This approach uses classes like new mysqli() and $stmt->execute(). Functionally, it’s nearly identical to the procedural style—both use the same underlying extension—but the OO syntax is generally cleaner and more in line with modern PHP practices. If you’re using MySQL and want an object-oriented API without committing to PDO’s abstraction layer, this is a solid choice.

Third, PDO: PDO provides a consistent, object-oriented interface that works across many database systems. If you want your code to be portable—or if you simply prefer a cleaner, more modern API—PDO is typically the best choice. It’s what I use for most new projects.

Generally speaking, the procedural MySQLi approach is the easiest for direct legacy migration. The object-oriented MySQLi approach is slightly more modern while staying MySQL-specific. PDO is the most versatile for future-proofing and cross-database compatibility. We’ll see code examples for the two most common paths: MySQLi (object-oriented) and PDO.

Upgrading to MySQLi

First, we establish a connection. With MySQLi, we’ll typically use the object-oriented approach, creating a mysqli instance. Notice that there’s no need for a separate mysql_select_db() call; the database is specified in the constructor.

<?php
// MySQLi Connection (Object-Oriented Style)
$mysqli = new mysqli("localhost", "user", "password", "database");

// Always check the connection
if ($mysqli->connect_errno) {
    // In production, you'd log this error rather than displaying it directly
    die("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
}

// Optional: Set the character set to ensure proper encoding
$mysqli->set_charset("utf8mb4");

For completeness, here’s the procedural style, which some teams prefer for its similarity to the old mysql_* functions:

<?php
// MySQLi Connection (Procedural Style)
$link = mysqli_connect("localhost", "user", "password", "database");

// Check connection
if (!$link) {
    die("Connection failed: " . mysqli_connect_error());
}

// Set charset
mysqli_set_charset($link, "utf8mb4");

Important: In the examples that follow, we’ll use the object-oriented style for clarity, but the procedural style works identically—the difference is in the naming: procedural style uses functions prefixed with mysqli_, while OO style uses methods on the mysqli object.

Now, let’s perform the secure query using prepared statements. The workflow follows five distinct steps:

<?php
// MySQLi Prepared Statement (Object-Oriented Style)
$username = $_POST['username'];
$password = $_POST['password']; // Note: In production, you must hash passwords!

// 1. Prepare the query with placeholders
$stmt = $mysqli->prepare("SELECT id, username FROM users WHERE username = ? AND password = ?");
if (!$stmt) {
    // Preparation can fail if the SQL is invalid or the connection is broken
    die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}

// 2. Bind the parameters (s = string type)
// The bind_param method associates PHP variables with the placeholders
$stmt->bind_param("ss", $username, $password);
// For multiple parameters, the type string has one character per parameter:
// s = string, i = integer, d = double, b = blob

// 3. Execute the prepared statement
if (!$stmt->execute()) {
    die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}

// 4. Get the result set
$result = $stmt->get_result();
// get_result() returns a mysqli_result object (or false for non-SELECT queries)
if ($result === false) {
    die("Getting result set failed: (" . $stmt->errno . ") " . $stmt->error);
}

$user = $result->fetch_assoc();

if ($user) {
    echo "Login successful! User ID: " . htmlspecialchars($user['id']);
} else {
    echo "Invalid credentials.";
}

// 5. Free the result and close the statement
$result->free();
$stmt->close();

Of course, you’re likely wondering about password security here. The example shows plain text password comparison for clarity, but in a real application, you must use password_hash() when storing passwords and password_verify() when checking them. Prepared statements still protect against SQL injection for the username check; the password verification happens in PHP, not SQL. We’ll demonstrate this best practice later.

For completeness, here’s the procedural style equivalent:

<?php
// MySQLi Prepared Statement (Procedural Style)
$username = $_POST['username'];
$password = $_POST['password'];

$stmt = mysqli_prepare($link, "SELECT id, username FROM users WHERE username = ? AND password = ?");
if (!$stmt) {
    die("Prepare failed: (" . mysqli_errno($link) . ") " . mysqli_error($link));
}

mysqli_stmt_bind_param($stmt, "ss", $username, $password);

if (!mysqli_stmt_execute($stmt)) {
    die("Execute failed: (" . mysqli_stmt_errno($stmt) . ") " . mysqli_stmt_error($stmt));
}

$result = mysqli_stmt_get_result($stmt);
if ($result === false) {
    die("Getting result set failed: (" . mysqli_stmt_errno($stmt) . ") " . mysqli_stmt_error($stmt));
}

$user = mysqli_fetch_assoc($result);
// ... handle result ...

mysqli_free_result($result);
mysqli_stmt_close($stmt);

Upgrading to PDO

Connecting with PDO requires constructing a DSN (Data Source Name) string that specifies the driver and connection parameters. PDO typically uses exception-based error handling rather than error codes, which many developers find cleaner. Let’s examine a robust connection pattern:

<?php
// PDO Connection with proper error handling
$host = '127.0.0.1';
$db   = 'database';
$user = 'user';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch associative arrays by default
    PDO::ATTR_EMULATE_PREPARES   => false,                  // Use native prepared statements when possible
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
    // In production, you'd log this and show a user-friendly message
    // Note that connection errors are often security-sensitive, so be cautious about what you expose
    error_log("PDO Connection failed: " . $e->getMessage());
    die("A database error occurred. Please try again later.");
}

Why these options matter:

  • PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION tells PDO to throw exceptions rather than returning error codes. This forces you to handle errors (or let them bubble up), preventing silent failures. Many developers find this style more explicit than checking return values after every call.

  • PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC makes $stmt->fetch() return associative arrays by default, matching the behavior you saw in the MySQLi examples. Without this, fetch() returns both numeric and associative indexes.

  • PDO::ATTR_EMULATE_PREPARES => false is a security best practice. When true (the default for some drivers), PDO emulates prepared statements in PHP rather than using the database’s native implementation. Setting it to false ensures the database itself handles the preparation, which provides stronger guarantees against SQL injection and can improve performance for repeated queries.

You may also notice the use of utf8mb4 charset. This is the recommended character set for MySQL—it fully supports UTF-8 including emoji and other four-byte characters. The older utf8 charset in MySQL only supports up to three-byte UTF-8 sequences.

And here is the PDO version of the prepared statement. You’ll notice PDO’s API is notably concise—it often combines steps that are separate in MySQLi.

<?php
// PDO Prepared Statement (using named placeholders)
$username = $_POST['username'];
$password = $_POST['password']; // Again, hash this in production!

// 1. Prepare the query with named placeholders
// PDO supports both named placeholders (:name) and positional (?)
$stmt = $pdo->prepare("SELECT id, username, password_hash FROM users WHERE username = :username");

// 2. Bind values and execute in one step (PDO allows this convenience)
$stmt->execute([
    'username' => $username,
]);

// 3. Fetch the result
$user = $stmt->fetch();

if ($user) {
    // Proper authentication: verify the provided password against the stored hash
    if (password_verify($password, $user['password_hash'])) {
        echo "Login successful!";
        // In a real app, you'd also start a session, regenerate session ID, etc.
    } else {
        echo "Invalid credentials.";
    }
} else {
    echo "Invalid credentials.";
}

One of PDO’s conveniences is the ability to bind parameters and execute in a single call using an associative array that maps placeholder names to values. This makes the code particularly readable.

For comparison, here’s the same query using positional placeholders:

<?php
$stmt = $pdo->prepare("SELECT id, username, password_hash FROM users WHERE username = ?");
$stmt->execute([$username]);  // Values are passed as a simple indexed array
$user = $stmt->fetch();

Strictly speaking, the availability of named placeholders is a PDO feature, not a difference between the two extensions per se. MySQLi only offers positional placeholders with bind_param(). Named placeholders often make queries with many parameters more maintainable because the binding array clearly shows which value goes where.

Additionally, PDO’s fetch() method returns the next row as an array (or false if no more rows). By default, with PDO::FETCH_ASSOC set in our options, it returns an associative array. You can also fetch objects: $user = $stmt->fetch(PDO::FETCH_OBJ); would give you $user->id, $user->username, etc. This flexibility is part of PDO’s design.

PDO vs. MySQLi: Which Should You Choose?

Let’s synthesize what we’ve seen in the code examples to help you make an informed decision. Both extensions accomplish the same fundamental goal—secure, parameterized database access—but they do so with different design philosophies.

MySQLi’s strengths:

  • Smooth migration for legacy code: The procedural style uses function names (mysqli_connect, mysqli_prepare) that feel familiar to anyone who used the old mysql_* functions. If you’re incrementally upgrading a large legacy codebase, this can make the transition less disruptive.
  • MySQL-specific features: While most common operations are identical, MySQLi can access MySQL-specific features like multi_query() for executing multiple statements at once.
  • Dual style support: You can use either procedural or object-oriented syntax, though the OO style is generally recommended for new code.

PDO’s strengths:

  • Database portability: The same code works with MySQL, PostgreSQL, SQLite, and others—only the DSN connection string changes. If you might ever switch databases, PDO saves you from rewriting your data access layer.
  • Cleaner API: Many developers find PDO’s syntax more intuitive, especially the ability to pass an associative array directly to execute() with named placeholders. This can make complex queries with many parameters more readable.
  • Flexible fetch modes: PDO offers multiple ways to retrieve results—associative arrays, objects, columns, etc.—through simple fetch mode constants.

Practical considerations:

One may wonder: what about performance? In practice, the performance difference between PDO and MySQLi is negligible for most applications. The database query itself dominates the cost, not the thin abstraction layer. Unless you’re executing thousands of queries per second, you won’t notice a difference.

Another question: can I mix PDO and MySQLi in the same project? Technically, yes—you can have both extensions installed and use them in different parts of your application. However, this is generally not recommended as it increases complexity without providing significant benefit. Choose one approach and stick with it for consistency.

My recommendation:

Given the choice for a new project, I typically choose PDO. The portability gives me confidence that if we ever need to switch databases (say, from MySQL to PostgreSQL for licensing or feature reasons), the transition is straightforward. The named placeholders improve code readability. And the consistent object-oriented interface aligns with modern PHP practices.

For an existing project, the best choice is whatever allows for the smoothest transition. If your team is deeply familiar with MySQL’s procedural style, MySQLi’s procedural interface might be the path of least resistance. The important thing—the critical thing—is to move away from the deprecated mysql extension to some secure, modern alternative. Do not let analysis paralysis over this decision prevent you from upgrading at all.

A Complete Migration Walkthrough

So far, we’ve shown isolated examples. Let’s walk through a more realistic migration scenario: converting a multi-parameter query that retrieves and updates user profile information. This will demonstrate how to handle different data types (strings, integers), error scenarios, and transactions.

Suppose we have this legacy code using mysql_* functions:

<?php
// Legacy code (DO NOT USE)
$user_id = $_GET['id'];
$email = $_POST['email'];
$age = $_POST['age'];
$active = isset($_POST['active']) ? 1 : 0;

$query = "UPDATE users SET email = '$email', age = $age, active = $active WHERE id = $user_id";
$result = mysql_query($query);

if ($result) {
    echo "Profile updated.";
} else {
    echo "Error: " . mysql_error();
}

This code is problematic in several ways: SQL injection risks, improper type handling, no validation, and deprecated functions. Let’s convert it to PDO with proper practices:

Step 1: Set up the PDO connection (if not already done)

We’ll assume we have our $pdo object from the connection code shown earlier.

Step 2: Validate and sanitize input

<?php
$user_id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($user_id === false) {
    die("Invalid user ID");
}

$email = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_EMAIL);
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    die("Invalid email address");
}

$age = filter_input(INPUT_POST, 'age', FILTER_VALIDATE_INT);
if ($age === false || $age < 0 || $age > 150) {
    die("Invalid age");
}

$active = isset($_POST['active']) ? 1 : 0; // This is a boolean/integer

Notice we’ve moved from trusting raw input to actually validating it. This is a separate security layer from SQL injection prevention.

Step 3: Use a prepared statement with proper parameter binding

<?php
$stmt = $pdo->prepare("UPDATE users SET email = :email, age = :age, active = :active WHERE id = :id");

try {
    $stmt->execute([
        ':email' => $email,
        ':age' => $age,
        ':active' => $active,
        ':id' => $user_id
    ]);
} catch (\PDOException $e) {
    error_log("Update failed: " . $e->getMessage());
    die("Could not update profile. Please try again later.");
}

$affected_rows = $stmt->rowCount();
echo "$affected_rows row(s) updated.";

Here we’ve used named placeholders for clarity. The execute() method automatically converts PHP types to database types. The rowCount() method tells us how many rows were actually changed.

Step 4: Consider transactions for multi-step operations

If your update involves multiple related database changes, you should use a transaction:

<?php
try {
    $pdo->beginTransaction();
    
    // First update
    $stmt1 = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
    $stmt1->execute([':email' => $email, ':id' => $user_id]);
    
    // Second update - perhaps log the change
    $stmt2 = $pdo->prepare("INSERT INTO user_audit (user_id, changed_field, old_value, new_value) VALUES (:user_id, :field, :old, :new)");
    $stmt2->execute([
        ':user_id' => $user_id,
        ':field' => 'email',
        ':old' => $old_email, // fetched earlier
        ':new' => $email
    ]);
    
    $pdo->commit();
    echo "Profile updated and change logged.";
} catch (\PDOException $e) {
    $pdo->rollBack();
    error_log("Transaction failed: " . $e->getMessage());
    die("Could not update profile.");
}

Transactions ensure that either all database changes succeed or none do, maintaining data consistency. Of course, this adds complexity—only use transactions when you truly need atomic multi-table operations.

This walkthrough demonstrates several principles we’ve discussed:

  • Validate input before using it
  • Use prepared statements consistently
  • Handle errors gracefully (with logging, not user-facing details)
  • Consider transactions for related operations
  • Provide meaningful feedback to users

You can adapt this pattern to your specific queries—the key is establishing a secure, consistent approach.

The mysql_* functions are a relic of a less secure era. Upgrading to either MySQLi or PDO isn’t just a recommendation; it’s an essential step to protect your application, your data, and your users from preventable security vulnerabilities. When we look back at the early days of PHP, it’s clear that the language has matured considerably—and the database extensions reflect that maturation.

By embracing prepared statements and modern database extensions, you gain more than just security. You also gain:

  • Better error handling through exceptions or explicit error checking
  • Support for modern MySQL features like transactions, stored procedures, and advanced data types
  • Cleaner, more maintainable code that’s easier to understand and modify
  • Future compatibility with current and future PHP versions

Your practical migration path:

  1. Audit your codebase: Search for mysql_, mysqli_ (if you’re already partially upgraded), or any raw SQL queries that concatenate user input. Tools like PHP_CodeSniffer with security rules can help automate this.

  2. Start with high-risk areas: Prioritize login forms, search functions, and any pages that accept user input for database queries. These are your most vulnerable points.

  3. Choose your target extension: As we discussed, both PDO and MySQLi are excellent choices. Pick one based on your project’s specific needs—portability favors PDO; incremental migration from legacy procedural code may favor MySQLi’s procedural style.

  4. Refactor systematically: Don’t try to rewrite everything at once. Start with new code written in the modern style, then gradually convert existing queries as you touch those parts of the codebase for other reasons. Consider introducing a database abstraction layer if your application is large enough to justify it.

  5. Test thoroughly: Prepared statements change how data is sent to the database. While they’re backward-compatible for most operations, it’s wise to test your queries with various inputs to ensure they behave identically to the old code (except, of course, being secure).

  6. Don’t forget related security practices: Prepared statements prevent SQL injection, but they don’t address other security concerns. Remember to use password_hash() and password_verify() for passwords, validate and sanitize all user input, apply the principle of least privilege to your database users, and keep your PHP and database software up to date.

By following these steps, you’ll transform vulnerable legacy code into a robust, secure, and maintainable application. For more details, consult the official PHP documentation for MySQLi and PDO.

Sponsored by Durable Programming

Need help with your PHP application? Durable Programming specializes in maintaining, upgrading, and securing PHP applications.

Hire Durable Programming