For an instructor lead, in-depth look at learning SQL click below.
Escaping special characters in a string is an issue that often comes up when you are working with SQL. The special characters like single quote (”), double quote (“”), backslash (\) and null (NUL) need to be escaped to prevent SQL injection attacks and to maintain data integrity.
What are Special Characters?
Special characters are those characters that have a special meaning in SQL. Some of these characters are used to perform certain operations, while some others are used to include white spaces, new lines, or other string-related operations.
Escaping Special Characters
In SQL, to escape a single quote, double it inside a string. You need to do this in order to prevent injection attacks and syntax errors. For example:
|
1 2 3 4 5 |
DECLARE @MyString NVARCHAR(100); SET @MyString = 'It''s a beautiful day!'; SELECT @MyString AS 'EscapedString'; |
In the above example, ‘It”s’ becomes ‘It’s’. Note that you have to use two single quotes (”) not a double quote (“), this because double quotes in SQL Server are used to identify names of tables or columns, not string literals.
Escaping Wildcard Characters
In SQL, wildcard characters used with the LIKE operator should also be escaped if they need to be searched in text. The wildcards are _, % and []. An example on how to escape them:
|
1 2 3 |
SELECT column FROM table WHERE column LIKE '%\%%' ESCAPE '\'; |
This query will search for the percentile sign (%) in the text of the column.
Conclusion
Knowing how to properly escape special characters in SQL is an essential skill for anyone working with this language. Escaping these characters correctly prevents errors while executing your queries and protects your data by preventing SQL injection attacks.