SQL String Escaping and Injection Prevention

SQL String Escaping

SQL strings are delimited by single quotes. Embedding special characters — especially quotes — in SQL values requires careful escaping. More importantly, improper SQL string handling is the root cause of SQL injection, one of the most dangerous security vulnerabilities.

The Standard: Quote Doubling

The SQL standard (ISO/IEC 9075) specifies that a single quote within a string is escaped by doubling it:

SELECT * FROM users WHERE name = 'O''Brien';
-- The two adjacent single quotes represent one literal quote

This is the safest manual escaping approach and works across all major databases.

Database-Specific Escaping

Different databases have additional escaping features:

MySQL:

-- Backslash escaping (enabled by default)
SELECT 'It\'s a test';
SELECT 'Line 1\nLine 2';

-- Disable with NO_BACKSLASH_ESCAPES SQL mode

PostgreSQL:

-- Standard quote doubling
SELECT 'O''Brien';

-- E-strings for C-style escapes
SELECT E'Line 1\nLine 2';

-- Dollar quoting (avoids all escaping)
SELECT $$O'Brien's "quote"$$;

SQL Server:

-- Only quote doubling, no backslash escaping
SELECT 'O''Brien';

-- Use N prefix for Unicode
SELECT N'Unicode text: \u00e9';

SQL Injection

Constructing SQL from unescaped user input allows attackers to execute arbitrary queries:

-- If user inputs: ' OR '1'='1
SELECT * FROM users WHERE name = '' OR '1'='1';
-- Returns all rows!

Parameterized Queries (The Real Solution)

Never build SQL strings with concatenation. Use parameterized queries:

# Python (psycopg2)
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

// Node.js (pg)
client.query('SELECT * FROM users WHERE name = $1', [name]);

Parameterized queries separate code from data at the protocol level, making injection impossible regardless of the input content.

LIKE Pattern Escaping

The LIKE operator treats % and _ as wildcards. To search for literal percent or underscore, escape them:

SELECT * FROM products WHERE name LIKE '%10\%%' ESCAPE '\\';