Wednesday, July 25, 2007

Escape Character In Microsoft SQL Server 2000

Here's something I was spent half a morning trying to find out: how do I escape a single quote (') in Microsoft SQL Server 2000? I first tried the most obvious character- the backslash (\), but that didn't work. Then I randomly tried a few other characters but to no avail. So I ran a Google search but most of the stuff I got was for MySQL or Oracle and they seem to accept backslashes for escaping.

I finally managed to discover that the escape character is, in fact, the single quote itself! So to insert a string with a single quote in it, you need to use an SQL query like:

INSERT INTO mytable ( randomstring) VALUES ( 'Where''s the answer?' )

So now you know. What I want to know is, why couldn't they just use a backslash like everyone else?

You could also check this article out though it didn't really work for me but I didn't try that hard.

31 comments:

Charles said...

I haven't an answer as to why, but the same goes for the double quote, if you need to use one within your string, you double it. "You ""know"" what I mean?" I was always in the habit of using the opposite quote of what I needed in the string, if I needed double quotes I'd surround the string with single quotes. But what if you need both single and double quotes? Double them up. MS is funny that way I guess.

no.good.at.coding said...

Charles, why do you need to double the double quotes? From what I've seen, double quotes can be freely used within single quoted strings; they're only used when naming columns.

Also, I love that opposite quote thing, but I've only ever seen it being used in JavaScript. Not sure it's applicable in SQL.

The more I use MS stuff, the more I wonder why they can't stick to standards instead of trying to set their own! :D

Charles said...

"why do you need to double the double quotes?" In the necessity of including a mixture of quotes in a string. If you need to pass a string containing a string for example. No matter which quotes you used to form the original string, you'd have to pass the doubled character in order to allow the internal quotes. Say you were looking for a field that contained a quote from a famous author, you might actually pass your query with that quote in it, but in order to do that, you might need to double them up. There's always something weird you'll be asked to do, something out of the norm, but you don't have to over think it this way.

no.good.at.coding said...

Actually, I don't think you need to with SQL Server. This is definitely a problem with languages like JavaScript where you could have strings enclosed in either type of quotes.

But I checked it out, with SQL Server, only single quoted strings are valid and they can contain double quotes without needing to be doubled.

Wolfestine said...

Hey... Even Oracle uses the same escape character... ie a single quote (') n not a backslash (\).

I tried using the backslash n it says :
Quoted string not properly terminated.

Parasuram....Let Truth Be Your Guide said...

Thanks 4 the blog man saved me many hours of running abt thanks again

no.good.at.coding said...

Glad to have been of help :)

Vishal said...

THANK YOU! Was close to spending all evening on this till I found your post.

no.good.at.coding said...

You're welcome, Vishal. Glad it helped. :)

Deepthi said...

Glad that I found your post in first short. Thanks!

Is there anything in MSSQL which says to ignore all the characters between xx to yy. Wherein the characters that go in between can have anything either quote or double quote?

oyen said...

That post saved me my weekend. Thanks a bunch!

PTS said...

thanks a lot ....

SaidK said...

Ahhhh, thanks for the help. I had this same problem. I posted a little function to do this now and cited you as my source here: http://phireitup.blogspot.com/2008/11/escape-character-in-mssql.html Thanks!

Anonymous said...

You spent half a morning figuring this out? You really are no good at coding ;-)

no.good.at.coding said...

@SaidK: Glad you found it useful :)

@Anonymous: Ouch!

Jake said...

SQL Server allows you to define your own escape character. If you add the following to the end of your query:

ESCAPE '\'

You can then use the backslash to escape any characters you need.

Ben said...

ESCAPE only works combined with LIKE such as:
LIKE '\'' ESCAPE '\'

For other purpuses on the Microsoft SQL SERVER you have to stick to single-quote.

Anonymous said...

As long as you are sending data to SQL Servere, you can write a routine that places brackets [] around whatever special character yuo want to send in your query and SQL Server will treat it as text.

Example -> select * from table
where criteria = 'string;string'

Replace With -> select * from table
where criteria = 'string[;]string'

This works for ANY characters.

Hope this helps..

Joel Coehoorn said...

> "why couldn't they just use a backslash like everyone else?"

Because everyone else doesn't do that. The sql spec says a quote character is escaped with itself. The only system that allows a \ is MySQL, and it's a potential security risk even there.

But the whole thing is moot. If you're escaping values from the user for your sql data you've already lost. You should be using parameterized queries/prepared statements instead.

Anonymous said...

select char(34) + 'my way' +
char(34)


--------
"my way"

(1 row(s) affected)

alan said...

cause MSSQL is stupid.

Sean said...

Nice tip! I was Google trying to find out why the \ backslash didn't work and now I know why... another reason I don't like MS SQL!

- Sean
Gallagher Website Design

Smart Parenting said...

Thanks for this. I've been working for this for almost a day.

Official Movie Trailer said...

I just did something like this and didn;t work.

INSERT INTO mytable ( randomstring) VALUES ( "+strValue.Replace("'","''")+" )

Prakash M. Nadkarni said...

Read the excellent article:
http://unixwiz.net/techtips/sql-injection.html

The use of doubled single quotes, as opposed to \', prevents SQL Injection attacks.

Simeon Potts said...

Escaping a single quote is not completely moot. What if you need to generate a SQL script that will insert a bunch of strings into a table? parameterized queries is not the answer to every problem.

adil said...

Thanks for the post. Can't believe how hard is was to find the sql quote escape character!

Bart said...

I use ` (backtick) to replace the normal ' to avoid problems with earlier set ESCAPE in unknown MS SQL databases. Usually my customers find that a reasonable solution.

Cristina said...

Thank you!

George said...

Everyone should review what Jake and Ben said in 2009. You can define your escape character, but you can only use it with a LIKE clause. The sad thing is that the OP even provides a link to a page that spells out the answer...
SELECT columns FROM table WHERE
column LIKE '%\%%' ESCAPE '\'

Chris Sebrell said...

When inserting strings with a single quote, you need to double them up. I also found that if you have a line ending with a backslash followed by a CR+LF, that you need to escape that too. This is what I use:

Public Function SQLSTR(ByVal szText As String) As String

Return szText.Replace("'", "''").Replace("\" & vbCrLf, "\\" & vbCrLf & vbCrLf)

End Function