April 6, 2011 at 11:32 pm
I am looking for a list of all the characters that need a '\' in front of them in order to prevent a SQL error such as ? , " ' etc. I am shocked I can not just Google it. Where do I find this type of documentation?
April 6, 2011 at 11:34 pm
What are you trying to escape the characters in? A LIKE expression in the WHERE clause? If so check the BOL documentation: http://msdn.microsoft.com/en-us/library/ms179859.aspx
Though I haven't seen the need to escape ? or ", and there isn't a default escape character, you have to specify it.
Maybe if you can provide an example of the problem you are running into it would be easier to help you.
April 6, 2011 at 11:53 pm
I have insert statement I will attach below. Sometimes funny characters come in and cause my insert to fail. I want to know what those characters are so I can kill them with '\'
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'That wasnt fair - putting the $10 back into my bag when I wasnt looking...','Saturday 01 January 2011 ','15:26:02')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Hahha yea I wanted to slip it in your pocket but you had none ','Saturday 01 January 2011 ','15:55:41')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Finish the salad','Saturday 01 January 2011 ','15:55:52')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'My sister and I devoured it as soon as we got home lol','Saturday 01 January 2011 ','15:59:25')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I didnt realize how hungry I was until we started eating','Saturday 01 January 2011 ','16:02:25')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You kept asking for a steak ','Saturday 01 January 2011 ','16:02:42')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'I have steaks','Saturday 01 January 2011 ','16:02:58')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'I have steaksLike 4 times a week','Saturday 01 January 2011 ','16:02:58')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You said salad I want real food hahaha','Saturday 01 January 2011 ','16:03:24')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'I love steak... Well I just love food in general','Saturday 01 January 2011 ','16:04:03')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'You going out again tonight ','Saturday 01 January 2011 ','16:05:56')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Lol I like salad but its leaves I wanted realllll food. Yeah actually Matt invited Larisa out to some party tonight so she wants Kate and I to go with her','Saturday 01 January 2011 ','16:07:29')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Ya Ill be there as well he wants to hop around the city hes usually pretty','Saturday 01 January 2011 ','16:09:30')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',1,0,'Ya Ill be there as well he wants to hop around the city hes usually prettyGood at planning shit so will see ','Saturday 01 January 2011 ','16:09:30')
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'Kate said shell go if her feet arent too bruised up haha','Saturday 01 January 2011 ','16:10:24')
April 7, 2011 at 12:01 am
sepich.eric (4/6/2011)
I have insert statement I will attach below. Sometimes funny characters come in and cause my insert to fail. I want to know what those characters are so I can kill them with '\'
First I don't think SQL server uses \ as an escape character, in fact I don't think it even has one for normal use.
As far as I know the only character you have to worry about is ', a single quote, which you would have to double, i.e. ''. For example if you have a column with the name O'Hare in it:
INSERT INTO sampletext(Usr,Send,Receive,Message,Dte,Tme) VALUES('Edita',0,1,'That wasnt fair for O''Hare to put the $10 back into my bag when I wasnt looking...','Saturday 01 January 2011 ','15:26:02')
But this sounds like you are generating dynamic SQL in an application which is very dangerous if you don't 100% trust the data source. (Have you seen all of the news lately about sites infected through SQL injection attacks?)
You should really call a stored procedure with parameters, then you don't have to worry about escaping characters or SQL injection. (At least not as much.)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply