JSON to SQL: Parameters and Safe Literal Escaping

1. Problem: why concatenation breaks SQL

A JSON string may contain a single quote such as O'Reilly. Placing it directly inside a quoted SQL literal closes that literal early. If input can influence an executed statement, concatenation also mixes data with SQL syntax; this is not a JSON.parse issue.

2. Minimal reproduction: PostgreSQL parameters are not concatenation

The first string only demonstrates the boundary and must not be sent to a database. PostgreSQL parameters use 1 and 2, with values encoded by the driver.

const user = { name: "O'Reilly", active: true };
const unsafe = "INSERT INTO users (name, active) VALUES ('" + user.name + "', " + user.active + ")";
console.log(unsafe);
// INSERT INTO users (name, active) VALUES ('O'Reilly', true)
const text = "INSERT INTO users (name, active) VALUES ($1, $2)";
const values = [user.name, user.active];
// PostgreSQL driver: await client.query(text, values)

3. Root cause: SQL syntax and values need separate layers

Parameterized queries keep a SQL template as code and values as data; the driver sends them through PostgreSQL's protocol. A quote replacer is only a dialect-specific literal rule. It cannot handle identifiers, types, or dynamic SQL structure and is not a substitute for parameters.

4. Recommendation: parameters for writes, literals only for exports

Use a fixed statement and bound values for application writes. Do not let JSON keys become unchecked column names. An offline PostgreSQL export may use a restricted literal encoder, but the result must be consumed as PostgreSQL text. Dynamic identifiers require an allow-list mapping.

5. Complete code: parameterized write and PostgreSQL literals

This TypeScript receives a driver adapter instead of inventing a database connection. toPgLiteral is for offline text only; it rejects non-finite numbers and backslash text, which should be handled by a driver or an export tool with explicit PostgreSQL string settings.

type Query = (text: string, values: readonly unknown[]) => Promise<unknown>;
async function insertUser(query: Query, user: { name: string; active: boolean | null }) {
  return query("INSERT INTO users (name, active) VALUES ($1, $2)", [user.name, user.active]);
}
function toPgLiteral(value: unknown): string {
  if (value === null) return "NULL";
  if (typeof value === "boolean") return value ? "TRUE" : "FALSE";
  if (typeof value === "number") { if (!Number.isFinite(value)) throw new TypeError("numeric literal must be finite"); return String(value); }
  const text = typeof value === "string" ? value : JSON.stringify(value);
  if (text === undefined) throw new TypeError("Value is not JSON-serializable");
  if (text.includes("\\")) throw new TypeError("Backslash requires explicit PostgreSQL literal settings");
  return "'" + text.replaceAll("'", "''") + "'";
}
console.log(toPgLiteral("O'Reilly")); // 'O''Reilly'
console.log(toPgLiteral(null)); // NULL

6. Incorrect approaches

Quoting every value turns SQL NULL into the string NULL and changes boolean and numeric semantics. A quote replacer is not portable across MySQL, PostgreSQL, and SQLite. Parameterizing values also does not make unchecked table or column names safe; identifiers need an allow-list.

7. Boundaries: undefined, JSON values, and dialects

JSON has no undefined; decide whether a missing property omits a column or becomes SQL NULL. NaN and Infinity are not standard JSON numbers. PostgreSQL jsonb columns need an explicit encoding policy. The export helper creates a text literal, not a guarantee that every target column accepts that value.

8. Verification

Run parameterized tests with quotes, backslashes, newlines, NULL, booleans, and Unicode, and assert the stored values. For offline text, assert O'Reilly becomes O''Reilly and NULL is unquoted. Run the script against the declared PostgreSQL dialect rather than another engine.

9. FAQ

Does doubling a quote prevent injection? It is a dialect-specific literal rule; application writes still need parameters.

Can parameters bind table names? Usually not; map identifiers through a fixed allow-list.

Why are null and 'null' different? The former is SQL NULL; the latter is a four-character string.

10. Summary

For PostgreSQL application writes, use a fixed statement with $1-style parameters. Doubling quotes is limited to explicit offline literal export and cannot secure dynamic identifiers. Test quotes, NULL, booleans, numbers, and Unicode against the declared dialect.