rfc:mysqli_execute_parameters

PHP RFC: MySQLi Execute with Parameters

Introduction

Make mysqli easier to use with parameterised queries.

The Problem

Using parameterised queries with mysqli is too complicated:

$db = new mysqli('localhost', 'test', 'test', 'test');
 
$sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?';
 
$name = '%a%';
$type = 'admin';
 
$statement = $db->prepare($sql);
$statement->bind_param('ss', $name, $type);
$statement->execute();
 
$result = $statement->get_result();
 
while ($row = $result->fetch_assoc()) {
    print_r($row);
}

Note that variables need to be used in bind_param(), because values cannot be passed by reference.

It's difficult to run a query which changes the parameters (e.g. filter by type might not always be used).

Most queries are single run, so the ability to re-issue the same $statement is rarely needed.

Proposal

Introduce mysqli::execute() (and replace the deprecated mysqli_execute() function) to make parameterised queries much easier, e.g.

$sql = 'SELECT * FROM user WHERE name LIKE ? AND type = ?';
 
$parameters = ['%a%', 'admin'];
 
$result = $db->execute($sql, $parameters);
 
while ($row = $result->fetch_assoc()) {
    print_r($row);
}

We probably cannot change mysqli::query() because it's second argument is $resultmode.

Rough Implementation

Something like this:

<?php
 
class mysqli_rfc extends mysqli {
    function execute($sql, $parameters = []) {
        $statement = mysqli_prepare($this, $sql);
 
        $ref_types = '';
        foreach ($parameters as $key => $value) {
            $ref_types .= (is_int($value) ? 'i' : 's'); // 'd' for double, or 'b' for blob.
            $ref_values[] = &$parameters[$key];
        }
 
        array_unshift($ref_values, $ref_types);
 
        call_user_func_array([$statement, 'bind_param'], $ref_values);
 
        $statement->execute();
 
        return $statement->get_result();
    }
}

Where $sql should be a safe literal string.

Backward Incompatible Changes

None

Proposed PHP Version(s)

Next PHP 8.x

RFC Impact

To SAPIs

Not sure

To Existing Extensions

  1. mysqli

To Opcache

Not sure

Open Issues

  1. Reliably detecting the variable types: integer, double, string, blob.

Alternatives

Not sure

Unaffected PHP Functionality

Not sure

Future Scope

Not sure

Proposed Voting Choices

Yes / No?

Patches and Tests

A volunteer is needed to help with implementation.

Implementation

N/A

Rejected Features

N/A

rfc/mysqli_execute_parameters.txt · Last modified: 2020/12/26 14:24 by dharman