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
• Status: Closed
• 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.