Escaping special characters in search string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Escaping special characters in search string

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-03-16 : 06:06:50

I'm creating a telephone directory application for our company intranet and I want people to be able to search on all fields including surname.
Currently, I've got a query which is something like this (this is a simplified version):

ELSE IF @SearchTerm LIKE '[A-Z]%'
BEGIN
-- do search on text fields
SELECT * 
FROM TelephoneList tl
WHERE 
tl.Surname like '%' + @SearchTerm + '%' OR
tl.Forename like '%' + @SearchTerm + '%'

. . where @SearchTerm is whatever comes out of my text box in ASP.net but what if someone wants to know the number for an employee called O'Neill or O'Connor. The apostrophe is going to casue my SQL query to fail.

Has anyone any suggestions for how to overcome this?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 06:18:35

Replace single apostrophes with 2 apostrophes in the string beore you assign it your variable.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-16 : 06:25:01

quote:

. . where @SearchTerm is whatever comes out of my text box in ASP.net but what if someone wants to know the number for an employee called O'Neill or O'Connor. The apostrophe is going to casue my SQL query to fail.

So you aren't doing any sanitisation of the input? AWOOGA! AWOOGA!

Try this string in the text box and you'll see why that's an awful plan

HINT -- DON'T DO THIS ON YOUR PRODUCTION ENVIORNMENT!!!!!!


foo'; DROP TABLE TelephoneList; --

HINT -- DON'T DO THIS ON YOUR PRODUCTION ENVIORNMENT!!!!!!

And maybe then you should go read about SQL INJECTION

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-03-16 : 06:34:20

Point taken. I've not got very far with coding the thing just yet, and it is my first development project, but thanks for the hint.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-16 : 07:54:07

Glad you see the problem:

Might want to check out this fantastic resource for all things dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html

It will show you how to (and probably more important: how NOT to) use dynamic sql safely and efficiently.

All the best,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources