MySQLPDOPHPSecurity

PHP PDO Prepared Statements to Prevent SQL Injection

Table of Contents

What's PDO?

PDO is an abstraction layer for your database queries and is an awesome alternative to MySQLi, as it supports 12 different database drivers. This is an immense benefit for people and companies that need it. However, if I'm using SQL, then I don't see a reason to switch from MySQL for myself. PHP and MYSQL are like peanut butter and jelly, which is why there are many developers who are like myself. NoSQL is a different story, and Firebase and MongoDB are excellent choices, especially the former, as it's a live database — both are obviously not supported in PDO.

Note: For this tutorial, I will be showing non-emulated (native) PDO prepared statements strictly with MySQL, so there might be some differences on a different driver.

If you know for a fact that the only SQL database you'll be using is MySQL, then you can choose between PDO and MySQLi. If you'd like to learn MySQLi, check out this tutorial. Either one of these is perfectly acceptable to use, despite the notions a bunch of self-righteous PHP developers have propagated. The freedom to perform a task in any you please is why programming is so beautiful. Coding is not to some religion where you have to do something one way just because some "coding gods" said so; part of evolving as a developer is learning how to think for yourself.

A lot of people regurgitate that the main advantage of PDO is that it's portable from database-to-database. This is an extremely overstated benefit and is essentially nonsense. SQL is not meant to be transferred this way, as each DB driver has its own nuances; plus how often are you really making decisions to switch databases on a specific project, unless you're at least a mid-level - large company?

The true advantage of PDO is the fact that you're using an identical interface for any of the 12 databases it supports, so you'll be familiar with the API, no matter which one you use. Named parameters are also undoubtedly a huge win for PDO, since you can reuse the same values in different places in the queries. Unfortunately, you can't use the same named parameters more than once with emulation mode turned off, therefore making it useless for the sake of this tutorial.

A controversial advantage of PDO is the fact that you don't need to use bindParam() or bindValue(), since you can simply pass in the values as arrays directly into execute. Some might argue that this is considered bad practice, as you can't specify the type (string, int, double, blob); everything will be treated as a string. In practice, this shouldn't affect your ints or doubles, and is safe from SQL injection. This tutorial will bind values directly into execute. Similar to bindValue(), you can use both values and variables.

If you'd like to learn how SQL injection works, you can read about it here.

Creating a New PDO Connection

I recommend creating a file named pdo_connect.php and place it outside of your root directory (ex: html, public_html).

$dsn = "mysql:host=localhost;dbname=myDatabase;charset=utf8";
$options = [
  PDO::ATTR_EMULATE_PREPARES   => false, // turn off emulation mode for "real" prepared statements
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array
];
try {
  $pdo = new PDO($dsn, "username", "password", $options);
} catch (Exception $e) {
  echo $e->getMessage(); //In developmenet
  error_log($e->getMessage()); //In production
  exit('Something weird happened'); //something a user can understand
}

So what's going on here? The first line is referred to as DSN and has three separate values to fill out, your hostname, database and charset. This is the recommended way to do it, and you can obviously set your charset to whatever your application needs (though utf8 is pretty standard). Now you can pass in your DSN info, username, password and options.

Alternatively, you can omit using a try/catch block by creating a global custom exception handler. If this is included on all your pages, then it will use this custom handler, unless you do restore_exception_handler() to revert back to the built-in PHP exception handler or call set_exception_handler() with a new function and custom message.

set_exception_handler(function($e) {
  echo $e->getMessage(); //use in development
  error_log($e->getMessage()); //In production
  exit('Something weird happened'); //something a user can understand
});
$dsn = "mysql:host=localhost;dbname=myDatabase;charset=utf8";
$options = [
  PDO::ATTR_EMULATE_PREPARES   => false, // turn off emulation mode for "real" prepared statements
  PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, //turn on errors in the form of exceptions
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //make the default fetch be an associative array
];
$pdo = new PDO($dsn, "username", "password", $options);

This is extremely debatable, but one thing I like about MySQLi is that error reporting is turned off by default. This is smart, so a beginner wouldn't accidentally print out his password. In PDO, even though you you have control to silence errors, you can't do this for the constructor. So obviously you should first set up your php.ini for production. To prevent leaking your password, here's what your php.ini file should look like in production: do both display_errors = Off and log_errors = On. Now all errors on your site will solely accumulate in your error log, instead of printing them out.

Still, I don't see a reason to print out your password in your error log, so I'd recommend doing try/catch and error_log() $e->getMessage(), not $e, which would still contain your sensitive information.

Named Parameters

I really love this feature, and it's a huge advantage for PDO. You specify a variable named :id and give it its value on execute. Though as stated earlier, its only advantage of being used multiple times is rendered useless if emulation mode turned off.

$stmt = $pdo->prepare("UPDATE myTable SET name = ? WHERE id = :id");
$stmt->execute([':id' => $_SESSION['id']]);
$stmt = null;

You technically don't need the leading colon on id for the execute part, as stated here. However, this isn't explicitly stated anywhere in the docs, so while it should work as some users have astutely concluded by looking in the C code, it is not technically recommended. My hunch is that PHP will document this eventually anyway, since it seems like there are enough people who omit the leading colon.

I dedicated a section to using named parameters, since the rest of the post will be using ? instead. Keep in mind that you can't mix both together when binding values.

Insert, Update and Delete

All of these are extremely similar to each other, so they will be combined.

Insert

$stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->execute([$_POST['name'], 29]);
$stmt = null;

Update

$stmt = $pdo->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt->execute([$_POST['name'], $_SESSION['id']]);
$stmt = null;

Delete

$stmt = $pdo->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->execute([$_SESSION['id']]);
$stmt = null;

Get Number of Affected Rows

Getting the number of affected rows is exceedingly simple, as all you need to do is $stmt->rowCount(). Normally if you update your table with the same values, it'll return 0. If you'd like to change this behavior, then the only way to do this is by globally adding this option when you create new connection PDO::MYSQL_ATTR_FOUND_ROWS => true.

$stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->execute([$_POST['name'], 29]);
echo $stmt->rowCount();
$stmt = null;

Select

Fetch all Selected Rows in Associative Array

Since we set the default fetch type to be an associative array, we don't have specify anything when fetching results.

$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id <= ?");
$stmt->execute([5]);
$arr = $stmt->fetchAll();
if(!$arr) exit('No rows');
$stmt = null;

There's also the slightly longer while loop version, which is sometimes handy for manipulations.

$arr = [];
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->execute([$_POST['name']]);
while ($row = $stmt->fetch()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
$stmt = null;

Get Single Row

$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->execute([$_POST['name']]);
$arr = $stmt->fetch();
if(!$arr) exit('No rows');
$stmt = null;

Now you access each variable, like $row['name'] for instance.

Get Single Row Like MySQLi bind_param()

$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->execute([3]);
$arr = $stmt->fetch(PDO::FETCH_NUM); //FETCH_NUM must be used with list
if(!$arr) exit('no rows');
list($id, $name, $age) = $arr;
$stmt = null;

This is is to mimic the (only beneficial) behavior of bind_param() in MySQLi, which is to be able to bind values to a variable name. Now you can access each variable like so: $name.

Like

You might intuitively try to do something like the following.

$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name LIKE %?%"); 

However, this will not work. This is how you would do it the right way.

$search = "%{$_POST['search']}%";
$stmt = $pdo->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?");
$stmt->execute([$search]);
$arr = $stmt->fetchAll();
if(!$arr) exit('No rows');
$stmt = null;

Where In Array

As you can see, PDO clearly excels in this too, as the code is much shorter, due to not specifying the type and using bindValue() or bindParam().

$inArr = [1, 3, 5];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id IN ($clause)");
$stmt->execute($inArr);
$resArr = $stmt->fetchAll();
if(!$resArr) exit('No rows');
$stmt = null;

With Other Placeholders

$inArr = [1, 3, 5];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$stmt = $pdo->prepare("SELECT * FROM myTable WHERE id IN ($clause) AND id < ?");
$fullArr = array_merge($inArr, [5]); //merge WHERE IN array with other value(s)
$stmt->execute($fullArr);
$resArr = $stmt->fetchAll();
if(!$resArr) exit('No rows');
$stmt = null;

Multiple Prepared Statements in Transactions

If you want to ensure that multiple SQL calls are concurrent, then you must use transactions. This ensures that either all of your operations or none of them will succeed. For instance, this could be useful for transferring a row to a different table. You'll want copy the row over to the new table and delete the other one. If one of the operations fails, then it needs to revert back to its previous state.

try {
  $pdo->beginTransaction();
  $stmt1 = $pdo->prepare("INSERT INTO myTable (name, location) VALUES (?, ?)");
  $stmt2 = $pdo->prepare("UPDATE myTable SET age = ? WHERE id = ?");
  if(!$stmt1->execute(["Rick", $_POST['location']])) {
    throw new Exception('Statement 1 Failed');
  }
  if(!$stmt2->execute([$_POST['age'], 27])) {
    throw new Exception('Statement 2 Failed');
  }
  $stmt1 = null;
  $stmt2 = null;
  $pdo->commit();
} catch(Exception $e) {
  $pdo->rollback();
  echo $e; //use in development
  error_log($e); //use in production
}

Reuse Same Template, Different Values

try {
  $pdo->beginTransaction();
  $stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  if(!$stmt->execute(["Joe", 19])) {
    throw new Exception('Statement 1 Failed');
  }
  if(!$stmt->execute(["Ryan", 44])) {
    throw new Exception('Statement 2 Failed');
  }
  $stmt = null;
  $pdo->commit();
} catch(Exception $e) {
  $pdo->rollback();
  echo $e; //use in development
  error_log($e); //use in production
}

Error Handling

If you turned on errors and forced them to be exceptions, then the easiest way to handle your errors is by putting them in a try/catch block. You should not wrap each database query in its own try/catch block. Instead, just use a single, global one.

try {
  $stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  if(!$stmt->execute([$_POST['name'], $_POST['age']])) {
    throw new Exception('Execute Failed');
  }
  $stmt = null;
} catch(Exception $e) {
  echo $e; //use in development 
  error_log($e); //use in production 
  exit('Error inserting');
}

Another way to handle the exceptions is by creating a user-defined exception handler, which I mentioned earlier. You would add the following on each page after including pdo_connect.php. This way you can leave out try/catch on almost all of your queries except for transactions, which you would throw an exception after catching if something went wrong

//include pdo_connect.php
set_exception_handler(function($e) {
  echo $e; //use in development 
  error_log($e); //use in production 
  exit('Error inserting');
});
$stmt = $pdo->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
if(!$stmt->execute([$_POST['name'], $_POST['age']])) {
  throw new Exception('Execute Failed');
}
$stmt = null;

You may have noticed that I'm throwing an exception for execute if it's fasly, which seems redundant, as we already turned on error handling in the form of exceptions. Nevertheless, I noticed an odd behavior, which is that execute() can solely return false in some scenarios if emulation mode is turned off, which is the only mode this tutorial is discussing. It could be MySQL specific, but I'm leaving it in since I personally have experienced this when there are too many parameters bound to execute. It will simply return false and act as if nothing went wrong. This would give especially undesirable behavior in transactions, since a query would silently fail, while the others would work, therefore defeating its purpose of being linearizable. This is why you must check for truthiness in case this happens. I actually couldn't find too much info about it, but this StackOverflow describes the issue pretty well. Weirdly enough, if you don't bind enough variables, it'll correctly throw an exception.

Some Extras

Do I need $stmt = null?

This is essentially the same as using $stmt->close() in MySQLi and the same applies. No, it's certainly not required, but is considered good coding practice by some (obviously subjective). I prefer to be explicit and I also do both $stmt = null and $pdo = null. If you are closing the PDO connection, then you must close the prepared statements as well, as stated here. While this isn't exactly the same as using $mysqli->close(), it's pretty similar. A PDO function to close the connection is something that has been requested for years, and is dubious if it'll ever be implemented.

Closing the prepared statements would be useful if you're reusing the same variable name. Both are not truly necessary, as they will close at the end of the script's execution anyway.

So Using Prepared Statements Means I'm Safe From Attackers?

While you are safe from SQL injection, you still need validate and sanitize your user-inputted data. To help prevent persistent XSS, you can use functions like filter_var() to validate before inserting it into the database and htmlspecialchars() to sanitize after retrieving it.

Author - Daniel Marcus

Firmly believes that web technologies should take over everything. Enjoys writing tutorials about JavaScript and PHP.