Escape a single quote in SQL

'O''Brien' is the string O’Brien in SQL: inside a single-quoted literal, two single quotes stand for one. That’s the SQL standard’s rule, and PostgreSQL, MySQL, SQL Server, SQLite and Oracle all follow it. The tool below, on SQL (standard), doubles the quote the same way: O''Brien.

SELECT 'O''Brien' AS name;
--   name
-- ---------
--  O'Brien

Double quotes don’t make a string in standard SQL: "O'Brien" would be a column name.

MySQL’s backslashes

MySQL also reads a backslash as an escape, so 'O\'Brien' works there too, and a backslash in the data changes meaning:

SELECT 'O''Brien' AS doubled, 'O\'Brien' AS backslashed;
SELECT 'C:\temp' AS path, HEX('C:\temp') AS hex;

+---------+-------------+
| doubled | backslashed |
+---------+-------------+
| O'Brien | O'Brien     |
+---------+-------------+
+--------+--------------+
| path   | hex          |
+--------+--------------+
| C:	emp | 433A09656D70 |
+--------+--------------+

The 09 is a tab: \t turned into one. With NO_BACKSLASH_ESCAPES in sql_mode, a backslash is an ordinary character, as the standard has it:

SET SESSION sql_mode = CONCAT(@@sql_mode, ',NO_BACKSLASH_ESCAPES');
SELECT 'C:\temp' AS path, HEX('C:\temp') AS hex;
-- C:\temp | 433A5C74656D70

Without that mode, a backslash in the data has to be doubled as well. That’s what the tool’s SQL (MySQL) adds: It's in C:\temp becomes It''s in C:\\temp, which MySQL reads back as It's in C:\temp.

PostgreSQL’s E’’ strings

PostgreSQL has kept backslashes literal in plain strings since 9.1 made standard_conforming_strings default to on, so 'C:\temp' is C:\temp. Backslash escapes work only in escape strings, with an E before the quote. Dollar quoting needs no escaping at all:

SELECT E'It\'s\tready';   -- It's    ready  (with a tab)
SELECT $$It's "ready"$$;   -- It's "ready"

Use parameters instead

Escaping depends on the server’s settings, which is how it fails. Doubling quotes alone, as SQL (standard) does, is safe in PostgreSQL but not in MySQL’s default mode. Take the input \' OR 1=1 -- and double its quote:

SELECT name FROM users WHERE name = '\'' OR 1=1 -- ';

PostgreSQL reads one string and finds nobody. MySQL reads \' as an escaped quote, so the next ' ends the string, OR 1=1 matches everything and -- comments out the rest: with alice and bob in the table, it returned both. SQL (MySQL) escapes the backslash too, giving \\'' OR 1=1 -- , and MySQL then reads one string and matches nobody. But that still depends on knowing the server’s mode.

A parameter never passes through the SQL parser. With psycopg 3 against PostgreSQL:

import psycopg

with psycopg.connect("host=localhost user=postgres password=x") as conn:
    conn.execute("INSERT INTO users (name) VALUES (%s)", ("O'Brien",))
    for name in ["O'Brien", "\\' OR 1=1 -- "]:
        rows = conn.execute("SELECT name FROM users WHERE name = %s", (name,)).fetchall()
        print(name, rows)

O'Brien [("O'Brien",)]
\' OR 1=1 --  []

The server log shows what arrived: SELECT name FROM users WHERE name = $1, with the value sent separately. That ran on PostgreSQL 18.6 with psycopg 3.3.6; the MySQL lines ran on MySQL 8.4.11. Parameters cover values only, so a table or column name must come from a fixed list or psycopg.sql.Identifier.