JS CSV解析函数异常：含未转义单引号的CSV验证失败

算力阿峰

2026-7-27

问题描述

我有一个包含113个元素的数组，其中两个CSV格式字符串如下：

"3a49c9bb-caaf-48d8-b77c-45161cdf8ff5,Stadtwerke Feldkirch,AT006110,\"Verteilernetzbetreiber,Strom\",http://www.stadtwerke-feldkirch.at ,,,2023-02-13T16:05:03.452Z,2023-02-13T16:05:03.452Z\r"

"a601f77b-c7ed-40a8-a639-9baa25ca28a5,Revertera'sches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r"

第一个字符串可通过正则验证并正常解析为数组，但第二个字符串（包含未转义单引号Revertera'sches）无法通过验证，返回null。使用的CSV解析函数来自Stack Overflow，代码如下：

const data = ["3a49c9bb-caaf-48d8-b77c-45161cdf8ff5,Stadtwerke Feldkirch,AT006110,\"Verteilernetzbetreiber,Strom\",http://www.stadtwerke-feldkirch.at ,,,2023-02-13T16:05:03.452Z,2023-02-13T16:05:03.452Z\r", "a601f77b-c7ed-40a8-a639-9baa25ca28a5,Revertera'sches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r"];

function CSVtoArray(text) {
    var re_valid = /^\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*(?:,\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*)*$/;
    var re_value = /(?!\s*$)\s*(?:'([^'\\]*(?:\\[\S\s][^'\\]*)*)'|"([^"\\]*(?:\\[\S\s][^"\\]*)*)"|([^,'"\s\\]*(?:\s+[^,'"\s\\]+)*))\s*(?:,|$)/g;
    // Return NULL if input string is not well formed CSV string.
    if (!re_valid.test(text)) return null
    var a = []; // Initialize array to receive values.
    text.replace(re_value, // "Walk" the string using replace with callback.
        function(m0, m1, m2, m3) {
            // Remove backslash from \' in single quoted values.
            if (m1 !== undefined) a.push(m1.replace(/\\'/g, "'"));
            // Remove backslash from \" in double quoted values.
            else if (m2 !== undefined) a.push(m2.replace(/\\"/g, '"'));
            else if (m3 !== undefined) a.push(m3);
            return ''; // Return empty string.
        });
    // Handle special case of empty last value.
    if (/,$/.test(text)) a.push('');
    return a;
}

console.log(data.map(CSVtoArray));

最小测试用例验证结果显示，仅包含未转义单引号的text2验证失败：

const re_valid = /^\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*(?:,\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*)*$/;
const text1 = "3a49c9bb-caaf-48d8-b77c-45161cdf8ff5,Stadtwerke Feldkirch,AT006110,\"Verteilernetzbetreiber,Strom\",http://www.stadtwerke-feldkirch.at ,,,2023-02-13T16:05:03.452Z,2023-02-13T16:05:03.452Z\r";
const text2 = "a601f77b-c7ed-40a8-a639-9baa25ca28a5,Revertera'sches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r";
const text3 = "a601f77b-c7ed-40a8-a639-9baa25ca28a5,Reverterasches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r";

console.log(re_valid.test(text1)); // true
console.log(re_valid.test(text2)); // false
console.log(re_valid.test(text3)); // true

问题原因

原正则表达式中，匹配非引号包裹字段的分支[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*将单引号'列入了禁止出现的字符集合。但根据CSV规范，非引号包裹的字段中的单引号不需要转义，属于合法字符，因此包含单引号的字段无法通过验证。

解决方案

修改re_valid和re_value两个正则中对应分支的字符集合，移除单引号'，允许非引号包裹的字段包含单引号：

修改后的CSVtoArray函数：

const data = ["3a49c9bb-caaf-48d8-b77c-45161cdf8ff5,Stadtwerke Feldkirch,AT006110,\"Verteilernetzbetreiber,Strom\",http://www.stadtwerke-feldkirch.at ,,,2023-02-13T16:05:03.452Z,2023-02-13T16:05:03.452Z\r", "a601f77b-c7ed-40a8-a639-9baa25ca28a5,Revertera'sches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r"];

function CSVtoArray(text) {
    // 移除了字符集合中的单引号'
    var re_valid = /^\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,"\s\\]*(?:\s+[^,"\s\\]+)*)\s*(?:,\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,"\s\\]*(?:\s+[^,"\s\\]+)*)\s*)*$/;
    var re_value = /(?!\s*$)\s*(?:'([^'\\]*(?:\\[\S\s][^'\\]*)*)'|"([^"\\]*(?:\\[\S\s][^"\\]*)*)"|([^,"\s\\]*(?:\s+[^,"\s\\]+)*))\s*(?:,|$)/g;
    if (!re_valid.test(text)) return null
    var a = [];
    text.replace(re_value,
        function(m0, m1, m2, m3) {
            if (m1 !== undefined) a.push(m1.replace(/\\'/g, "'"));
            else if (m2 !== undefined) a.push(m2.replace(/\\"/g, '"'));
            else if (m3 !== undefined) a.push(m3);
            return '';
        });
    if (/,$/.test(text)) a.push('');
    return a;
}

console.log(data.map(CSVtoArray));
// 输出：两个数组，均正常解析，无null

修改后的测试用例：

// 移除了字符集合中的单引号'
const re_valid = /^\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,"\s\\]*(?:\s+[^,"\s\\]+)*)\s*(?:,\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,"\s\\]*(?:\s+[^,"\s\\]+)*)\s*)*$/;
const text1 = "3a49c9bb-caaf-48d8-b77c-45161cdf8ff5,Stadtwerke Feldkirch,AT006110,\"Verteilernetzbetreiber,Strom\",http://www.stadtwerke-feldkirch.at ,,,2023-02-13T16:05:03.452Z,2023-02-13T16:05:03.452Z\r";
const text2 = "a601f77b-c7ed-40a8-a639-9baa25ca28a5,Revertera'sches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r";
const text3 = "a601f77b-c7ed-40a8-a639-9baa25ca28a5,Reverterasches Elektrizitätswerk,AT003540,\"Verteilernetzbetreiber,Strom\",http://www.revertera.at ,,,2023-02-13T16:05:03.492Z,2023-02-13T16:05:03.492Z\r";

console.log(re_valid.test(text1)); // true
console.log(re_valid.test(text2)); // true
console.log(re_valid.test(text3)); // true

内容的提问来源于stack exchange，提问作者Ilijanovic