Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > searches and returns with an apostrophe

Reply
Thread Tools

searches and returns with an apostrophe

 
 
JJP
Guest
Posts: n/a
 
      09-30-2005
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.



 
Reply With Quote
 
 
 
 
Ray Costanzo [MVP]
Guest
Posts: n/a
 
      09-30-2005
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorted Returns List and Reversed Returns Iterator ++imanshu Python 7 08-23-2008 04:25 AM
Ruby, MySQL, and apostrophe problem Mark Adkins Ruby 4 02-12-2008 05:45 AM
createImage sometime returns null and sometime returns non-null. vizlab Java 3 10-17-2007 11:21 AM
block returns and hash element returns Trans Ruby 2 11-06-2005 12:15 PM
DB insert problems with apostrophe in a Surname and Datetime value =?Utf-8?B?Z2VvZGV2?= ASP .Net 1 10-20-2005 04:25 PM



Advertisments