Properly Escaping Strings When Converting JSON to SQL

String Escaping for SQL Safety

When converting JSON string values to SQL literals, proper escaping is essential to prevent syntax errors and SQL injection vulnerabilities. The primary concern is handling single quotes, which delimit string literals in SQL.

The Problem

{
  "name": "O'Brien",
  "bio": "She said "hello"",
  "path": "C:\\Users\\data"
}

Without escaping, the generated SQL would break:

-- BROKEN: Single quote terminates the string early
INSERT INTO users (name) VALUES ('O'Brien');

Correct Escaping

INSERT INTO users (name, bio, path) VALUES
  ('O''Brien', 'She said "hello"', 'C:\Users\data');

Escaping Rules by Database

Character	PostgreSQL	MySQL	SQL Server
Single quote '	''	'' or \'	''
Backslash \	Literal	\\\\ (if NO_BACKSLASH_ESCAPES off)	Literal
NULL byte	Not allowed	\0	Not allowed

SQL Injection Prevention

Escaping is the first line of defense against SQL injection. Consider this malicious JSON:

{ "name": "'; DROP TABLE users; --" }

With proper escaping:

INSERT INTO users (name) VALUES ('''; DROP TABLE users; --');
-- The single quote is escaped, so the entire string is treated as data

Unicode Handling

JSON natively supports Unicode via \uXXXX escape sequences. Most modern databases (with UTF-8 encoding) accept Unicode characters directly in string literals:

INSERT INTO products (name) VALUES ('Caf\u00e9 Latt\u00e9');
-- Or directly: INSERT INTO products (name) VALUES ('Cafe Latte');

Best Practice: Parameterized Queries

While the converter generates escaped SQL for convenience, production applications should use parameterized queries ($1, ?, :name) to avoid injection entirely. The generated SQL is best used for one-time imports, seeding, and debugging.