Paramaterized SQL MS Access Multiple Input Types

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

filter all input against a white list of allowed characters and must (See: Filter Input Better)
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. Better examples use the Parameterized SQL Library, which reduces the amount of code necessary while also providing robust error handling.

[See the entries in the table] [Using MS ADODB Parameterized Queries With PHP]

<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Option Explicit
Session.CodePage=65001
Response.Charset="UTF-8"
%>
<!--#include virtual="/include/generalPurpose.asp"-->
<%
' no browser caching of this page !! to be used on all pages
Response.Expires=-1
Response.ExpiresAbsolute = Now() - 1

' do not allow proxy servers to cache this page !! to be used on all pages
Response.CacheControl="private"
Response.CacheControl="no-cache"
Response.CacheControl="no-store"


Dim conn, cmd, rs, command, param, numAffected, htmlOut, readonly
Dim first, last, dob, age, salary, description

if LCase(Request.ServerVariables("HTTP_METHOD")) = "post" then
    first = getField("first,rXname")
    last = getField("last,rXname")
    dob = getField("dob,rXdate")
    age = getField("age,rXint")
    salary = getField("salary,rXdecimal")
    description = getField("description,rXtextarea")

    'Response.Write "First " & first & "<br>"
    'Response.Write "Last " & last & "<br>"
    'Response.Write "DOB " & dob & "<br>"
    'Response.Write "Age " & age & "<br>"
    'Response.Write "Salary " & salary & "<br>"
    'Response.Write "Description " & description & "<br>"


    command = "INSERT INTO employees ([first], [last], [dob], [age], [salary], [description]) VALUES (@first, @last, @dob, @age, @salary, @description);"
    Set cmd = Server.CreateObject("ADODB.Command")
    Set conn = Server.CreateObject("ADODB.Connection")

    conn.open Application("examples")
    Set cmd.ActiveConnection = conn
    Set param = cmd.CreateParameter("@first",adVarChar,adParamInput,CLng(Len(first)),first)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@last",adVarChar,adParamInput,CLng(Len(last)),last)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@dob",adDate,adParamInput,CLng(Len(dob)),dob)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@age",adInteger,adParamInput,CLng(Len(age)),age)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@salary",adNumeric,adParamInput,CLng(Len(salary)),salary)
    cmd.Parameters.Append param
    Set param = cmd.CreateParameter("@description",adLongVarWChar,adParamInput,CLng(Len(description)),description)
    cmd.Parameters.Append param

    cmd.CommandText = command

    ' Now we open a record set using the command object we prepared.
    cmd.Execute numAffected,, adExecuteNoRecords

    Set param = nothing
    Set conn = nothing
    Set cmd = nothing

    htmlOut = "<h2>Record Submitted</h2>"
    readonly = " readonly"
else 
    htmlOut = "<p><input class=""sbutton"" type=""submit"" value=""Submit""></p>"
    readonly = ""
end if


%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="author" content="Roderick Divilbiss">
<meta name="copyright" content="© 2005-2010 Roderick Divilbiss">
<meta name="MSSmartTagsPreventParsing" content="TRUE">
<title>Parameterized SQL MS-Access Form</title>
<style type="text/css">

input { 
  width: 100%;
  padding:0;
  margin:0;
}

label {
  display:block;
  width:100%;
  padding:0;
  margin:0 0.5em 0 0;
  font-size:0.9em;
  font-weight:bold;
}

#label_first, #label_last { 
  width: 15em;
}

#label_dob {
  clear:left;
  width: 10em;
}

#label_age {
  width:3em;
}

#label_salary {
  width:15em;
}

.sbutton {
  clear:left;
  width:auto;
}
</style>
</head>

<body>

<form method="post" action="parameterized_sql_multi_parameter.asp">
  <fieldset>
    <legend>Employee Details</legend>
    <p><label id="label_first">First Name: <input type="text" name="first" value="<%=first%>"<%=readonly%>></label></p>
    <p><label id="label_last">Last Name: <input type="text" name="last" value="<%=last%>"<%=readonly%>></label></p>
    <p><label id="label_dob">Date Of Birth: <input type="text" name="dob" value="<%=dob%>"<%=readonly%>></label></p>
    <p><label id="label_age">Age: <input type="text" name="age" value="<%=age%>"<%=readonly%>></label></p>
    <p><label id="label_salary">Salary: <input type="text" name="salary" value="<%=salary%>"<%=readonly%>></label></p>
    <p><label id="label_description">Description: <textarea name="description" rows="8" cols="70"<%=readonly%>><%=description%></textarea></label></p>
    <%=htmlOut%>
  </fieldset>
</form></p>
</div>
</body>

</html>

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