mysql sql语句参数化_MySQL存储过程准备语句(动态SQL)参数化

weixin_39990029

于 2021-02-26 23:32:16 发布

阅读量353

点赞数

文章标签： mysql sql语句参数化

本文链接：https://blog.csdn.net/weixin_39990029/article/details/114762917

版权

我正在尝试编写一个MySQL搜索函数来构建动态sql值并通过预准备语句执行它.显然我想通过参数传递用户输入(搜索词)以确保安全性,但我无法弄清楚如何将一个参数匹配到多个？查询中的标记.可能最能表明我的意思：

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作为参数发送多少时间EXECUTE stmt USING searchWordIn;？

谢谢！

解决方法:

必须为EXECUTE语句提供一个固定的参数列表,因此您必须在IF / THEN / ELSE块中准备并执行该语句.

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 can’t do the suggestion above – the system I am using has about 7 modules.

我看到……你可以使用CASE statement而不是IF / THEN / ELSE,但实际上你有27 = 128个不同的查询字符串案例,因为我认为这7个模块中的任何一个都可以被搜索到.

允许您使用查询参数的替代方法是忘记使用UNION,而是以最多运行7个单独的SELECT查询并将所有这些查询作为多个结果集返回的方式编写过程.这就是存储过程的目的.但是您必须在PHP层中编写代码以依次获取每个结果集.也就是说,循环遍历结果集,并在该循环内循环遍历当前结果集的行.参见PDO::nextRowset()或mysqli::next_result()的示例.

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

不,如果你这样做,你就不安全了！使用PHP中的查询参数将字符串传递给CALL WEBSITE_mainSearch(？)对于防止SQL注入是没有用的,如果然后将该参数值连接到过程内的另一个字符串并执行动态SQL解析和执行.使用查询参数不会使参数值“安全”,它们只是将这些值与SQL分析阶段分开.

如果在连接字符串时使用MySQL的内置函数QUOTE(),则会更安全. QUOTE()会转义特殊字符,就像mysql_real_escape_string()一样.除了它略有不同,因为它还产生分隔字符串的单引号,就像PDO::quote()那样.

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根据累计计数使用不同数量的参数执行准备好的查询.

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: