January 12, 2011 at 10:58 am
We were recently upgraded to a SQL 2008. In the past, if I wanted to search for text that inadvertently contained something SQL would recognize as a command, I would contain it in double quotations: For example select * from table where column 1 = ' "Dad's" ' (spaces just for viewing purposes)
and that would return it. Now if I do that, it recognizes the apostrophe as the end of the field. Is there an easy way to deal with this?
Thanks,
Teena
January 12, 2011 at 11:08 am
I'm not sure how double quotes ever worked, but there are a couple of options. Here are two: 1. using teh CHAR function and 2. using the double-up method:DECLARE @T TABLE (Val VARCHAR(50))
INSERT @T (Val)
VALUES
('Dad''s'),
('Billy'),
('Sams'''),
('''Frank'),
('Jane')
SELECT *
FROM @T
WHERE Val LIKE '%' + CHAR(39) + '%'
SELECT *
FROM @T
WHERE Val LIKE '%''%'
January 12, 2011 at 11:18 am
It sounds like you're working on preventing SQL injection in dynamic SQL. Is that correct?
If so, have you looked into using sp_executesql for parameterized queries instead of executing a concatenated string directly?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 11:28 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply