AI & Agent Dev Bug Sandbox logo
AI & Agent Dev Bug Sandbox
Back to Radar

Copy/Export Rows As SQL Corrupts Json/Jsonb Values With Quotes Or Backslashes

The Table Editor's 'Copy rows as SQL' and 'Export table as SQL' features produce invalid SQL or silently corrupt JSON/JSONB data due to incorrect string escaping in the json branch of formatTableRowsToSQL.

criticalConfidence 95%React

Origin Analysis

The json branch uses a chain of replace calls that incorrectly doubles backslashes and mangles double quotes, resulting in malformed SQL string literals. The correct approach is to treat the JSON value as plain text and only escape single quotes for the SQL string literal.
1. Create a table with a jsonb column. 2. Insert rows containing double quotes inside strings and backslashes (e.g., jsonb_build_object('note', 'say "hi"') and jsonb_build_object('path', 'C:\\Users\\me')). 3. In the Table Editor, select the rows and choose 'Copy rows as SQL'. 4. Observe that the generated INSERT for the row with double quotes is invalid and fails to reimport, and the row with backslashes has every backslash doubled, causing silent data corruption.

Fixing Code Block

Edge Case Audit

The fix assumes standard_conforming_strings is enabled (PostgreSQL default). If a user has disabled it, backslashes would need additional escaping, but this is uncommon. After applying the fix, any previously generated broken exports must be re-exported to obtain correct data. Rolling back to the previous version would reintroduce the corruption bug. Additionally, the function assumes the input is either a valid JSON string or an object that can be stringified; invalid input may still produce incorrect output, but that is outside the scope of this fix.

Ecosystem Topology