MySQL Bugs: #21108: procedures failing when cursors fetch strings with quotes or spaces

Bug #21108	procedures failing when cursors fetch strings with quotes or spaces
Submitted:	18 Jul 2006 8:16	Modified:	26 Aug 2006 8:47
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.12	OS:	Linux (Fedora core 5)
Assigned to:		CPU Architecture:	Any

Description:
The below script demonstrates two examples of ways in which cursors fail within procedures when fetched strings have complex formatting.

sample 1: the single string record has embedded quotes in it

sample 2: a view name has a space in it ( `t 1` ).  This string is then fetched from the VIEWS table, again throwing an error.

These errors are particularly frustrating as the error is thown simply against the procedure call, often with very little information other than "error in your SQL syntax"

How to repeat:
DROP DATABASE IF EXISTS fooDB;
CREATE DATABASE fooDB;
use fooDB;
CREATE TABLE t1 (x CHAR(64));
CREATE TABLE t2 (x CHAR(64));
INSERT INTO t1 VALUES ('cold''slaw''');
CREATE VIEW `v 1` AS SELECT * FROM t1;

DELIMITER |;
CREATE PROCEDURE cp()
BEGIN
   DECLARE vname CHAR(64);
   DECLARE done INT DEFAULT 0;
   DECLARE c CURSOR FOR SELECT x FROM t1;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   OPEN c;
   REPEAT
     FETCH c INTO vname;
     IF NOT done THEN
        SET @cmd:= concat('INSERT INTO t2 VALUES (\'', vname, '\')');
        PREPARE stmt FROM @cmd;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
     END IF;
   UNTIL done END REPEAT;
   CLOSE c;
END|

CREATE PROCEDURE clear_views()
BEGIN
   DECLARE vname CHAR(64);
   DECLARE done INT DEFAULT 0;
   DECLARE c CURSOR FOR SELECT TABLE_NAME FROM information_schema.VIEWS
             WHERE TABLE_SCHEMA='fooDB';
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   OPEN c;
   REPEAT
      FETCH c INTO vname;
      IF NOT done THEN
         SET @cmd:= concat('DROP VIEW fooDB.', vname);
         PREPARE statement FROM @cmd;
         EXECUTE statement;
         DEALLOCATE PREPARE statement;
      END IF;
   UNTIL done END REPEAT;
   CLOSE c;
END|
DELIMITER ;|

# when either of these procedures are called, an error is thrown
CALL cp();
CALL clear_views();

DROP PROCEDURE cp;
DROP PROCEDURE clear_views;
DROP TABLE t1, t2;
DROP DATABASE fooDB;

# output
mysqltest: At line 51: query 'CALL cp()' failed: 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 'slaw'')' at line 1

Please clarify which of the commands in the procedure is failing when encountering a string with quotes

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/.

No bug here. With

  SET @cmd:= concat('INSERT INTO t2 VALUES (\'', vname, '\')');

you construct a query

  INSERT INTO t2 VALUES ('cold'slaw'')

this is syntaxically wrong, naturally it fails when you try to execute it.
Same with the second procecedure, you try to execute the following query:

  DROP VIEW fooDB.t 1

In order to get around this issue you need to use the quote function
See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
If the problem still exists please clarify and set back to verify