escaping special character from string asp and ms access query

Question

I have ASP code with MS Access and I am updating my records. While updating when I put special characters like single quotes(') in string it displays an error. When string is without special chars it works correctly.

Please help or give any solution. Here is my code.

<!--#INCLUDE FILE="ConnStr.asp"-->
dim fileTitle
dim fileDescription
dim fromDateX
dim toDateX
dim romTimeX
dim toTimeX
dim Location
dim LocationURL
dim FileID


 fileTitle= request("fileTitle")
 fileDescription= request("description")
 fromDateX= request("fdate")
 toDateX= request("tdate")
 romTimeX= request("ftime")
 toTimeX= request("ttime")
 Location= request("location")
 LocationURL= request("locationurl")
 FileID= request("jID")


sql = "Update tblFileInfo Set sFDate='" & fromDateX & "',sTDate='" & toDateX & "', sFTime='" & romTimeX & "',sTTime='" & toTimeX & "',location='" & Location & "', locationURL='" & LocationURL & "', filetitle='" & fileTitle & "', description='" & fileDescription & "' Where ID=" & FileID

    objConn.Execute(sql)
    Response.Redirect("adminfiles.asp?msg=save")

Preventing SQL Injection in ASP.NET for your reference: http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET — Dean Taylor, Dec 19 '13 at 09:55
@Dean Taylor you provide asp.net example, i am working in classic asp... :( — Muhammad Saqlain Arif, Dec 19 '13 at 10:09
The basics are the same - except you should use functions like `CreateParameter` and `Parameters.Append` as shown in this answer: http://stackoverflow.com/a/5193590/406712 — Dean Taylor, Dec 19 '13 at 14:16

score 1 · Answer 1 · answered Dec 19 '13 at 11:37

As previous answer mentions, you should avoid updating or accessing your database in this way due to SQL injection.

If your script is just for a temporary database update and for personal use, the quick and dirty way is to escape the apostrophe by repeating it again with a Replace function.

sql = "Update tblFileInfo Set sFDate='" & Replace(fromDateX,"'","''") & "'  ...."

OR replace with HTML equivalent.

sql = "Update tblFileInfo Set sFDate='" & Replace(fromDateX,"'","&#39;") & "'  ...."

Do not use this for anything but a quick one off scripts if you're strapped for time. Not recommended under any other circumstance.

score 0 · Answer 2 · answered Dec 19 '13 at 09:49

Don't insert parameter values like this, be aware of SQL injections!

Use ADO Command with parameters or create a stored procedure that handles the insert/ updates. Both solutions will solve your problem with single quotes. A good example can be found here: http://www.xtremevbtalk.com/showthread.php?t=309329#post1337389

escaping special character from string asp and ms access query

2 Answers2