In PHP's mysqli, how does multi_query() differ from query(), and what must happen before the connection can run another statement?
answer
- query() keeps multi-statements off
- returns after the first statement
- store_result, more_results, next_result
- Commands out of sync
- stacked statements on injection
basics
~10 smysqli::query() runs one statement; multi_query() sends several separated by semicolons and returns after the first. Every result must be read with store_result() and next_result() before the connection accepts another statement.
solid answer
~40 s`query()` runs a single statement with the multi-statement option off, so a second statement after a `;` is a syntax error. `multi_query()` switches it on, sends the whole string and returns once the **first** statement finishes, while the server runs the rest. I then loop: `store_result()`, process and `free()`, then `more_results()` and `next_result()` until nothing is left; until then the connection is busy and other calls fail with "Commands out of sync". A failure in a later statement surfaces at the `next_result()` that reaches it, which throws under the PHP 8.1+ default report mode, and earlier statements have already run. Because it allows appending statements, I never interpolate input into it and use it only for trusted scripts or procedures returning several result sets.
go deeper
Recall that query() runs one statement while multi_query() runs several, and that its results must all be read.
Explain the do/while loop with store_result(), more_results() and next_result(), and the out-of-sync error.
Diagnose late errors and partial application in multi_query() scripts, and remove it from code paths that touch user input.
Decide where multi-statement execution is allowed at all, such as migration tooling only, and enforce it in review.
## One statement versus several `mysqli::query()` sends **one** SQL statement. Internally it makes sure the connection's multi-statement option is off, so a string such as `DELETE FROM rooms WHERE id = 3; DROP TABLE lessons` is rejected by the server as a syntax error instead of running both parts. `mysqli::multi_query(string $query): bool` turns that option **on** and sends the whole string. The server runs the semicolon-separated statements one after another. The manual describes it this way: the call waits for the **first** statement to complete and returns; the server keeps processing the rest and makes their results available for fetching. ## Reading the results Every statement produces a result entry, whether or not it returns rows. The connection is **busy** until all of them have been read, so the standard loop is a `do`/`while`: 1. `store_result()` fetches the current statement's result set (`false` for statements without rows). 2. Process it, then `free()` it. 3. `more_results()` reports whether another result follows. 4. `next_result()` advances to it, waiting for the server if needed. ```php if ($db->multi_query($script)) { do { if ($result = $db->store_result()) { $result->free(); } } while ($db->more_results() && $db->next_result()); } ``` ## What goes wrong if you do not drain it - **"Commands out of sync; you can't run this command now."** Any other statement on the same connection fails until every pending result has been consumed. - **Errors in later statements surface late.** `multi_query()`'s return value reflects the first statement. A failure in the third one is reported by the `next_result()` call that reaches it — with PHP 8.1+'s default report mode, that call throws `mysqli_sql_exception`. - **Partial application.** Statements before the failing one have already run, and each was committed on its own unless the script manages a transaction. ## Why it widens an injection With `query()`, an injected fragment can alter only the single statement it lands in. With `multi_query()`, the same fragment can close the statement with `;` and **append new ones** — an `UPDATE`, a `DROP`, a `GRANT`. The mechanics of injection belong elsewhere; the mysqli-specific point is that `multi_query()` removes the one-statement limit, so: - never interpolate user input into a `multi_query()` string; - prepared statements — `prepare()`, `execute_query()` — accept a single statement only, so they cannot be used to stack statements either. ## Legitimate uses | Use | Why multi_query() fits | |---|---| | Running a trusted SQL script, such as a schema or seed file shipped with the app | the file is a list of statements written by developers | | Calling a stored procedure that returns several result sets | the extra results must be read with `next_result()` | | Admin tooling that executes an operator's script | the operator already has database access | For a legacy timetable app, the typical finding is `multi_query()` used for convenience — two `UPDATE`s concatenated to save a round-trip — with a variable interpolated into one of them. The fix is two prepared statements, ideally inside a transaction. ## Stored procedures and multiple results A `CALL` to a stored procedure can return several result sets, one per `SELECT` it runs, followed by a final status result. Even when it is issued with `query()`, the extra results are still pending on the connection, so they must be drained with `more_results()` and `next_result()` in the same way before the next statement. Code that calls a procedure and immediately runs another query is a classic source of "Commands out of sync" in legacy apps. ## Comparing with the other APIs PDO has no `multi_query()` method. Whether a PDO MySQL connection accepts several statements in one call depends on the driver's multi-statement setting and on emulated prepares; that is PDO's topic, not mysqli's. Within mysqli the rule is simple: `query()` and prepared statements run one statement, and `multi_query()` is the explicit, opt-in exception.
- multi_query() returned true, but the third statement in the script had a typo. When do you find out?When the loop calls `next_result()` to advance to that statement's result. The return value of `multi_query()` covers only the first statement; the error for the third is reported at the step that reaches it, and with the default report mode that `next_result()` call throws. The first two statements have already run by then.
- Does multi_query() make the statements in one call atomic?No. Each statement runs on its own in autocommit mode unless the connection is in a transaction, so an error halfway leaves the earlier statements applied. For atomic multi-step changes use a transaction with separate prepared statements.
saying these in an interview costs you the question
- Believing query() also runs several semicolon-separated statements.
- Assuming multi_query() returning true means every statement succeeded.
- Running a new query before draining multi_query() results.
- Interpolating user input into a multi_query() string.
- Treating the statements in one multi_query() call as a single transaction.