mysqli prepared statement returns 0 rows, no errors

Question

I created a series of helper functions in php that I have been using because they make prepared mysqli statements a lot quicker and easier to follow for me. They have worked on other projects but for some reason the problem on this particular project is eluding me... It works perfectly offline but when I upload it to my server I get 0 rows returned. I am assuming it must be a setting somewhere but I can't for the life of me figure out what it is. No errors in the Apache log appear when I run this.

In this particular case I am trying to verify that a series of values exists in the database. If they do, return true. If they don't, return false.

Here is the helper function:

function verifyRow($a_verify) {
    //Pass $mysqli connection variable
    global $mysqli;

    //Create a string from the selection criteria
    $selection = implode(", ", (array) $a_verify->selection);

    //Transfer table contents to table variable just for consistency
    $table = $a_verify->table;

    //Create a string from the parameter types
    $type = implode("", (array) $a_verify->type);

    //Push a reference to the string of types to the parameter array
    $a_params[] =& $type;

    //For each result parameter, push a column = result string into the colvals array and create a reference to the parameter in the parameters array
    foreach ($a_verify->columns as $key => $value) {
        $a_colvals[] = "{$value} = ?";
        $a_params[] =& $a_verify->results->$key;
    }

    //Create a string from the column = result array
    $colvals = implode(" AND ", $a_colvals);

    //Generate a query
    $query = "SELECT {$selection} FROM {$table} WHERE {$colvals} LIMIT 1";

    //Prepare statement and error checking
    if (!($stmt = $mysqli->prepare($query))) {
        print "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
    }

    //Bind parameters and error checking
    if (!(call_user_func_array(array($stmt, 'bind_param'), $a_params))) {
        print "Binding parameters failed: (" . $mysqli->errno . ") " . $mysqli->error;
    }

    //Execute statement and error checking
    if (!$stmt->execute()) {
        print "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }

    //Get the result
    $result = $stmt->get_result();

    //Bind the result to a variable
    $row = $result->fetch_assoc();

    //If the row exists, return true
    if ($row) {
        return true;
    }
    //If the row doesn't exist, return false
    else {
        return false;
    }
}

Here is how I am trying to retrieve the result:

// Confirm there is a user in the db with this user code
$a_verify = (object) array(
    "selection" => (object) array(
        "*"
    ),
    "table" => "`users`",
    "columns" => (object) array(
        "`code`"
    ),
    "results" => (object) array(
        "'123456'"
    ),
    "type" => (object) array(
        "s"
    )
);
print verifyRow($a_verify); //Ideally 'true'

Here is how the "users" database is set up:

|-----|---------------|--------|
| id  |     email     |  code  |
--------------------------------
| 0   | test@test.com | 123456 |
--------------------------------

Any idea what the problem might be? It's driving me crazy.

Your Common Sense · Accepted Answer · 2019-10-05T06:41:05.950

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

This is great and a lot simpler! Thanks! (The reason my code wasn't working was because I needed to update php) — tomc, Oct 05 '19 at 06:35
I just checked your other question for the context and added an important paragraph on SQL injections. — Your Common Sense, Oct 05 '19 at 06:41

mysqli prepared statement returns 0 rows, no errors

1 Answers1