# RFC: MySQLi Execute Query

- Status: Implemented (PHP 8.2)
- Authors: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]
- Version: 1
- Target: PHP 8.2
- Implementation: From Kamil Tekiela (proof of concept)
- Original: https://wiki.php.net/rfc/mysqli_execute_query
- Page: https://externals.io/rfc/mysqli_execute_query
- More for agents: search with https://externals.io/search.md?q={query} · RFCs at https://externals.io/rfc.md · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

## Votes

### mysqli_execute_query (closed)

Yes: 24 · No: 0

Voters: asgrim (Yes), ashnazg (Yes), bmajdak (Yes), cschneid (Yes), dharman (Yes), didou (Yes), galvao (Yes), gasolwu (Yes), girgias (Yes), kalle (Yes), kocsismate (Yes), levim (Yes), marandall (Yes), nicolasgrekas (Yes), ocramius (Yes), petk (Yes), pierrick (Yes), ramsey (Yes), reywob (Yes), sergey (Yes), sirsnyder (Yes), svpernova09 (Yes), timwolla (Yes), yunosh (Yes)

## Discussions on the mailing list

- [MySQLi Execute Query RFC](https://externals.io/message/117486.md) — started on 2022-04-06, 6 messages
- [\[Vote\] MySQLi Execute Query](https://externals.io/message/117717.md) — started on 2022-05-11, 3 messages

---

## 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:

1. *mysqli\_prepare()*
2. *mysqli\_execute()*
3. *mysqli\_stmt\_get\_result()*

It follows the original Draft RFC [MySQLi Execute with Parameters](https://wiki.php.net/rfc/mysqli_execute_parameters), which proposed a single function to execute a parameterised query; and the Implemented RFC [mysqli bind in execute](https://externals.io/rfc/mysqli_bind_in_execute), which addressed the difficulties with *bind\_param()*.

Using an example, assume we start with:

```php
$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](https://github.com/craigfrancis/php-is-literal-rfc/blob/main/justification/escaping.php?ts=4), e.g.

```php
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](https://eiv.dev/)), but can be fairly complex:

```php
$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.

```php
$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:

```php
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:

```php
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()](https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#executequery).

### Returning false

The implementation is effectively calling [mysqli\_stmt\_get\_result()](https://www.php.net/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](https://externals.io/rfc/mysqli_default_errmode) was accepted, and Exceptions are used by default.

### Properties

Because [mysqli\_stmt](https://www.php.net/manual/en/class.mysqli-stmt.php) 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*).

### 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](https://externals.io/rfc/mysqli_support_for_libmysql) 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

1. 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

**Vote: mysqli\_execute\_query** — Yes: 24 · No: 0 (closed)

## Implementation

[From Kamil Tekiela](https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query) (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