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. Advertising

  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. Advertising

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. =?Utf-8?B?Z2VvZGV2?=
    Replies:
    1
    Views:
    2,107
    Alan Samet
    Oct 20, 2005
  2. vizlab
    Replies:
    3
    Views:
    4,203
    Michael Bar-Sinai
    Oct 17, 2007
  3. ++imanshu
    Replies:
    7
    Views:
    471
    ++imanshu
    Aug 23, 2008
  4. Trans
    Replies:
    2
    Views:
    223
    Trans
    Nov 6, 2005
  5. Mark Adkins

    Ruby, MySQL, and apostrophe problem

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

Share This Page