Sunday, September 03, 2006

MySQL Error Checking in PHP

So often it seems that I am seeing someone struggling to find out what is wrong with their PHP script. They see the following error message:
Warning: mysql_fetch_assoc():
supplied argument is not a valid MySQL result resource
in /var/www/htdocs/somefile.php on line 18
and have no clue what this means. The line number has no bearing on where the real problem is, and there may be a whole cascade of these messages. Let's assume the code looks something like this:
mysql_connect ("server", "user", "password");
mysql_select_db ("dbname");
$result = mysql_query ("SELECT something FROM mytable");
while ($row = mysql_fetch_assoc ($result))
{
...
}
Where did the error occur? The answer is it could be any number of places. Let's make a list:
  1. The server name is wrong.
  2. MySQL is not running on the server
  3. The script is being run on a host that is not allowed to connect to the MySQL server
  4. Incorrect username
  5. Incorrect password
  6. The database does not exist
  7. The username does not have permission to access the database at all

    And finally:

  8. There is an error in the query
So we can play the guessing game trying to figure out where the error is. Or we can do error checking. The next mistake is to do some error checking, but not in all potential places for error. This can be more confusing than no error checking at all. Let's say that we checked for errors after the call to mysql_query but nowhere else, and the connect call failed. We put in the password wrong perhaps. So the call to mysql_connect fails. Then the call to mysql_select_db fails. Finally the call to mysql_query fails. We actually do check the error message here and what we get is the following:
Access denied for user 'nobody'@'localhost' (using password: NO)
At this point our user is scratching his head, because he knows he put in the right credentials, he's double-checked. This error message gives a complete different set of credentials: wrong username, no password, maybe even a diffrent server name. The reason for this, which is not always obvious, is that a connection did not get established via mysql_connect, so PHP reverts to a default set that is in its config file. Chances are nobody has edited it to put valid credentials in, and I wouldn't personally recommend it for security reasons.

So we can see how it is essential to perform error checking every step of the way to eliminate any guesswork as to what went wrong, where it went wrong, and why. The remaining item to discuss is how to check for errors. There are numerous different styles and techniques, each with their own pros and cons. My personal preference is to do several things:
  1. Store the credentials in a separate script. This might even be outside of the htdocs directory tree entirely, so there is no chance of someone referencing it maliciously.
  2. Write a small script that references the credentials script, connects to the database and selects the database, using proper error checking of course.

    (At this point we have eliminated the possibility of any connection error being mistaken for a faulty query)

  3. Display a compound error message with detailed information about a failing query.
I prefer a compound error message since it provides complete details, but any of the following will work. I recommend aborting the page load by calling die since it can print a message and do the abort in a single call. Plus it can be piggy-backed onto the mysql function calls using the short-circuit boolean logic which says that if the left-hand operand of an or is true, the right-hand side is ignored. So simply append one of the following forms:
  • or die()
  • or die("Query failed!")
  • or die ("Query failed: " . mysql_error())
  • or die ("Query failed: " . mysql_error() . " Actual query: " . $query)
I prefer the latter with its complete display of pertinent information. If you have more than one query in a particular document, it may be helpful to add some indication of which on failed to further eliminate confusion.

10 Comments:

Blogger خليّل said...

Thank you but the last one is very dangerous if you have an error into your website.
(haker can show the field names)

So i think that this one is correct

$sql_0= {request} or die ("Query failed 0: " . mysql_error());

"0" is a var whose you can change over your code.

Now, you know exactly where error into your code and it will not show your request.

Thanks

5:06 AM, September 05, 2006  
Blogger Bob Field said...

Maybe not for a production site, and maybe you want a debug flag that you can turn on to enable this display, but I find this information invaluable for debugging. Something may have gone wrong with the symbol substitution. Being able to see the actual query can save me hours of guesswork.

8:47 AM, September 05, 2006  
Blogger Arnold Daniels said...

I don't think using die() is a good way to handle errors.

Better is to user trigger_error() or, when using PHP 5, to throw an exception.

This has the advantage, that the regulair error_handler or exception_handler will handle the error. Also you may choose to skip a certain action if failing the action isn't leatal.

Examples:
$result = mysql_query("SELECT something FROM mytable");
if (!$result) trigger_error("Query failed: " . mysql_error(), E_USER_ERROR);

$result = mysql_query("SELECT something FROM mytable");
if (!$result) throw new Exception("Query failed: " . mysql_error());

$result = mysql_query("SELECT something FROM mytable");
if ($result) {
while ($row = mysql_fetch_assoc($result)) {
...
}
} else {
trigger_error("Could not perform action. Query failed: " . mysql_error(), E_USER_WARNING);
}

5:22 AM, September 20, 2006  
Blogger Bob Field said...

This was meant as an example of how to diagnose issues with MySQL and not as a PHP error-handling tutorial, seeing as how people cobble together a page, get misleading diagnostics, and then get onto the forums saying "it doesn't work" but they don't know what went wrong and neither does anyone else since proper diagnostics were not displayed.

7:39 AM, September 20, 2006  
Blogger Brian said...

Hey Bob,

this is exactly what I was looking for. I use to recreate my querys and paste them into the mysql cmd procesor. This will save me a ton of time.

Thanks,

Brian

10:32 PM, November 21, 2006  
Blogger Lenz_Kappov said...

Hey Bob, Thanks for that. Two and a half years on and your posting is still helping people. Feel good mon amis. :)

5:40 AM, February 22, 2009  
Blogger BlaqueHat said...

Good article.

10:01 AM, March 16, 2009  
Blogger Des said...

Hi Bob,

Thanks for a great article. I've been programming for 35 years but not much in PHP so it helped a lot.

12:22 AM, July 21, 2010  
Blogger sravya kamatham said...

( '".$a"', '".$b"', '".$c"', '".$d"', '".$e"', '".$f"', '".$g"', '".$h"', '".$i"','".$j"', '".$k"', '".$l"')


please find the error in this code

11:24 PM, May 27, 2013  
Blogger sravya kamatham said...

( '".$a"', '".$b"', '".$c"', '".$d"', '".$e"', '".$f"', '".$g"', '".$h"', '".$i"','".$j"', '".$k"', '".$l"')


please let me know the error in this code

11:24 PM, May 27, 2013  

Post a Comment

Links to this post:

Create a Link

<< Home