CODEWITHSUNDEEP
sqlms-accessasp-classic
Muhammad Saqlain Arif
Muhammad Saqlain Arif

Reputation: 544

escaping special character from string asp and ms access query

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")

Upvotes: 3

Views: 2868

Answers (2)

Aki
Aki

Reputation: 626

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.

Upvotes: 1

kloarubeek
kloarubeek

Reputation: 2846

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

Upvotes: 0

Related Questions