参数化的MySQL存储过程准备语句(Dynamic SQL) [英] MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

问题描述

我正在尝试编写一个MySQL搜索功能，该功能构建一个动态sql值并通过准备好的语句执行它.显然，为了安全起见，我想通过一个参数传递用户输入(搜索词)，但是我不知道如何将一个参数与多个参数匹配?查询中的标记.可能最好地证明我的意思:

I'm trying to write a MySQL search function that builds a dynamic sql value and executes it via a prepared statement. Obviously I want to pass the user input (the search word) via a parameter for security but I can't work out how to match one parameter to multiple ? marks in the query. Probably best to show what I mean:

CREATE DEFINER=`admin`@`localhost` PROCEDURE `WEBSITE_mainSearch`(
IN searchWordIn VARCHAR(128)
)
BEGIN
DECLARE articlesModule BIT;
SET @query = '';
SET @searchWordIn = searchWordIn;
SELECT articlesModuleEnabled INTO articlesModule FROM sys_options WHERE ID = 1;
SET @query = CONCAT(@query, 'SELECT blockName AS itemName, blockPath AS seoName, blockID AS itemID, MATCH(blockName, blockBody) AGAINST (?) AS relevance, \'block\' AS itemType FROM content_blocks WHERE MATCH(blockName, blockBody) AGAINST (?)') ;
IF articlesModule = 1 THEN
    SET @query = CONCAT(@query, 'UNION SELECT articleName AS itemName, seoName, articleID AS itemID, MATCH(articleName, articleBody) AGAINST (?) AS relevance, \'article\' AS itemType FROM news_articles WHERE MATCH(articleName, articleBody) AGAINST (?)') ;
END IF;
PREPARE stmt FROM @query;


EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;


DEALLOCATE PREPARE stmt;
END

由于要根据启用了哪些模块来动态确定?s的数量，我如何知道在此语句中将searchWordIn作为参数发送多少时间使用searchWordIn执行语句； ?

As the number of ?s is going to be dynamically determined based on which modules are enabled, how do I know how many time to send searchWordIn as a parameter in this statement EXECUTE stmt USING searchWordIn;?

谢谢！

推荐答案

必须为EXECUTE语句提供固定的参数列表，因此您必须准备并在以下位置执行该语句IF/THEN/ELSE块.

The EXECUTE statement must be given a fixed list of arguments, so you'll have to prepare and execute the statement in an IF/THEN/ELSE block.

IF articlesModule = 1 THEN
    SET @query = ... UNION ...
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
ELSE
    SET @query = ...; /* no UNION */
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @searchWordIn, @searchWordIn;
END IF;

我不知道在有限的MySQL存储过程语言范围内解决此问题的任何方法.对我来说，这是在存储过程中不使用动态SQL的另一个很好的理由.

I don't know any way to solve this in the limited scope of MySQL stored procedure language. To me, it's another good reason not to use dynamic SQL in stored procedures.

发表您的评论

我不能接受上面的建议-我使用的系统大约有7个模块.

I can't do the suggestion above - the system I am using has about 7 modules.

我知道...您可以使用 CASE语句而不是IF/THEN/ELSE，但是实际上您有2 ⁷ = 128个可能的查询字符串不同情况，因为我假设可以搜索或不搜索这7个模块中的任何一个.

I see... you could use a CASE statement instead of an IF/THEN/ELSE, but you actually have 2⁷ = 128 potential different cases for query strings, because I assume any of those 7 modules could either be searched or not.

一种允许您使用查询参数的替代方法是忘记使用UNION，而是以运行多达7个单独的SELECT查询并将它们全部作为返回的方式编写过程.多个结果集.这就是存储过程要做的事情.但是您必须在PHP层中编写代码才能依次获取每个结果集.也就是说，循环遍历结果集，并在该循环内遍历当前结果集的行.请参见 PDO :: nextRowset()或

An alternative that would allow you to use query parameters is to forget about using UNION, and instead write the procedure in such a way that runs up to 7 separate SELECT queries and returns all of them as multiple result sets. That's something that stored procedures are intended to do. But you have to write code in your PHP layer to fetch each result set in turn. That is, loop over the result sets, and within that loop, loop over the rows of the current result set. See example at PDO::nextRowset() or mysqli::next_result().

我以为我很安全，只需将搜索词连接到动态SQL中即可

I supposed I'm safe simply CONCATenating the search word in to the dynamic SQL

否，这样做是不安全的！在PHP中使用查询参数将字符串传递给CALL WEBSITE_mainSearch(?)对于防止SQL注入毫无用处，如果您将其串联起来将参数值放入过程内的另一个字符串中，并执行动态SQL解析和执行.使用查询参数不会使参数值安全"，它们只是将这些值与SQL解析阶段分开.

No, you're not safe if you do that! Using a query parameter in PHP to pass a string to the CALL WEBSITE_mainSearch(?) is useless for protecting against SQL injection, if you then concatenate that parameter value into another string inside the procedure and do a dynamic SQL parse-and-execute. Using query parameters does not make parameter values "safe," they just separate those values from the SQL parse phase.

如果使用MySQL的内置函数 PDO :: quote().

You're safer if you use MySQL's built-in function QUOTE() when concatenating the strings. QUOTE() does escaping of special characters, just like mysql_real_escape_string(). Except it's slightly different, because it also produces the single-quotes delimiting the string, like PDO::quote() does.

SET @query = CONCAT(@query, 'SELECT blockName AS itemName, blockPath AS seoName, 
  blockID AS itemID, MATCH(blockName, blockBody) AGAINST (',
  QUOTE(searchWordIn), ') AS relevance, \'block\' AS itemType 
  FROM content_blocks WHERE MATCH(blockName, blockBody) AGAINST (',
  QUOTE(searchWordIn),')') ;

---

更新:另一种替代方法:使用UNION添加更多子查询，并保留模块数量.然后使用CASE根据累计的计数使用不同数量的参数执行准备好的查询.

---

Update: one more alternative: use UNION to add more subqueries, and keep count of the modules. Then use a CASE to execute the prepared query with a different number of parameters based on the accumulated count.

SET @n = 0;
IF articlesModule = 1 THEN
    SET @query = ... UNION ...
    SET @n = @n+1;
END IF;

IF newsModule = 1 THEN
    SET @query = ... UNION ...
    SET @n = @n+1;
END IF;

... and similar for the other 5 modules ...

PREPARE stmt FROM @query;

CASE @n
WHEN 1:
    EXECUTE stmt USING @searchWordIn, @searchWordIn;
WHEN 2:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
WHEN 3:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn;
WHEN 4:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
WHEN 5:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn;
WHEN 6:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn;
WHEN 7:
    EXECUTE stmt USING @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn,
      @searchWordIn, @searchWordIn, @searchWordIn, @searchWordIn, 
      @searchWordIn, @searchWordIn;
END;

这篇关于参数化的MySQL存储过程准备语句(Dynamic SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！