Characters That Must Be Escaped in T-Sql

Characters that must be escaped in T-SQL

The only character that needs escaping in a string is a single quote (which is done with two single quotes together). Otherwise, it's a string and t-sql will fuss with it no further.

If you're using a LIKE statement, see this SO topic Escape a string in SQL Server so that it is safe to use in LIKE expression

As an aside, any framework that doesn't let me use parameters, that doesn't properly escape stuff for me, is a hard stop. Trying to sanitize string input manually is like relying on the pull out method; eventually it's gonna get you.

Escape a string in SQL Server so that it is safe to use in LIKE expression

To escape special characters in a LIKE expression you prefix them with an escape character. You get to choose which escape char to use with the ESCAPE keyword. (MSDN Ref)

For example this escapes the % symbol, using \ as the escape char:

select * from table where myfield like '%15\% off%' ESCAPE '\'

If you don't know what characters will be in your string, and you don't want to treat them as wildcards, you can prefix all wildcard characters with an escape char, eg:

set @myString = replace( 
                replace( 
                replace( 
                replace( @myString
                ,    '\', '\\' )
                ,    '%', '\%' )
                ,    '_', '\_' )
                ,    '[', '\[' )

(Note that you have to escape your escape char too, and make sure that's the inner replace so you don't escape the ones added from the other replace statements). Then you can use something like this:

select * from table where myfield like '%' + @myString + '%' ESCAPE '\'

Also remember to allocate more space for your @myString variable as it will become longer with the string replacement.

Escape Character in SQL Server

To escape ' you simly need to put another before: ''

As the second answer shows it's possible to escape single quote like this:

select 'it''s escaped'

result will be

it's escaped

If you're concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then I'd recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you don't have to worry about escaping quotes like this (which you do by doubling up the quotes).

e.g. instead of doing

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = ''AAA'''
EXECUTE(@SQL)

try this:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = @Field1'
EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'

What characters have to be escaped to prevent (My)SQL injections?

The MySQL manual page for strings says:

• \0   An ASCII NUL (0x00) character.
• \'   A single quote (“'”) character.
• \"   A double quote (“"”) character.
• \b   A backspace character.
• \n   A newline (linefeed) character.
• \r   A carriage return character.
• \t   A tab character.
• \Z   ASCII 26 (Control-Z). See note following the table.
• \\   A backslash (“\”) character.
• \%   A “%” character. See note following the table.
• \_   A “_” character. See note following the table.

T-SQL special characters to escape for LIKE operator wildcard search

It looks like you got them all, although I think escaping ']' is unnecessary. Technically you should just need to escape the opening bracket ('[').

DECLARE @Table1 TABLE
(
   Column1 VARCHAR(32) NOT NULL PRIMARY KEY
);

INSERT @Table1(Column1)
VALUES
   ('abc%def'),
   ('abc_def'),
   ('abc[d]ef'),
   ('abc def'),
   ('abcdef');

DECLARE @p VARCHAR(32) = 'abc*]*';

DECLARE @Escaped VARCHAR(64) = REPLACE(@p, '[', '[[]');
SET @Escaped = REPLACE(@Escaped, '_', '[_]');
SET @Escaped = REPLACE(@Escaped, '%', '[%]');
SET @Escaped = REPLACE(@Escaped, '*', '%');

SELECT T.Column1
FROM @Table1 T
WHERE T.Column1 LIKE @Escaped;

---

Related Topics