SQL Escape

SQL escape for string literals

String literals in SQL are wrapped in single quotes, so a name like O'Brien ends the string early and breaks the statement. A SQL escape fixes that so the value can be pasted into an INSERT, UPDATE or WHERE clause of a manual script, migration or seed file.

Important: this is not a defence against SQL injection

In application code, always use parameterised queries (prepared statements) — ? or :name placeholders with PDO, JDBC, SqlCommand parameters, psycopg or your ORM. The database driver then sends values separately from the SQL text, which is safe regardless of what the value contains. Hand escaping is easy to get wrong for a particular dialect, connection character set or context (identifiers, LIKE patterns, numbers), so keep this tool for one-off scripts you write and review yourself.

The two dialects

Standard SQL

The SQL standard, PostgreSQL (with the default standard_conforming_strings = on), SQL Server, Oracle and SQLite all escape a single quote by writing it twice. Nothing else changes; backslashes and double quotes are ordinary characters.

Input:

It's O'Brien's "C:\temp" file

Standard SQL:

It''s O''Brien''s "C:\temp" file

MySQL (backslash)

MySQL and MariaDB, unless the NO_BACKSLASH_ESCAPES SQL mode is enabled, treat the backslash as an escape character. This mode mirrors mysql_real_escape_string: it escapes ', ", \, NUL (\0), line feed (\n), carriage return (\r), backspace, tab and Ctrl+Z (\Z).

It\'s O\'Brien\'s \"C:\\temp\" file

Note how the backslash in the Windows path is doubled: in MySQL a single backslash would otherwise swallow the next character. That is also why running MySQL-escaped text against PostgreSQL, or vice versa, stores stray backslashes in your data.

Dialect features that avoid escaping

Doubling quotes works everywhere, but some databases offer alternatives that are easier to read in long scripts:

• PostgreSQL dollar quoting: $$It's O'Brien's file$$ needs no escaping at all, which is handy for function bodies and text containing many quotes. A tag such as $body$…$body$ helps when the text itself contains $$.
• PostgreSQL escape strings: E'It\'s\n' turns on backslash escapes for that one literal, even with standard_conforming_strings on.
• SQL Server Unicode literals: prefix with N, as in N'Café O''Brien', when inserting into nvarchar columns; without it, characters outside the database's code page can be replaced with question marks.

Whichever form you use, the escaped text from this tool goes between the quotes, so a seed row becomes INSERT INTO authors (name) VALUES ('O''Brien');.

What is not escaped

• Identifiers. Table and column names use "double quotes" in standard SQL, `backticks` in MySQL and [brackets] in SQL Server.
• LIKE wildcards. % and _ keep their meaning inside LIKE; escape them with an ESCAPE clause if you need them literally.

Tidy your finished script with the SQL Formatter, or reverse escaping with SQL Unescape. The tool runs entirely in your browser.