2个准备好的语句，2个存储过程，1个mysqli连接 [英] 2 prepared statements, 2 stored procedures, 1 mysqli connection

问题描述

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

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

获得了在教学点

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

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 | ...
└───────┴────────────┴────────────┴─────

我以前只是将每周摘要存储在数据库表中，并使用存储过程来获取所有每周摘要的摘要.在我的PHP代码中，我只选择了week表中的所有行，然后将其称为getWeeksSummary存储过程.

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.

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

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.

$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);

当第一个准备好的语句是SELECT week, sales, commission, ... FROM week WHERE a=?, b=?, c=?;而不是CALL getWeeks(?,?,?);时，此代码可以正常工作.现在我得到了这些错误:

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

尝试

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

Attempts

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

2)成功: :我关闭了mysqli连接，并在第二条语句之前打开了一个新连接.第二个mysqli连接带有其自己的准备好的语句确实可以很好地运行，但是连接到数据库的代码完全分开，因此并没有真正的帮助.

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)失败: 出于好奇，我回到了原来的 working 代码并重新排列了语句的顺序，将存储过程放入了存储过程SELECT语句之前的语句.同样的错误.因此，在存储过程之前的查询中，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.

4)失败: :我尝试将$mysqli->next_result();放在第一条语句之后.同样的错误.但是，如果我使用query()而不是prepare()来调用存储过程，则next_result()确实确实允许两个存储过程都运行.我还是想使用预处理语句，因为它们有助于防止SQL注入.

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.

A): 我可以将其分为两个后端调用，但是当数据刷新时，摘要在前端将不同步.

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): 我可以将它们加入一个MySQL存储过程中，然后在PHP中将它们分开，但是我也需要将它们分开，因此相同的代码将是那里两次.

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): 我可以停止使用准备好的语句，但是我不知道有什么其他方法可以避免SQL注入.

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

有什么建议吗?

推荐答案

好吧，假设第一条语句不是常规查询，而是，我将尝试回答问题标题. >调用了上述两个存储过程之一.

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();

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

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

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

，因为您必须释放"在服务器端等待的结果集.可以用多种方法完成操作，但最简单的方法是再调用一次fetch()，或更严格地说，您必须调用fetch()直到返回FALSE，这表明结果集中没有剩余的行了. .在while循环中调用fetch()时，您正在[其他]片段中[静静地]执行此操作，但是在这里，仅获取一行，则必须显式调用它.

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.

还有另一种方法，更方便:使用get_result()(如果有)，它将立即解决您的所有问题.代替了您当前拥有的冗长而多风的代码，实际上只需要四行:

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()将释放等待的结果集，同时允许您使用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个准备好的语句，2个存储过程，1个mysqli连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！