Handling errors

Where are we?

Yow know how to help people add new data to a database. But what happens when things go wrong?

This lesson’s goals

We’ve talked about error handling before. Both on the client side and the server side.

Using databases adds more things that can go wrong. In this lesson, we’ll see how you can handle database errors.

We’ll talk about two new types of errors:

  • Database connection errors
  • SQL errors

You can check for both.

We’ll also talk about simple server-side validation. Even if you can check all data on the client, you should check it on the server as well. To foil Evil Doers.

New types of errors

There are two new types of errors:

  • Connection errors
  • SQL errors

A connection error is when a PHP program can’t connect to the database. An SQL error is when MySQL says something is wrong with an SQL statement. Like SELECT being typed as SELCT.

Let’s look at each type of error.

Connection errors

We had this earlier:

require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);

Figure 1. Connecting to a database

If all is well, $db will be a valid connection object, something your PHP can use to send SQL to the database. But what if you have the wrong password, or user name? Or if the database server is down? Here’s how you check:

$db = new mysqli(...);
//Did it work?
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}
Do something.

Figure 2. Checking for a connection error

mysqli_connect_error() will be FALSE if there was no connection error. Otherwise, it will contain an error message. So if there was no connection error, the if will skip over lines 4 to 7, and continue with normal processing on line 8.

exit() causes PHP to stop immediately and end the page.

So this is how you handle a connection error. But there are also…

SQL errors

It’s easy for errors to find their way into SQL. For example, suppose I tried to use this to create a new record in the DogRock database:

$db = new mysqli(...);
...
$query = "insert into articles
  (title, author, body, when_published)
  values ($title, $author, $body, $when_published)";
$db->query($query);

Figure 3. SQL error

Can you see what’s wrong?

Quotes are missing. Suppose $title had the value Why I like fish. This:

... values($title,

would become:

... values(Why I like fish,

What we want is:

... values('Why I like fish',

The quotes keep everything together, so the title is treated as one value. When the quotes are missing, MySQL doesn’t know what to do.

Here’s how to check that an SQL query is correct:

$db = new mysqli(...);
...
$query = "insert into articles
  (title, author, body, when_published)
  values ($title, $author, $body, $when_published)";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
    </blockquote>";
  exit();
}

Figure 4. SQL error checking

Line 7 is:

if ( $db->error != '' ) {

If there is no error, then $db->error will be empty. If there is an error, then $db->error will not be empty.

If there’s an error, line 8 shows the error message. Lines 9 to 12 show the query that caused the error.

Line 13 stops the program immediately.

Summary so far – There are two new types of errors:

  • Database connection errors
  • SQL errors

You’ve seen how to test for them.

Renata
Renata

So, if you don’t get any errors from the $db->error check, that means that everything is OK?

Kieran
Kieran

No!

It means that the format of the SQL statement was correct. But you might still have coding errors.

Suppose this was in your PHP:

$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$image_file_name', '$description', $price)";
$db->query($query);

Does that look OK?

Renata
Renata

Let me check… Hmm… Wait, the image file name and description are backwards. You have:

(name, description, image_file_name, price)

in one place, and:

('$name', '$image_file_name', '$description', $price)

in another.

Kieran
Kieran

Right! It’s a bug.

But the format of the SQL statement is just fine. MySQL would detect no errors, and would do what you told it to.

CC
CC

So what’s the point, if it won’t tell you about all of the errors?

Kieran
Kieran

$db->error will tell you about some of the errors. And that’s better than nothing. But you still need to look in the database, and make sure that your data was added correctly.

Renata
Renata

And we could use phpMyAdmin to check the data, right?

Kieran
Kieran

Yes. You can use phpMyAdmin to check whether your PHP program did what you thought it should.

DogToys: Saving a new product record

Let’s see a new version of save-new-product.php. Recall that save-new-product.php is sent the form data the user types, and saves it to the database.

Here is the code with error checking:

<?php
//Save a new product.
//Input (all POST):
//  name: Name of the product.
//  description: Description of the product.
//  image_file_name: Name of the file containing an image of the product.
//  price: Selling price of the product.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Did it work?
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 5. save-new-product.php

The code starting at line 14 checks whether the connection to MySQL was made successfully.

The code starting at line 37 checks whether the SQL statement ran without error.

DogRock: Saving a new article

Let’s do the same with DogRock.

<?php
//Save a new article.
//Input (all POST):
//  title: Title of the article.
//  body: Body of the article.
//  author: Author of the article.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}

//Get the form fields.
$title = stripslashes($_POST['title']);
$body = stripslashes($_POST['body']);
$author = stripslashes($_POST['author']);

//Make the fields safe.
$title = $db->escape_string($title);
$body = $db->escape_string($body);
$author = $db->escape_string($author);

//Prepare the publish date.
$when_published = date('Y-m-d');

//Create and run the SQL.
$query = "insert into articles
  (title, author, body, when_published)
  values ('$title', '$author', '$body', '$when_published')";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 6. save-new-article.php

It has shiny new error checking code.

CC
CC

I can see how this error checking is needed.

But aren’t most errors made by people? Maybe typing something wrong? How are they checked?

Kieran
Kieran

Ooo, good question! You’re right, people make more mistakes than computers. Let’s see how to check for those sorts of errors

Validating user data

We looked earlier at how to do validation. Remember that we did some client-side checking, and some server side checking.

We ended up with an architecture like this:

Validation architecture

Figure 7. Validation architecture

order.php would do client-side checking with JavaScript. It would also do server-side checking with PHP. order.php would send form data to itself to do any server-side checking. Then, if all was OK, it would send the data to process.php.

We hadn’t written process.php at that point. But now we can write it, storing information to a database.

Let’s see how that will work for our two sample applications

Validating in DogToys

Here’s the input form for adding a new product:

Add product form

Figure 8. Add product form – add-product.php

What errors can the user make? Not many.

  • Leaving any field blank.
  • Entering a non-number in price, or a negative number.

We could add other checks, like making sure that the image name is a valid file name. But let’s leave it for now.

Both of these checks can be done on the client side. And I added code to do them to add-product.php. Like this:

...
//Check the name field.
var name = $("#name").val();
if ( name == '' ) {
  data_ok = false;
  show_field_error_message('Sorry, there must be a name.', 'name');
}
else {
  hide_error_message('name');
}
...
<p>
  Name<br>
  <input type="text" name="name" id="name" size="30"><br>
  <span id="name_message_container" class="message_container">
    <img src="<?php print $path_to_root; ?>/library/error.png" alt="Error">
    <span id="name_message"/>
  </span>
</p>
...

Figure 9. Client-side error checking

I used the same approach we used earlier to show error messages. The result is like this:

Client-side error

Figure 10. Client-side error

So all of our validation can be done on the client, in JavaScript.

But we want to add server-side validation anyway, in PHP.

Why?

Security. A smart hacker could grab the code from add-product.php (the page that shows the form). S/he could delete the JavaScript code that did the client-side error checks, and run the modified page. It would accept bad data (like a negative price), and send it to save-new-product.php for storage in the database. If save-new-product.php didn’t check the data, it would let the evil data into the database.

So we’ll add some simple validation to save-new-product.php. Nothing fancy, but enough to make sure that evil data doesn’t get into the database.

Here’s yet another version of save-new-product.php.

<?php
//Save a new product.
//Input (all POST):
//  name: Name of the product.
//  description: Description of the product.
//  image_file_name: Name of the file containing an image of the product.
//  price: Selling price of the product.

$path_to_root = '..';
//Connect to DB.
require $path_to_root . '/library/db-connect.php';
$db = new mysqli($host, $user_name, $password, $db);
//Did it work?
if ( mysqli_connect_error() ) {
  print '<p>Error! Could not connect to the database. ';
  print 'Error message: '.mysqli_connect_error().'</p>';
  exit();
}

//Get the form fields.
$name = stripslashes($_POST['name']);
$description = stripslashes($_POST['description']);
$image_file_name = stripslashes($_POST['image_file_name']);
$price = stripslashes($_POST['price']);

//Validate
if ( $name == '' ) {
  print '<p>Error! Name is missing.</p>';
  exit();
}
if ( $description == '' ) {
  print '<p>Error! Description is missing.</p>';
  exit();
}
if ( $image_file_name == '' ) {
  print '<p>Error! Image file name is missing.</p>';
  exit();
}
if ( $price == '' ) {
  print '<p>Error! Price is missing.</p>';
  exit();
}
if ( is_nan($price) ) {
  print "<p>Error! Price is not a number: $price.</p>";
  exit();
}
if ( $price <= 0 ) {
  print "<p>Error! Price is too low: $price.</p>";
  exit();
}

//Make the fields safe.
$name = $db->escape_string($name);
$description = $db->escape_string($description);
$image_file_name = $db->escape_string($image_file_name);
$price = $db->escape_string($price);

//Create and run the SQL.
$query = "insert into products
  (name, description, image_file_name, price)
  values ('$name', '$description', '$image_file_name', $price)";
$db->query($query);
if ( $db->error != '' ) {
  print '<p>SQL error! Message: ' . $db->error . '</p>';
  print "<p>Query:</p>
    <blockquote>
    $query
  </blockquote>";
  exit();
}

//Back to admin menu.
header('location:index.php');
exit();
?>

Figure 11. Another version of save-new-product.php

The new stuff starts at line 26. If there’s an error, a message is shown, and the program exits at once. This gives ugly error messages, but that’s acceptable. These errors should never happen, unless either:

  • The DogToys application is broken.
  • An Evil Doer is attacking.

Line 43 shows the “is not a number” function in PHP:

is_nan()

The syntax is a little different from JavaScript. In JS, it’s:

isNaN() This is JavaScript, not PHP.

But they do the same thing.

You could also use:

if ( ! is_numeric($price) ) {

is_numeric() is TRUE if you give it a numeric value. ! means “not.” So this version of the if says: “if it is not true that $price is numeric.”

Use either is_nan() or ! is_numeric(). Use the one that makes more sense to you.

Line 44 shows a good practice: it outputs the value that is incorrect. This helps in debugging.

Validating in DogRock

Here’s some new code for save-new-article.php:

//Validate
if ( $title == '' ) {
  print '<p>Error! Title is missing.</p>';
  exit();
}
if ( $body == '' ) {
  print '<p>Error! Body is missing.</p>';
  exit();
}
if ( $author == '' ) {
  print '<p>Error! Author is missing.</p>';
  exit();
}

Figure 12. Code for save-new-article.php

It just makes sure that each field has a value.

Exercise: Check for errors in Jokes

Add error checking to save-new-joke.php. Check for:

  • Connection errors.
  • SQL errors.
  • Validation errors. Missing data, or an invalid funniness value.

How to check whether your error checking worked? You need to break stuff. Here are some suggestions.

  • Fill in the add-joke.php form, and, before you click the Save button, use the XAMPP control panel to stop MySQL. Then click the Save button. This will simulate a DBMS crash.
  • Fill in the add-joke.php form, and, before you click the Save button, change admin/db-connect.php. Use the wrong password. Then click the Save button.
  • Sabotage the JavaScript error checking in save-joke.php. Now try to save bad data, like a joke with a funniness rating of -8. This simulates a hacker attack.

One way to sabotage the client-side error checking is to add a new line:

$("#new_joke_form").submit(function() {
   return true;

The return statement will make sure that the form data is always accepted, even if some fields are invalid.

Once you have it working, you can check my solution. But don’t look at it now!

If you want to share your solution with other people, you will need to upload it to your hosting server.

(Log in to enter your solution to this exercise.)

Summary

We talked about error checking and validation on this page. We saw two new types of errors:

  • Database connection errors
  • SQL errors

You can check for them both.

We also talked about simple server-side validation. Even if you can check all data on the client, you should check it on the server as well. To foil Evil Doers.

What now?

You know how to add data to the database. But how do you show the data that was added? That’s coming up next.


How to...

Lessons

User login

Log in problems? Try here


Dogs