A Log Export With Unescaped Quotes and How to Read It

A web server or an application writes its log as CSV, and one of the columns is a request path or a user message, which can contain double quotes. The exporter wraps the field in quotes and does not double the ones inside, so the file has "/search?q="red shoes" size 9" where RFC 4180 requires "/search?q=""red shoes"" size 9". Standard parsers are written for the rule, and when it is broken they diverge: each reads the line in its own way, and none of the ways is an error. The result is fields that look plausible and are wrong. This article feeds the same four lines to Python and DuckDB, compares what comes back, finds the setting that reads them as intended and shows how to prove the repair. It is one of the problems in worked CSV problems and the patterns behind them, and the rules it breaks are in how CSV quoting works.

The file

ts,method,path,status
2026-10-02T10:00:01,GET,"/search?q=red shoes",200
2026-10-02T10:00:04,GET,"/search?q="red shoes" size 9",200
2026-10-02T10:00:09,POST,"/cart",302
2026-10-02T10:00:12,GET,"/search?q=5" nails",404

Lines 1 and 3 are fine: quoted fields with no quotes inside. Line 2 has a quoted field whose value contains "red shoes", and the exporter wrote the inner quotes bare. Line 4 has a quote that stands for inches, 5", also bare. A human reads line 2 as /search?q="red shoes" size 9 and line 4 as /search?q=5" nails.

What each parser makes of it

Python's csv module with its defaults reads a quote in the middle of a quoted field as the start of an escape, or as a literal, depending on the next character, and for these lines it returns:

['2026-10-02T10:00:01', 'GET', '/search?q=red shoes', '200']
['2026-10-02T10:00:04', 'GET', '/search?q=red shoes" size 9"', '200']
['2026-10-02T10:00:09', 'POST', '/cart', '302']
['2026-10-02T10:00:12', 'GET', '/search?q=5 nails"', '404']

Each row has four fields, and the damaged ones are wrong: the quote that opened "red shoes" has vanished and reappeared after size 9, and the line 4 path lost its inch mark and gained a trailing quote. The field count is right and the content is not, which is the worst kind of error.

DuckDB's default settings sniff the file and, finding the quoting inconsistent, return the fields with the outer quotes kept as ordinary characters:

('2026-10-02T10:00:01', 'GET', '"/search?q=red shoes"', '200')
('2026-10-02T10:00:04', 'GET', '"/search?q="red shoes" size 9"', '200')
('2026-10-02T10:00:09', 'POST', '"/cart"', '302')
('2026-10-02T10:00:12', 'GET', '"/search?q=5" nails"', '404')

Now every path, including the good ones, has quotes around it. The sniffer decided that the quote character is not a quote, so line 1 and line 3 are damaged too. That is the sniffer's honest answer to a file that does not follow the rules, and it is a file-wide change of meaning, so it does more harm in this case than Python's mistake, which was confined to the bad lines.

The lenient setting

DuckDB has a strict_mode option. With strict_mode=false, the reader allows quotes inside quoted fields and closes a field when the quote is followed by a delimiter or the end of the line, which is the rule a human applies to this file:

SELECT * FROM read_csv('weblog.csv', all_varchar=true, strict_mode=false);

('2026-10-02T10:00:01', 'GET', '/search?q=red shoes', '200')
('2026-10-02T10:00:04', 'GET', '/search?q="red shoes" size 9', '200')
('2026-10-02T10:00:09', 'POST', '/cart', '302')
('2026-10-02T10:00:12', 'GET', '/search?q=5" nails', '404')

All four match the intended values: the good lines are unchanged, "red shoes" keeps both its quotes inside the path and the inch mark survives. Adding ignore_errors=true on top gave the same four rows, and it would also skip rows the reader cannot parse, which I did not need and which hides damage, so I left it off.

The rule that worked was that a quote ends a field only when the next character is a comma or a line break. That heuristic is exactly right for this file and it is still a heuristic: a legitimate field containing ", in its text, say a quoted phrase followed by a comma and a space, would be closed early. So the fix is not "use lenient mode" in general. It is "use lenient mode for this exporter and prove that it reads this exporter's output correctly".

The proof

Run three checks on the repaired read. Every row must have exactly four fields, which in DuckDB is true by construction of the schema and needs checking in a tool that does not enforce columns. The count of rows must equal the count of lines in the source minus the header, adjusted for embedded breaks, which here is 4. And the broken lines must read as expected, which you check by eye: pull out the rows whose path contains a quote character and look at them.

SELECT path FROM read_csv('weblog.csv', all_varchar=true, strict_mode=false) WHERE path LIKE '%"%';

/search?q="red shoes" size 9
/search?q=5" nails

Two rows, both as intended. On a real log with millions of lines, that query might return thousands, and reading a hundred of them tells you whether the heuristic holds. If any of them looks cut off or merged with a neighbor, the heuristic fails for some shape of line you have not thought of.

A further check catches the dangerous cases: look for fields that end with a comma followed by something that looks like the next column, and for rows where a later column, the status, is not a three-digit number. A mis-split line puts text in a numeric column. A numeric cast on the status column (TRY_CAST(status AS INTEGER)) with a count of failures is a cheap alarm.

If you cannot use DuckDB

Other parsers have similar escape hatches. Python's csv module accepts strict=True to raise on malformed input instead of guessing, which is better for finding the bad lines and does not repair them, and quoting=csv.QUOTE_NONE with a custom quote character turns quoting off, which breaks fields with delimiters. pandas has on_bad_lines for skipping or warning. A regular expression that implements the "quote ends before a comma or end of line" rule is a handful of lines and works on any language, at the cost of being your code to maintain:

import re
FIELD = re.compile(r'"((?:[^"]|"(?!,|$))*)"(?:,|$)|([^,]*)(?:,|$)')

def split_line(line):
    out, pos = [], 0
    while pos < len(line):
        m = FIELD.match(line, pos)
        out.append(m.group(1) if m.group(1) is not None else m.group(2))
        pos = m.end()
    return out

Run on the four lines, it returned the same values as DuckDB's lenient mode:

['2026-10-02T10:00:04', 'GET', '/search?q="red shoes" size 9', '200']
['2026-10-02T10:00:12', 'GET', '/search?q=5" nails', '404']

That pattern reads a quoted field as a run of characters where a quote is allowed if it is not followed by a comma or the end, which is the same heuristic. Test it on your broken lines, include a line with ", inside a field in the tests, and be ready to find that it does not handle it; I did not try that case. For anything beyond a one-off, prefer the parser option and keep the regex as a cross-check.

When no parser setting can recover the field

Some damage is not recoverable. If the quoted field contains both an unescaped quote and the delimiter, such as "/search?q=5", 6", there are two readings and the text itself does not choose. If a log line is truncated in the middle of a quoted field, there is no closing quote to find. If a field contains a newline and an unescaped quote, the record boundary is lost. For these, count the rows that fail the field-count and status checks and send them to a rejects file with their line numbers (logging rejected rows from a CSV import for review), and tell the owner of the exporter.

The real fix is at the source: configure or patch the log writer to double quotes inside quoted fields, or switch the format to one that has no such ambiguity, such as tab-separated with escaping or JSON lines. RFC 4180 states the rule in one sentence, and DuckDB documents the reader options in its CSV overview and its page on reading faulty CSV files.