PHP PDO Prepared Statements: Stop SQL Injection in 5 Steps

One unescaped variable dropped into a SQL string is all it takes to hand a stranger read access to an entire database. PHP PDO prepared statements close that door by design, not by remembering to escape every value correctly every single time.

A raw query built by gluing a variable into a SQL string asks the database to treat user input as part of the command itself. Anything typed into that input becomes something the database will execute.

A prepared statement changes that relationship entirely. The query structure is sent to MySQL first, compiled, and locked in place. The values arrive afterward, purely as data, with no way to change what the query does.

That separation is the entire security model. It does not depend on remembering to escape a string correctly, because there is no string concatenation step left to forget. Once PHP PDO prepared statements are the default habit for every query, an entire class of vulnerability simply stops being possible by accident.

The OWASP SQL Injection Prevention Cheat Sheet lists parameterized queries as the single most effective primary defense, ahead of input validation or escaping as a first line of protection.

The connection string, called a DSN, tells PDO which driver to use and where to find the server. Getting the character set right here matters more than it looks.

$dsn = 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4';

$pdo = new PDO($dsn, 'app_user', $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

Setting ATTR_EMULATE_PREPARES to false is easy to skip, but it matters. Without it, PDO can quietly build the final query as a string on the PHP side before sending it, which reopens some of the exact risk prepared statements exist to close.

Turning it off forces MySQL itself to handle the parameter binding natively, which is both the more secure setting and, in most cases, the faster one. PHP's own PDO::setAttribute() manual page lists every driver option available, including the emulation flag used above.

MySQL's own mysqlnd documentation explains why native prepared statement support depends on which underlying driver PHP was built against, which matters if a hosting provider ships an older configuration.

PDO supports two placeholder styles, and mixing them in the same query is not allowed. Picking one deliberately, rather than whichever comes to mind first, keeps a query easier to read later.

// positional placeholders, values passed in order
$stmt = $pdo->prepare('SELECT * FROM orders WHERE status = ? AND total > ?');
$stmt->execute(['shipped', 100]);

// named placeholders, order does not matter
$stmt = $pdo->prepare('SELECT * FROM orders WHERE status = :status AND total > :total');
$stmt->execute(['status' => 'shipped', 'total' => 100]);

Positional placeholders read cleanly for a short query with one or two values. Named placeholders scale better once a query grows past three or four parameters, since the array keys document what each value actually means.

Everything covered in these PHP PDO prepared statements examples works identically against MariaDB too, since it speaks the same wire protocol and PDO driver as MySQL itself.

It is one thing to be told prepared statements are safe. It is more convincing to actually feed a classic injection payload to both a raw concatenated query and a prepared one, side by side, and watch what each one does with it.

function find_user_unsafe(PDO $pdo, string $username): array {
    // never do this: raw string concatenation, shown only to prove the point
    $sql = "SELECT * FROM users WHERE username = '$username'";
    return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}

function find_user_safe(PDO $pdo, string $username): array {
    $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
    $stmt->execute(['username' => $username]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

$payload = "x' OR '1'='1";

That payload is the textbook example for a reason: against a raw concatenated query, it turns a search for one specific username into a condition that matches every row in the table instead.

Actual output from running the same malicious input against both versions of the query.

The unsafe version leaks every row in the table. The prepared statement version returns nothing, because MySQL never interprets the payload as anything other than the literal text of a username that does not exist.

Injection remains listed in OWASP's Top 10 as one of the most common and most preventable web application risks, largely for exactly this reason.

PDO can hand back a result row shaped several different ways, and picking the wrong one usually just means extra conversion code later rather than any real bug.

Setting PDO::ATTR_DEFAULT_FETCH_MODE once at connection time, the way the earlier connection example does, means every later call to fetch() or fetchAll() behaves consistently without repeating the mode argument everywhere it is called.

Some operations only make sense as a single atomic unit. Moving money between two accounts is the classic example: either both balances update, or neither does.

function transfer(PDO $pdo, string $from, string $to, int $amount): void {
    $pdo->beginTransaction();

    try {
        $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE name = :name')
            ->execute(['amt' => $amount, 'name' => $from]);

        $pdo->prepare('UPDATE accounts SET balance = balance + :amt WHERE name = :name')
            ->execute(['amt' => $amount, 'name' => $to]);

        $pdo->commit();
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Simulating a failure partway through the transfer, after the first update has already run but before the second one does, shows exactly what the rollback is protecting against.

Actual output from a transfer that fails partway through and rolls back cleanly.

Without the transaction wrapping both statements, that same failure would have left one account debited with no matching credit ever applied, a half finished write with no way to tell it happened at all.

With ERRMODE_EXCEPTION set, a failed query throws a PDOException instead of returning false silently. That exception often carries the exact table and column names involved, which is exactly the kind of detail that should never reach a user facing response, even in an application already built around PHP PDO prepared statements throughout.

Catching it, logging the full detail internally, and returning a generic message to the caller follows the same principle covered in building a REST API in PHP without a framework, where a consistent JSON error shape never exposes internal exception messages directly.

• Always use PHP PDO prepared statements for values: the query structure comes from code, the values come from a bound parameter, never a concatenated string.
• Disable emulated prepares: lets MySQL itself handle the parameter binding natively instead of PHP building the final string beforehand.
• Set a real error mode: a silent false on failure is far easier to miss than a thrown exception.
• Wrap related writes in a transaction: a multi step update should never be allowed to finish only halfway.
• Never log full exception details to the user: internal logs can hold the detail, the response back to the caller should not.

PDO speaks the same interface regardless of which database driver sits behind it. Swapping the DSN for sqlite::memory: during a test run creates a real, disposable database in memory that behaves identically for prepared statement mechanics.

Every real output shown in this article, including the injection test and the transaction rollback, was produced exactly this way: real PDO code, executed for real, just against a fast in memory database instead of a network connection to MySQL. PHP Delusions' PDO tutorial is one of the most thorough independent references on this exact topic, worth reading after this article for the deeper edge cases.

Input that eventually reaches one of these queries should already have passed through the same kind of checks covered in validating and sanitizing user input in PHP forms. A prepared statement stops injection, but it will still happily store a value that is simply wrong.

A scheduled task that runs these same queries repeatedly, the way the PHP cron jobs article covers, benefits from the exact connection and fetch mode setup shown here too, and a long running script that reads the same reference data over and over is often a good candidate for the caching database queries in PHP with Redis pattern covered elsewhere in this series.