MySQL PREPARE statement in stored procedures

Question

I have this sql file:

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
                     IN sqlq VARCHAR(5000)
                      ) COMMENT 'Executes the statement'
BEGIN
  PREPARE stmt FROM sqlq;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

When I try to run it with

# cat file.sql | mysql -p

I get

ERROR 1064 (42000) at line 6: 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 'sqlq;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  END' at line 5

What am I doing wrong?

score 26 · Accepted Answer · edited Jul 10 '21 at 04:35

26

You can only prepare and execute SQL that's a string literal or a user-defined variable that contains the text of the statement.

Try this:

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //

CREATE PROCEDURE execSql (IN sqlq VARCHAR(5000)) COMMENT 'Executes the statement'
BEGIN
  SET @sqlv = sqlq;
  PREPARE stmt FROM @sqlv;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

edited Jul 10 '21 at 04:35

informatik01

16,038
10
74
104

answered Nov 06 '11 at 01:53

mpf

391
5
4

Note that in a cursor, `FETCH [foo] INTO y`, inside the cursor you have to create a new `@yValue = y' and use @yValue for the prepared statement. – Russell Fox Sep 14 '20 at 17:57

score 2 · Answer 2 · edited Jul 10 '21 at 04:44

2

You can use CONCATENATE, PREPARE and EXECUTE statements as bellow:

CREATE DEFINER=`products`@`localhost` PROCEDURE `generateMeritList`(
   IN `mastercategory_id` INT(11), 
   IN `masterschools_id` INT(11)
) NO SQL
BEGIN

  DECLARE total INT DEFAULT 0;
  DECLARE conditions varchar(255) DEFAULT ''; 
  DECLARE finalQuery varchar(60000) DEFAULT '';

  IF mastercategory_id > 0 THEN
    SET conditions =  CONCAT(' AND app.masterschools_id = ', mastercategory_id);
  END IF;

  SET @finalQuery = CONCAT(
    "SELECT *
    FROM applications app
    INNER JOIN masterschools school ON school.id = app.masterschools_id
    WHERE app.status = 'active' ", conditions, " LIMIT ", total); 

   PREPARE stmt FROM @finalQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END

edited Jul 10 '21 at 04:44

informatik01

16,038
10
74
104

answered May 07 '16 at 07:33

Dinesh Vaitage

2,983
19
16

4

Thanks for your effort, but, besides the fact I already accepted mpf's answer about 5 years ago, I fail to understand how your post is supposed to answer my question. – Lucio Crusca May 07 '16 at 17:31
1

@Dinesh Vaitage - I am sorry to say, this does not prevent SQL injection attacks. There is no parameters to use in the preparation of the statement. In other words, where is the @conditions? – Nandostyle Aug 22 '21 at 16:32

MySQL PREPARE statement in stored procedures

2 Answers2

Linked