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:
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.
34 comments:
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.
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
"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.
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.
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.
Thanks 4 the blog man saved me many hours of running abt thanks again
Glad to have been of help :)
THANK YOU! Was close to spending all evening on this till I found your post.
You're welcome, Vishal. Glad it helped. :)
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?
That post saved me my weekend. Thanks a bunch!
thanks a lot ....
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!
You spent half a morning figuring this out? You really are no good at coding ;-)
@SaidK: Glad you found it useful :)
@Anonymous: Ouch!
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.
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.
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..
> "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.
select char(34) + 'my way' +
char(34)
--------
"my way"
(1 row(s) affected)
cause MSSQL is stupid.
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
Thanks for this. I've been working for this for almost a day.
I just did something like this and didn;t work.
INSERT INTO mytable ( randomstring) VALUES ( "+strValue.Replace("'","''")+" )
Read the excellent article:
http://unixwiz.net/techtips/sql-injection.html
The use of doubled single quotes, as opposed to \', prevents SQL Injection attacks.
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.
Thanks for the post. Can't believe how hard is was to find the sql quote escape character!
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.
Thank you!
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 '\'
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
Thank's so much. This was driving me crazy.
Evelina
Excellent post. You have shared some wonderful tips. I completely agree with you that it is important for any blogger to help their visitors. Once your visitors find value in your content, they will come back for more How to get back contacts from google drive
Post a Comment