2 prepared statements, 2 stored procedures, 1 mysq

问题:

Problem

How to call two MySQL stored procedures in the same mysqli connection using prepared statements (or another query method equally safe against SQL injections) without getting the following errors:

Warning: Packets out of order. Expected 1 received 61. Packet size=7 in /...
Warning: mysqli::prepare(): MySQL server has gone away in /...

Got the code hooked up online at tutorialspoint

Story

I'm making a PHP backend with a MySQL database. I have two results that I want to get from one query: a list of weekly summaries, and a summary of all the weeks.

┌───────┬────────────┬────────────┬─────
|  Week |    Sales   | Commission | ...
├───────┼────────────┼────────────┼─────
| week1 |  $7,912.12 |    $923.41 | ...
| week2 |  $6,423.48 |    $824.87 | ...
| week3 |  $8,180.67 |    $634.04 | ...
|  ...  |    ...     |    ...     | ...
├───────┼────────────┼────────────┼─────
| total | $67,012.23 |  $7,532.58 | ...
| avg   |  $7,012.54 |    $787.38 | ...
└───────┴────────────┴────────────┴─────

I used to just store the weekly summaries in a database table, and used a stored procedure to get the summary of all the weekly summaries. In my PHP code, I just selected all the rows in the week table and then called the getWeeksSummary stored procedure.

Now I have to be able to filter the data in the weekly summaries. I replaced a simple SELECT ... FROM week with a stored procedure getWeeks() to calculate all the weekly summaries.

Code

$weeksSummary = new stdClass();

if ($stmt = $mysqli->prepare('CALL getWeeks(?,?,?);')) {
    $stmt->bind_param('sss', $a, $b, $c);
    $stmt->execute();
    $stmt->bind_result($week, $sales, $commission, ...);
    $weeksSummary->weeks = [];
    while($stmt->fetch())
    {
        $week = new stdClass();
        $week->week = $week;
        $week->sales = $sales;
        $week->commission = $commission;
        ...
        $weeksSummary->weeks[] = $week;
    }
    $stmt->free_result();
    $stmt->close();
}

if ($stmt = $mysqli->prepare('CALL getWeeksSummary(?,?,?);')) {
    $stmt->bind_param('sss', $a, $b, $c);
    $stmt->execute();
    $stmt->bind_result($avgSales, $totSales, $avgCommission, $totCommission ...);
    $stmt->fetch();
    $weeksSummary->summary = new stdClass();
    $weeksSummary->summary->avgSales = $avgSales;
    $weeksSummary->summary->totSales = $totSales;
    $weeksSummary->summary->avgCommission = $avgCommission;
    $weeksSummary->summary->totCommission = $totCommission;
    ...
    $stmt->free_result();
    $stmt->close();
}

echo json_encode($weeksSummary);

This code worked fine when the first prepared statement was SELECT week, sales, commission, ... FROM week WHERE a=?, b=?, c=?; instead of CALL getWeeks(?,?,?);. Now I get these errors:

Warning: Packets out of order. Expected 1 received 61. Packet size=7 in /...
Warning: mysqli::prepare(): MySQL server has gone away in /...

Attempts

1) Failed: I used a new statement object $stmt2 for the second query. Same errors.

2) Success: I closed the mysqli connection and opened a new one before the second statement. A second mysqli connection with its own prepared statement does run fine, but the code to connect to the database is kept completely separate, so that doesn't really help.

3) Failed: Just out of curiosity, I went back to my original working code and reordered the statements, putting the stored procedure statement before the SELECT statement. Same errors. So the mysqli connection is fine with queries before the stored procedure, but doesn't like anything after the stored procedure.

4) Failed: I tried putting $mysqli->next_result(); after the first statement. Same errors. However, if I use query() instead of prepare() to call the stored procedures, the next_result() does indeed allow both stored procedures to run. I'd like to use prepared statement though, as they help against SQL injections.

Undesirable Potential Solutions

A): I could separate it into two calls to the back-end, but the summaries would be out of sync on the front-end when data refreshes.

B): I could join them into one MySQL stored procedure and then separate them in PHP, but I need them separate as well, so the same code would be there twice.

C): I could stop using prepared statements, but I don't know any other ways to avoid SQL injections.

Help

Any suggestions?

回答1:

Well, I'll try to answer for the question title, assuming that in the first statement not a regular query but one of two aforementioned stored procedures were called.

After calling a stored procedure, you always have to move over additional empty result set returned by every stored procedure:

$mysqli->next_result();

Also, after first prepared function call, add one extra fetch() after getting your data:

$stmt->fetch();
$stmt->free_result();

as you have to "free" the result set waiting on the server side. It could be done in many ways, but simplest would be just calling fetch() one more time, or, more strictly put, you have to call fetch() until it returns FALSE, indicating that there are no more rows left in the resultset. You are doing it [silently] in the other snippets, when calling fetch() in the while loop, but here, fetching only one row, you have to call it explicitly.

There is another way, way more convenient: use get_result() (if available) which will solve all your problems at once. Instead of that long and windy code you have at the moment, only four lines actually needed:

$stmt = $mysqli->prepare('CALL getWeeksSummary(?,?,?)');
$stmt->bind_param('sss', $a, $b, $c);
$stmt->execute();
$weeksSummary = $stmt->get_result()->fetch_object();

get_result() will free that waiting resultset and at the same time allow you to use fetch_object() method, which will let you to get the resulting object in just one line.

回答2:

Reading the mysqli documentation, it says that $stmt->free_result() is to free the memory allocated from $stmt->store_result(). Since the code doesn't use store_result(), removing free_result() solves the errors.

Of course, it also says to use store_result() whenever a query returns a result set. I don't really understand why (something to do with buffering), but since these two prepared statements and stored procedures work without store_result(), the problem is solved.

I'm still curious why it doesn't work with store_result() and free_result(), but at least there is some working code now. Here is the modified code at tutorialspoint.

---

As a side note, instead of using two prepared statements with two stored procedures, a way around it is to use one prepared statement to set variables

$stmt = $mysqli->prepare('SET @a = ?, @b = ?, @c = ?')
...

then use those variables in queries to call the stored procedures

$result = $mysqli->query('CALL getWeeks(@a,@b,@c)')
...
$result = $mysqli->query('CALL getWeeksSummary(@a,@b,@c)')
...