SQL INSERT to JSON — Why Escaped Quotes Disappear
Feed a value with doubled single quotes like 'O''Brien' into a tool that converts SQL INSERT statements to JSON, and the result comes out as "OBrien". A whole apostrophe has vanished. This isn't an accidental error — it's a scheduled result of the value-splitting parser's design itself. This guide explains why this happens, based on the actual source code, and how far the fallout spreads.
1. Why SQL uses doubled quotes
In the SQL standard, to put a real apostrophe character inside a string literal wrapped in single quotes, you write that character twice in a row. So 'O''Brien' is valid SQL and actually means the single 7-character string O'Brien. This is a SQL-specific rule, different from backslash escaping (\') like in JS or Python.
2. The actual code: dissecting the splitCSVLine function
The core function this tool uses to split values works like this (quoting the actual source):
for(const c of line){if(c===','&&!inQ){res.push(cur);cur='';}else if(c==="'"||c==='"'){inQ=!inQ;}else cur+=c;}
Scanning one character at a time, when it hits a quote character (' or "), it just flips the inQ flag indicating "am I currently inside a quote," and it never adds that quote character itself to the result string (cur). So in any value, quotes never survive in the result — they disappear.
3. Following 'O''Brien' character by character
Watching this function process the input 'O''Brien' in order:
| Character | Action | inQ | cur |
|---|---|---|---|
' | flip flag | true | "" |
O | append to cur | true | "O" |
' | flip flag | false | "O" |
' | flip flag | true | "O" |
B r i e n | append to cur | true | "OBrien" |
' | flip flag | false | "OBrien" |
With all 4 quotes dropped from the result, the character that should have represented the apostrophe evaporates with them, producing "OBrien" — a 6-character string that doesn't actually exist. Silently producing a wrong value with no error is what makes this bug especially dangerous.
4. The dead "wrapped in quotes" check code
After this parser there's one more regex that checks whether a value was wrapped in quotes:
if(/^'.*'$/.test(val))val=val.slice(1,-1).replace(/''/g,"'");
This code turns '' back into ' — logic that would fix exactly this bug. But by the time this check runs, splitCSVLine has already stripped every quote, so val can't have any quotes left to begin with. As a result the /^'.*'$/ condition can never be true — it's dead code. A case where fixable logic already exists but is rendered useless by execution order.
5. Why this kind of bug is common
Perfectly parsing SQL string literals with a single regex or simple loop is harder than it looks. To handle it truly correctly you have to explicitly track the state "I'm currently inside a quote AND the character I just saw might be the first quote of an escape" — a state machine that's awkward to express with a single regex match. That's why this kind of escape-handling bug shows up repeatedly in lightweight "convert SQL to X" tools.
6. Practical approaches
- For data with fields that can contain apostrophes (O'Brien, D'Angelo, etc.) — names, addresses — always re-check the result visually after conversion.
- Don't use this tool's conversion output directly for a production data migration — use it only in a staging step for verification.
- If accurate conversion is essential, replace it with a script that uses a real SQL parser library (e.g. node-sql-parser).
Frequently Asked Questions
Q. Why does this bug happen silently, with no error?
A. Because the parser doesn't throw an exception — it just produces a different (shorter) string. "OBrien" is a perfectly valid JSON string value, so to the tool it looks like a successful conversion with no problem.
Q. Is a value wrapped in double quotes (") a problem the same way?
A. Yes. The inQ flag doesn't distinguish single from double quotes and shares one flag, so quote characters disappear the same way. And if both kinds of quote are mixed in one value, the flag can flip off at the wrong time and a comma can be mistaken for a field separator.
Q. What actually distinguishes numbers from strings?
A. It was meant to be distinguished by whether quotes were present, but that logic is dead, so in practice values are split only by whether they match the number regex (-?\d+(\.\d+)?). If it's not NULL and not a number pattern, it stays a string whether or not it originally had quotes.
Q. Is NULL handling correct?
A. Yes. NULL written without quotes (case-insensitive) is checked separately by regex and converted correctly to JSON null. This part is unaffected by the quote scanner.