Searching foreign characters - Classic ASP & SQL 2005

Discussion in 'ASP General' started by Matt, Oct 29, 2008.

  1. Matt

    Matt Guest

    I originally posted this in microsoft.public.sqlserver.server, and it
    was suggested that I post here.

    I'm having problems with searches via a classic ASP front-end of terms
    including foreign characters. For example, Profiler indicates that a
    search for: ×ÓÅ ÏË, ÀÚÁÊ ÎÁ ÚÄÏÒÏ×ØÅ

    is actually executed as:
    SELECT top 1 '1' from dbo.mytable where contains(myfield,
    '"все ок, юзай
    на
    здоровье"')

    How do I prevent the foreign characters (in this case Cyrillic) from
    being converted to HTML ASCII?

    Thanks!
     
    Matt, Oct 29, 2008
    #1
    1. Advertising

  2. Matt

    Bob Barrows Guest

    Matt wrote:
    > I originally posted this in microsoft.public.sqlserver.server, and it
    > was suggested that I post here.
    >
    > I'm having problems with searches via a classic ASP front-end of terms
    > including foreign characters. For example, Profiler indicates that a
    > search for: ×ÓÅ ÏË, ÀÚÁÊ ÎÁ ÚÄÏÒÏ×ØÅ
    >
    > is actually executed as:
    > SELECT top 1 '1' from dbo.mytable where contains(myfield,
    > '"все ок, юзай
    > на
    > здоровье"')
    >
    > How do I prevent the foreign characters (in this case Cyrillic) from
    > being converted to HTML ASCII?
    >

    How are you passing the data? Oh wait ... "contains" ... this is a
    full-text search. I've no experience with those, but I guess I am
    wondering if parameters can be used.

    Anyways, we need to see the code that receives the strings and
    builds/passes them to the sql statement.

    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Oct 29, 2008
    #2
    1. Advertising

  3. Matt

    keyser soze Guest

    hi
    how you are making the query?


    "Matt" <> escribió en el mensaje
    news:...
    I originally posted this in microsoft.public.sqlserver.server, and it
    was suggested that I post here.

    I'm having problems with searches via a classic ASP front-end of terms
    including foreign characters. For example, Profiler indicates that a
    search for: ×ÓÅ ÏË, ÀÚÁÊ ÎÁ ÚÄÏÒÏ×ØÅ

    is actually executed as:
    SELECT top 1 '1' from dbo.mytable where contains(myfield,
    '"все ок, юзай
    на
    здоровье"')

    How do I prevent the foreign characters (in this case Cyrillic) from
    being converted to HTML ASCII?

    Thanks!


    ---
    avast! Antivirus: Inbound message clean.
    Virus Database (VPS): 081029-0, 29/10/2008
    Tested on: 30/10/2008 11:04:12 a.m.
    avast! - copyright (c) 1988-2008 ALWIL Software.
    http://www.avast.com





    ---
    avast! Antivirus: Outbound message clean.
    Virus Database (VPS): 081029-0, 29/10/2008
    Tested on: 30/10/2008 11:06:08 a.m.
    avast! - copyright (c) 1988-2008 ALWIL Software.
    http://www.avast.com
     
    keyser soze, Oct 30, 2008
    #3
  4. Matt

    Matt Guest

    On Oct 29, 10:29 am, "Bob Barrows" <> wrote:
    > Matt wrote:
    > > I originally posted this in microsoft.public.sqlserver.server, and it
    > > was suggested that I post here.

    >
    > > I'm having problems with searches via a classic ASP front-end of terms
    > > including foreign characters.  For example, Profiler indicates that a
    > > search for: ×ÓÅ ÏË, ÀÚÁÊ ÎÁ ÚÄÏÒÏ×ØÅ

    >
    > > is actually executed as:
    > > SELECT top 1 '1' from dbo.mytable where contains(myfield,
    > > '"все ок, юзай
    > > на
    > > здоровье"')

    >
    > > How do I prevent the foreign characters (in this case Cyrillic) from
    > > being converted to HTML ASCII?

    >
    > How are you passing the data? Oh wait ... "contains" ... this is a
    > full-text search. I've no experience with those, but I guess I am
    > wondering if parameters can be used.
    >
    > Anyways, we need to see the code that receives the strings and
    > builds/passes them to the sql statement.
    >
    > --
    > HTH,
    > Bob Barrows


    Thanks to you both for replying!

    Here's the code that accepts user input:
    <tr>
    <td><b>My Search</b></td>
    <td><input type="text" name="strMySearch" size="40"></td>
    </tr>

    And here's the code that receives the string and creates the sql
    statement (on a separate page from code above):
    strMySearch = Trim(Request("strMySearch"))

    strSQL = "SELECT top 1 '1' " & _
    "FROM mydb.dbo.mytable "
    'use full-text indexing
    strSQL = strSQL & "WHERE contains(myfield, '" & chr(34) &
    strMySearch & chr(34) & "') " & _
    "OR (myfield2 = '" & strMySearch & "' OR myfield3= '" &
    strMySearch & "') "

    set objRS = objConn.execute(strSQL)
    if not objRS.EOF then
    strExists = true
    end if

    FWIW, I'm also encountering this problem when not using full-text
    indexes - here's an example:

    <tr>
    <td><b>My Search</b></td>
    <td><input type="text" name="strMySearch" size="50"></td>
    </tr>

    strMySearch = Trim(Request("strMySearch"))

    strSQL = "SELECT distinct myfield " & _
    "FROM mydb.dbo.mytable " & _
    "WHERE 1=1 "
    If Len(strMySearch) > 0 Then
    strSQL = strSQL & "AND myfield like '%" & UCASE(strMySearch) & "%'
    "
    End If

    set objRS = objConn.execute(strSQL)
    if not objRS.EOF then
    arrMySearch = objRS.GetRows()
    strUbound = UBound(arrMySearch,2)
    end if
     
    Matt, Nov 3, 2008
    #4
  5. "Matt" <> wrote in message
    news:...
    On Oct 29, 10:29 am, "Bob Barrows" <> wrote:
    > Matt wrote:
    > > I originally posted this in microsoft.public.sqlserver.server, and it
    > > was suggested that I post here.

    >
    > > I'm having problems with searches via a classic ASP front-end of terms
    > > including foreign characters. For example, Profiler indicates that a
    > > search for: ×ÓÅ ÏË, ÀÚÁÊ ÎÁ ÚÄÏÒÏ×ØÅ

    >
    > > is actually executed as:
    > > SELECT top 1 '1' from dbo.mytable where contains(myfield,
    > > '"все ок, юзай
    > > на
    > > здоровье"')

    >
    > > How do I prevent the foreign characters (in this case Cyrillic) from
    > > being converted to HTML ASCII?

    >
    > How are you passing the data? Oh wait ... "contains" ... this is a
    > full-text search. I've no experience with those, but I guess I am
    > wondering if parameters can be used.
    >
    > Anyways, we need to see the code that receives the strings and
    > builds/passes them to the sql statement.
    >
    > --
    > HTH,
    > Bob Barrows
    >>>>>>>>>>>>>>>>>>>

    Thanks to you both for replying!

    Here's the code that accepts user input:
    <tr>
    <td><b>My Search</b></td>
    <td><input type="text" name="strMySearch" size="40"></td>
    </tr>

    And here's the code that receives the string and creates the sql
    statement (on a separate page from code above):
    strMySearch = Trim(Request("strMySearch"))

    strSQL = "SELECT top 1 '1' " & _
    "FROM mydb.dbo.mytable "
    'use full-text indexing
    strSQL = strSQL & "WHERE contains(myfield, '" & chr(34) &
    strMySearch & chr(34) & "') " & _
    "OR (myfield2 = '" & strMySearch & "' OR myfield3= '" &
    strMySearch & "') "

    set objRS = objConn.execute(strSQL)
    if not objRS.EOF then
    strExists = true
    end if

    FWIW, I'm also encountering this problem when not using full-text
    indexes - here's an example:

    <tr>
    <td><b>My Search</b></td>
    <td><input type="text" name="strMySearch" size="50"></td>
    </tr>

    strMySearch = Trim(Request("strMySearch"))

    strSQL = "SELECT distinct myfield " & _
    "FROM mydb.dbo.mytable " & _
    "WHERE 1=1 "
    If Len(strMySearch) > 0 Then
    strSQL = strSQL & "AND myfield like '%" & UCASE(strMySearch) & "%'
    "
    End If

    set objRS = objConn.execute(strSQL)
    if not objRS.EOF then
    arrMySearch = objRS.GetRows()
    strUbound = UBound(arrMySearch,2)
    end if
    >>>>>>>>>>>>>>>>>>>>



    I'm not convinced this is a SQL issue at all, not to say there isn't one, we
    first need to discover how we are seeing entities, this is
    definitely not being done by SQL.

    How are you acquiring the text of the SQL executed, (please be a specific as
    possible describing your process of discovering the text, in these cases the
    devil is in the detail)?

    Is this a METHOD="POST or a METHOD="GET" form?
    Is the form action URL the same page that holds for form or different one?
    Are you intiailising the value of the input when generating the HTML form?
    What codepage is set for the form page and the receiving page?
    What charset is specified on the response for the form page?

    BTW, You should google up SQL Injection Attack, the technique of
    concatenating into SQL code value posted from a client leaves your site
    vunerable.

    --
    Anthony Jones - MVP ASP/ASP.NET
     
    Anthony Jones, Nov 4, 2008
    #5
  6. Matt

    Matt Guest

    On Nov 4, 4:20 am, "Anthony Jones" <>
    wrote:
    > "Matt" <> wrote in message
    >
    > news:...
    > On Oct 29, 10:29 am, "Bob Barrows" <> wrote:
    >
    > > Matt wrote:
    > > > I originally posted this in microsoft.public.sqlserver.server, and it
    > > > was suggested that I post here.

    >
    > > > I'm having problems with searches via a classic ASP front-end of terms
    > > > including foreign characters. For example, Profiler indicates that a
    > > > search for: ,

    >
    > > > is actually executed as:
    > > > SELECT top 1 '1' from dbo.mytable where contains(myfield,
    > > > '"все ок, юзай
    > > > на
    > > > здоровье"')

    >
    > > > How do I prevent the foreign characters (in this case Cyrillic) from
    > > > being converted to HTML ASCII?

    >
    > > How are you passing the data? Oh wait ... "contains" ... this is a
    > > full-text search. I've no experience with those, but I guess I am
    > > wondering if parameters can be used.

    >
    > > Anyways, we need to see the code that receives the strings and
    > > builds/passes them to the sql statement.

    >
    > > --
    > > HTH,
    > > Bob Barrows

    >
    > Thanks to you both for replying!
    >
    > Here's the code that accepts user input:
    > <tr>
    > <td><b>My Search</b></td>
    > <td><input type="text" name="strMySearch" size="40"></td>
    > </tr>
    >
    > And here's the code that receives the string and creates the sql
    > statement (on a separate page from code above):
    > strMySearch = Trim(Request("strMySearch"))
    >
    > strSQL = "SELECT top 1 '1' " & _
    > "FROM mydb.dbo.mytable "
    > 'use full-text indexing
    > strSQL = strSQL & "WHERE contains(myfield, '" & chr(34) &
    > strMySearch & chr(34) & "') " & _
    >    "OR (myfield2 = '" & strMySearch & "' OR myfield3= '" &
    > strMySearch & "') "
    >
    > set objRS = objConn.execute(strSQL)
    > if not objRS.EOF then
    > strExists = true
    > end if
    >
    > FWIW, I'm also encountering this problem when not using full-text
    > indexes - here's an example:
    >
    > <tr>
    > <td><b>My Search</b></td>
    > <td><input type="text" name="strMySearch" size="50"></td>
    > </tr>
    >
    > strMySearch = Trim(Request("strMySearch"))
    >
    > strSQL = "SELECT distinct myfield " & _
    > "FROM mydb.dbo.mytable " & _
    > "WHERE 1=1 "
    > If Len(strMySearch) > 0 Then
    > strSQL = strSQL & "AND myfield like '%" & UCASE(strMySearch) & "%'
    > "
    > End If
    >
    > set objRS = objConn.execute(strSQL)
    > if not objRS.EOF then
    > arrMySearch = objRS.GetRows()
    > strUbound = UBound(arrMySearch,2)
    > end if
    >
    >
    >
    > I'm not convinced this is a SQL issue at all, not to say there isn't one,we
    > first need to discover how we are seeing entities, this is
    > definitely not being done by SQL.
    >
    > How are you acquiring the text of the SQL executed, (please be a specificas
    > possible describing your process of discovering the text, in these cases the
    > devil is in the detail)?
    >
    > Is this a METHOD="POST or a METHOD="GET" form?
    > Is the form action URL the same page that holds for form or different one?
    > Are you intiailising the value of the input when generating the HTML form?
    > What codepage is set for the form page and the receiving page?
    > What charset is specified on the response for the form page?
    >
    > BTW, You should google up SQL Injection Attack, the technique of
    > concatenating into SQL code value posted from a client leaves your site
    > vunerable.
    >
    > --
    > Anthony Jones - MVP ASP/ASP.NET


    Thanks for the response, Anthony. You pointed me in the right
    direction and I was able to solve the problem by setting the codepage
    on both the input and output forms to 65001 and the character set to
    utf-8. Also, thanks for the warning about SQL injection. While this
    is an internal site with trusted users, I would agree we should fix
    this vulnerability ASAP.

    Thanks again!
     
    Matt, Nov 13, 2008
    #6
    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. Replies:
    1
    Views:
    517
    Joerg Jooss
    Aug 22, 2005
  2. Replies:
    2
    Views:
    2,147
    Juan T. Llibre
    Nov 8, 2005
  3. MartyNg
    Replies:
    3
    Views:
    582
    MartyNg
    Dec 28, 2005
  4. H5N1
    Replies:
    0
    Views:
    445
  5. nick chan

    sql 2005 xml searching

    nick chan, Apr 16, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    270
    nick chan
    Apr 16, 2008
Loading...

Share This Page