mysql - 在 mysql sp 中处理单引号和双引号

问题描述

我有以下存储程序

DELIMITER $$

USE `database_name`$$

DROP FUNCTION IF EXISTS `get_data`$$

CREATE DEFINER=`database_name`@`%` FUNCTION `get_data`(
                    v_search_text TEXT
) RETURNS TEXT CHARSET utf8
    DETERMINISTIC
BEGIN

    DECLARE v_select TEXT DEFAULT 'SELECT ';
    DECLARE v_from TEXT DEFAULT ' FROM ';
    DECLARE v_order,v_group TEXT DEFAULT '';
    DECLARE v_where TEXT DEFAULT ' WHERE 1 = 1 ';
    DECLARE v_return_string TEXT DEFAULT '';

    SET v_select = CONCAT(v_select,"id, title, detail", v_from, "table");

    IF(IFNULL(v_search_text,'') != '' ) THEN
        SET v_search_text = QUOTE(v_search_text);
        SET v_where = CONCAT(v_where," AND (title like '%",v_search_text,"%' or detail like '%",v_search_text,"%' )");
    END IF;

    -- other stuff like order, pagination etc.
    SET v_return_string = CONCAT(
            v_select,
            v_from,
            v_where
        );

    PREPARE v_return_string FROM @count_query;
    EXECUTE v_return_string;
END$$

DELIMITER ;

现在的问题是让我们假设，我在表中有如下数据

id---title---detail
1-----A'a---- Detail's
2-----B"b---- Detail"b

现在，如果我将在 SP 上方调用并传递搜索参数，'那么它将中断：

调用 get_data('\''); -- 表示搜索包含单引号的数据

要解决这个问题，我可以像下面这样更改一行

SET v_where = CONCAT(v_where," AND (title like '%",v_search_text,"%' or detail like '%",v_search_text,"%' )");

到

SET v_where = CONCAT(v_where,' AND (title like "%',v_search_text,'%" or detail like "%',v_search_text,'%" )');

然后它将适用于单引号，但它会中断双引号。那么有没有办法解决这两种情况？

标签： mysqlstored-procedures

我已更改以下行：

SET v_where = CONCAT(v_where," AND (title like '%",v_search_text,"%' or detail like '%",v_search_text,"%' )");

与以下：

SET v_search_text = QUOTE(CONCAT('%',v_search_text,'%'));
SET v_where = CONCAT(v_where," AND (title like ",v_search_text," or detail like ",v_search_text," )");

问题就解决了。

mysql - 在 mysql sp 中处理单引号和双引号

问题描述

解决方案

推荐阅读