Classic ASP (which IS still alive) - and parametised queries
Ok, this is an entry I’ve been meaning to write for a while, but have just never got around to. Getting the motivation to actually write takes an effort after spending every day looking at a screen - hence the complete lack of updates since I got the new site design up and running. Anyway, now that I have a couple of days off, I thought it time I get back to writing some blog entries.
I’ve long held the belief that if something works, don’t mess with it. If something does everything you need it to, and does it well, I reckon it’s a good idea to stick with it.
And that, ladies and gentlemen, is my justification for a sentiment that will probably rile a lot of people: I like classic ASP. It works well for a lot of things. I can almost hear the sighs of “but .NET is more powerful”, “Ruby is more elegant… and more powerful”, “ASP it leaks memory”, “ASP security is crap” and a multitude of other whines.
Well, yes, .NET et al may be more elegant and robust languages, but I have one thing to say: lazy code and shoddy server development from MS has given ASP a bad name, but if you know what you’re doing - and do it right - it’s still perfectly good for a lot of applications.
Basically: just because something is new, doesn’t mean it is the best thing for the job. This is becoming more apparent with the drive toward .NET – it’s the language of the future for MS hosted applications, which I don’t dispute, but - it should be recognised as one of many tools in a developer’s inventory. Anyway, this has turned what should have been an introduction into a bit of a rant - this post is actually about running more secure SQL code in ASP.
As ASP declines, tutorials and help become more difficult to find. Here, for example: finding information about parametised queries in classic ASP is like trying to find a needle in a haystack. And trying to find working examples written in plain english… well, then the haystack gets to be the size of a small benelux country.
So, to offer something back to the wider world, here’s my attempt at a quick run through with some examples (if you need to know what this is all about, go read about parametised queries and sql injection first).
I like to keep nice clean objects, clearing and closing as I go – which probably means more code than there could be, but hey, I like to be thorough.
First, and most importantly:
VALIDATE USER INPUT FIRST!
Do it server side. If you’re using client side validation, go read a book on how to use computers first, realise your mistake, take a course, then come back and start again. Ok, done that? Now, set up the DB connection:
set objDBConnection = Server.CreateObject("ADODB.Connection")
objDBConnection.ConnectionTimeout = 15
objDBConnection.CommandTimeout = 30
objDBConnection.Open "YOUR CONNECTION STRING"
And now, a sample select statement:
set objDBCommand = Server.CreateObject("ADODB.Command")
objDBCommand.ActiveConnection = objDBConnection
objDBCommand.CommandText = "SELECT id FROM yourtable WHERE column=? AND anothercolumn=?"
objDBCommand.CommandType = 1
You’ll notice that I select specific columns. This is far more efficient than using wildcards, and allows me later to form a more easily understood array holding the output recordset from this select statement.
The two question marks are the most important thing here - these represent variables that you are going to pass into the SQL string using parameters. This is the whole crux of the argument, as passing parametised values is inherently more secure than simply concatenating them into a single SQL statement. So, how to parametise those two values?
First things first: the processing of parameters is done in order. So in this instance, first I must define the value for “column=?”, then second, “anothercolumn=?”.
set objDBParam = objDBCommand.CreateParameter("@column",200,1,50)
objDBCommand.Parameters.Append objDBParam
objDBCommand.Parameters("@column") = YOUR-INPUT-VARIABLE-HERE
set objDBParam = Nothing
What’s going on here then? Well, first I define the parameter I am going to set. The syntax for this is quite straight forward:
CreateParameter("@TEMPORARYNAME",DATATYPE,DIRECTION,LENGTH)
The temporary name can be anything you like, and does not need to match the column name of the database. The datatype is the ADO datatype of the column you are addressing: refer to the W3Schools list of datatypes to get the appropriate value for this. The direction… well, 99% of the time, it’s going into the query, so it’s set to “1″. Finally, length - this should be the size of the column being addressed in the database.
In this case, I’m creating a parameter using the temporary name “@column”. I assign it the data type “200″ (varchar). It is going INTO the statement, so I give it the direction “1″. Finally, I know the length of the variable should be at most 50 characters, so I set the upper limit to that.
When the parameter has been created, it is appended to the SQL command object. Once this is complete, you can pass in your validated user input using the temporary parameter name you used earlier. Simple, eh?
This would be repeated for each parameter in your SQL statement.
Now that you have a parametised query, I usually choose to output the result into an array, clear all objects, and close out of the DB connection as quickly as possible. This has performance benefits on several levels… and besides, ASP’s array handling is far superior to it’s recordset handling. Here we go:
set objDBRecords = objDBCommand.Execute
if objDBRecords.EOF<>True then
arrOutput=objDBRecords.GetRows
end if
set objDBRecords = Nothing
set objDBCommand = Nothing
Finally, close out of the DB connection:
objDBConnection.Close
set objDBConnection = Nothing
You can then test for the array using isArray(arrOutput), which allows you some nice error catching/handling possibilities without resorting to the old “on error resume next” horror show.
The code is exactly the same for UPDATE, DELETE and INSERT statements - you are still constructing SQL statements, after all. The only difference is, as such statements rarely require any records to be returned, I would forgoe the transfer of the recordset object into an array, and simply execute the command object instead. So, rather than the “objDBRecords” block above, simply use:
objDBCommand.Execute
And that’s it. Simple, eh?
Here’s the code in full for those that want it:
set objDBConnection = Server.CreateObject("ADODB.Connection")
objDBConnection.ConnectionTimeout = 15
objDBConnection.CommandTimeout = 30
objDBConnection.Open "YOUR CONNECTION STRING"
set objDBCommand = Server.CreateObject("ADODB.Command")
objDBCommand.ActiveConnection = objDBConnection
objDBCommand.CommandText = "SELECT id FROM yourtable WHERE column=? AND anothercolumn=?"
objDBCommand.CommandType = 1
set objDBParam = objDBCommand.CreateParameter("@column",200,1,50)
objDBCommand.Parameters.Append objDBParam
objDBCommand.Parameters("@column") = YOUR-INPUT-VARIABLE-HERE
set objDBParam = Nothing
set objDBParam = objDBCommand.CreateParameter("@anothercolumn",200,1,255)
objDBCommand.Parameters.Append objDBParam
objDBCommand.Parameters("@anothercolumn") = YOUR-OTHER-INPUT-VARIABLE-HERE
set objDBParam = Nothing
set objDBRecords = objDBCommand.Execute
if objDBRecords.EOF<>True then
arrOutput=objDBRecords.GetRows
end if
set objDBRecords = Nothing
set objDBCommand = Nothing
objDBConnection.Close
set objDBConnection = Nothing
You can probably see from that, I like my code neat and compact! And yes, I know you can use the while loop to run all this through in shorter order, but I like my code nicely laid out and understandable so that anyone can see what’s actually happening at every step. After all, maintainable code is the order of the day.
