Dealing with errors in PDO queries

PHP
1

Hi,
I am starting to learn how to use PDO rather than mysqli for PHP queries and have been reading this oft advised page - https://phpdelusions.net/pdo#emulation
However, I am struggling re dealing with errors. I don’t understand what (s)he means by

Reporting PDO errors

TL;DR:
Despite what all other tutorials say, you don’t need a try…catch operator to report PDO errors. Catch an exception only if you have a handling scenario other than just reporting it. Otherwise just let it bubble up to a site-wide handler (note that you don’t have to write one, there is a basic built-in handler in PHP, which is quite good).

I have a query:

$stmt = $pdo->query("SELECT * FROM commodities ORDER BY commodityName");

but don’t understand how to implement an error message if the query fails, without using a try… …catch routine. And I only just understand how the try… …catch business works.

Thank you.

2

I get the impression that the built in error reporting will log the errors and render to the screen if and only if error reporting is set.

Try the following:

ini_set(‘display_errors’, “true”); // show on the screen
error_reporting(0); // prevent all errors from being logged or showing on the screen

$stmt = $pdo->query("SELECT * FROM commodities ORDER BY INVALID_NAME");

// execute query and errors should be **NOT** logged or shown

error_reporting(-1); // activate maximum errors

// execute query and errors and or warnings should be logged and shown
1 Like
3

When learning, developing, and debugging code/query(ies), you, the programmer/developer, want to see all php errors and the raw database statement errors so that you get immediate feedback as to any problems.

When you put your application onto a live/public server, you want to log all this error information, so that if a legitimate visitor manages to do something that you didn’t account for or a hacker/bot attempts or succeeds in breaking in, you will have a log of what occurred so that you can find and fix what’s wrong.

Most database statement errors are fatal problems, and are non-recoverable, e.g. database server is not running or mistakes in the sql query statement, that the visitor/hacker to your site doesn’t need to know anything at all about, because they cannot do anything to correct what’s causing them. In these cases, all the visitor/hacker should see is a http 500 error page, i.e. the current web page is not working, and no, cute messages like try again/later, being output by your code tells a hacker that they managed to trigger something at the code level, not just that the web page isn’t working.

The rest of the database statement errors, such as inserting/updating duplicate or out of range values, are recoverable, since the visitor can potentially alter the value(s) and submit the data again.

If you use exceptions for database statement errors, do as suggested in the linked to article, and NOT catch them in your code, php will catch them and use its error related settings to control what happens with the actual error information, via an uncaught exception error (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) This will address the fatal, non-recoverable errors. If you are logging all php errors, which will now include the uncaught database statement errors, the response to the visitor will automatically be a http 500 page.

For recoverable database statement errors, you would have try/catch logic for the insert/update query, where you would test the error number in the catch code to see if the error that occurred is for something your code is designed to handle. If it is, you would setup a message for the user telling them exactly what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it.

Short-version: only catch recoverable database statement errors in your code and let php catch all other database statement errors, where they will get displayed/logged the same as php errors do using the current php error settings.

For a SELECT query, it won’t fail on a live/public server due to anything that the visitor to your web site can do anything about and doesn’t need to know anything about a database error occurring at all.

2 Likes
4

With a Try…Catch you can bubble up the errors from specific PDO errors to errors by “throwing” up to the next level.

An example of what I’m talking about:

public function create():bool
{
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Setup the query using prepared states with static:$params being
         * the columns and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . static::$table . '(' . implode(", ", array_keys(static::$params)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys(static::$params)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = Database::pdo()->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach (static::$params as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:
    } catch (PDOException $e) {

        /*
         * echo "unique index" . $e->errorInfo[1] . "<br>";
         *
         * An error has occurred if the error number is for something that
         * this code is designed to handle, i.e. a duplicate index, handle it
         * by telling the user what was wrong with the data they submitted
         * failure due to a specific error number that can be recovered
         * from by the visitor submitting a different value
         *
         * return false;
         *
         * else the error is for something else, either due to a
         * programming mistake or not validating input data properly,
         * that the visitor cannot do anything about or needs to know about
         *
         * throw $e;
         *
         * re-throw the exception and let the next higher exception
         * handler, php in this case, catch and handle it
         */

        if ($e->errorInfo[1] === 1062) {
            return false;
        }

        throw $e;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
    }

    return true;
1 Like
5

I now understand. I needed that full explanation. Thank you for that.

6

OK, something goes wrong and you send a http 500 page and the customer waits for you to examine the logs and get back to them. How often do you examine the logs?
OR you use exceptions which when something goes wrong emails you a report with stack traces and error codes which, when received, immediately email the customer informing them that you have a report on their problem and are actively engaged in solving it and will notify them when the problem is solved or if further information from them is needed.
Which would you prefer?

1 Like
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.