How to Escape Single Quotes in SQL Strings

Posted by Kyle Hankinson May 30, 2024

---

Single quotes delimit strings in SQL. When your data contains a single quote — like O'Brien — you need to escape it. The approach varies by database.

The Universal Method: Double the Quote

Every SQL database supports escaping a single quote by doubling it:

-- Instead of 'O'Brien' (syntax error), write:
SELECT * FROM customers WHERE last_name = 'O''Brien';

INSERT INTO customers (last_name) VALUES ('O''Brien');

Two single quotes inside a string literal produce one single quote in the result.

MySQL: Backslash Escaping

MySQL also supports C-style backslash escaping by default:

SELECT * FROM customers WHERE last_name = 'O\'Brien';

Both '' and \' work in MySQL. However, backslash escaping can be disabled with NO_BACKSLASH_ESCAPES SQL mode. The double-quote method is more portable.

PostgreSQL: Dollar-Quoted Strings

PostgreSQL supports dollar-quoting, which avoids escaping entirely:

SELECT $$It's a string with 'quotes' inside$$;

-- Or with a tag for nesting
SELECT $body$It's a string with $$dollar signs$$ and 'quotes'$body$;

This is especially useful in function bodies:

CREATE FUNCTION greet(name TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, ' || name || '! It''s nice to meet you.';
END;
$$ LANGUAGE plpgsql;

Oracle: Q-Quoting

Oracle offers the q quoting mechanism using custom delimiters:

SELECT q'[It's a string with 'quotes']' FROM dual;
SELECT q'{O'Brien's order}' FROM dual;
SELECT q'<Don't worry about 'escaping'>'' FROM dual;

The delimiter can be [], {}, <>, (), or any character that does not appear in the string.

SQL Server: No Special Escaping

SQL Server only supports the double-quote method:

SELECT * FROM customers WHERE last_name = 'O''Brien';

For building dynamic SQL, use QUOTENAME for identifiers and REPLACE for values:

DECLARE @name VARCHAR(50) = 'O''Brien';
-- QUOTENAME is for identifiers (table/column names), not string values
SELECT QUOTENAME('table name');  -- Returns [table name]

The Real Solution: Parameterized Queries

Manual escaping is error-prone and opens the door to SQL injection. Always use parameterized queries in application code:

# Python (psycopg2)
cursor.execute("SELECT * FROM customers WHERE last_name = %s", ("O'Brien",))

# NOT this (vulnerable to SQL injection):
# cursor.execute(f"SELECT * FROM customers WHERE last_name = '{name}'")

// PHP (PDO)
$stmt = $pdo->prepare("SELECT * FROM customers WHERE last_name = ?");
$stmt->execute(["O'Brien"]);

// Swift (SQLPro / GRDB)
let rows = try db.execute("SELECT * FROM customers WHERE last_name = ?", ["O'Brien"])

Manual escaping is fine for ad-hoc queries in a SQL client. For application code, parameterized queries are non-negotiable.

Quick Reference

Database	Methods
MySQL	'' or \'
PostgreSQL	'' or $$..$$ dollar quoting
SQL Server	'' only
Oracle	'' or q'[..]' q-quoting
SQLite	'' only

---