SQL Injection Mitigation: Using Parameterized Queries part 2 (types and recordsets)
(Part 1 is here)
Previously, I provided a simple example of using parameterized queries in classic ASP; however, that sample lacked a few things such as explicit typing for the parameters. It also created a read-only ADODB.RecordSet which, obviously, isn't one-size-fits-all.
Typing
In the last installment, we had worked up this code to do our query:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _
& "Initial Catalog=website;User Id=user;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
strSql = "SELECT name, info FROM [companies] WHERE name = ?;"
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSql
objCommand.Parameters(0).value = strSearch
Set objSearchResults = objCommand.Execute()
As I noted then, this code has a minor performance issue because ADODB is going to have to made a round-trip to SQL to figure out the parameter type before it can execute the query. We can fix this and do input validation by explicitly typing our parameters like this:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _
& "Initial Catalog=website;User Id=user;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
strSql = "SELECT name, info FROM [companies] WHERE name = ?;"
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSql
set objParameter = objCommand.CreateParameter("search", adVarChar, adParamInput, 20)
objCommand.Parameters.Append objParameter
obParameter.value = strSearch
Set objSearchResults = objCommand.Execute()
Here, we are creating an explicit parameter with a type of adVarChar (ie, it's a string) that is an input parameter with a maximum length of 20. We append the parameter to our ADODB.Command object and set the parameter's value to the search string we want in our command. More info about ADODB.Parameter objects is here, more info about the possible types is here.
RecordSets
We may want to be able to write to the ADODB.RecordSet that we create; however, the code above won't work for that because it creates a recordset with the default parameters (Set objSearchResults = objCommand.Execute()). If we want to be able to update the recordset, we have to create it with explicit parameters:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _
& "Initial Catalog=website;User Id=user;Password=password;" _
& "Connect Timeout=15;Network Library=dbmssocn;"
strSql = "SELECT name, info FROM [companies] WHERE name = ?;"
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSql
set objParameter = objCommand.CreateParameter("search", adVarChar, adParamInput, 20)
objCommand.Parameters.Append objParameter
obParameter.value = strSearch
Set objSearchResults = Server.CreateObject("ADODB.RecordSet")
objSearchResults.Open objCommand,null,adOpenDynamic,adLockOptimistic
Now, we are explicitly providing parameters to indicate that we want a dynamic cursor (adOpenDynamic) and that we want optimistic locking (adLockOptimistic). This creates a recordset that can be updated via the RecordSet.Update method (http://msdn.microsoft.com/en-us/library/ms676529(VS.85).aspx).
