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:Microsoft Windows (XPSP2)
Assigned to: CPU Architecture:Any
Tags: FUNCTION, metadata, PROCEDURE, quotes
Triage: Triaged: D3 (Medium)

[20 Nov 2010 2:41] Jorge Rivera
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.
[21 Nov 2010 21:02] Valeriy Kravchuk
Please, specify the exact server version used, 5.1.x, and provide complete, repeatable test case.
[21 Nov 2010 22:02] Jorge Rivera
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)
[22 Nov 2010 8:23] Valeriy Kravchuk
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)
[24 May 2011 0:35] Jorge Rivera
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.
[10 Aug 2011 11:43] Peter Laursen
Also see http://bugs.mysql.com/bug.php?id=62129
[10 Aug 2011 12:35] Jorge Rivera
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.
[14 Sep 2011 22:08] Roland Bouman
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.
[16 Mar 2013 22:21] david trillo
I am using MySQL 5.6.10 and i am suffering this issue!

I am using heidiSQL.
[29 Nov 2014 9:15] Mikhail Gavrilov
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
[29 Nov 2014 9:17] Mikhail Gavrilov
OS: Linux