MySQLi Prepared Statements Error Reporting

MySQLi prepared statements error reporting

Each method of mysqli can fail. You should test each return value. If one fails, think about whether it makes sense to continue with an object that is not in the state you expect it to be. (Potentially not in a "safe" state, but I think that's not an issue here.)

Since only the error message for the last operation is stored per connection/statement you might lose information about what caused the error if you continue after something went wrong. You might want to use that information to let the script decide whether to try again (only a temporary issue), change something or to bail out completely (and report a bug). And it makes debugging a lot easier.

$stmt = $mysqli->prepare("INSERT INTO testtable VALUES (?,?,?)");
// prepare() can fail because of syntax errors, missing privileges, ....
if ( false===$stmt ) {
  // and since all the following operations need a valid/ready statement object
  // it doesn't make sense to go on
  // you might want to use a more sophisticated mechanism than die()
  // but's it's only an example
  die('prepare() failed: ' . htmlspecialchars($mysqli->error));
}

$rc = $stmt->bind_param('iii', $x, $y, $z);
// bind_param() can fail because the number of parameter doesn't match the placeholders in the statement
// or there's a type conflict(?), or ....
if ( false===$rc ) {
  // again execute() is useless if you can't bind the parameters. Bail out somehow.
  die('bind_param() failed: ' . htmlspecialchars($stmt->error));
}

$rc = $stmt->execute();
// execute() can fail for various reasons. And may it be as stupid as someone tripping over the network cable
// 2006 "server gone away" is always an option
if ( false===$rc ) {
  die('execute() failed: ' . htmlspecialchars($stmt->error));
}

$stmt->close();

Just a few notes six years later...

The mysqli extension is perfectly capable of reporting operations that result in an (mysqli) error code other than 0 via exceptions, see mysqli_driver::$report_mode.

die() is really, really crude and I wouldn't use it even for examples like this one anymore.

So please, only take away the fact that each and every (mysql) operation can fail for a number of reasons; even if the exact same thing went well a thousand times before....

detecting errors in mysqli prepared statement

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.

Prepared statements and bind_param error handling

You can't get an error (nor have you gotten one) out of something that hasn't yet been executed, therefore the second conditional statement won't throw an error. You need to check if the execution was successful and not the against the bind_param() method.

Then the third (conditional statement) won't theoretically thrown an error because of what you have in your query that would theoretically be considered as being a valid (query) statement.

What you need to do is to remove the if(!$statement) statement from the bind, but keep it in the execution part.

You will then receive an error.

Your first conditional statement for if($statement = $con->prepare($sqlQuery)) is valid, so the else for it won't throw an error since it hasn't been executed.

Consult the following reference manuals on PHP.net on how to query/check for errors properly and don't try to reinvent what wasn't intended to throw errors in the first place:

http://php.net/manual/en/mysqli.query.php
http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

In short, error handling is done on the query (and its execution) and not on the binding.

Consult the manual on bind_param():

http://php.net/manual/en/mysqli-stmt.bind-param.php

There is no mention or examples of error handling on that method.

PHP stmt prepare fails but there are no errors

Here is a slightly adapted example script from php.net with error handling:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
   echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT idDataSources FROM DataSources WHERE `description`=(?)"))) {
     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* Prepared statement, stage 2: bind and execute */
$description = "File - 01/10/2015";
if (!$stmt->bind_param('s', $description)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

/* explicit close recommended */
$stmt->close();
?>

Please note that either $mysqli or $stmt can hold the error description.

error reporting for bind_param() in prepared statements

First of all, you cannot catch such a Warning with a condition like this. Simply because it is raised on the line with bind_param hence it happens before the checking condition below.

Hence, probably, your confusion - the condition works all right but it is just too late.

I would suggest to catch all errors with a simple error handler. the problem is not specific to mysqli, generally you want to catch all warnings and other errors and handle them uniformly. You can find an example in my article on PHP error reporting:

set_error_handler("myErrorHandler");
function myErrorHandler($errno, $errstr, $errfile, $errline)
{
    error_log("$errstr in $errfile:$errline");
    header('HTTP/1.1 500 Internal Server Error', TRUE, 500);
    readfile("500.html");
    exit;
}

It will do exactly what you wanted in your condition but without the need to write such a condition every time you are preparing an SQL query.

Mysqli prepared statements error?

Going through your code you didn't really need to query you DB twice, you should read the adminflag in that same select.

SELECT * is never a good idea always select specific fields.

And I also noticed you are using two differnt style, I suggest you to stick to the Object oriented approach.

<?php
if (isset($_POST['submit'], $_POST['username'] , $_POST['password'])){

$username = $_POST['username'];
$password = $_POST['password'];

$mysqli = new mysqli("localhost","root","","phplogin");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT adminflag FROM users WHERE username = ? AND password = ? LIMIT 1";
if ($stmt = $mysqli->prepare($query)) {
    $stmt -> bind_param("ss", $username, $password);
    $stmt->execute();

        $stmt->store_result();
        $numrows = $stmt->num_rows;
        printf("Number of rows: %d.\n", $numrows );

    if($numrows == 1){
        $stmt->bind_result($admin_flag);
        $stmt->fetch();
        session_start();
        if ($admin_flag== 1) {
            $_SESSION['isadmin'] = true;
        }
        $_SESSION['username'] = $username;
        $_SESSION['loggedin'] = true ;
        header("Location: {$pageLoc}");
    }else{
        echo 'user not found';
    }

}
$stmt->close();
$mysqli->close();
}else{
    echo 'required field missing';
}
?>

php - mysqli prepare statement error - procedure style

You could use mysqli_error:

$stmt = mysqli_prepare(ConnectionObject, $query);
if(!$stmt) {
    printf("Cannot prepare query <%s>. Error message: %s\n", 
           $query, mysqli_error(ConnectionObject));
}

mysqli prepared statement returns 0 rows, no errors

Helper functions are great and here I am totally with you. But... you call this dazzling skyscraper of code "a lot quicker and easier" than vanilla SQL for real?

For some reason you are constantly moving your code back and forth from one format into another. For example, you are creating an array array("*") then convert it into object (object) and then... convert it back into array (array) $a_verify->selection. Why?

Or you take a simple SQL query, SELECT * FROM users WHERE code = ? and create a multiple-line construction where SQL keywords are substituted with array keys with an addition of a lot of quotes, parentheses and stuff. And then convert it back into SQL.

SQL is an amazing language, it survived for decades thanks to its simplicity and strictness. There is no reason to dismantle it into pieces to add a structure. SQL already has a structure. And it has many features that just aren't supported by your helper. In the end, with so much effort you get a severely limited SQL version with complicated syntax and entangled implementation. You call it a helper function?

Last but not least. Such functions where you are adding column and table names freely are asking for SQL injection. And your other question displays this appalling practice - you are adding the user input directly in your SQL. Why bother with prepared statements if you have an injection anyway? See this answer on how to deal with such situations.

Let me show you a real helper function. It makes your code short and readable and it supports full features of SQL (ordering the results for example):

function prepared_query($mysqli, $sql, $params, $types = "")
{
    $types = $types ?: str_repeat("s", count($params));
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    return $stmt;
}

If you're interesting in how it works you can read the explanation I wrote here.

Now let's rewrite your helper function based on mine

function verifyRow($mysqli, $sql, $parameters) {
    $result = prepared_query($mysqli, $sql, $parameters)->get_result();
    return (bool)$result->fetch_assoc();
}

Just two lines of code!

And now to verify the actual row:

var_dump(verifyRow($mysqli, "SELECT 1 FROM users WHERE code = ?", ['12345']));

One line instead of a dozen.

Now to the question why it doesn't find anything. There are two possible reasons.

indeed there is some misconfiguration.
a much simpler case: there is no such data in the table you are querying at the moment

To test for the former you must enable PHP error reporting. If your code doesn't work as expected then there must be an error somewhere and all you need is to catch it. Add these two lines to your code

error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
ini_set('log_errors',1); // well to be 100% sure it will be logged

in order to make sure that all PHP and mysql errors will present themselves. Then run the code again. If it won't produce any error then likely your system is configured properly.

Now to test your data. Write a different query that would return all rows from the table

$sql = "SELECT code FROM users WHERE 1 = ?";
$result = prepared_query($mysqli, $sql, [1])->get_result();
var_dump($result->fetch_all(MYSQLI_ASSOC));

and see what you really have in your database. Probably there are no rows or a different code value. A hint: there could be non-printable characters that would prevent the matching. to reveal them use a very handy function rawurlencode(): it will leave all basic characters as is but convert everything else into hex codes making them an easy spot

Error inserting multiple values with mysqli prepared statement

I think it's seeing $data as a single value

Yes, of course. Why would it do otherwise if by any means it is a single value?

i don't know what to do now

Well, the best thing you could do is to ask a question. Not that stub you asked here but a real question explaining what are you trying to do and why. As there is no such question we can only guess that you need to do a multiple insert, but some peculiar way.

To do so, create a single array that holds all the data.

$data = [];
$data[] = $userid;
$data[] = $ortype;
$data[] = $amount;
$data[] = 1;
$data[] = 3;
$data[] = 500;
$count = count($data);

then create a string with placeholders

$values = implode(',', array_fill(0,  $count, '(?, ?, ?)'));

then create a string with types

$types = str_repeat("iii", $count);

and finally create your query and execute it

$stmt = $conn->prepare("INSERT INTO tranx (user, type, amount) VALUES $values");
$stmt->bind_param($types, ...$data);
$stmt->execute();