How to Escape a Single Quote in SQL

-- Breaks: the literal ends at the apostrophe
INSERT INTO customers (last_name) VALUES ('O'Brien');
--                                          ^ literal closes here

-- Correct in every mainstream database: double the quote
INSERT INTO customers (last_name) VALUES ('O''Brien');

-- Stored value: O'Brien   (one apostrophe, not two)
SELECT last_name, length(last_name) FROM customers;  -- 7

-- MySQL: both of these store O'Brien under default settings
SELECT 'O''Brien', 'O\'Brien';

-- Which escapes are live right now?
SELECT @@sql_mode;

-- With NO_BACKSLASH_ESCAPES on, only the doubled form parses
SET SESSION sql_mode = CONCAT(@@sql_mode, ',NO_BACKSLASH_ESCAPES');
SELECT 'O''Brien';    -- OK
-- SELECT 'O\'Brien'; -- syntax error: backslash is now a plain character

-- Standard doubling (always correct)
SELECT 'Dianne''s horse';

-- Escape string constant: backslash escapes are enabled by the E prefix
SELECT E'line one\nline two';
SELECT E'O\'Brien';

-- Dollar quoting: nothing inside is escaped at all
SELECT $$Dianne's horse$$;
SELECT $tag$He said "it's fine" and typed C:\temp$tag$;

-- Which behaviour is active?
SHOW standard_conforming_strings;  -- on, since PostgreSQL 9.1

-- SQL Server: doubling, Unicode prefix, and generated literals
SELECT 'O''Brien', N'Michel', QUOTENAME('O''Brien', '''');
-- QUOTENAME returns: 'O''Brien'
SELECT REPLACE(@long_value, '''', '''''');  -- for values over 128 chars

-- Oracle: doubling, or a q-string with no escaping at all
SELECT 'O''Brien' FROM dual;
SELECT q'[O'Brien said "hello"]' FROM dual;

-- SQLite: doubling only
SELECT 'O''Brien';

-- Wrong: the % in the value is treated as a wildcard
SELECT * FROM codes WHERE code LIKE '%SAVE20%%';

-- Portable: nominate an escape character explicitly
SELECT * FROM codes WHERE code LIKE '%SAVE20\%%' ESCAPE '\';

-- Underscore is a single-character wildcard, so it needs escaping too
SELECT * FROM files WHERE path LIKE 'log\_2026%' ESCAPE '\';

-- SQL Server only: a bracket class also disables the wildcard
SELECT * FROM codes WHERE code LIKE '%SAVE20[%]%';

# psycopg (PostgreSQL) - %s is a placeholder, not string formatting
import psycopg

with psycopg.connect("dbname=shop") as conn, conn.cursor() as cur:
    cur.execute(
        "SELECT id, email FROM customers WHERE last_name = %s AND active = %s",
        ("O'Brien", True),          # values travel separately from the SQL
    )
    rows = cur.fetchall()

# sqlite3 (stdlib) and most MySQL drivers use ?
import sqlite3

con = sqlite3.connect("shop.db")
con.execute("SELECT id FROM customers WHERE last_name = ?", ("O'Brien",))

# Never do this, no matter how carefully you escape:
# cur.execute(f"SELECT id FROM customers WHERE last_name = '{name}'")

// node-postgres: numbered placeholders with the values in an array
const { rows } = await client.query(
  "SELECT id, email FROM customers WHERE last_name = $1 AND active = $2",
  ["O'Brien", true],
);

// mysql2/promise: execute() is a real server-side prepared statement
const [rows2] = await conn.execute(
  "SELECT id, email FROM customers WHERE last_name = ? AND active = ?",
  ["O'Brien", 1],
);

// query() interpolates client-side; still escaped, but the value ends up in the SQL text
const [rows3] = await conn.query(
  "SELECT id FROM customers WHERE last_name = ?",
  ["O'Brien"],
);

// ?? quotes an IDENTIFIER, not a value - only ever from an allow-list
const sort = ALLOWED.has(req.query.sort) ? req.query.sort : "created_at";
const [rows4] = await conn.query("SELECT id FROM customers ORDER BY ?? DESC", [sort]);