Description:
When exporting data from the result grid, special XML characters are not encoded during export.
Specifically:
& must be converted to &
< must be converted to <
" must be converted to " (when attributes are encoded in double-quotes)
' must be converted to ' (when attributes are encoded in single-quotes)
> can optionally be converted to >
As a result, exported XML data cannot be loaded and processed using an XML parser when the exported data contains one or more &, <, or " characters.
How to repeat:
Step 1: Create test table using the following:
DROP TABLE IF EXISTS test;
CREATE TABLE test (test_column VARCHAR(255) NOT NULL);
INSERT INTO test
VALUES ('& (ampersand) must be converted to \&')
, ('< (less-than) must be converted to <')
, ('" (double-quote) must be converted to "')
, ('> (greater-than) can optionally be converted to >')
, ('Attributes are encoded using double-quote therefore do not need to encode \' (apostrophe or single quote) with '')
Step 2: Select all table records using: SELECT * FROM test
Step 3: In the result grid, click the export button
Step 4: Using the save as type drop down, select "XML (mysql format)"
Step 5: Save the file to any location with any name
Step 6: Open the file to see the following:
<?xml version="1.0"?>
<resultset statement="SELECT * FROM test
LIMIT 0, 50000
"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="test_column">& (ampersand) must be converted to &</field>
</row>
<row>
<field name="test_column">< (less-than) must be converted to <</field>
</row>
<row>
<field name="test_column">" (double-quote) must be converted to "</field>
</row>
<row>
<field name="test_column">> (greater-than) can optionally be converted to ></field>
</row>
<row>
<field name="test_column">Attributes are encoded using double-quote therefore do not need to encode ' (apostrophe or single quote) with '</field>
</row>
</resultset>
Suggested fix:
Update workbench to behave the same as mysql.exe with --xml option.
When mysql.exe is run with the --xml option:
& is converted to &
< is converted to <
" is converted to "
> is converted to >
' is NOT converted to ' which is not needed because attributes are enclosed in quotes.