How to fix this dynamic SQL query function in PHP 8?

Question

In my older projects, I used a function to 'shorten' my code a bit when doing queries.

Instead of using the usual approach of

$conn = [...]
$stmt = $conn->prepare(...)
$stmt->bind_param(...)
$stmt->execute();
$stmt->close();
$conn->close();

I got a function to do that fore me, called dynamic_db_reader($mysqli, $param, $qry).

It returns an array (or null) like: $array[index]['column_name'] = value

Or at least, that's what it used to do in previous versions. (Worked in PHP 7.4.16)

Here is the code to my function:

/**
 * Dynamically executes a given sql statement as prepared statement (?-placeholder).
 * Expects correct parameters as an array to replace ?.
 * Returns an array with ($arr[index]['column_name'] = value), or null.
 *
 * @param $ms       mysqli
 * @param $params   array
 * @param $qry      string
 * @return array|null
 */
function dynamic_db_reader($ms, $params, $qry){

    $fields = array();
    $results = array();

    // Replace prefix (DBPREF in: inc/config.php)
    if (strpos($qry, 'prefix_') !== false){
        $qry = str_replace('prefix', DBPREF, $qry);
    }

    // Set charset
    mysqli_set_charset($ms, 'utf8mb4');

    if ($stmt = $ms->prepare($qry)){

        // Dynamically bind parameters from $params
        if (!isset($params) || !empty($params)){
            // Parameters are set
            $types = '';

            foreach($params as $param){
                // Set parameter data type
                if (is_string($param)){
                    $types .= 's';              // Strings
                } else if (is_int($param)){
                    $types .= 'i';              // Integer
                } else if (is_float($param)){
                    $types .= 'd';              // Double/Float
                } else {
                    $types .= 'b';              // Default: Blob and unknown types
                }
            }

            $bind_names[] = $types;
            for ($i = 0; $i < count($params); $i++){
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }

            call_user_func_array(array($stmt, 'bind_param'), $bind_names);
        }

        $stmt->execute();

        $meta = $stmt->result_metadata();

        // Dynamically create an array to bind the results to
        while ($field = $meta->fetch_field()){
            $var = $field->name;
            $$var = null;
            $fields[$var] = &$$var;
        }

        // Bind results
        call_user_func_array(array($stmt, 'bind_result'), $fields); // --> Error :(

        // Fetch results
        $i = 0;
        while ($stmt->fetch()){
            $results[$i] = array();
            foreach($fields as $k => $v){
                $results[$i][$k] = $v;
            }
            $i++;
        }

        // Close statement
        $stmt->close();

        if (sizeof($results) > 0){
            return $results;
        }
    }
    return NULL;
}

The error:

Fatal error:  Uncaught ArgumentCountError: mysqli_stmt::bind_result() does not accept unknown named parameters in [...]\inc\db.php:87
Stack trace:
#0 [...]\root\inc\db.php(87): mysqli_stmt->bind_result(data_key: NULL, data_value: NULL)
#1 [...]\root\inc\func\common.php(76): dynamic_db_reader(Object(mysqli), Array, 'SELECT * FROM v...')
#2 [...]\root\www\index.php(22): getTestArray()
#3 {main}
  thrown in [...]\root\inc\db.php on line 87

How can I fix this code, so that it works in PHP 8 too?

Dharman · Answer 1 · 2021-07-08T21:52:43.593

That is an awfully long method for something that is so simple. PHP 8 has added named arguments. When you unpack an array to be used as arguments, its keys are used as parameter names. mysqli_stmt::bind_result() doesn't accept named arguments like you are passing it.

If we simplify this code then it should look something like this:

/**
 * Dynamically executes a given sql statement as prepared statement (?-placeholder).
 * Expects correct parameters as an array to replace ?.
 * Returns an array with ($arr[index]['column_name'] = value), or null.
 */
function dynamic_db_reader(mysqli $ms, array $params, string $qry): ?array
{
    // Replace prefix (DBPREF in: inc/config.php)
    if (strpos($qry, 'prefix_') !== false) {
        $qry = str_replace('prefix', DBPREF, $qry);
    }

    $stmt = $ms->prepare($qry);

    // Dynamically bind parameters from $params
    if ($params) {
        $stmt->bind_param(str_repeat('s', count($params)), ...$params);
    }

    $stmt->execute();

    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC) ?: null;
}


mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8mb4');

$results = dynamic_db_reader($mysqli, ['foo'], 'SELECT ?');

If for some reason you are using mysqli compiled from libmysql client, then ... well, it's time to figure out how to enable mysqlnd or switch over to PDO.

P.S. Please make sure that you have mysqli error reporting enabled. How to get the error message in MySQLi?. Also, there is no point in setting the charset each time. Set it right after making the connection.

JSG · Answer 2 · 2021-09-02T13:54:38.040

What I see happening is that call_user_func_array is being passed $fields as ['array_key_txt'=>'array_value'] causing the error.

Try to wrap your $fields array in "array_values"

call_user_func_array(array($stmt, 'bind_result'), array_values($fields));

UPDATED: The site here explains about the new "Named Parameters" in PHP8

It states that:

str_replace(time_limit: "mi");

will produce the error:

Fatal error: Uncaught Error: Unknown named parameter $time_limit in...

https:php.watch say's:

all call_user_func_array() function calls must be aware that PHP 8.0 will interpret associative arrays and numeric arrays different.

As a precautionary measure, call_user_func_array() calls can use array_values if the parameters array might contain non-numeric keys.

$params = [
      'replace' => 'Iron Man',
      'search' => 'Inevitable',
      'subject' => 'I am Inevitable', ];  

echo call_user_func_array('str_replace', array_values($params));

With array_values call, PHP will always use the positional calling pattern, ensuring the outcome will be the same in PHP 8.0 and later.

How to fix this dynamic SQL query function in PHP 8?

2 Answers2

Linked

Related