Protect Your PHP Application from SQL Injection

# The Problem

One of the most common security threats in web applications is SQL injection. It continues to top the OWASP application security risk list. Yet somehow many developers don't even know what it is.

SQL injection is when a malicious SQL query is injected into a legitimate query run by the application, usually by a nefarious user through an input field in the user interface. It happens when the application isn't protecting the database from raw user input—common in so many PHP applications—and a cleverly formed input tricks the database into running the malicious query.

# A Simple Example

Imagine this query in an application with user profiles, where $_GET['userId'] comes from the query string to identify the user for the profile currently being viewed.

$query = 'SELECT * FROM Users WHERE id=' + $_GET['userId'];

This query would return the user data we're looking for.

But what if the viewer changes the query string to profile.php?userId=1%20OR%201=1?

Given the code above, the query would look like this going into the database.

SELECT * FROM Users WHERE id=1 OR 1=1

That's a valid query, and if your application just runs it as-is, doing nothing to protect the database from the user input, that query will evaluate true for every record in the Users table. It will return all the data for every record in the table. You'll likely leak details about all your users.

Now yes, of course you should be validating all user input coming into your application, but that is an entirely different responsibility. That's more about rejecting bad data than protecting the database.

And contrary to scores of outdated tutorials on the subject, sanitizing user input doesn't cut it. As the SQL injection guide at Paragon Initiative puts it:

While it's possible to prevent attacks by rewriting the incoming data stream before you send it to your database driver, it's rife with dangerous nuance and obscure edge-cases. (Both links in the previous sentence are highly recommended.)

Unless you want to take the time to research and attain complete mastery over every Unicode format your application uses or accepts, you're better off not even trying to sanitize your inputs. Prepared statements are more effective at preventing SQL injection than escaping strings.

Furthermore, altering your incoming data stream can cause data corruption, especially if you are dealing with raw binary blobs (e.g. images or encrypted messages).

Prepared statements are easier and can guarantee SQL Injection prevention.

(You may see this same technique referred to as "parameterized queries" or "parameter binding" elsewhere. Practically speaking, the terminology is interchangeable.)

# The Solution

It's actually not that hard to prevent SQL injections in a PHP application with a MySQL database.

Native prepared statements are guaranteed to prevent SQL injection attacks.

Think of prepared statements like this: you “prepare” the database for a query you’re going to run by sending a template with placeholders for variable data in the query. Then separately, you send data for those placeholders and tell the database to execute the query.

The code above rewritten as a prepared statement looks like this.

$statement = $pdo->prepare('SELECT * FROM Users WHERE id=?');

$statement->execute([$_GET['userId']]);
$user = $statement->fetch(PDO::FETCH_ASSOC);

With native prepared statements, that interaction does indeed take place in 2 separate requests. Because the statement and the data are sent separately—literally sent as separate packets of data to the database server—there’s no way for cleverly formed data to alter the structure of the SQL query.

The prepared statement and data are not sent separately if emulation is enabled, so you should disable it. Emulated prepare statements are constructed differently (by PHP rather than by the database server), and don’t fully protect you from SQL injection.

Important: never allow user input directly in the statement template, such as letting user input dictate which table or column is being queried. Doing so would still leave you open to attack. Consider using a whitelist in this case.

Use PDO to connect to your database, explicitly set your connection charset, and disable emulated prepare statements. For clarity on errors, I strongly recommend enabling exceptions for error handling as well.

$pdo = new PDO(
    'mysql:host=localhost;dbname=db_name;charset=utf8mb4',
    'db_user',
    'db_pass'
);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Note that your connection charset must be set in PDO’s DSN parameter (rather than with a SET NAMES statement) to prevent this potential vulnerability.

For a deep dive on the details behind all this, read the articles already referenced many times in this post: the Paragon Initiative’s definitive guide on preventing SQL injection and this exhaustive PDO explainer.

# You Might Already Be Protected

If you’re using a good query builder or ORM that always uses native prepared statements and sets the connection charset in PDO’s DSN parameter under the hood, you’re already protected. But don’t breathe that sigh of relief until you do some research to ensure your favorite database abstraction makes use of native prepared statements.

Still, it’s important to be very familiar with these techniques. At some point, you’ll run into a situation where you need to directly query the database. Or you’ll find yourself working on a legacy application that doesn’t have a query builder or ORM that you can rely on for protection.

It is ultimately your responsibility to ensure your application is protected from one of the most continually pervasive web application threats out there.