searches and returns with an apostrophe

[JJP]

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]]

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


"JJP" <an*******@discussions.microsoft.com> wrote in message
news:uN**************@TK2MSFTNGP09.phx.gbl...

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.