Bug #7811 XML-output is not well-formed with special column- or alias-names

Submitted: 11 Jan 2005 16:51 Modified: 3 Feb 2005 5:37
Reporter: Walter Ronken Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.1.3b OS:Windows (Windows-NT / Linux)
Assigned to: Jim Winstead CPU Architecture:Any

[11 Jan 2005 16:51] Walter Ronken
Description:
In a SELECT-statement column-names or alias-names have to be valid XML-names,
i.e.:

1.   SELECT count(*) from test;

produces on the mysql-client without error message:

<?xml version="1.0"?>
<resultset statement="select count(*) from test">
  <row>
    <count(*)>1078</count(*)>
  </row>
</resultset>

count(*) is not a valid element-name, so the output is not well-formed.

2.    SELECT "Hello" from test;

produces:

<?xml version="1.0"?>
<resultset statement="select "Hello" from test">
  <row>
    <Hello>Hello</Hello>
  </row>
</resultset>

The quotation marks inside the attribute value has to be escaped:

<?xml version="1.0"?>
<resultset statement="select &quote;Hello&quote; from test">
  <row>
    <Hello>Hello</Hello>
  </row>
</resultset>

How to repeat:
a) Take any other function inside a SELECT-statement without alias-name.
b) Take any column-name / alias-name which doesn't start with a letter, i.e. a backtick, inside a SELECT-statement.
c) Take any constant or alias-names with quotation marks inside a SELECT-statement 

Suggested fix:
1.  wrong element-name:
   Use a translate-function on field-names for not allowed characters.
   Allowed names are:
   Name    ::=    (Letter | '_' | ':') (NameChar)* 
   NameChar    ::=    Letter | Digit | '.' | '-' | '_' | ':' | CombiningChar | Extender 
   ...
   see XML Recommendation: http://www.w3.org/TR/2004/REC-xml-20040204

2. wrong attribute-value:
   Replace the characters: & < > ' " 
   with the predefined entities:
   &amp; &lt; &gt; &apos; &quote; 

3. check if the output is a well-formed xml-document 
    otherwise produce an error message
[25 Jan 2005 22:37] Jim Winstead
Instead of transforming the name of columns to create valid xml element names, this patch encodes fields like mysqldump does: <field name="NAME">VALUE</field> (with NAME and VALUE properly encoded).
[28 Jan 2005 20:30] Jim Winstead
Approved for 5.0.
[1 Feb 2005 3:44] Jim Winstead
Pushed to 5.0.
[3 Feb 2005 5:37] Paul DuBois
Mentioned in 5.0.3 change notes.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.