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 (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 isAnonymous
January 01, 2003
It is very common to us from CSS Security receive calls about SQL Injection and sometimes customers prefersAnonymous
January 01, 2003
- 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 toAnonymous
January 01, 2003
Microsoft has recently published a series of best practices to help developers build SQL code that isAnonymous
January 01, 2003
The comment has been removedAnonymous
January 01, 2003
While the default apps on a SBS 2003 (and upcoming SBS 2008) go through a SDL process so that I'mAnonymous
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