Learning PHP Data Objects

This book will introduce you to one of the most important extensions to PHP that are available,
starting with PHP version 5.0—the PHP Data Objects, commonly known as PDO.

PHP grew in to a very popular web programming language due to its simplicity and ease of use.
One of the key factors of this growing success is the built-in possibility to access many popular
relational database management systems (RDBMS), such as MySQL, PostgreSQL, and SQLite, to
name just a few. Today, most of the existing and newly created web applications interconnect with
these databases to produce dynamic, data-driven websites.

While most PHP-enabled web servers are still running PHP versions prior to 5.0, the
enhancements and performance improvements introduced with this new version will lead to wide
acceptance of PHP 5 at all levels during coming years. This imposes the need to start familiarizing
ourselves with all the advanced features available in this version today.

What This Book Covers

Chapter 1 gives an overview of PDO along with a few features likes single interface for creating a
connection, connection strings, uniform statement methods, and use of exceptions and a singe
system of error codes.

Chapter 2 helps to get you started with PDO, by creating a sample database and then by creating a
connection object. It also introduces PDOStatement classes.

Chapter 3 deals with various error-handling processes and their uses.

Chapter 4 introduces prepared statements. It deals with using prepared statements without binding
values, binding a variable, and binding a parameter to a prepared statement. We also take a look at
how to work with BLOBs using streams so that we do not risk query failures.

Chapter 5 helps us determine the number of rows in the returned result set. Also, we come across
a new concept—scrollable cursors, which allow us to fetch subsets of rows from a result set.

Chapter 6 talks about advanced uses of PDO and includes setting connection parameters,
transactions, and methods of PDO and the PDOStatement class.

Chapter 7 gives an example, where creation of the method part of an MVC application is
discussed.

Appendix A explains the object-oriented features like inheritance, encapsulation, polymorphism,
and exception handling.

Error Handling

Now that we have built our fi rst application that uses PDO, we will take a closer look
at an important aspect of user-friendly web applications—error handling. Not only
does it inform the user about an error condition, it also limits the damage if an error
is not detected when it occurred.

Most web applications have rather simple error handling strategy. When an error
occurs, the script terminates and an error page is presented. The error should
be logged in the error log, and the developers or maintainers should check the
logs periodically. The most common sources of errors in database-driven web
applications are the following:

• Server software failure or overload such as the famous “too many
  connections” error
• Inappropriate confi guration of the application, which may happen when
  we use an incorrect connection string, a rather common mistake when an
  application is moved from one host to another
• Improper validation of user input, which may lead to malformed SQL and
  subsequent failure of the query
• Inserting a record with a duplicate primary key or unique index value, which
  either results from an error in the business logic of the application or may
  occur in a controlled situation
• Syntax errors in SQL statements

In this chapter, we will extend our application so that we can edit existing records as
well as add new records. As we will deal with user input supplied via web forms, we
have to take care of its validation. Also, we may add error handling so that we can
react to non-standard situations and present the user with a friendly message.

Before we proceed, let’s briefl y examine the sources of errors mentioned above and
see what error handling strategy should be applied in each case. Our error handling
strategy will use exceptions, so you should be familiar with them. If you are not,
you can refer to Appendix A, which will introduce you to the new object-oriented
features of PHP5.

We have consciously chosen to use exceptions, even though PDO can be instructed
not to use them, because there is one situation where they cannot be avoided. The
PDO constructors always throw an exception when the database object cannot
be created, so we may as well use exceptions as our main error-trapping method
throughout the code.

Sources of Errors

To create an error handling strategy, we should fi rst analyze where errors can
happen. Errors can happen on every call to the database, and although this is rather
unlikely, we will look at this scenario. But before doing so, let’s check each of the
possible error sources and defi ne a strategy for dealing with them.

Server Software Failure or Overload

This can happen on a really busy server, which cannot handle any more incoming
connections. For example, there may be a lengthy update running in the background.
The outcome is that we are unable to get any data from the database, so we should
do the following.

If the PDO constructor fails, we present a page displaying a message, which says
that the user’s request could not be fulfi lled at this time and that they should
try again later. Of course, we should also log this error because it may require
immediate attention. (A good idea would be emailing the database administrator
about the error.)

The problem with this error is that, while it usually manifests itself before a
connection is established with the database (in a call to PDO constructor), there is
a small risk that it can happen after the connection has been established (on a call
to a method of the PDO or PDOStatement object when the database server is being
shutdown). In this case, our reaction will be the same—present the user with an error
message asking them to try again later.

Improper Configuration of the Application

This error can only occur when we move the application across servers where
database access details differ; this may be when we are uploading from a
development server to production server, where database setups differ. This is not
an error that can happen during normal execution of the application, but care should
be taken while uploading as this may interrupt the site’s operation.

If this error occurs, we can display another error message like: “This site is under
maintenance”. In this scenario, the site maintainer should react immediately, as
without correcting, the connection string the application cannot normally operate.

Improper Validation of User Input

This is an error which is closely related to SQL injection vulnerability. Every
developer of database-driven applications must undertake proper measures to
validate and fi lter all user inputs. This error may lead to two major consequences:
Either the query will fail due to malformed SQL (so that nothing particularly
bad happens), or an SQL injection may occur and application security may be
compromised. While their consequences differ, both these problems can be
prevented in the same way.

Let’s consider the following scenario. We accept some numeric value from a form
and insert it into the database. To keep our example simple, assume that we want to
update a book’s year of publication. To achieve this, we can create a form that has
two fi elds: A hidden fi eld containing the book’s ID, and a text fi eld to enter the year.
We will skip implementation details here, and see how using a poorly designed
script to process this form could lead to errors and put the whole system at risk.

The form processing script will examine two request variables:

$_REQUEST[‘book’], which holds the book’s ID and $_REQUEST[‘year’], which
holds the year of publication. If there is no validation of these values, the fi nal code
will look similar to this:

	$book = $_REQUEST['book'];
	$year = $_REQUEST['year'];
	$sql = "UPDATE books SET year=$year WHERE id=$book";
	$conn->query($sql);

Let’s see what happens if the user leaves the book fi eld empty. The fi nal SQL would
then look like:

	UPDATE books SET year= WHERE id=1;

This SQL is malformed and will lead to a syntax error. Therefore, we should ensure
that both variables are holding numeric values. If they don’t, we should redisplay the
form with an error message.

Now, let’s see how an attacker might exploit this to delete the contents of the entire
table. To achieve this, they could just enter the following into the year field:

	2007; DELETE FROM books;

This turns a single query into three queries:

	UPDATE books SET year=2007; DELETE FROM books; WHERE book=1;

Of course, the third query is malformed, but the fi rst and second will execute, and
the database server will report an error. To counter this problem, we could use
simple validation to ensure that the year fi eld contains four digits. However, if we
have text fi elds, which can contain arbitrary characters, the fi eld’s values must be
escaped prior to creating the SQL.

Inserting a Record with a Duplicate Primary Key or Unique Index Value

This problem may happen when the application is inserting a record with duplicate
values for the primary key or a unique index. For example, in our database of
authors and books, we might want to prevent the user from entering the same book
twice by mistake. To do this, we can create a unique index of the ISBN column of the
books table. As every book has a unique ISBN, any attempt to insert the same ISBN
will generate an error. We can trap this error and react accordingly, by displaying an
error message asking the user to correct the ISBN or cancel its addition.

Syntax Errors in SQL Statements

This error may occur if we haven’t properly tested the application. A good
application must not contain these errors, and it is the responsibility of the
development team to test every possible situation and check that every SQL
statement performs without syntax errors.

If this type of an error occurs, then we trap it with exceptions and display a fatal
error message. The developers must correct the situation at once.

Now that we have learned a bit about possible sources of errors, let’s examine how
PDO handles errors.