Introduction
This article provides a generalized solution to a specific problem: preventing SQL injections by simplifying the creation of parameterized queries. The provided class functions as a general database connection wrapper class, exposing a few key methods to manage queries and transactions. The class methods are much easier to use than the standard parameter object method.
Background
Recently, I had to perform some extensive development in classic ASP and needed a way to manage parameterized queries. One major concern was protecting against SQL injection attacks. Another concern was consolidation of database access code to one centralized point in the application. Historically, classic ASP applications have database access code littered throughout the codebase, making maintenance more complex than necessary.
The standard guidance for prepared statements/parameterized queries in ASP is to issue a series of statements constructing parameter objects, appending them to the command, then executing the command. This process is generally cumbersome and frankly not very enjoyable. It is also rather error-prone as you have a complex solution when it is often not necessary.
Instead, I created a simple custom VBScript class that encapsulates all necessary* database operations into one object that only exposes those items that are actually needed. ADO is a Swiss Army Knife but in reality I have never used more than a tiny subset of all of its features, so the class exposes only those I have ever really found useful.
* necessary for my needs of course, and probably enough for 80% of usage scenarios, but you can modify as needed.
The class takes advantage of one key concept in parameter creation: It is possible to pass an array of parameters when executing a query, and the parameters will be dynamically built for you at run-time. Yes, this incurs a small database hit, but we should remember that the guidance for ASP performance was largely written 15 years ago, and server and database performance and caching has come very far since then. In other words, the tradeoff of a very minor database hit is worth it to gain clarity and programmer time, especially when your time is more expensive than the hardware you are using.
Using the Code
The class functions by wrapping an ADO connection object and exposing developer-friendly methods. The default implementation is to keep the connection object private and not expose it, but if you like you can certainly make it public if you need direct access to the underlying connection.
Note: To make the demonstration simple, we use the master.INFORMATION_SCHEMA in SQL Server for the examples, so they should run anywhere you have SQL Server installed.
We begin by instantiating the class and initializing it with a connection string:
dim DB : set DB = new Database_Class
DB.Initialize YOUR_CONNECTION_STRING_HERE
Internally, the class retains the connection string and references to the connection are made via a private function simply named Connection. This function in turn creates the connection if it does not already exist. This enables the connection to be lazily loaded when actually needed, which means you can include the class globally and the connection will only be created the moment it is actually needed.
When using ADO to execute SQL, there are generally two "modes", executing SQL that returns data or executing SQL that writes data. This naturally leads us to expose two different methods, Query() and Execute(). Each accepts a SQL statement and one or more parameters (through another developer-friendly use of scalar-or-array parameter passing), binds the parameters, and executes the SQL. The only difference in the two is that Query() returns a recordset and Execute() does not; in fact, Execute() calls Query() internally but discards the result. Parameters are identified in the SQL using the ? character as expected.
The following query uses no parameters:
set rs = DB.Query("select table_name, column_name, is_nullable from master.information_schema.columns", empty)
Notice the use of the VBScript keyword empty to denote no parameter was passed. This allows us to have rather flexible methods even though we can't have true function overloading. The return value of this method is a recordset as expected.
The Query() method has a developer-friendly feature in that it can accept an arbitrary number of SQL parameters. It does this by checking the type of the second parameter. If the second parameter is not an array, then the Query() method internally converts this single parameter into an array before execution. This allows us to avoid the ugly Array(...) syntax when it is not needed.
A query with one parameter:
set rs = DB.Query("select table_name, column_name, is_nullable from master.information_schema.columns where column_name like ?", "x%")
Two parameters, this is where the parameter type changes:
set rs = DB.Query("select table_name, column_name, is_nullable from master.information_schema.columns where column_name like ? and is_nullable = ?", Array("x%", "YES"))
Here, we pass an array as the second parameter to Query(). The array contains each of the parameters that will be bound to the SQL, in the order in which the question marks appear in the SQL string. A virtually infinite number of parameters can be passed using the array construct.
Execute() looks exactly the same as Query(), except it is a Sub and therefore has no return value:
DB.Execute "update foo set a = ?", "b"
DB.Execute "insert into foo values (?, ?, ?)", Array(1, "two", "three")
Paging Dr. Simple...
Paging (naive in-recordset paging, that is) is rather easy to manage by extending the Query() method to accept a few more parameters. The signature looks like this:
PagedQuery(sql, params, per_page, page_num, ByRef page_count, ByRef record_count)
The first two parameters are exactly the same as in the previous two methods. per_page is the number of records to be returned on each logical page. page_num is the current page number you wish to view in the recordset. page_count is a return parameter that will contain the total number of pages in the recordset. record_count is also a return parameter that will contain the total number of records in all pages.
The caller must also take a step to ensure paging is used when iterating the recordset. The following is a crude function in the demo that displays a paged recordset:
Sub putpaged(rs, per_page)
put "<table border='1' cellpadding='5'>"
put "<tr>"
dim field
For Each field in rs.Fields
put "<th>" & field.Name & "</th>"
Next
put "</tr>"
dim x : x = 0
Do While x < per_page and Not rs.EOF
put "<tr>"
For Each field in rs.Fields
put "<td>" & rs(field.Name) & "</td>"
Next
put "</tr>"
x = x + 1
rs.MoveNext
Loop
put "</table>"
End Sub
In this demo, the put method is simply a sub that wraps response.write for easier typing and reading, with one twist: if the passed variable is a recordset, it calls a method that displays the recordset as a table. So the demo has lines that appear like so:
put "<p><strong>No parameters: All tables and columns</strong></p>"
set rs = DB.Query("select table_name, column_name, is_nullable from master.information_schema.columns", empty)
put rs
Developer-friendly coding like this greatly simplifies the ability to read and understand the code at a glance. It also makes it reasonable to indent code within HTML output commands as in the previous example, where code that constructs a table row is logically nested inside the put calls that create the <tr> element itself.
Transactions
Because the class wraps a connection object, it is only logical to expose the transaction capabilities as well. We could expose the connection object itself, but why should we if we do not need the extra functionality? The following is a completely contrived example to demonstrate the process.
dim sql, rs<br />dim order_type = "some type"
dim order_name = "some name"
DB.BeginTransaction
sql = "insert into orders (order_type, order_name) values (?, ?)"
DB.Execute sql, Array(order_type, order_name)
sql = "select max(id) from orders where order_type = ? and order_name = ?"
dim new_id : new_id = DB.Query(sql, Array(order_type, order_name))(0)
DB.Execute "insert into order_status (order_id, status) values (?, ?)", Array(new_id, "N")
DB.CommitTransaction
We could call DB.RollbackTransaction at any point to cancel the transaction, as expected.
Developer Friendly is Best Friendly
I think the key takeaway from this is that it is possible to write very clean code in ASP. Maybe it doesn't matter as much now, with it being technically obsolete for many years... :\
But sometimes we still have to work with it, and sometimes extensively. Many times, we are inheriting legacy code that was written using less-than-ideal structures, so taking control of and simplifying the code whenever possible makes our maintenance burden that much easier. And when we do have to build something from scratch in ASP (yes it happens), this type of approach makes our product more stable from the beginning.
Finally, there are many other ways VBScript/ASP can be extended and expanded using techniques like the ones shown here to simplify development and provide more capabilities beyond the usual out-of-the-box experience. In fact, while certainly not in the league of C#, it still has a lot more potential than many people would expect. I hope to write more articles in the near future covering these other possibilities. And I hope you enjoy this and get some use out of the code presented.
