Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7231

Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside.

    XMLWordPrintable

    Details

    • Sprint:
      10.1.11, 10.1.12

      Description

      Demonstration:

      SELECT 
      (SELECT  ROUTINE_DEFINITION   FROM  INFORMATION_SCHEMA.`ROUTINES` IR WHERE IR.ROUTINE_SCHEMA = 'BPLNew'  AND IR.`SPECIFIC_NAME` = 'get_dict_source')
      =
      (SELECT body FROM `mysql`.`proc` mp WHERE mp.db='BPLnew'  AND mp.`name` = 'get_dict_source') compare

      compare  
      ---------
              0

      As you can see on the screenshot comparison only difference is the amount of shielding quotes.

      Why?

      Yet another example:

      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                                                                                                               

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            mikhail Mikhail Gavrilov
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration