Description:
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.
How to repeat:
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""
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.
Suggested fix:
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."
Properly escape double quotes by adding another double quote character before any double quote characters found in a field.