rfc:mysqli_execute_query

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
rfc:mysqli_execute_query [2022/04/04 16:51] – Feedback from Kamil craigfrancisrfc:mysqli_execute_query [2022/05/25 19:18] – close vote craigfrancis
Line 1: Line 1:
 ====== PHP RFC: MySQLi Execute Query ====== ====== PHP RFC: MySQLi Execute Query ======
  
-  * Version: 0.1 +  * Version: 1 
-  * Voting Start: ? +  * RFC Started: 2022-04-21 
-  * Voting End: ? +  * RFC Updated: 2022-05-11 
-  * RFC Started: 2022-04-04 +  * Voting Start: 2022-05-11 15:00 UTC / 16:00 BST 
-  * RFC Updated: 2022-04-04+  * Voting End: 2022-05-25 15:00 UTC / 16:00 BST
   * Author: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]   * Author: Kamil Tekiela, and Craig Francis [craig#at#craigfrancis.co.uk]
-  * Status: Draft+  * Status: Accepted
   * First Published at: https://wiki.php.net/rfc/mysqli_execute_query   * First Published at: https://wiki.php.net/rfc/mysqli_execute_query
   * GitHub Repo: https://github.com/craigfrancis/php-mysqli-execute-query-rfc   * GitHub Repo: https://github.com/craigfrancis/php-mysqli-execute-query-rfc
-  * Implementation: [[https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query|From Kamil Tekiela]]+  * Implementation: [[https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query|From Kamil Tekiela]] (proof of concept)
  
 ===== Introduction ===== ===== Introduction =====
Line 32: Line 32:
 <code php> <code php>
 $db = new mysqli('localhost', 'user', 'password', 'database'); $db = new mysqli('localhost', 'user', 'password', 'database');
- 
-$sql = 'SELECT * FROM user WHERE name LIKE ? AND type IN (?, ?)'; 
  
 $name = '%a%'; $name = '%a%';
-$type1 = 'admin'; +$type1 = 1; // Admin 
-$type2 = 'editor';+$type2 = 2; // Editor 
 +</code> 
 + 
 +Traditionally someone might use escaping, which is [[https://github.com/craigfrancis/php-is-literal-rfc/blob/main/justification/escaping.php?ts=4|very error prone]], e.g. 
 + 
 +<code 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); 
 +}
 </code> </code>
  
-Before PHP 8.1, a typical parameterised query would look like this:+To avoid mistakes, parameterised queries should be used (with a [[https://eiv.dev/|literal-string]]), but can be fairly complex:
  
 <code php> <code php>
-$statement = $db->prepare($sql); +$statement = $db->prepare('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)'); 
-$statement->bind_param('sss', $name, $type1, $type2);+$statement->bind_param('sii', $name, $type1, $type2);
 $statement->execute(); $statement->execute();
  
Line 55: Line 61:
  
 <code php> <code php>
-$statement = $db->prepare($sql);+$statement = $db->prepare('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)');
 $statement->execute([$name, $type1, $type2]); $statement->execute([$name, $type1, $type2]);
  
Line 63: Line 69:
 </code> </code>
  
-The proposed function will simplify this even further, by allowing developers to write this in a one line foreach:+This proposed function will simplify this even further, by allowing developers to write this in a one line foreach:
  
 <code php> <code php>
-foreach ($db->execute_query($sql, [$name, $type1, $type2]) as $row) {+foreach ($db->execute_query('SELECT * FROM user WHERE name LIKE ? AND type_id IN (?, ?)', [$name, $type1, $type2]) as $row) {
     print_r($row);     print_r($row);
 } }
Line 100: Line 106:
 ==== Returning false ==== ==== Returning false ====
  
-Because the implementation is effectively calling [[https://www.php.net/mysqli_stmt_get_result|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 [[https://wiki.php.net/rfc/mysqli_default_errmode|Change Default mysqli Error Mode RFC]] was accepted, and Exceptions are used by default.+The implementation is effectively calling [[https://www.php.net/mysqli_stmt_get_result|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 [[https://wiki.php.net/rfc/mysqli_default_errmode|Change Default mysqli Error Mode RFC]] was accepted, and Exceptions are used by default. 
 + 
 +==== Properties ==== 
 + 
 +Because [[https://www.php.net/manual/en/class.mysqli-stmt.php|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 ==== ==== Re-using Statements ====
  
-The implementation discards the //mysqli_stmt// object immediately, so you cannot re-issue a statement with new parameters. This is a rarely used feature, and anyone who would benefit from this (to skip running prepare again), can still use //mysqli_prepare()//.+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 ==== ==== Updating Existing Functions ====
  
-Cannot change //mysqli_query()// because it's second argument is //$resultmode//.+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. Cannot replace the deprecated //mysqli_execute()// function, which is an alias for //mysqli_stmt_execute()//, because it would create a backwards compatibility issue.
Line 148: Line 171:
 ===== Open Issues ===== ===== Open Issues =====
  
-==== Affected Rows ==== +None
- +
-Currently //$mysqli->affected_rows// and //mysqli_affected_rows($mysqli)// returns -1. +
- +
-==== Properties ==== +
- +
-Because [[https://www.php.net/manual/en/class.mysqli-stmt.php|mysqli_stmt]] is not returned, it's not possible to use its properties: +
- +
-  - int|string **$affected_rows** +
-  - int|string **$insert_id** - can 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** - can use //mysqli_errno($mysqli)//, //$mysqli->errno// +
-  - string **$error** - can use //mysqli_error($mysqli)//, //$mysqli->error// +
-  - array **$error_list** - can use //mysqli_error_list($mysqli)//, //$mysqli->error_list// +
-  - string **$sqlstate** - can use //mysqli_sqlstate($mysqli)//, //$mysqli->sqlstate// +
-  - int **$id**+
  
 ===== Unaffected PHP Functionality ===== ===== Unaffected PHP Functionality =====
Line 179: Line 185:
 Accept the RFC Accept the RFC
  
-TODO+<doodle title="mysqli_execute_query" auth="craigfrancis" voteType="single" closed="true"> 
 +   * Yes 
 +   * No 
 +</doodle>
  
 ===== Implementation ===== ===== Implementation =====
  
-[[https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query|From Kamil Tekiela]]+[[https://github.com/php/php-src/compare/master...kamil-tekiela:execute_query|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 ===== ===== References =====
Line 191: Line 202:
 ===== Rejected Features ===== ===== Rejected Features =====
  
-TODO+None
  
rfc/mysqli_execute_query.txt · Last modified: 2022/08/04 13:28 by dharman

Page Tools