如何使用mysqli_multi_query识别导致错误的查询？ [英] How to identify the query that caused the error using mysqli_multi_query?

问题描述

使用SO上其他地方的一个例子来更好地捕捉隐藏错误。虽然下面的代码将捕获并返回一个错误，是否可以改进这一点以报告发生错误的查询？

使用下面的代码，输出是：

 列： 18 
错误：您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册，使用正确的语法在第1行FRO库存附近使用
  

正在测试的代码：

  $ query =SELECT * FROM orders WHERE location ='IN'ORDER BY orderNum DESC LIMIT 20;; 
 $ query。=SELECT * FRO库存; //有错误
 $ ord = array（）; 
 $ invent = array（）; 
 
 if（mysqli_multi_query（$ link，$ query））{
 do {
 // fetch results 
 if（$ result = mysqli_store_result（$ link））{ 
 echo'Columns：'。 mysqli_field_count（$ link）。 <峰; br> 中; 
 while（$ row = mysqli_fetch_assoc（$ result））{
 if（count（$ row）> 17）
 $ orders [] = $ row; 
 elseif（count（$ row）== 6）
 $ inv [] = $ row; 
} 
} 
 if（！mysqli_more_results（$ link））
 break; 
 if（！mysqli_next_result（$ link））{
 // report error 
 echo'Error：'。 mysqli_error（$链接）; 
 break; 
} 
} while（true）; 
 mysqli_free_result（$ result）; 
} 
  

解决方案

  $ q [订单] =SELECT * FROM orders WHERE location ='IN'ORDER BY orderNum DESC LIMIT 20; 
 $ q [库存] =SELECT * FRO库存; 
 
 if（！$ link = mysqli_connect（host，user，pass，db））{
 echo无法连接到MySQL：，mysqli_connect_error ）; 
} elseif（mysqli_multi_query（$ link，implode（';'，$ q）））{
 do {
 $ q_key = key（$ q）; //当前查询的键名（Orders或Inventory）
 if（$ result = mysqli_store_result（$ link））{//如果结果集... SELECTs do 
 while（$ row = mysqli_fetch_assoc结果））{//如果一行或多行，迭代所有
 $ rows [$ q_key] [] = $ row; 
} 
 mysqli_free_result（$ result）; 
 echo< div>< pre>; //< pre>是更容易的数组读取
 var_export（$ rows [$ q_key]）; 
 echo< / pre>< / div>; 
} 
} while（next（$ q）&&mysqli_more_results（$ link）&&mysqli_next_result（$ link））; 
} 
 if（$ mysqli_error = mysqli_error（$ link））{// check&在同一步骤中声明变量以避免重复的func调用
 echo< div style = \color：red; \> Query Key =，key（$ q），，Query = current（$ q），，Syntax Error = $ mysqli_error< / div>; 
} 
  

第一个查询错误：
如果您的第一个查询尝试访问指定数据库中不存在的表，如下所示： ordersXYZ
数组 $ rows 将不存在，不会出现 var_export（），您将看到此响应：

Query Key = Orders，Query = SELECT * FROM ordersXYZ WHERE location ='IN'ORDER BY orderNum DESC LIMIT 20，Syntax Error = Table'[someDB] .ordersXYZ'不存在

第二个查询错误：
如果您的第一个查询成功，但第二个查询尝试访问不存在的表如： inventory2

$ rows [Orders] 将保存所需的行数据将为 var_export（）'ed， $ row [Inventory] 将不存在，您将看到此回复：

查询键=库存，查询= SELECT * FROM inventory2，Syntax Error = Table'[someDB] .inventory2'不存在

没有错误：
如果这两个查询都是无错误的，您的 $ rows 数组将填充所需的数据， var_export（） ed，并且不会有错误响应。将查询的数据保存在 $ rows 中，您可以从 $ rows [Orders] 访问所需内容， $ rows [库存] 。

---

注意事项：

1. 您可能会注意到我同时进行变量声明和条件检查，这使得代码更加干燥。




2. 由于我的方法在 implode（） > elseif 行，确保不要在查询中添加一个尾随分号。




3. 这组查询总是返回一个结果集，因为所有的都是SELECT查询，如果您有一个混合的查询集合 affect_rows ，您可能会在此链接找到一些有用的信息（ https://stackoverflow.com/a/22469722/2943403 ）。




4. mysqli_multi_query（）将在出现错误时立即停止运行查询。如果您希望捕获所有错误，您将发现不会有多个错误。




5. 编写有条件的断点，如OP的问题和解决方案是不可取的。虽然在其他情况下可以正确使用自定义断点，但在这种情况下，断点应位于中的 while（）语句之内（）块




6. 返回零行的查询不会导致错误消息 - 它不会创建任何 $ rows 中的子数组，因为不会输入 while（）循环。




7. 通过使用 key（）函数，OP的 if / elseif 条件计算每个结果集行中的列可以避免。这是更好的做法，因为在某些情况下，在每次迭代中运行条件都可能变得昂贵。请注意，数组指针在每个 do（）迭代结尾的 $ q 之前进行。这是一个额外的技术，你不会在php手册页面找到;它允许 key（）按照预期工作。




8. 当然，< div>< pre> var_export（）...< / pre>< / div> 行可以从您的工作代码中删除 - 它纯粹用于演示。




9. 如果要在该代码块重用变量后再运行任何查询，请确保清除所有使用的变量，以使残差数据不会干扰。例如 $ mysqli_error = NULL; // clear errors & 复位（$ Q）; //重置数组指针。




10. 请注意这个含糊不清的警告，由您自行决定： http://php.net/manual/en/mysqli.use-result.php ：

    
    

    如果执行
    客户端上的大量处理，则不应使用mysqli_use_result（），因为这将绑定服务器和
    阻止其他线程更新从中获取数据
    的任何表。

    
    




11. 最后和最重要的出于安全考虑，请勿公开显示查询或查询错误信息 - 您不希望邪恶的人看到这种反馈。同样重要的是，始终保护您的查询免受注入黑客攻击。如果您的查询包含用户提供的数据，则需要在 mysqli_multi_query（）中使用之前对数据进行过滤/清理。事实上，当处理用户输入时，我非常强烈的建议是离开 mysqli_multi_query（）并使用准备语句或 pdo 为您的数据库交互提供更高级别的安全性。

Using a example from elsewhere on SO to better catch 'hiding' errors. While the code below will catch and return an error, is it possible to improve this to report for which query the error occurred?

With the code below, the output is:

Columns: 18
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRO inventory' at line 1

Code being tested:

$query = "SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20;";
$query .= "SELECT * FRO inventory";             //  With error
$ord = array();
$invent = array();

if(mysqli_multi_query($link, $query)) {
    do {
        // fetch results
        if($result = mysqli_store_result($link)) {
           echo 'Columns: ' . mysqli_field_count($link) . "<br>"; 
           while($row = mysqli_fetch_assoc($result)) {
                if(count($row) > 17)
                    $orders[] = $row;
                elseif(count($row) == 6)
                    $inv[] = $row;
            }
        }
        if(!mysqli_more_results($link))
            break;
        if(!mysqli_next_result($link)) {
            // report error
            echo 'Error: ' . mysqli_error($link);
            break;
        }
    } while(true);
    mysqli_free_result($result);
}

解决方案

Here is an approach that will not only improve the quality of your error messages, it will improve the way you handle your result sets.

$q["Orders"]="SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20";
$q["Inventory"]="SELECT * FRO inventory";

if(!$link=mysqli_connect("host","user","pass","db")){
    echo "Failed to connect to MySQL: ",mysqli_connect_error();
}elseif(mysqli_multi_query($link,implode(';',$q))){
    do{
        $q_key=key($q);  // current query's key name (Orders or Inventory)
        if($result=mysqli_store_result($link)){   // if a result set... SELECTs do
            while($row=mysqli_fetch_assoc($result)){  // if one or more rows, iterate all
                $rows[$q_key][]=$row;
            }
            mysqli_free_result($result);
            echo "<div><pre>";  // <pre> is for easier array reading
                var_export($rows[$q_key]);
            echo "</pre></div>";
        }
    } while(next($q) && mysqli_more_results($link) && mysqli_next_result($link));
}
if($mysqli_error=mysqli_error($link)){  // check & declare variable in same step to avoid duplicate func call
    echo "<div style=\"color:red;\">Query Key = ",key($q),", Query = ",current($q),", Syntax Error = $mysqli_error</div>";
}

Error on first query: If your first query tries to access a table that doesn't exist in the nominated database like: ordersXYZ Array $rows will not exist, no var_export() will occur, and you will see this response:

Query Key = Orders, Query = SELECT * FROM ordersXYZ WHERE location='IN' ORDER BY orderNum DESC LIMIT 20, Syntax Error = Table '[someDB].ordersXYZ' doesn't exist

Error on second query: If your first query is successful, but your second query tries to access a non-existent table like: inventory2
$rows["Orders"] will hold the desired row data and will be var_export()'ed, $row["Inventory"] will not exist, and you will see this response:

Query Key = Inventory, Query = SELECT * FROM inventory2, Syntax Error = Table '[someDB].inventory2' doesn't exist

No errors: If both queries are error free, your $rows array will be filled with the desired data and var_export()'ed, and there will be no error response. With the queried data saved in $rows, you can access what you want from $rows["Orders"] and $rows["Inventory"].

---

Things to note:

1. You may notice that I am making variable declarations and conditional checks at the same time, this makes the code more DRY.

2. As my approach uses implode() with a semi-colon on the elseif line, be sure not to add a trailing semi-colon to your queries.

3. This set of queries always returns a result set because all are SELECT queries, if you have a mixed collection of queries that affect_rows, you may find some useful information at this link(https://stackoverflow.com/a/22469722/2943403).

4. mysqli_multi_query() will stop running queries as soon as there is an error. If you are expecting to catch "all" errors, you will discover that there never be more than one.

5. Writing conditional break points like in the OP's question and solution is not advisable. While custom break points may be rightly used in other circumstances, for this case the break points should be positioned inside of the while() statement of the do() block.

6. A query that returns zero rows will not cause a error message -- it just won't create any subarrays in $rows because the while() loop will not be entered.

7. By using the key() function, the OP's if/elseif condition that counts the columns in each resultset row can be avoided. This is better practice because running a condition on every iteration can become expensive in some cases. Notice that the array pointer is advanced inside of $q at the end of each do() iteration. This is an additional technique that you will not find on the php manual page; it allows key() to work as intended.

8. And, of course, the <div><pre>var_export()...</pre></div> lines can be removed from your working code -- it was purely for demonstration.

9. If you are going to run any more queries after this code block that reuse variables, be sure to clear all used variables so that residual data does not interfere. e.g. $mysqli_error=null; // clear errors & reset($q); // reset array pointer.

10. Take heed to this somewhat vague warning at your own discretion: http://php.net/manual/en/mysqli.use-result.php :

    One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

11. Lastly and MOST IMPORTANTLY for security reasons, do not display query or query error information publicly -- you don't want sinister people to see this kind of feedback. Equally important, always protect your queries from injection hacks. If your queries include user-provided data, you need to filter/sanitize the data to death before using it in mysqli_multi_query(). In fact when dealing with user input, my very strong recommendation is to move away from mysqli_multi_query() and use either prepared statements or pdo for your database interactions for a higher level of security.

这篇关于如何使用mysqli_multi_query识别导致错误的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！