passing table name and limit to mysql stored procedure not working

Question

I have created procedure as below. I am passing table name and limit along with username,

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 ;

But when I call this,

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

It is giving me error,

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

Please help me out.

score 3 · Accepted Answer · edited May 23 '17 at 12:15

+ is not a string concatenation operator—use MySQL's CONCAT() function instead;
user-defined (session) variables (which are prefixed with @) are different to stored-program/declared variables (which have no prefix);
beware of your procedure being called with a tblname that results in SQL injection;
column names in a UNION are determined by the first query; and
parameterise your prepared statement, where possible.

Therefore:

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$$

However, all that said, having a variable table name is a strong indicator that your schema is badly denormalised—consider merging all such tables together into a single one with columns that identify their differences.

can u give me full statement with replace? – Kautil Apr 30 '13 at 10:49 — Kautil, Apr 30 '13 at 10:49

score 0 · Answer 2 · answered Jul 06 '14 at 06:41

The code above is missing the end parenthesis for the CONCAT function. So it should be this:

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$$

passing table name and limit to mysql stored procedure not working

2 Answers2