January 30, 2013

Introduction

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:

DRIVER:host=HOST;dbname=DATABASE_NAME

Example:

$dsn = 'mysql:host=localhost;dbname=my_db&charset=utf8';

And the full syntax:

$db = new PDO('mysql:host=localhost;dbname=my_db&charset=utf8', 'user', 'password');

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:

try {
    $db = new PDO('mysql:host=localhost;dbname=my_db&charset=utf8', 'user', 'password');
} catch (PDOException $e) {
    // There was an error with the connection
}

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:

try {
    $db = new PDO('mysql:host=localhost;dbname=my_db&charset=utf8', 'user', 'password', 
        array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    // There was an error with the connection
}

“Regular” Queries & Retrieving Results

So, now that we’ve successfully connected to our database, we can start interacting. Firstly, we’ll look at the query() method. This will execute a regular SQL query and return a resource:

$query = $db->query(QUERY);

Easy enough, right? The query() method returns a PDO object. Think of this as a resource, much similar to using your mysql_query. Now we must “fetch” the data. We can do this in one of two ways:

fetch()
Using the fetch() method will return ONE result from your query, unless you couple it with a while loop, as shown below:

$query = $db->query('SELECT username FROM users');
 
# One result
$fetch = $query->fetch();
 
echo $fetch['username'];
 
# All results
while ($fetch = $query->fetch()) {
	echo $fetch['username'];
}

fetchAll()
This method is similar to fetch(), however it returns ALL the results. Example:

$query = $db->query('SELECT name FROM fruits');
 
$fetch = $query->fetchAll();
 
# First result
echo $fetch[0]['name'];
 
# All results
foreach ($fetch as $fruit) {
	echo $fruit['name'];
}

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.

Inside of the prepare() method, you put your query. I’m going to show you a couple of ways preparing a statement for user inputted values. First of all, replace any values in your query that would you want to “escape” with :name. That’s a COLON followed by the name. Like the following:

SELECT id FROM fruit
WHERE name = :name

This is can be done multiple times within a query:

SELECT id FROM users
WHERE username = :username AND password = :password

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():

$query = $db->prepare('SELECT id FROM users WHERE username = :username AND password = :password');
 
$array = array(
	'username' => 'Carrot',
	'password' => 'sup homie'
);
 
$query->execute($array);

The query would look like this:

SELECT id FROM users WHERE username = 'carrot' AND password = 'sup homie'

Awesome! Now we can just do as we did before to retrieve the results:

$fetch = $query->fetch();
 
echo $fetch['id'];

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 ( ? ):

$query = $db->prepare('SELECT id FROM users WHERE username = ? AND password = ?');

Now we can use the execute() again, however this time, we can use a regular array and the values wil be replaced in the order of the question marks:

$query = $db->prepare('SELECT id FROM users WHERE username = ? AND password = ?');
 
$array = array('carrot', 'password');
$query->execute($array);

I think that’s much simpler :) This is called “binding” since we are “binding” data to our query. Let me show you one more example of this:

$query = $db->prepare('INSERT INTO users (username, name, password) VALUES (?, ?, ?)');
 
# A possibly problematic query!
$array = array('carrot', "John O'Dear", 'password');
 
$query->execute($array);

Same as:

$query = "INSERT INTO users (username, name, password) VALUES ('carrot', 'John O\'Dear', 'password')";

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.

Example:

$id = 17;
$query = $db->prepare('SELECT name FROM fruits WHERE id = :id');
 
$query->bindParam(':id', $id, PDO::PARAM_INT);
$query-execute();
 
# Runs the query:
SELECT name FROM fruits WHERE id = 17

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:

$query = $db->prepare('INSERT INTO people (username, name, age) VALUES (?, ?, ?)');
 
$query->bindParam(1, 'Carrot');
$query->bindParam(2, 'John');
$query->bindParam(3, 17, PDO::PARAM_INT);
 
$query->execute();

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:

0 – SQLSTATE error code
1 – Driver-specific error code
2 – Driver-specific error message

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?

$query = $db->prepare('INVALID QUERY HERE');
 
# So, if there's an error
if ($query === false) {
	var_dump($query->errorInfo());
}

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:

$query = $db->query('INVALID SQL');
 
if ($query !== FALSE) {
	echo 'Query was successful!';
}
else {
	$error = $query->errorInfo();
	echo 'MySQL Error: ' . $error[2];
}

Conclusion

To conclude: If you use PDO correctly, there will be a ZERO risk of SQL injections. PDO is great and you should always use it where you can :)




Comments

Very informative tutorial. Well written.

- Mohatu, Jan 30, 2013

Very helpful, thanks!

- haywire, Jan 31, 2013

Nicely made, thank you!

- Manu, Jan 31, 2013

Great tut, keep it up!!!

- Dave85, Jan 31, 2013

thanks man, keep it up brosky... <3

- abdi, Jan 31, 2013

Very nice! However, please bear in mind that there are no things such as "ZERO SQL Injection". But it would be like inserting a very big cable in an eye of a needle. :) Be good! :P

- DAVEologist, Feb 3, 2013

Whilst I'd normally agree with you there, I must quote myself: "If used correctly [..]" If you prepare a statement correctly with all user input, there IS no SQLi vulnerability :)

- Prash, Feb 3, 2013

Very nice indeed! Thanks for this tutorial, I've been using the outdated and soon to be deprecated mysql_query($query); method of doing things. I was looking into learning MySQLi, but I think now PDO would be a better option!

- Mike57, Feb 4, 2013

I think PDO is a lot simpler to use than MySQLi, even without a grasp of OOP, it makes things a lot easier. And not to mention portable! ^.^

- Prash, Feb 4, 2013

Great stuff. Nice work, buddy!

- Dec, Feb 17, 2013

Very helpful tutorial. It will help the PHP programmers.

- Morshedul Arefin, Feb 27, 2013

[...] read this tutorial about PDO. I had an error and tried the errorInfo() function. However, I get this error when trying [...]

- PDO: Call to a member function errorInfo() | BlogoSfera, Jun 9, 2013

Replied.

- Prash, Jun 10, 2013

Thanks a lot for making it so clear, I thought PDO as shady, but you made it very clean.

- Herland, Jun 16, 2013

Very good tutorial, I enjoyed the language as well, "saved our asses" nice one.

- Tigere, Sep 26, 2013

After reading many tutorials on PDO, this is the first one that makes PDO seem friendly and not scary. Cheers.

- infomiho, Oct 24, 2013

Nice Job!! Helpful ...............Thank you so muck!!!!!!!!!!!!!!!

- Ko Soe, Feb 8, 2014

Hello, Very simple and clear tutorial in PDO, any one want to get a general overview of what is PDO should him read this tut. Some things to clear, PDO::rowCount() is not supported by all PDO drivers, some databases 'll return the number of rows like MySQL and some will not. In order to pass this issue, you can either see the result of: 'SELECT count(1) FROM table_name' Or you can count the array return by PDO::fetchALL like this: $rows = $db->fetchAll(); $ttl_rows = count($rows); But this would be a little memory insufficient.

- H Aßdøµ, Mar 5, 2014

This is the best PDO tutorial that I have found and the clearest. Thank you for taking the time to write it. I had to change the constructor for the character set string. The "&" didn't work for me. I changed it to: ;charset=utf8 instead of &charset=utf Best,

- Alex R., May 22, 2014

Thanks so much, very good explanation!

- Alejandro, May 30, 2014

New Comment