/php-mysqli-execute-query-rfc

PHP RFC: MySQLi Execute Query

Introduction

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.

Proposal

This new function is a simple combination of:

  • mysqli_prepare()
  • mysqli_execute()
  • 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();
}

Notes

Function Name

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

Returning false

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.

Properties

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

  • int|string $affected_rows - use $mysqli->affected_rows or mysqli_affected_rows($mysqli)
  • int|string $insert_id - use $mysqli->insert_id or mysqli_insert_id($mysqli)
  • int|string $num_rows - also available on mysqli_result
  • int $param_count
  • int $field_count - also available on mysqli_result
  • int $errno - use mysqli_errno($mysqli), $mysqli->errno
  • string $error - use mysqli_error($mysqli), $mysqli->error
  • array $error_list - use mysqli_error_list($mysqli), $mysqli->error_list
  • string $sqlstate - use mysqli_sqlstate($mysqli), $mysqli->sqlstate
  • 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).

Re-using Statements

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().

Updating Existing Functions

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.

Why Now

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

Backward Incompatible Changes

None

Proposed PHP Version(s)

PHP 8.2

RFC Impact

To SAPIs

None known

To Existing Extensions

  • mysqli, adding a new function.

To Opcache

None known

New Constants

None

php.ini Defaults

None

Open Issues

None

Unaffected PHP Functionality

N/A

Future Scope

N/A

Voting

Accept the RFC

* Yes * No

Implementation

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.

References

N/A

Rejected Features

None