PHP RFC: MySQLi Execute Query

• Version: 1
• RFC Started: 2022-04-21
• RFC Updated: 2022-05-11
• Voting Start: 2022-05-11 15:00 UTC / 16:00 BST
• Voting End: 2022-05-25 15:00 UTC / 16:00 BST
• Author: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]
• Status: Accepted
• Target Version: PHP 8.2
• First Published at: https://wiki.php.net/rfc/mysqli_execute_query
• GitHub Repo: https://github.com/craigfrancis/php-mysqli-execute-query-rfc
• Implementation: From Kamil Tekiela (proof of concept)

Make parameterised MySQLi queries easier, with mysqli_execute_query($sql, $params).

This will further reduce the complexity of using parameterised queries - making it easier for developers to move away from mysqli_query(), and the dangerous/risky escaping of user values.

This new function is a simple combination of:

1. mysqli_prepare()
2. mysqli_execute()
3. mysqli_stmt_get_result()

It follows the original Draft RFC MySQLi Execute with Parameters, which proposed a single function to execute a parameterised query; and the Implemented RFC mysqli bind in execute, which addressed the difficulties with bind_param().

Using an example, assume we start with:

$db = new mysqli('localhost', 'user', 'password', 'database');
 
$name = '%a%';
$type1 = 1; // Admin
$type2 = 2; // Editor

Traditionally someone might use escaping, which is very error prone, e.g.

foreach ($db->query('SELECT * FROM user WHERE name LIKE "' . $db->real_escape_string($name) . '" AND type_id IN (' . $db->real_escape_string($type1) . ', ' . $db->real_escape_string($type2) . ')') as $row) { // INSECURE
    print_r($row);
}

To avoid mistakes, parameterised queries should be used (with a literal-string), but can be fairly complex:

$statement = $db->prepare('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)');
$statement->bind_param('sii', $name, $type1, $type2);
$statement->execute();
 
foreach ($statement->get_result() as $row) {
    print_r($row);
}

Since PHP 8.1, we no longer have problems with binding by reference, or needing to specify the variable types via the first argument to bind_param(), e.g.

$statement = $db->prepare('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)');
$statement->execute([$name, $type1, $type2]);
 
foreach ($statement->get_result() as $row) {
    print_r($row);
}

This proposed function will simplify this even further, by allowing developers to write this in a one line foreach:

foreach ($db->execute_query('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)', [$name, $type1, $type2]) as $row) {
    print_r($row);
}

In pseudo-code it's basically:

function mysqli_execute_query(mysqli $mysqli, string $sql, array $params = null)
{
    $driver = new mysqli_driver();
 
    $stmt = $mysqli->prepare($sql);
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $mysqli->error) {
        return false;
    }
 
    $stmt->execute($params);
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {
        return false;
    }
 
    return $stmt->get_result();
}

The name was inspired by Doctrine\DBAL\Connection::executeQuery().

The implementation is effectively calling mysqli_stmt_get_result() last. While it will return false on failure, it will also return false for queries that do not produce a result set (e.g. UPDATE). Historically this has been addressed by using mysqli_errno(), but since 8.1 the Change Default mysqli Error Mode RFC was accepted, and Exceptions are used by default.

Because mysqli_stmt is not returned, it's not possible to use its properties directly:

1. int|string $affected_rows - use $mysqli->affected_rows or mysqli_affected_rows($mysqli)
2. int|string $insert_id - use $mysqli->insert_id or mysqli_insert_id($mysqli)
3. int|string $num_rows - also available on mysqli_result
4. int $param_count
5. int $field_count - also available on mysqli_result
6. int $errno - use mysqli_errno($mysqli), $mysqli->errno
7. string $error - use mysqli_error($mysqli), $mysqli->error
8. array $error_list - use mysqli_error_list($mysqli), $mysqli->error_list
9. string $sqlstate - use mysqli_sqlstate($mysqli), $mysqli->sqlstate
10. int $id

It's also worth noting the error property usage will hopefully reduce, as more developers use mysqli_sql_exception for errors (because the mysqli Error Mode now defaults to MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT).

The implementation discards the mysqli_stmt object immediately, so you cannot re-issue a statement with new parameters. Anyone who would benefit from this (to skip running prepare again), can still use mysqli_prepare().

Cannot change mysqli_query() because its second argument is $resultmode.

Cannot replace the deprecated mysqli_execute() function, which is an alias for mysqli_stmt_execute(), because it would create a backwards compatibility issue.

Because the Remove support for libmysql from mysqli RFC has been accepted, it makes it much easier to implement with mysqlnd.

None

PHP 8.2

None known

1. mysqli, adding a new function.

None known

None

None

None

N/A

N/A

Accept the RFC

mysqli_execute_query
Real name	Yes	No
asgrim (asgrim)
ashnazg (ashnazg)
bmajdak (bmajdak)
cschneid (cschneid)
dharman (dharman)
didou (didou)
galvao (galvao)
gasolwu (gasolwu)
girgias (girgias)
kalle (kalle)
kocsismate (kocsismate)
levim (levim)
marandall (marandall)
nicolasgrekas (nicolasgrekas)
ocramius (ocramius)
petk (petk)
pierrick (pierrick)
ramsey (ramsey)
reywob (reywob)
sergey (sergey)
sirsnyder (sirsnyder)
svpernova09 (svpernova09)
timwolla (timwolla)
yunosh (yunosh)
Final result:	24	0
This poll has been closed.

From Kamil Tekiela (proof of concept)

This implementation copies some details to the mysqli object, but not the affected rows. This means mysqli_affected_rows($mysqli) and $mysqli->affected_rows will currently return -1.

N/A

None