In this article, 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 briefly 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 first 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 define a strategy for dealing with them.
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 fulfilled 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 PDO Statement 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 filter 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 fields: A hidden field containing the book’s ID, and a text field 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 final code will look similar to this:
$book = $_REQUEST['book'];
$year = $_REQUEST['year'];
$sql = "UPDATE books SET year=$year WHERE id=$book";
Let’s see what happens if the user leaves the book field empty. The final 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 first 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 field contains four digits. However, if we have text fields, which can contain arbitrary characters, the field’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.
Types of Error Handling in PDO
By default, PDO uses the silent error handling mode. This means that any error that arises when calling methods of the PDO or PDOStatement classes go unreported. With this mode, one would have to call PDO::errorInfo(), PDO::errorCode(), PDOStatement::errorInfo(), or PDOStatement::errorCode(), every time an error occurred to see if it really did occur. Note that this mode is similar to traditional database access—usually, the code calls mysql_errno(),and mysql_error() (or equivalent functions for other database systems) after calling functions that could cause an error, after connecting to a database and after issuing a query.
Another mode is the warning mode. Here, PDO will act identical to the traditional database access. Any error that happens during communication with the database would raise an E_WARNING error. Depending on the configuration, an error message could be displayed or logged into a file.
Finally, PDO introduces a modern way of handling database connection errors—by using exceptions. Every failed call to any of the PDO or PDOStatement methods will throw an exception.
As we have previously noted, PDO uses the silent mode, by default. To switch to a desired error handling mode, we have to specify it by calling PDO::setAttribute() method. Each of the error handling modes is specified by the following constants, which are defined in the PDO class:
- PDO::ERRMODE_SILENT – the silent strategy.
- PDO::ERRMODE_WARNING – the warning strategy.
- PDO::ERRMODE_EXCEPTION – use exceptions.
To set the desired error handling mode, we have to set the PDO::ATTR_ERRMODE attribute in the following way:
To see how PDO throws an exception, edit the common.inc.php file by adding the above statement after the line #46. If you want to test what will happen when PDO throws an exception, change the connection string to specify a nonexistent database. Now point your browser to the books listing page.
You should see an output similar to:
This is PHP’s default reaction to uncaught exceptions—they are regarded as fatal errors and program execution stops. The error message reveals the class of the exception, PDOException, the error description, and some debug information, including name and line number of the statement that threw the exception. Note that if you want to test SQLite, specifying a non-existent database may not work as the database will get created if it does not exist already. To see that it does work for SQLite, change the $connStr variable on line 10 so that there is an illegal character in the database name:
$connStr = 'sqlite:/path/to/pdo*.db';
Refresh your browser and you should see something like this:
As you can see, a message similar to the previous example is displayed, specifying the cause and the location of the error in the source code.
Defining an Error Handling Function
If we know that a certain statement or block of code can throw an exception, we should wrap that code within the try…catch block to prevent the default error message being displayed and present a user-friendly error page. But before we proceed, let’s create a function that will render an error message and exit the application. As we will be calling it from different script files, the best place for this function is, of course, the common.inc.php file.
Our function, called showError(), will do the following:
- Render a heading saying “Error”.
- Render the error message. We will escape the text with the htmlspecialchars() function and process it with the nl2br() function so that we can display multi-line messages. (This function will convert all line break characters to tags.)
- Call the showFooter() function to close the opening and tags. The function will assume that the application has already called the showHeader() function. (Otherwise, we will end up with broken HTML.)
We will also have to modify the block that creates the connection object in common.inc.php to catch the possible exception. With all these changes, the new version of common.inc.php will look like this:
* This is a common include file
* PDO Library Management example application
* @author Dennis Popel
// DB connection string and username/password
$connStr = 'mysql:host=localhost;dbname=pdo';
$user = 'root';
$pass = 'root';
* This function will render the header on every page,
* including the opening html tag,
* the head section and the opening body tag.
* It should be called before any output of the
* This function will 'close' the body and html
* tags opened by the showHeader() function
* This function will display an error message, call the
* showFooter() function and terminate the application
* @param string $message the error message
// Create the connection object
$conn = new PDO($connStr, $user, $pass);
showError("Sorry, an error has occurred. Please try your request
latern" . $e->getMessage());
As you can see, the newly created function is pretty straightforward. The more interesting part is the try…catch block that we use to trap the exception. Now with these modifications we can test how a real exception will get processed. To do that, make sure your connection string is wrong (so that it specifies wrong databasename for MySQL or contains invalid file name for SQLite). Point your browser to books.php and you should see the following window: