Bug #62429 XML: ExtractValue, UpdateXML max arg length 127 chars
Submitted: 14 Sep 2011 4:30 Modified: 4 Jul 2012 15:18
Reporter: Do-jeon Kim Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: XML functions Severity:S3 (Non-critical)
Version:5.5.14-log, 5.5.17 OS:Any (XP SP3, Mac OS X)
Assigned to: CPU Architecture:Any
Tags: 127, 128, 1512, ExtractValue, LENGTH, To deep XML, updatexml, XPath
Triage: Needs Triage: D2 (Serious)

[14 Sep 2011 4:30] Do-jeon Kim
Description:
I met 'EXTRACTVALUE' function returns NULL and not working in some XML fragments.

In various tests, I've found that 'EXTRACTVALUE' does not working when XML's hierarchial expression (element name with its ancestors and namespace, eg: 'namespace:root/child/grandchild') is over 127 in character length

Of course, 'UPDATEXML' does not working in the same manner.

How to repeat:
drop table xpathtest;

create table xpathtest (id int auto_increment, txt varchar(1000), primary key (id));

insert into xpathtest (txt) values 
(concat('<', repeat('a',127), '>127</', repeat('a',127), '>')),
(concat('<', repeat('a',128), '>128</', repeat('a',128), '>')),
(concat('<', repeat('a',63), '><', repeat('b',63), '>63/63</', repeat('b',63), '></', repeat('a',63),'>')),
(concat('<', repeat('a',63), '><', repeat('b',64), '>63/64</', repeat('b',64), '></', repeat('a',63),'>'));

select 
txt, 
EXTRACTVALUE(txt, concat('/', repeat('a', 127))) as a127, 
EXTRACTVALUE(txt, concat('/', repeat('a', 128))) as a128,
EXTRACTVALUE(txt, concat('//', repeat('b', 63))) as a63b63, 
EXTRACTVALUE(txt, concat('//', repeat('b', 64))) as a63b64
from xpathtest;
-- Column a128 (or a63b64) returns NULL.

select UPDATEXML(txt, concat('//', repeat('b', 63)), '63/63+') from xpathtest;
-- Row 2 (or 4) return NULL.

Suggested fix:
If this is designed for preventing some attack such like DoS with oversized XML, I thought an optional configuration is recommendable.

Thanks.
[14 Sep 2011 8:39] Valeriy Kravchuk
Thank you for the bug report. Verified with current mysql-5.5 from bzr on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.17 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create table xpathtest (id int auto_increment, txt varchar(1000), primary key (id));
Query OK, 0 rows affected (0.15 sec)

mysql> 
mysql> insert into xpathtest (txt) values 
    -> (concat('<', repeat('a',127), '>127</', repeat('a',127), '>')),
    -> (concat('<', repeat('a',128), '>128</', repeat('a',128), '>')),
    -> (concat('<', repeat('a',63), '><', repeat('b',63), '>63/63</', repeat('b',63), '></',
    -> repeat('a',63),'>')),
    -> (concat('<', repeat('a',63), '><', repeat('b',64), '>63/64</', repeat('b',64), '></',
    -> repeat('a',63),'>'));
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> select 
    -> txt, 
    -> EXTRACTVALUE(txt, concat('/', repeat('a', 127))) as a127, 
    -> EXTRACTVALUE(txt, concat('/', repeat('a', 128))) as a128,
    -> EXTRACTVALUE(txt, concat('//', repeat('b', 63))) as a63b63, 
    -> EXTRACTVALUE(txt, concat('//', repeat('b', 64))) as a63b64
    -> from xpathtest;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+--------+--------+
| txt                                                                                                                                                                                                                                                                           | a127 | a128 | a63b63 | a63b64 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+--------+--------+
| <aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa>127</aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa>        | 127  |      |        |        |
| <aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa>128</aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa>      | NULL | NULL | NULL   | NULL   |
| <aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa><bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb>63/63</bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb></aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa>   |      |      | 63/63  |        |
| <aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa><bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb>63/64</bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb></aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa> | NULL | NULL | NULL   | NULL   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+------+--------+--------+
4 rows in set, 8 warnings (0.02 sec)
[16 Sep 2011 1:42] Do-jeon Kim
When i repeated this, I found a warning.

Maybe this thread deals the same issue, but not solved.
http://forums.mysql.com/read.php?10,419682,419682#msg-419682

show warnings;

|| *Level* || *Code* || *Message* ||
|| Warning || 1525 || Incorrect XML value: 'parse error at line 1 pos 130: To deep XML' ||
|| Warning || 1525 || Incorrect XML value: 'parse error at line 1 pos 203: '</bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb>' unexpected ('</bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb>' wanted)' ||
[4 Jul 2012 14:55] Jon Stephens
Fixed in 5.6+.
[4 Jul 2012 15:18] Jon Stephens
Fixed in 5.6+, documented as follows in the 5.6.7 changelog:

      The maximum length supported for the XPath expressions as arguments with
      the ExtractValue() and UpdateXML() functions was formerly 127 characters.
      This limitation has been removed. 

Also updated the descriptions of these functions in all versions of the Manual, 5.1+.

Closed.