MySQL中带有参数的第一个存储过程

this is my query in MySQL on table tbl_T367

mysql> SELECT
    oID,
    xName
FROM
    `tbl_T367`
WHERE
    oID IN ('2')
AND xName IN ('T367');

+-----+-------+
| oID | xName |
+-----+-------+
|   2 | T367  |
+-----+-------+
1 row in set

我需要为上面的相同输出创建mysql存储过程

我的下面的代码

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(xName char(4), oID INT (11))
BEGIN

DECLARE xNamenew CHAR (4);
DECLARE IDnew INT (11);

SET @xNamenew = xName;
SET @IDnew = oID;

SET @s = CONCAT('SELECT * FROM tbl_', @xNamenew, 
                ' WHERE oID IN (' + @IDnew + ') AND xName IN (' + @xNamenew + ')');
PREPARE stmt FROM @s;
EXECUTE stmt;

END

DELIMITER ;

但是结果

Procedure execution failed 1146 - Table 'tbl_T3672' doesn't exist

为什么将第二个变量的值添加到表名?

mysql> SELECT
    oID,
    xName
FROM
    `tbl_T3672`
WHERE
    oID IN ('2')
AND xName IN ('T367');
1146 - Table 'tbl_t3672' doesn't exist
mysql> 

该如何解决呢?

更新

SET @s = CONCAT('SELECT * FROM tbl_', @xNamenew, 
                ' WHERE oID IN (' , @IDnew , ') AND xName IN (' , @xNamenew ,')');

PREPARE stmt FROM @s;
EXECUTE stmt;

Procedure execution failed
1054 - Unknown column 'T367' in 'where clause'
评论
  • Ctrl
    Ctrl 回复

    一些问题:

    • MySQL doesn't have a string concatenation operator. + is the addition operator thus your strings will be cast to numbers and added.
    • You're inflicting yourself SQL injection. Literal strings in SQL must be single-quoted, but that isn't something you need to take care yourself.
    • You're invoking the procedure from itself.
    • You are missing the ending delimiter.
    DROP PROCEDURE IF EXISTS my_sqrt;
    
    DELIMITER $$
    CREATE PROCEDURE my_sqrt(xName char(4), oID INT (11))
    BEGIN
        DECLARE xNamenew CHAR (4);
        DECLARE IDnew INT (11);
    
        SET @xNamenew = xName;
        SET @IDnew = oID;
    
        SET @s = CONCAT('SELECT * FROM tbl_', @xNamenew, 
                        ' WHERE oID = ? AND xName = ?');
        PREPARE stmt FROM @s;
        EXECUTE stmt USING @IDnew, @xNamenew;
    END
    $$
    DELIMITER ;
    
    CALL my_sqrt('T367', '2');
    
  • 啊呸!
    啊呸! 回复

    Alvaro的答案向您展示了如何使用参数化查询来减少注入的机会。下面的过程仅出于教育目的,向您显示您所拥有的东西与达到正确目的的距离之间的区别。

    您不需要一些变量。您可以接受输入并创建一个串联的字符串。由于您将发送“ 2”,因此它可能不会与单引号连接在一起。因此,您必须在串联中包括单引号,如下所示。

    delimiter $$
    
    drop procedure if exists my_sqrt$$
    
    create procedure my_sqrt(xName varchar(10), oID varchar(10))
    begin
    
        set @s = concat(
            "select * from tbl_", xName,
            " where oID in ('", oID, "')",
            " and xName in ('", xName, "')"
        );
    
        prepare stmt from @s;
        execute stmt;
    
    end$$
    
    delimiter ;
    

    同样,这是出于教育目的。使用Alvaro的方法。