Using MS Access Databases

Part:   1  2  3  4  5 

(Continued from previous part...)

Protecting Data in SQL Statements

Respecting SQL syntax rules is easy, if you are writing SQL statements as string literals. But in ASP pages, SQL statements must be composed with variables. It is very often that special characters in the variable values will break some syntax rules. When this happens, your script will not do what it is expected to do, or users will get crash pages.

SQL statement syntax rules related to MS Access database are simple:

• SQL numeric value literals requires no quotes
• SQL string value literals must be enclosed by single quotes ('). If a string literal contains a single quote ('), it should be replaced by two single quotes ('').
• SQL date value literals must be enclosed by pond signs (#). A date literal should not contain any pond signs (#). The default date format is "mm/dd/yyyy". But MS Access can take other too: "dd/mm/yyyy", "yyyy/mm/dd", "dd-MMM-yyyy", ...
• If a table field is declared as "AutoNumber", INSERT statement should not provide any value to this field.
• If a table name or field name matches a SQL or MS Access reserved key word, it must be enclosed square brackets "[]". For example "Order" is SQL reserved key word. If you use it as a table name, you must use it as "[Order]". Other reserved key words include: "Date", "Timestamp", "Note", ...

To help obey those rules, your ASP script should pre-process all variables used as field values. Here are some suggestions:

• Always use Interger/Long variables for numeric table fields.
• Always use strVar = Replace(strVar, "'", "''") to prepare variables for string table fields.
• Always use strVar = Replace(strVar, "#", "") to prepare variables for date table fields.
• Always enclose table names and field names in square brackets "[]".

Here is a test script to show you how apply these rules, protect_data_access.asp:

<script language="vbscript" runat="server">
'  protect_data_access.asp
'  Copyright (c) 2005 by Dr. Herong Yang, http://www.herongyang.com/

   Set oConn = Server.CreateObject("ADODB.Connection")
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      Server.MapPath("/cgi-bin/hello.mdb")
   
   If Request.Form("submit") = "Submit" Then
      Set oRes = oConn.Execute("INSERT INTO [order]" _
         & " ([Item], [Quantity], [Date]) VALUES (" _ 
         & " '" & Replace(Request.Form("Item"), "'", "''") & "'" _
         & ", " & Clng(Request.Form("Quantity")) _
         & ", #" & Replace(Request.Form("Date"), "#", "") & "#)")
   End If
   
   Response.Write("<table><form action=" _ 
      & Request.ServerVariables("SCRIPT_NAME") & " method=post>")
   Response.Write("<tr><td>Item:</td>" _ 
      & "<td><input type=text size=40 name=Item></td></tr>")
   Response.Write("<tr><td>Quantity:</td>" _ 
      & "<td><input type=text size=40 name=Quantity></td></tr>")
   Response.Write("<tr><td>Date:</td>" _ 
      & "<td><input type=text size=40 name=Date></td></tr>")
   Response.Write("<tr><td></td>" _
      & "<td><input name=submit value=Submit type=submit><td></tr>")
   Response.Write("</form></table>")

   Set oRes = oConn.Execute("SELECT * FROM [order]")
   Response.Write("<table border=1>")
   Do While NOT oRes.EOF
      Response.Write("<tr><td>" & oRes("ID") & "</td>" _
         & "<td>" & oRes("Item") & "</td>" _
         & "<td>" & oRes("Quantity") & "</td>" _
         & "<td>" & oRes("Date") & "</td></tr>")
      oRes.MoveNext
   Loop
   Response.Write("</table>")

   oRes.close
   oConn.close 
</script>

(Continued on next part...)

Part:   1  2  3  4  5