Why escape text in SQL?

Escaping SQL is necessary when you want to include user-provided data in SQL queries to prevent SQL injection attacks. SQL injection occurs when malicious SQL code is inserted into a query, potentially allowing attackers to access, modify, or delete data in your database.

Examples of SQL escaping

Original text

SELECT * FROM users WHERE username = 'john' AND password = 'p@ss''word';
INSERT INTO messages (sender, content) VALUES ('O''Reilly', 'Hello, this is a "test" message');
UPDATE products SET description = 'This product's name is "Super Product"' WHERE id = 123;
DELETE FROM logs WHERE entry LIKE '%error%' AND timestamp < '2023-01-01';
-- This is a comment with special chars: ', ", ,

Escaped text (standard)

SELECT * FROM users WHERE username = ''john'' AND password = ''p@ss''''word'';
INSERT INTO messages (sender, content) VALUES (''O''''Reilly'', ''Hello, this is a "test" message'');
UPDATE products SET description = ''This product''s name is "Super Product"'' WHERE id = 123;
DELETE FROM logs WHERE entry LIKE ''%error%'' AND timestamp < ''2023-01-01'';
-- This is a comment with special chars: '', ", ,

SQL Escaping by Dialect

Standard SQL

In standard SQL, single quotes are escaped by doubling them:

Single quote (') becomes ''

Example: 'O''Reilly'

MySQL

MySQL uses backslash escaping for special characters:

Single quote (') becomes \'
Double quote (") becomes \"
Backslash (\) becomes \\
Newline (\n) becomes \\n
Carriage return (\r) becomes \\r
Tab (\t) becomes \\t
Null byte (\0) becomes \\0
Ctrl+Z (\x1a) becomes \\Z

Example: 'O\'Reilly'

PostgreSQL

PostgreSQL primarily uses doubled single quotes for escaping:

Single quote (') becomes ''

Example: 'O''Reilly'

Note: PostgreSQL also supports the E'' syntax for strings with escape sequences, but this tool focuses on the standard escaping method.

SQL Server (MSSQL)

SQL Server uses doubled single quotes for escaping:

Single quote (') becomes ''

Example: 'O''Reilly'

When to use SQL escaping

When building SQL queries that include user input
When working with data that contains special characters like quotes
When generating SQL scripts programmatically
When storing text that contains SQL special characters in a database

Security Note:

While escaping SQL is important, using parameterized queries or prepared statements is generally a safer approach to prevent SQL injection. This tool is useful for educational purposes and for cases where parameterized queries are not an option.