detecting errors in mysqli prepared statement

Question 1

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?

Question 2

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.

Question 3

$stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?");
/* BK: always check whether the prepare() succeeded */
if ($stmt === false) {
  trigger_error($this->mysqli->error, E_USER_ERROR);
  return;
}
$id = 1;
/* Bind our params */
/* BK: variables must be bound in the same order as the params in your SQL.
 * Some people prefer PDO because it supports named parameter. */
$stmt->bind_param('si', $content, $id);

/* Set our params */
/* BK: No need to use escaping when using parameters, in fact, you must not, 
 * because you'll get literal '' characters in your content. */
$content = $_POST['content'] ?: '';

/* Execute the prepared Statement */
$status = $stmt->execute();
/* BK: always check whether the execute() succeeded */
if ($status === false) {
  trigger_error($stmt->error, E_USER_ERROR);
}
printf("%d Row inserted.n", $stmt->affected_rows);

Re your questions:

I get a message from my script saying 0 Rows Inserted

This is because you reversed the order of parameters when you bound them. So you're searching the id column for the numeric value of your $content, which is probably interpreted as 0. So the UPDATE's WHERE clause matches zero rows.

do I need to declare all the fields or is it ok to just update one field??

It's okay to set just one column in an UPDATE statement. Other columns will not be changed.

Question 4

Using VolkerK's suggestion of mysqli_statement->result_metadata() I was able to fashion together the following code that accomplishes what I'm looking for, though the performance isn't any faster than using a standard query. I get the statement->result_metadata() to build an associative array to call bind_result on. I build up a bind_result statement as a string and eval it. I know this isn't particularly safe but it is my first pass.

public function executePreparedStatement()
{
    if($this->statement->execute())
    {
        $this->record = array();
        $md = $this->statement->result_metadata();
        $fields = $md->fetch_fields();

        $bindResult = '$this->statement->bind_result(';
        foreach($fields as $field)
        {
            $bindResult .= "$this->record['" . $field->name . "'],";
        }

        $bindResult = substr($bindResult,0,strlen($bindResult) - 1) . ');';

        eval($bindResult);
        return true;
    }
    else
    {
        $this->error = $this->db->error;
        return false;
    }
}

    ...
    $this->prepareStatement($query);
    $this->bindParameter('i',$runId);
    if($this->executePreparedStatement())
    {
        $report = new Report();
        while($this->statement->fetch())
        {
            $row = $this->record;
            $line = array();
            foreach($row as $key => &$value)
            {
                array_push($line,$value);
            }
            $report->addLine($line);
        }
        return $report          
    }

Question 5

Take out the single quotes around the ?. Your code should read:

$prep->prepare("SELECT * FROM details WHERE id REGEXP ?");
$prep->bind_param("s", $search_query);

As it stands now, you are passing in one param, but the ? in single quotes is treated as a string, not a parameter marker.

Question 6

you can handle the mysql error in this way

 if ( ! $this->db->query('SELECT `name`,`age` FROM `example`'))
{
        $error = $this->db->error(); // Has keys 'code' and 'message'
}

detecting errors in mysqli prepared statement

Answers

php,mysqli,error-handling,prepared-statement