Bug #21513 SP having body starting with quoted label rendered unusable
Submitted: 8 Aug 2006 17:03 Modified: 17 May 2007 14:14
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0/5.1BK OS:Any (All)
Assigned to: Marc ALFF CPU Architecture:Any

[8 Aug 2006 17:03] Andrey Hristov
Description:
According to the standard labels might be quoted. Here is an example of a CREATE PROCEDURE which starts with such a label. However, the parser cuts the body and the leading quote is not included. Every attempt to use p5 will be unsuccessful.

create procedure p5 () ` l123`: begin end

How to repeat:
mysql> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 5.0.25-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.01 sec)

mysql> create procedure p1234() ` label`: begin end;
Query OK, 0 rows affected (0.03 sec)

mysql> show create procedure p1234;
ERROR 1064 (42000): 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 ': begin end' at line 2
mysql> select body from mysql.proc where name='p1234';
+--------------------+
| body               |
+--------------------+
|  label`: begin end |
+--------------------+
1 row in set (0.07 sec)

mysql> create procedure p12345() `l label`: begin end;
Query OK, 0 rows affected (0.01 sec)

mysql> show create procedure p12345;
ERROR 1064 (42000): 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 'label`: begin end' at line 2
mysql> select body from mysql.proc where name='p12345';
+---------------------+
| body                |
+---------------------+
| l label`: begin end |
+---------------------+
1 row in set (0.02 sec)

mysql> call p12345();
ERROR 1457 (HY000): Failed to load routine test.p12345. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

-----------------
create procedure p1234() ` label`: begin end;
show create procedure p1234;
select body from mysql.proc where name='p1234';
create procedure p12345() `l label`: begin end;
show create procedure p12345;
select body from mysql.proc where name='p12345';
call p12345();

Suggested fix:
===== sql/sp_head.cc 1.221 vs edited =====
--- 1.221/sql/sp_head.cc        2006-08-08 18:02:52 +02:00
+++ edited/sql/sp_head.cc       2006-08-08 16:36:56 +02:00
@@ -529,6 +529,22 @@
   */
   endp= skip_rear_comments(m_body_begin, endp);

+  /*
+    There might be a whitespace which we cannot skip in the parser.
+    We used to use lex->tok_start but it skips the opening ` if the
+    the proc starts with a label which is quoted. tok_end points before
+    the statement but contains spaces, which has to be trimmed.
+    See bug#11986.
+    It could be whitespace because in the following case there is no
+    whitespace:
+    CREATE PROCEDURE bug11986()`mylabel`: BEGIN END;
+    In this case there are two whitespace chars
+    CREATE PROCEDURE bug11986()  `mylabel`: BEGIN END;
+  */
+  while (my_isspace(thd->variables.character_set_client, *m_body_begin))
+    m_body_begin+= my_mbcharlen(thd->variables.character_set_client,
+                                (uchar) *m_body_begin);
+
   m_body.length= endp - m_body_begin;
   m_body.str= strmake_root(root, (char *)m_body_begin, m_body.length);
   m_defstr.length= endp - lex->buf;
===== sql/sql_yacc.yy 1.477 vs edited =====
--- 1.477/sql/sql_yacc.yy       2006-08-08 18:02:54 +02:00
+++ edited/sql/sql_yacc.yy      2006-08-08 15:41:42 +02:00
@@ -9225,9 +9225,10 @@
        sp_c_chistics
        {
          LEX *lex= Lex;
-
+          int i = yychar;
+          sql_print_information("%d", i);
          lex->sphead->m_chistics= &lex->sp_chistics;
-         lex->sphead->m_body_begin= lex->tok_start;
+         lex->sphead->m_body_begin= lex->tok_end;
        }
        sp_proc_stmt
        {
-cut-here------------------------------------------
There is another solution, which is used in the parser.
The `remember_name` rule is actually $$= Lex->tok_start =>
$1 = Lex->tok_start. In the code below -1 is accessed and checked whether
it is "`"

select_item:
	  remember_name select_item2 remember_end select_alias
	  {
	    if (add_item_to_list(YYTHD, $2))
	      YYABORT;
	    if ($4.str)
            {
              $2->is_autogenerated_name= FALSE;
	      $2->set_name($4.str, $4.length, system_charset_info);
            }
	    else if (!$2->name) {
	      char *str = $1;
	      if (str[-1] == '`')
	        str--;
	      $2->set_name(str,(uint) ($3 - str), YYTHD->charset());
	    }
	  };
[8 Aug 2006 17:23] MySQL Verification Team
Thank you for the bug report. Verified as described.
[27 Apr 2007 23:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25640

ChangeSet@1.2440, 2007-04-27 17:14:25-06:00, malff@weblab.(none) +5 -0
  Bug#21513 (SP having body starting with quoted label rendered unusable)
  
  Before this fix, the parser would sometime change where a token starts by
  altering Lex_input_string::tok_start, which later confused the code in
  sql_yacc.yy that needs to capture the source code of a SQL statement,
  like to represent the body of a stored procedure.
  
  This line of code in sql_lex.cc :
  
  case MY_LEX_USER_VARIABLE_DELIMITER:
    lip->tok_start= lip->ptr; // Skip first `
  
  would <skip the first back quote> ... and cause the bug reported.
  
  In general, the responsibility of sql_lex.cc is to *find* where token are
  in the SQL text, but is *not* to make up fake or incomplete tokens.
  With a quoted label like `my_label`, the token starts on the first quote.
  Extracting the token value should not change that (it did).
  
  With this fix, the lexical analysis has been cleaned up to not change
  lip->tok_start (in the case found for this bug).
  
  The functions get_token() and get_quoted_token() now have an extra
  parameters, used when some characters from the beginning of the token need
  to be skipped when extracting a token value, like when extracting 'AB' from
  '0xAB', for example, for a HEX_NUM token.
  
  This exposed a bad assumption in Item_hex_string and Item_bin_string,
  which has been fixed:
  
  The assumption was that the string given, 'AB', was in fact preceded in
  memory by '0x', which might be false (it can be preceded by "x'" and
  followed by "'" -- or not be preceded by valid memory at all)
  
  If a name is needed for Item_hex_string or Item_bin_string, the name is
  taken from the original and true source code ('0xAB'), and assigned in
  the select_item rule, instead of relying on assumptions related to how
  memory is used.
[16 May 2007 13:48] Bugs System
Pushed into 5.0.42
[16 May 2007 13:49] Bugs System
Pushed into 5.1.19-beta
[17 May 2007 14:14] Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs.

Quoted labels in stored routines were mishandled, rendering the
routines unusable.