Prevent PHP SQL Injection with PDO Prepared Statements

Sep 24, 2012

Table of contents:

Connecting to MySQL
Getting started with PDO
Setting up
Run
Insert
Select
Delete
Update
Error handling
Conclusion
Further reading and resources

SQL Injection is a common problem when a new or inexperienced developer writes code that is vulnerable to attack. PHP is one of the most easily accessible programming languages, however this often leads to insecure code or bad practices.

PHP is so prevalent and easy to set up, it makes it a good first choice if you are just getting started. On most LAMP servers, all you have to do is to write a one page file and you can instantly begin creating database powered websites.

However, you can leave yourself, your users and their data completely exposed to any number of attacks.

SQL Injection does not need to be a problem as there are a number of best practices to avoid these insecurities.

PDO (PHP Data Objects) is a a database abstraction layer that allows you to work with many different types of databases quickly and securely. This enables you to support multiple types of database with the same code and protects you from SQL Injection by using Prepared Statements.

A Database Abstraction Layer hides the interaction with the database by providing all functionality through an API. All this basically means is, you talk to the database through a separate layer that handles all the complexity and processing.

This is a tutorial to help you get started with using PDO in your PHP development. I promise you, once you get going with PDO, you won’t look back.

Connecting to MySQL

A while ago, I wrote a tutorial about connecting to MySQL through PHP. If you are currently doing any kind of PHP work, you will probably be using this method.

Connecting to MySQL through PHP using this method is entirely secure if you take the precautions to protect your statements from injection. However, many developers do not take these precautions.

Also, if in the future you need to switch databases, this old way of connecting will be completely useless.

As I mentioned in the introduction, using the PDO API, you will be protected from SQL Injection and you will be able to switch out databases without completely overhauling your code.

Getting started with PDO

For this tutorial, we will be working with PHP PDO Wrapper Class from the good folks at Imavex. This is a class for quickly getting started with PDO by providing with you with the common methods you are going to need to get going. By using this class, we are effectively using two layers of abstraction.

So go and download the class if you haven’t already and we’ll begin.

Setting up

So now that you have downloaded the class, open up the zip file and put all the files into a new directory.

Next, create a new PHP file and include the class.db.php file. I’ll be referring to this new file as tutorial.php.

// Include the database class
include "class.db.php";

Next, open phpMyAdmin and create a new user, password and database.

Finally, open up tutorial.php again and create a new instantiation of the database class.

// Connect to database
$db = new db("mysql:host=localhost;dbname=mydb", "dbuser", "dbpasswd");

Replace the values mydb, dbuser and dbpasswd with the details you just set up in phpMyAdmin.

If you are familiar with Object Oriented Programming (OOP) PHP, the previous line should be very familiar. However, for those of you who are just getting to grips with OOP, take a look at this blog post by Treehouse that explains the concept more in-depth, Getting started with OOP and PHP5.

Run

The first method we will look at is the Run method. The Run method allows you to run SQL statements that do not fit into the delete, insert, select, or update methods.

In order to use the delete, insert, select, or update methods later on, we are going to need to set up a table in our database. Creating a table can of course be achieved by running an SQL statement, and so we use the run method to do just that.

// The SQL statement
$sql = <<<SQL
    CREATE TABLE mytable (
    ID int(11) NOT NULL AUTO_INCREMENT,
    FName varchar(50) NOT NULL,
    LName varchar(50) NOT NULL,
    Age int(11) NOT NULL,
    Gender enum('male','female') NOT NULL,
    PRIMARY KEY (ID)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11;
SQL;
// Run the statement
$db->run($sql);

In the code above, we save the SQL statement into the $sql variable, and then use the run method to run the statement. Now if you look at your database in phpMyAdmin, you should see the new table.

Insert

The next method we will be looking at is the insert method.

The insert method takes the name of the table which you want to insert into and an associative array with the names of the fields and the data you wish to enter. This method will also automatically filter the array’s keys against the available field names in the database table.

// Create array of data to insert
$insert = [
    "FName" => "John",
    "LName" => "Doe",
    "Age" => 26,
    "Gender" => "male",
];
// Insert the array into the table
$db->insert("mytable", $insert);

Run the code above and then check your database again in phpMyAdmin. In mytable, you should see the newly inserted row.

If there have been no SQL errors, this method will return the number of new rows created.

For example:

// Run the insert method and save the return into a variable
$result = $db->insert("mytable", [
    "FName" => "John",
    "LName" => "Doe",
    "Age" => 27,
    "Gender" => "male",
]);
// Display the number of rows inserted
echo $result;

When you run the code above, you should be returned the value 1 to the screen.

Select

The next method we will be looking at is the select method.

The select method allows to to specify different inputs to enable you to run various select queries.

To select all the results from a table:

$results = $db->select("mytable");
print_r($results);

This will return all of the results from mytable.

Next, we can specify a WHERE clause:

$results = $db->select("mytable", "Gender = 'male'");
print_r($results);

Next we can use a Prepared Statement:

$search = "J";
$bind = [
    ":search" => "$search%",
];
$results = $db->select("mytable", "FName LIKE :search", $bind);
print_r($results);

And finally we can set the fields we wish to return.

$search = "J";
$bind = [
    ":search" => "$search%",
];
$results = $db->select("mytable", "FName LIKE :search", $bind, "Age");
print_r($results);

In the second and third examples we used a Prepared Statement. A Prepared Statement uses placeholders for data, rather than the data itself. SQL Injection happens because the attacker is tricking the statement into including extra information into the statement. By using Prepared Statements, you avoid the risk of SQL Injection.

Delete

The next method we will be looking at is the delete method.

The delete method works in much the same way as the select method so I won’t go into detail.

Here are two examples of the delete method in action:

// Delete records where age is less than 30
$db->delete("mytable", "Age < 30");

// Delete using a prepared statement
$lname = "Doe";
$bind = [
    ":lname" => $lname,
];
$db->delete("mytable", "LName = :lname", $bind);

Update

And finally, the last method we’ll be looking at is the update method.

Again, nothing really new here. The examples should be pretty self explanatory from the previous explanations.

// Update
$update = [
    "FName" => "Jane",
    "Gender" => "female",
];
$db->update("mytable", $update, "FName = 'John'");

// Update using a prepared statement
$update = [
    "Age" => 24,
];
$fname = "Jane";
$lname = "Doe";
$bind = [
    ":fname" => $fname,
    ":lname" => $lname,
];
$db->update("mytable", $update, "FName = :fname AND LName = :lname", $bind);

Error handling

The final method we will be look at is how this class handles errors.

The method we will be using is setErrorCallbackFunction.

To set the method, use:

$db->setErrorCallbackFunction("myErrorHandler");

You can then either use a function to handle the error, or display it to the screen.

To display the error to the screen:

$db->setErrorCallbackFunction("echo");

Or to use a function:

function myErrorHandler($error)
{
    // Handle the error or send an email notification
}
// Captures the error as text
$db->setErrorCallbackFunction("myErrorHandler", "text");

Conclusion

And there you have it, you now have everything you need to start using PDO Prepared Statements in your current or next PHP project.

PDO will protect you from SQL Injection and allows you to use a clean abstraction layer from the database that will ensure you have future flexibility should you wish to change databases.

If you are just wanting to start using a ready made PDO class straight away, hopefully this tutorial will be sufficient.

However, if you are like me and you need to know exactly what is going on under the hood, check back in a couple of weeks where I will show you how to roll your own PDO class.

In the mean time, take a look at the following links for further information and background reading on PDO.