2个准备好的语句，2个存储过程，1个mysqli连接

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:

如何使用预准备语句(或另一种对SQL注入同样安全的查询方法)在同一mysqli连接中调用两个MySQL存储过程,而不会出现以下错误:

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

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

我正在使用MySQL数据库制作PHP后端。我想从一个查询中得到两个结果:每周摘要列表,以及所有周的摘要。

┌───────┬────────────┬────────────┬─────
|  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.

我曾经只将每周摘要存储在数据库表中,并使用存储过程来获取所有每周摘要的摘要。在我的PHP代码中,我只选择了周表中的所有行,然后调用了getWeeksSummary存储过程。

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.

现在我必须能够过滤每周摘要中的数据。我用存储过程getWeeks()替换了一个简单的SELECT ... FROM周来计算所有的每周摘要。

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:

当第一个准备好的声明是SELECT周,销售,佣金,...... FROM周WHERE a =?,b =?,c = ?;而不是CALL getWeeks(?,?,?);.现在我收到这些错误:

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.

1)失败:我为第二个查询使用了新的语句对象$ stmt2。同样的错误。

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.

2)成功:我关闭了mysqli连接并在第二个语句之前打开了一个新连接。与其自己的预处理语句的第二个mysqli连接运行正常,但连接到数据库的代码保持完全独立,因此这并没有真正帮助。

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.

3)失败:出于好奇,我回到原来的工作代码并重新排序语句,将存储过程语句放在SELECT语句之前。同样的错误。所以mysqli连接在存储过程之前的查询很好,但是在存储过程之后不喜欢任何东西。

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.

4)失败:我试过把$ mysqli-> next_result();在第一次声明之后。同样的错误。但是,如果我使用query()而不是prepare()来调用存储过程,则next_result()确实允许两个存储过程运行。我想使用预备语句,因为它们有助于防止SQL注入。

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.

A):我可以将它分成两个后端调用,但是当数据刷新时,前端的摘要将不同步。

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.

B):我可以将它们加入到一个MySQL存储过程中,然后在PHP中将它们分开,但我也需要将它们分开,所以相同的代码会有两次。

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

C):我可以停止使用预准备语句,但我不知道任何其他方法来避免SQL注入。

Help

Any suggestions?

2 个解决方案

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

此外,在第一次准备函数调用后,在获取数据后添加一个额外的fetch():

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

因为你必须“释放”在服务器端等待的结果集。它可以通过多种方式完成,但最简单的方法是再次调用fetch(),或者更严格地说,你必须调用fetch()直到它返回FALSE,表明结果集中没有剩下的行。当你在while循环中调用fetch()时,你正在静默地在其他片段中这样做,但是在这里,只需要获取一行,你必须明确地调用它。

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:

还有另一种方式,更方便:使用get_result()(如果可用),它将立即解决您的所有问题。而不是你现在拥有的长而大风的代码,实际上只需要四行:

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

get_result()将释放该等待结果集,同时允许您使用fetch_object()方法,这将允许您只在一行中获取结果对象。

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

阅读mysqli文档,它说$ stmt-> free_result()是释放从$ stmt-> store_result()分配的内存。由于代码不使用store_result(),因此删除free_result()可以解决错误。

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.

当然,它还表示每当查询返回结果集时都使用store_result()。我真的不明白为什么(与缓冲有关),但由于这两个预处理语句和存储过程在没有store_result()的情况下工作,问题就解决了。

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.

我仍然很好奇为什么它不能与store_result()和free_result()一起使用,但至少现在有一些工作代码。这是在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)')
...