Bug: CSV export doesn't escape double quotes
Posted by:
D H
Date: March 21, 2019 07:51AM
The CSV export functionality isn't properly escaping double quotes inside of values. This causes missing double quote characters and potentially incorrect column splitting if a comma is included in the value.
Tested: MySQL Workbench 8.0.15
To reproduce:
CREATE SCHEMA `test` ;
CREATE TABLE `test`.`test` (
`test_id` INT NOT NULL AUTO_INCREMENT,
`description` VARCHAR(45) NOT NULL,
PRIMARY KEY (`test_id`));
INSERT INTO `test`.`test` (`description`) VALUES ('This record has \'single quotes\'');
INSERT INTO `test`.`test` (`description`) VALUES ('This record has \"double quotes\"');
INSERT INTO `test`.`test` (`description`) VALUES ('This record has \"double quotes\" and, a comma');
INSERT INTO `test`.`test` (`description`) VALUES ('This, record, has, lots, of, commas');
INSERT INTO `test`.`test` (`description`) VALUES ('This record has \"a, comma\" in \"quotes\"');
Select all rows in table, export to CSV.
Actual output:
test_id,description
1,"This record has 'single quotes'"
2,"This record has "double quotes""
3,"This record has "double quotes" and, a comma"
4,"This, record, has, lots, of, commas"
5,"This record has "a, comma" in "quotes""
You can open this file up in a CSV viewer and see that some records have missing double quotes and others have been split into 3 columns instead of two.
Correct behavior: Conform to RFC 4180 2.7 "If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote."
Expected output:
test_id,description
1,"This record has 'single quotes'"
2,"This record has ""double quotes"""
3,"This record has ""double quotes"" and, a comma"
4,"This, record, has, lots, of, commas"
5,"This record has ""a, comma"" in ""quotes"""
Open this file in a CSV viewer and see that all previously mentioned issues are gone.
Thanks.