PHP: Prepared Statements with PDO Tutorial

January 30th, 2013Tutorial

PDO is a library for PHP specifically designed for secure database interactions. PDO is an abstract layer, so it allows the use of different RDBMS (Relational Database Management Systems) whilst using a consistent SQL syntax. For example, you can easily switch between MySQL, SQLite, MariaDB etc. This allows for portability with code. Note: if you use any driver-specific SQL functions, they will not be modified. PDO stands for PHP Data Objects.

PDO comes shipped with any PHP installation > 5.1 however may require some custom configuration to work with your preferred database driver. Most should already include MySQL. PDO requires at least PHP 5.0 to run and will not work with older versions as it utilises the new OO features that came with 5.0. PDO is completely object-oriented. There are no procedural functions for its use.

Getting Started

As with anything else, you must first connect to a database before interacting with it. In PDO, you create one object per database you would like to interact with. You do this by instantiating the PDO class with a DSN. This stands for Data(base) Source Name. Inside the DSN, you put the connection details of your DB. Below is syntax/parameter to instantiate the PDO class:

$db = new PDO($dsn, $databaseUsername, $databasePassword);

The DSN should include the type of database you’ll be working with; the host and the database name. Its format:

We add the character set parameter for security reasons, it’s pretty important, so don’t forget it! If the connection fails, however, PDO will throw a PDOException, so we can wrap our connect statement in a try/catch block:

For development/learning, it’s a good idea to set the PDO error mode attribute PDO::ERRMODE_EXCEPTION. This will throw an exception if there is an error. If you’ve worked with the native MySQL functions, this can be likened to or die(mysql_error()). Also, to completely defend ourselves against SQL injection attacks (expanded upon later), we must set the PDO::EMULATE_PREPARES to false. You can read about why here. We can set both of these attributes by passing an array as the fourth parameter, so your final connection syntax is:

By default, both fetch() and fetchAll() will return an associative array of result(s). This can be change in the first parameter of each function – read up on the documentation linked.

Prepared Statements

This is where it starts to get fun. Whenever you interact with a database using user input, you should ALWAYS use prepared statements. But what are they?

A basic explanation: You write a query and state which values inside of the query will “user input”. You then bind the values of the user input to the query, and any malicious code is removed! Like magic :). Okay, so let’s see some examples. First, we must introduce the prepare() method.

Great! We stick that inside the prepare() method and we’re good to go. Next thing we have to do is execute() the statement. When we call the prepare() method, it will return another PDO object. We then call the execute() function with out user-inputted parameters in them. The execute() takes first parameter array, which is formatted:

$array = array(
'key' => 'value',
'foo' => 'bar'
);

Each array KEY is the same as the ‘key’ we gave in our prepare() statement. So let’s look at a full query, using prepare() and execute():

Now I’m going to show you another method. This is a much, MUCH faster method than above, however it could get confusing if you have a long query. Instead of using :name, we simple use a question mark ( ? ):

See how it saved our asses? Without preparing/escaping the data, we would have had an SQL error because of the single quote ( ‘ ) inside “John O’Dear”. This is escape so our query is safe. So no more SQL injection vulnerabilities! :D

Binding Datatypes

With the array and execute() method above, all our binded values are wrapped in quotes, so they are all passed as strings. But what if we want to bind data that is of a different data type? Like an integer? Well, we use the bindParam() method.

Let’s use this with our named params. First parameter of the method is name of the parameters, second is the value we want to replace with and the last is the datatype using the PDO::PARAM_* constants. We then use the execute() method without passing any parameters.

You can bind as many parameters as you like with the bindParam() method:

$query = $db->prepare('INSERT INTO people (username, name, age) VALUES (:username, :name, :age)');
# If we leave out the third parameters, it is set to
# PDO::PARAM_STRING by default
$query->bindParam(':username', 'Carrot');
$query->bindParam(':name', 'John');
# Now we bind an integer
$query->bindParam(':age', 17, PDO::PARAM_INT);
# Execute the query
$query->execute();

This can also be done with the question mark syntax, however you replace the first parameter with the position of the question mark in the prepared statement:

Counting Results

If we want to count the number of results returned, there’s a method for that! It’s called rowCount().

After using the query() method or executing a prepared statement, we can use the rowCount() method to return the number of results from the query:

$query = $db->query('SELECT id FROM users');
echo $query->rowCount();

Error Handling

Handling errors is a huge part of development. PDO makes it really easy. We’ll be using the errorInfo() method. After you’ve prepared a statement/ran a query, this method will return any errors. The errorInfo() method will return an array with 3 elements:

If there is no error, the 1st and 2nd element will be blank, however the 0th element will have “00000″. I’m not going to explain these SQLSTATE error messages right now. Anyway, if a query fails, the prepare() and query() methods will return FALSE. So let’s play, shall we?

Really simple. Makes debugging your SQL a real doddle. Remember, the errorInfo() method is attached to the PDO statement that is prepared (before being executed) or after using the query() method :) One more example: