Home|Sitemap|Contact

How can you use parameterized SQL with ASP and MS Access?

When you build in-line SQL commands in a server side scripting language, such as ASP, and you use form input as part of the in-line SQL Command, you run the risk of SQL Injection.  A malicious web user can try different combinations of input and can likely compromise your database, stealing data, by-passing logins and even deleting your data.

To avoid this, a web developer must

  1. filter all input against a white list of allowed characters and must
  2. use parameterized SQL (or prepared statements / stored procedures).

With ASP, you use ADODB and either an OLEDB or OBDC data connection to interact with MS Access databases.  As the OLEDB driver is much, much faster it is the most common method.

Fortunately, ADODB and OLEDB allow you to use parameterized SQL statement with MS Access database files. [It gets better]

Number of Records: 2
IDCompanyAddressCitySTZipCodePhone
14A. O. Smith Corporation11270 W. Park PlaceMilwaukeeWI53224-9508414-359-4000

Change Company



<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Dim conn, cmd, rs, command, param, companyType, item, htmlOut

' Did we POST the form?
if LCase(Request.ServerVariables("HTTP_METHOD")) = "post" then
    companyType = Request.Form("companyType")
else 
    companyType = "construction"
end if

' a string with which we will create the HTML for a table to hold the results
htmlOut = ""

' Note the use of @companyType, that's a text parameter
command = "SELECT id as ID, company as Company, address as Address, city as City, state as ST, postalCode as ZipCode,"
command = command & " phoneNumber as Phone FROM companys WHERE ([type] = @companyType);"

Set cmd = Server.CreateObject("ADODB.Command")
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open Application("examples")
Set cmd.ActiveConnection = conn

' This is where we tell ADODB what our parameter is, (an Integer), it's maximum allowed length
' and it's value, from out variable companyID
'
' Doing this means our value is only used as variable to compare against the ID column
' and can not be executed as in-line SQL
Set param = cmd.CreateParameter("@companyType",adVarChar,adParamInput,CLng(Len(companyType)),companyType)

' We must add our parameter to the command objects Parameters collection
cmd.Parameters.Append param

' We also must assign our SQL command to the ADODB command object.
cmd.CommandText = command

' Now we open a record set using the command object we prepared.
rs.Open cmd,,adOpenStatic

' From here down it is similar as any other data access method.
if NOT (rs.eof AND rs.eof) then
    htmlOut = "<table><thead><tr><td colspan="""& rs.fields.count &""">"
    htmlOut = htmlOut & "Number of Records:&nbsp;"& rs.recordcount 
    htmlOut = htmlOut & "</td></tr></thead><tbody><tr>"

    for each item in rs.fields
        htmlOut = htmlOut & "<td>"& item.name &"</td>"
    next
    htmlOut = htmlOut & "</tr><tr>"
    for each item in rs.fields
        htmlOut = htmlOut & "<td>"& item &"</td>"
    next
    htmlOut = htmlOut & "</tr></tbody></table>" 
end if

' clean up
rs.close
Set rs=nothing
conn.close
Set conn=nothing
Set param = nothing
Set cmd = nothing