Protecting Data in SQL Statements

ASP Tutorials - Herong's Tutorial Examples

∟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 "[]".