| Bug #64651 | mysqldump xml fails to quote string literals | ||
|---|---|---|---|
| Submitted: | 14 Mar 2012 19:01 | Modified: | 14 Mar 2012 19:14 |
| Reporter: | Jeffrey Paul | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 5.1.61, 5.5.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysqldump, XML | ||
[14 Mar 2012 19:14]
Valeriy Kravchuk
Verified with 5.1.61 also: macbook-pro:5.1 openxs$ bin/mysqldump --xml -uroot test tableName <?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="test"> <table_structure name="tableName"> <field Field="col1" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" Comment="" /> <field Field="col2" Type="timestamp" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" Comment="" /> <options Name="tableName" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="0" Avg_row_length="0" Data_length="0" Max_data_length="2533274790395903" Index_length="1024" Data_free="0" Create_time="2012-03-14 19:12:18" Update_time="2012-03-14 19:12:18" Collation="latin1_swedish_ci" Create_options="" Comment="" /> </table_structure> <table_data name="tableName"> </table_data> </database> </mysqldump>
Description: The "Default" attribute of a <field> tag in the <table_structure> of an XML-format mysqldump does not correctly quote string literals within the attribute value. Example: <table_structure name="tableName"> [snip] <field Field="col1" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" Comment="" /> <field Field="col2" Type="timestamp" Null="NO" Key="" Default="0000-00-00 00:00:00" Extra="" Comment="" /> [snip] </table_structure> In a traditional mysqldump, these columns appear like this: CREATE TABLE `tableName` ( [snip] `col1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `col2` timestamp NOT NULL default '0000-00-00 00:00:00', [snip] It seems that in the xml-format mysqldump, the <field> should appear as follows (note the entity ' for a single quote on each side of the default value inside of the double quotes of the attribute value: <field Field="field2" Type="timestamp" Null="NO" Key="" Default="'0000-00-00 00:00:00'" Extra="" Comment="" /> This prevents a user who has relied on an xml-format mysqldump from being able to accurately reconstruct their database without human review of the data (or ugly edge-case handling to detect strings versus function names)! How to repeat: Create a table with a timestamp column with a default of CURRENT_TIMESTAMP, and another timestamp column with a default of '0000-00-00 00:00:00'. Perform an xml-format mysqldump (mysqldump --xml). There is no way to discern from the resultant xml dumpfile whether or not the default value (in the attribute) should be single-quoted (e.g. in the case of 0000-00-00 00:00:00) or not (e.g. in the case of CURRENT_TIMESTAMP). This poses problems for any utility attempting to re-create tables based on the contents of an xml-format mysqldump. Suggested fix: Place entity-escaped single quotes inside the "Default" attribute value within the <field> tags when they are specifying string literals, such as '0000-00-00 00:00:00'. <field ... Default="0000-00-00 00:00:00"... /> becomes <field ... Default="'0000-00-00 00:00:00'"... />