SQL Injection Mitigation: Using Parameterized Queries part 2 (types and recordsets)

  • Article

(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 (https://msdn.microsoft.com/en-us/library/ms676529(VS.85).aspx).

Comments

  • Anonymous
    January 01, 2003
    Microsoft has recently published a series of best practices to help developers build SQL code that is

  • Anonymous
    January 01, 2003
    It is very common to us from CSS Security receive calls about SQL Injection and sometimes customers prefers

  • Anonymous
    January 01, 2003

  1. Introduction SQL Injection is a potential threat to any web application that has a SQL based database

  • Anonymous
    January 01, 2003
    I just sent a piece of e-mail to my team about input validation and SQL injection and it occurred to

  • Anonymous
    January 01, 2003
    Microsoft has recently published a series of best practices to help developers build SQL code that is

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'm

  • Anonymous
    January 01, 2003
    本文翻译自微软博客上刊载的相关文章，英文原文版权归原作者所有，特此声明。（特别感谢NeilCarpenter对本文写作提供的帮助） 近期趋势 从去年下半年开始，很多网站被损害，他们在用于生成动...

  • Anonymous
    January 01, 2003
    While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'm