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的方法。

点赞
评论