使用存储过程:mysqli->query(“CALL select_procedure") VS mysqli->query(“SELECT ...") 中的不同结果 [英] using STORED PROCEDURES: different results in mysqli->query("CALL select_procedure") VS mysqli->query("SELECT ...")

问题描述

我正在将我所有的 mysqli 查询迁移到 STORED PROCEDURE.应该像在 mysqli 调用中更改一行一样简单，但是，以下两个代码给出了不同的结果:

I am migrating all my mysqli queries to STORED PROCEDURE. It should be as easy as changing one line in the mysqli call, howver, the two following codes give different results:

正常查询，正常工作:

$query = $this->mysqli->query("SELECT DISTINCT ID FROM user
                                   WHERE 
                                   MATCH (name) AGAINST ('* *$sanitized* *') ");

if ($query) {   
  $nrows = $query -> num_rows;
  if ($nrows > 0) {
      $searchResult = 'We found '. $nrows .' results';
  }
}

调用 PROCEDURE，它返回一个fetch_array() on boolean"错误:

CALL to PROCEDURE, which returns a "fetch_array() on boolean" error:

$query = $this->mysqli->query("CALL myfunction('.$sanitized.')");

其中程序定义为:

DELIMITER $$
CREATE PROCEDURE myfunction (sanitized VARCHAR(124))
BEGIN
    SELECT DISTINCT ID FROM user
                                   WHERE 
                                   MATCH (name) AGAINST ('* *sanitized* *');
END 
$$
DELIMITER ;

我找不到解决方案，而且本论坛中似乎没有人遇到类似问题.

I can't find a solution and it seems that no one has a similar issue in this forum.

推荐答案

考虑 Prepared Statements 与 concat() 因为它们经常是这样.

consider Prepared Statements used with concat() as they often are.

DROP PROCEDURE if exists myStoredProc101;
DELIMITER $$
CREATE PROCEDURE myStoredProc101
(   pSanitized VARCHAR(124)
)
BEGIN
    set @mySql:=concat("SELECT DISTINCT ID FROM user where match(name) against ('* *",pSanitized,"* *')");
    PREPARE stmt1 FROM @mySql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END 
$$
DELIMITER ;

您的存储过程没有机会工作，因为它甚至没有使用您的参数.你所做的是在字符串文字中埋了一些东西.另外， varchar(124) 有点奇怪:p

Your stored proc had no chance of working as it wasn't even using your parameter. What you did was bury something inside of a string literal. Also, varchar(124) is a bit odd :p

人们使用准备好的语句获得的唯一成功是使用用户变量(带有 @)与使用局部变量的失败尝试(来自 DECLARE).因此，这可能会为您节省几个小时的头脑风暴.

About the only success people have with prepared statements is with using a User Variable (with an @) versus failed attempts of using Local Variables (from DECLARE). So, that may save you a few hours of head banging in the future.

来自 PHP 手册页 存储过程:

From the PHP Manual Page Stored Procedures:

处理结果集

存储过程可以返回结果集.从一个返回的结果集无法使用 mysqli_query 正确获取存储过程.这mysqli_query 函数结合了语句执行和获取第一个结果集到缓冲的结果集，如果有的话.然而，有是对用户隐藏的附加存储过程结果集导致 mysqli_query 无法返回用户预期的结果集.

Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli_query. The mysqli_query function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query to fail returning the user expected result sets.

从存储过程返回的结果集使用mysqli_real_query 或 mysqli_multi_query.这两个函数都允许获取语句返回的任意数量的结果集，例如 CALL.无法获取存储过程返回的所有结果集导致一个错误.

Result sets returned from a stored procedure are fetched using mysqli_real_query or mysqli_multi_query. Both functions allow fetching any number of result sets returned by a statement, such as CALL. Failing to fetch all result sets returned by a stored procedure causes an error.

关于从mysqli调用存储过程，请看Pablo的答案托巴.有很多变量看起来并不特别令人愉快，但这似乎就是它所在的地方.剧透警告:使用 mysql 变量，而不是 PHP 变量.

As for calling the stored proc from mysqli, please take a look at the Answer from Pablo Tobar. It does not look especially pleasant with many variables, but that seems to be where it is at. Spoiler Alert: use mysql variables, not PHP variables.

当然，Pablo 没有返回结果集，而是写入存储过程中的 OUT 变量.也许你需要做他对 IN 参数所做的事情，然后调用 multi_query()，然后是 store_result()，然后是 fetch_all()(简而言之，PHP 引用了一个页面).

Granted, Pablo was not returning a resultset, but rather writing to an OUT var in the stored proc. Perhaps you need to do what he did for the IN parameters, and call multi_query(), then a store_result(), then a fetch_all() (in short, the PHP reference a page up).

或者，可以像 Palladium 此处那样进行调用.

Alternatively, a call would be made as done by Palladium here.

无论哪种情况，都必须采取措施以避免将SQL 注入传递给存储过程例程的已知漏洞.

In either case, case must be taken to avoid the known vulnerability of passing SQL Injection over to stored procedure routines.

这篇关于使用存储过程:mysqli->query(“CALL select_procedure") VS mysqli->query(“SELECT ...") 中的不同结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！