ASP and MySQL ' character problem

[telmessos]

Hi all,

I couldn't decide to here or to MySQL section but I have a problem with ASP + MySQL with the character of ' .

When a client types character ' in the form fields and send the form MySQL gives an error message of


Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'tolo' and aracmodel='test'' at line 1


I sorted this problem before by using the following code but I would like to learn if there's an easier way.

note1 = Request.Form("note1")
note1 = Replace(note1, CHR(39), "'")

I have about 30 to 50 form fields and I don't want to do this for each field. Can anyone recommend an easier way if there's one?

Thanks

Ceyhun

[gil davis]

I ran into the same problem and ended up replacing single quotes with "`" on submit. Fortunately I only have three textarea fields that had the problem.

You could use "onkeydown" event on each field to filter the single quote, but that would probably be just as tedious.

I hope someone comes up with a better solution, too! ;)

[telmessos]

Anybody have an idea about this subject ???

[ahk2chan]

I don't know if it will work because I never tried it... (I actually escape the special characters myself)... but have anyone tried to use Prepared Statement? Since with prepared statement, all you do is to provide the values for the ? parameters, and I don't know if the object will escape special characters for you during the assignment?

[telmessos]

what is a prepared statement ?

[ahk2chan]

Try Here:

http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/iisbook/c07_prepared_queries.mspx?mfr=true
(http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/iisbook/c07_prepared_queries.mspx?mfr=true)

Prepared statement is used to improve performance if you are going to execute your query multiple times (with different parameter values)

here is a code snipplet from the link:

<%
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = Application("ConnectionString")
cmd.Prepared = True
cmd.CommandText = "Select * From Catalogue Where Id=?"
Set p = cmd.Parameters
p.Append cmd.CreateParameter("prodId",adInteger,adParamInput)
cmd("prodId") = Request("Id1")
Set rs = cmd.Execute

cmd("prodId") = Request("Id2")
Set rs = cmd.Execute
%>

Basically you can substitue the ? in the query string, and execute the query. So I was wondering if we subsititute ? (string field) with a string value (with quotes in it), I want to see if the CreateParameter method will encode the value for you.