searches and returns with an apostrophe -- inetserver asp general

JJP
9/30/2005 11:12:04 AM

hi,
I am searching a SQL database from an ASP page.
When the user enters criteria with an apostrophe in it, result set is empty
when there should be records.

For example, the SQL database contains the record Children's Museum
When a search is done without an apostrophe i.e. "children", the record is
returned.
When a search is done with an apostrophe i.e. "children's", the record is
NOT returned.

Here is the code:

sql="SELECT OrgName, City, State FROM tblCharReg WHERE (OrgName LIKE '%" &
Srchvarf & "%') ORDER BY OrgName"

"Srchvarf" is a variable that holds OrgName that the user enters

Thanks in advance.

Ray Costanzo [MVP]
9/30/2005 12:29:17 PM

And what happens when the person enters this search string? (DON'T TRY IT.)

'; DROP TABLE tblChargReg

The way a ' is escaped in SQL is by doubling at up. At an absolute minimum,
handle that character.

Srchvarf = Replace(Srchvarf, "'", "''")

REad about SQL injection.

Ray at work

[quoted text, click to view]

inetserver asp general : searches and returns with an apostrophe