Characters that must be escaped in T-SQL

Question

I was looking for a list of special characters that must be escaped in ms sql server but could not find one and most of answers I saw for the similar questions advised to use Parameterised queries.. which I am already doing but the framework I am using does not does any escaping for me.

Thus I thought I will give a bunch of those a try and see which one fails.... and I tried a simple query

select * from x where value = '<characters>'

in such query I tried almost all the characters I could find on my keyboard and all of them seem to work... besides the Singe Quote.. that one fails.

Thus I want to know the list of characters that are invalid and must be escaped in ms sql server - tsql and do not want to take the risk of just escaping the single quote and leave the rest that could cause trouble

Appreciate your help

they behave differently in different frameworks... not working for me — Hell Boy, May 07 '12 at 03:14

score 38 · Accepted Answer · edited May 23 '17 at 12:10

38

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.

edited May 23 '17 at 12:10

Community

1
1

answered May 07 '12 at 03:16

moribvndvs

42,191
11
135
149

Thanks HackedByChinese, that was the only confirmation I was after ! – Hell Boy May 07 '12 at 03:22
7

First time I've ever seen manual query escaping likened to pulling out... lol. – Corbin May 07 '12 at 03:23
Thanks to Apache Cayenne for this ! – Hell Boy May 07 '12 at 03:24
If it is true that the only thing that ever needs escaping is the single quote (what about ascii control characters and \0?) then using or not using parameterized queries is not really that important, is it? You could write your own custom parameterized query formatter with proper escaping in 15 minutes (in a dynamic language). – jpc Jul 09 '14 at 19:31
What about the back slash \ ?, in MySql, I have to escape it just like the single quote. – user2747502 Oct 30 '14 at 17:37
Also known as the apostrophe. – defines Mar 12 '21 at 15:48

score 2 · Answer 2 · answered Oct 14 '16 at 08:16

I just wanted to say that _ (underscore) also needs to be escaped.

select * from Products where SomeColumn like 'FD[_]%'

By the way, nothing wrong with parameterized queries, but sometimes you want to go to the SQL management console and quickly run a query to find something out.

score 0 · Answer 3 · edited Aug 22 '16 at 19:12

0

Not sure this is accurate.

% and . and other wildcards depending on the query, may also need escaping. where you are looking for a dot. This will fail

select * from xxxx where field like '%.%'

edited Aug 22 '16 at 19:12

Tunaki

132,869
46
340
423

answered Aug 22 '16 at 14:49

Sven Hansen

9
2

1

Please don't vandalize your answer. If you believe it is are incorrect and want to delete it, click the "delete" link below your post. – Tunaki Aug 22 '16 at 19:12

Characters that must be escaped in T-SQL

3 Answers3

Linked