[MDEV-6508] Missing escaping in SELECT ROUTINE_BODY FROM I_S.ROUTINES Created: 2014-07-30  Updated: 2022-09-12  Resolved: 2022-09-12

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.5.36-galera, 5.3.12, 10.0.12
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

SHOW CREATE FUNCTION displays correctly escaped double quotes and backslashes in string literals.

I_S.ROUTINES.ROUTINE_BODY displays unescaped values, which looks wrong.

This SQL script demonstrates the problem:

DROP FUNCTION IF EXISTS f1;
 
CREATE FUNCTION f1() RETURNS VARCHAR(1) RETURN 'a\x\t\\t''t';
 
SHOW CREATE FUNCTION f1;
 
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
 
WHERE ROUTINE_NAME='f1';

The output from SHOW is:

+----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 
| Function | sql_mode | Create Function                                                                                          | character_set_client | collation_connection | Database Collation |
 
+----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 
| f1       |          | CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS varchar(1) CHARSET latin1
 
RETURN 'a\x\t\\t''t' | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
+----------+----------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

The output from SELECT is:

+--------------------+
 
| ROUTINE_DEFINITION |
 
+--------------------+
 
| RETURN 'ax	\t't'   |
 
+--------------------+

ROUTINE_DEFINITION should also display escaped values.

 Comments   
Comment by Sergei Golubchik [ 2014-08-05 ]

This is expected behavior. I_S.ROUTINES table shows the routine definition in the UTF8 character set. It is generally impossible to represent a definition of the arbitrary routine in the UTF8. Thus the purpose of the I_S.ROUTINES.ROUTINE_DEFINITION column is to provide a user-readable representation of the routine definition, but it may not always be used to recreate the routine and won't necessarily be valid SQL.

For the latter one should use SHOW CREATE FUNCTION. But its output won't necessarily be in any specific character set.

Comment by Alexander Barkov [ 2014-08-05 ]

Right, the purpose of I_S.ROUTINES is not to give a valid SQL code.
But anyway, it should escape, for user readability.

Note, among other dangerous characters, '\0' is also not escaped!
That means the client application cannot use usual C string
routines, like printf, for the column I_S.ROUTINES.ROUTINE_DEFINITION.

Although it's probably not very important, but still, this IS a bug.

Comment by Alexander Barkov [ 2015-03-26 ]

A similar problem happens with binary strings.
This script:

DROP FUNCTION IF EXISTS f1;
 
CREATE FUNCTION f1() RETURNS VARBINARY(10) RETURN '\t\0\t';
 
SHOW CREATE FUNCTION f1;
 
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
 
WHERE ROUTINE_NAME='f1';

returns:

+--------------------+
 
| ROUTINE_DEFINITION |
 
+--------------------+
 
| RETURN '	 	'       |
 
+--------------------+

Comment by Sergei Golubchik [ 2022-09-12 ]

10.0 was EOLed in March 2019

Generated at Thu Feb 08 07:12:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.