ASP Tutorials - Herong's Tutorial Examples - v5.10, by Dr. Herong Yang
Protecting Data in SQL Statements - Test Script
This section provides a tutorial example on running the test script, insert_access.asp, persist data to MS Access database using the Execute() method to run an INSERT statement.
Here is a test script to show you how to apply those rules described in the previous section:
<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>
Of course, you need to create a new table called "Order" in the "hello.mdb" before running this script. The "Order" must have the following fields:
ID - "Autonumber" type Item - "Text" type Quantity - "Number" type Date - "Date/Time" type
Run this script with Internet Explorer, you should see a blank form. Enter a set of good values, like:
Item: Coke Quantity: 3 Date: 12/25/2020
Then press the "Submit" button. You will see the page comes back again with one row display the values you entered below the form.
The second test you can do is to enter some protected characters in the values, like:
Item: Today's paper Quantity: 1 Date: #25/10/2020#
The script should have no problem to insert those values into the database.
Another test you can do is to enter some bad values, like:
Item: Milk Quantity: 2L Date: 12/25/2020
This time, you will get a crash page with an error: "Microsoft VBScript runtime (0x800A000D), Type mismatch: 'Clng'"
As you can see, my script does not have any logic for type checking. I will leave it to you to improve the script in this area.
Table of Contents
ASP (Active Server Pages) Introduction
IIS (Internet Information Services) 5.0
Managing Sessions with and without Cookies
scrrun.dll - Scripting Runtime DLL
Managing Response Header Lines
Calculation Speed and Response Time
►Working with MS Access Database
Connecting ASP Pages to MS Access Databases
"hello_access.asp" - MS Access Example
Persisting Data to MS Access Databases
Protecting Data in SQL Statements
►Protecting Data in SQL Statements - Test Script