SQL Escape / Unescape

SQL string escaping explained

Why escape?

In SQL a string literal is delimited by single quotes. A single quote inside the value would terminate the literal early and either cause a syntax error or, much worse, let the rest of the value be executed as SQL. The standard way to represent a literal single quote is to double it: ''.

-- The value:      a single quote ' is offensive
select * from table where value = 'a single quote '' is offensive';

Rules applied by this tool

The standard style (ANSI SQL-92) is understood by every relational database: PostgreSQL, SQL Server, Oracle, SQLite, DB2, MySQL and MariaDB. The backslash style is a MySQL extension, equivalent to what mysqli_real_escape_string() produces; it is disabled when the NO_BACKSLASH_ESCAPES SQL mode is on, and PostgreSQL only honours it inside E'…' strings.

Before / after

Use prepared statements, not escaping

Escaping only protects string literals. It does nothing for numbers, identifiers, LIKE wildcards (% and _), multi-byte encoding tricks or a value that is later used in dynamic SQL. Every modern database driver supports parameterized queries: the SQL text and the values travel separately and the server never has to parse user data as SQL. This is the only reliable defense against SQL injection.

-- Java (JDBC)
PreparedStatement ps = con.prepareStatement("select * from t where value = ?");
ps.setString(1, userInput);

-- PHP (PDO)
$stmt = $pdo->prepare('select * from t where value = :v');
$stmt->execute(['v' => $userInput]);

-- Python (psycopg / sqlite3)
cur.execute("select * from t where value = %s", (user_input,))

-- Node.js (pg)
await client.query('select * from t where value = $1', [userInput]);

Hand escaping remains useful for values you type yourself into seed scripts, fixtures and migrations, which is exactly what this tool is for.