Searching foreign characters - Classic ASP & SQL 2005

[Matt]

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!

 

[Bob Barrows]

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.

 

[keyser soze]

hi
how you are making the query?


"Matt" <[email protected]> escribió en el mensaje
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

 

[Matt]

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.

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

 

[Anthony Jones]

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.

[/QUOTE][/QUOTE]
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.

 

[Matt]

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.

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!