searches and returns with an apostrophe

Discussion in 'ASP General' started by JJP, Sep 30, 2005.

  1. JJP

    JJP Guest

    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.
     
    JJP, Sep 30, 2005
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:...
    > 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], Sep 30, 2005
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Antonio Maciel

    Full-text searches and ASP.NET

    Antonio Maciel, Jun 27, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    532
    samham
    Jun 28, 2003
  2. =?Utf-8?B?Z2VvZGV2?=
    Replies:
    1
    Views:
    2,247
    Alan Samet
    Oct 20, 2005
  3. vizlab
    Replies:
    3
    Views:
    4,547
    Michael Bar-Sinai
    Oct 17, 2007
  4. Ray Wesley Kinserlow Jr.

    Searches and websites

    Ray Wesley Kinserlow Jr., Nov 23, 2003, in forum: HTML
    Replies:
    5
    Views:
    599
    kayodeok
    Nov 24, 2003
  5. javaman

    Lucene and learning searches

    javaman, Jun 6, 2006, in forum: Java
    Replies:
    2
    Views:
    411
    Chris Uppal
    Jun 7, 2006
  6. ++imanshu
    Replies:
    7
    Views:
    713
    ++imanshu
    Aug 23, 2008
  7. Trans
    Replies:
    2
    Views:
    416
    Trans
    Nov 6, 2005
  8. Mark Adkins

    Ruby, MySQL, and apostrophe problem

    Mark Adkins, Feb 12, 2008, in forum: Ruby
    Replies:
    4
    Views:
    209
    Sharon Rosner
    Feb 12, 2008
Loading...