php - 将表名和限制传递给 mysql 存储过程不起作用

Question

我已经创建了如下程序。我正在传递表名和限制以及用户名，

DELIMITER $$
DROP PROCEDURE IF EXISTS `GetPosteingang`$$
CREATE PROCEDURE  `GetPosteingang`
(
IN stlimit INT,
IN tblname VARCHAR(100),
IN userId INT
)
BEGIN
DECLARE t1 VARCHAR(5000); 
SET @t1 =
"SELECT msg_id,msg_from_name as fromname,msg_titel as title,msg_date as date,msg_gelesen,msg_replied,msg_nodel,'msg' as type 
FROM "+@tblname+"
UNION
SELECT files_id as msg_id,from_username as fromname,files_oname as title,files_time as date,files_name as msg_gelesen,files_extension as msg_replied,files_filesize as msg_nodel,'file' as type 
FROM community_files
WHERE user_id = "+@userId+"
ORDER BY date DESC
LIMIT "+@stlimit+",30";
#SET @t1 =CONCAT('SELECT * FROM ',tab_name );

PREPARE STMT FROM @t1;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

END $$
DELIMITER ;

但是当我调用它时，

CALL GetPosteingang('1','community_msgin8','658468'); 

它给我错误，

CALL GetPosteingang('1','community_msgin8','658468')    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

请帮帮我。

Answer 1

1. + 不是字符串连接运算符——使用 MySQL 的 CONCAT()代替函数；

2. 用户定义( session )变量(前缀为@)are different存储程序/声明的变量(没有前缀)；

3. 当心使用 tblname 调用您的过程，这会导致 SQL 注入(inject)；

4. UNION 中的列名由第一个查询确定；和

5. 尽可能参数化准备好的语句。

因此:

CREATE PROCEDURE  `GetPosteingang`
(
  IN stlimit INT,
  IN tblname VARCHAR(100),
  IN userId INT
)
BEGIN
  SET @t1 := CONCAT("
    SELECT msg_id,
           msg_from_name AS fromname,
           msg_titel     AS title,
           msg_date      AS date,
           msg_gelesen,
           msg_replied,
           msg_nodel,
           'msg'         AS type
    FROM   `", REPLACE('`','``',tblname), "`
  UNION
    SELECT files_id,
           from_username,
           files_oname,
           files_time,
           files_name,
           files_extension,
           files_filesize,
           'file' as type
    FROM   community_files
    WHERE  user_id = ?
  ORDER BY date DESC
  LIMIT    ?, 30
  ", @t2 := userId, @t3 := stlimit;

  PREPARE stmt FROM @t1;
  EXECUTE stmt USING @t2, @t3;
  DEALLOCATE PREPARE stmt;
END$$

然而，综上所述，具有可变表名是一个强烈的指标，表明您的架构严重非规范化——考虑将所有此类表合并到一个表中，并使用列来标识它们之间的差异。