"Matt" <mj*****@gmail.comwrote in message
news:13**********************************@z6g2000p re.googlegroups.com...
On Oct 29, 10:29 am, "Bob Barrows" <reb01...@NOyahoo.SPAMcomwrote:
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 &#xxxx; 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