Protecting Data in SQL Statements
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.
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 "".
Last update: 2005.
Table of Contents
About This Book
ASP (Active Server Pages) Introduction
IIS (Internet Information Services) 5.0
MS Script Debugger
ASP Built-in Run-time Objects
Creating and Managing Cookies
Managing Sessions with and without Cookies
scrrun.dll - Scripting Runtime DLL
Managing Response Header Lines
Calculation Speed and Response Time
ADO (ActiveX Data Object) DLL
►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
Guest Book Application Example
PDF Printing Version