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.
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.