Multiple Statements

MySQL optionally allows having multiple statements in one statement string.
Sending multiple statements at once reduces client-server
round trips but requires special handling.

Multiple statements or multi queries must be executed
with mysqli_multi_query(). The individual statements
of the statement string are separated by semicolon.
Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that do return result sets and
statements that do not return result sets in one multiple statement.

The API functions mysqli_query() and
mysqli_real_query() do not set a connection flag necessary
for activating multi queries in the server. An extra API call is used for
multiple statements to reduce the likeliness of accidental SQL injection
attacks. An attacker may try to add statements such as
; DROP DATABASE mysql or ; SELECT SLEEP(999).
If the attacker succeeds in adding SQL to the statement string but
mysqli_multi_query is not used, the server will not
execute the second, injected and malicious SQL statement.

User Contributed Notes 1 note

reasons:Multi_query only returns a non false response if a data/result set is returned and only checks for the first query entered. Switching the first SELECT query with the INSERT query will result in a premature exit of the example with the message "Multi query failed: (0)".The example assumes that once the first query doesn't fail that the other queries have succeeded as well. Or rather it just exits without reporting that one of the queries after the first query failed seeing that if a query fails next_result returns false.

The changes in the example comes after the creation of the string $sql.

// get result of the next query to process // don't bother to check for success/failure of the result // since at the start of the loop there is an error check & // report block.$mysqli->next_result()

} while (true); // exit only on error or when there are no more queries to process?>

Note that the normal while ($mysqli->more_results() && $mysqli->next_result() has been replaced by two checks and while (true);This is due to the 'problem' that next_result will return false if the query in question failed.So one either needs to do one last check after the while loop to check if there was an error or one has to split up the different actions.The changes in the example do the splitting.