detecting errors in mysqli prepared statement

Question

I have a custom error handler in place but I need to know where to test for errors within my prepared statement.

Do I test at all prepared() , bind(), execute() and store_result() stages or just selected stages?

$statement = $databaseHandler->mysqli->prepare($query) or trigger_error($mysqli->error);
$statement->bind_param('s', $userIp) or trigger_error($mysqli->error);
$statement->execute() or trigger_error($mysqli->error);
$statement->store_result() or trigger_error($mysqli->error);

Do i even have to check for errors when looking for num_rows or would that just be php failing to do a basic job as its no longer communicating with the mysqli server so nothing should go wrong?

What's the point you should stop looking for errors, is it after execute()? as store_result() not working would be php failing to do its job, which would be the server not actually functioning?

Also I have seen code where the prepare() stage is just surrounded in an if statement for errors, by doing this does this mean an error in the other stages would not be handled? Is the prepared stage most likely to go wrong the most?

Answer 1

AFAIK, you need to convert mysqli errors into PHP errors only for prepare and execute - commands interacting with server. All other commands will emit regular PHP errors already.

There is another method though, but it is quite new and I haven't tested it much. But it is quite tempting, as it will let you get rid of all these trigger errors:

mysqli_report(MYSQLI_REPORT_ERROR);

this simple call will make mysqli emit PHP errors automatically.

Although it MYSQLI_REPORT_STRICT seems better choice, but it doesn't work for my version yet. While MYSQLI_REPORT_ALL would translate mysqli hints as well, which is, on one hand, quite good, but it can spam you with notices like 'No index used in query/prepared statement', which is bad. Thus, the only usable setting at the moment is MYSQLI_REPORT_ERROR

So, you can make it just

mysqli_report(MYSQLI_REPORT_ERROR);

$statement = $db->prepare($query);
$statement->bind_param('s', $userIp);
$statement->execute();
$statement->store_result();

Update

Finally, I've got the proper usage of MYSQLI_REPORT_STRICT:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Will make mysqli throw exceptions instead of regular errors.
Exceptions are better than regular errors in many ways: they always contains a stack trace, they can be caught using try..catch or handled using dedicated error handler. And even unhandled, they act as regular PHP errors providing all the important information, following site-wide error reporting settings.