MySQL Bugs: #58342: ROUTINES.ROUTINE_DEFINITION removes double quotes

Bug #58342	ROUTINES.ROUTINE_DEFINITION removes double quotes
Submitted:	20 Nov 2010 2:41	Modified:	10 Aug 2011 12:35
Reporter:	Jorge Rivera	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.36, 5.1.52, 5.5.8	OS:	Windows (XPSP2)
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, metadata, PROCEDURE, quotes

Description:
The code in the Information_schema.routines table, column routine_definition, omits one quote when the original text included escaped quotes.  This makes the metadata unreliable, and it is not possible to automatically return the code to its original form.

How to repeat:
Create a procedure which contains escaped quotes.  A common case is Dynamic SQL.  For example,
SET @sSQLcmd=CONCAT(@sSQLcmd,'where item_date>= ''',date_min,''' and item_date < ''',date_max,''' ');

Then do Select routine_definition from information_schema.routines where routine_schema='schema' and routine_name='procedure';

The line above will appear as:
SET @sSQLcmd=CONCAT(@sSQLcmd,'where item_date>= '',date_min,'' and item_date < '',date_max,'' ');

The corresponding dynamic SQL will then say:

... where item_date>=',date_min,' and item_date < ',date_max,' '

instead of the expected:
... where item_date>='2010-11-01' and item_date<'2010-11-20'

Suggested fix:
Make sure that the metadata includes the escaped quotes as such.  Currently, a workaround is using show create procedure db.procname; which does include the escaped quotes, but also includes the CREATE DEFINER=`user`@`server` PROCEDURE...
This may not work if the client tool or library tries to parse and pre-process the query, because it will not match ANSI standard syntax.

Please, specify the exact server version used, 5.1.x, and provide complete, repeatable test case.

I have also tested with 5.0.24a on Linux.
A simple case would be this:

USE dbname;
DELIMITER $$
CREATE PROCEDURE stp_test (IN date_min datetime)
BEGIN
  set @sqlstr = concat('SELECT * from my_table where my_date>''',date_min,'''');
  PREPARE stmt FROM @sqlstr;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

Now run the following query:

select ROUTINE_DEFINITION
from information_schema.routines
where routine_schema='dbname'
and routine_name='stp_test';

The value returned will be:

BEGIN
  set @sqlstr = concat('SELECT * from my_table where my_date>'',date_min,''');
  PREPARE stmt FROM @sqlstr;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

(note the missing quotes)

Verified as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.52-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER $$
mysql> CREATE PROCEDURE stp_test (IN date_min datetime)
    -> BEGIN
    ->   set @sqlstr = concat('SELECT * from my_table where my_date>''',date_min
,'''');
    ->   PREPARE stmt FROM @sqlstr;
    ->   EXECUTE stmt;
    ->   DEALLOCATE PREPARE stmt;
    -> END $$
Query OK, 0 rows affected (0.30 sec)

mysql> DELIMITER ;
mysql> select ROUTINE_DEFINITION
    -> from information_schema.routines
    -> where routine_schema='test'
    -> and routine_name='stp_test';
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---+
| ROUTINE_DEFINITION

   |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---+
| BEGIN
  set @sqlstr = concat('SELECT * from my_table where my_date>'',date_min,''');
  PREPARE stmt FROM @sqlstr;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---+
1 row in set (0.02 sec)

I have verified that this error still appears in 5.5.8 on Windows platforms. It does NOT appear to occur in Linux anymore, though.

Also see http://bugs.mysql.com/bug.php?id=62129

I have to concur with Sveta. While the correct statement may be retrieved, even on Windows, with SHOW CREATE PROCEDURE/TRIGGER, it is not a client issue or a regression that the values returned by SHOW CREATE and SELECT from I_S.ROUTINES is inconsistent, and furthermore, that the returned value of SELECT from I_S.ROUTINES is inconsistent, for the same code, between the Linux version (which does return the right quotes) and the Windows version.

Hi all, 

is this being looked into? I'm surprised this is triaged as d3 medium. In my opinion, this should be considered a case of returning wrong data, which is in my opinion critical.

I am using MySQL 5.6.10 and i am suffering this issue!

I am using heidiSQL.

Example:

SHOW CREATE PROCEDURE test

DELIMITER $$

CREATE  PROCEDURE `test`(
)
BEGIN
SELECT CONCAT('ABC = ''',1,''''), CONCAT('ABC = ',2);
END$$

DELIMITER ;

Information schema:
SELECT  ROUTINE_DEFINITION   FROM  INFORMATION_SCHEMA.`ROUTINES` IR WHERE IR.ROUTINE_SCHEMA = 'test'  AND IR.`SPECIFIC_NAME` = 'test'

ROUTINE_DEFINITION                                             
---------------------------------------------------------------
BEGIN                                                          
select concat('ABC = '',1,'''), CONCAT('ABC = ',2);            
END                                                            

mysql:
SELECT body FROM `mysql`.`proc` mp WHERE mp.db='test'  AND mp.`name` = 'test'

body                                                             
-----------------------------------------------------------------
BEGIN                                                            
select concat('ABC = ''',1,''''), CONCAT('ABC = ',2);            
END

OS: Linux

The same issue and with MYSQL 8.0

The same issue is happen and with double quotes "

mysql> DELIMITER $$

CREATE  PROCEDURE _development.`test_1`(
)
BEGIN
SELECT CONCAT("ABC = """,1,""""), CONCAT("ABC = ",2);
END$$

DELIMITER ;

SELECT  ROUTINE_DEFINITION   FROM  INFORMATION_SCHEMA.`ROUTINES` IR WHERE IR.ROUTINE_SCHEMA = '_development'  AND IR.`SPECIFIC_NAME` = 'test_1';
Query OK, 0 rows affected (0.01 sec)
 
+--------------------------------------------------------------+
| ROUTINE_DEFINITION                                           |
+--------------------------------------------------------------+
| BEGIN
SELECT CONCAT("ABC = "",1,"""), CONCAT("ABC = ",2);
END |
+--------------------------------------------------------------+
1 row in set (0.01 sec)