Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   ASP General (http://www.velocityreviews.com/forums/f65-asp-general.html)
-   -   searches and returns with an apostrophe (http://www.velocityreviews.com/forums/t799699-searches-and-returns-with-an-apostrophe.html)

JJP 09-30-2005 04:12 PM

searches and returns with an apostrophe
 
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] 09-30-2005 04:29 PM

Re: searches and returns with an apostrophe
 
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" <anonymous@discussions.microsoft.com> wrote in message
news:uNcBxmdxFHA.3812@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.
>
>
>





All times are GMT. The time now is 10:10 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.