SQL Injection Mitigation: Using Parameterized Queries
本文详细介绍了如何做Use SQL Parameterized Queries.
Michael Howard wrote an excellent article yesterday on how the SDL addresses SQL injection. He walks through three coding requirements/defenses:
- Use SQL Parameterized Queries
- Use Stored Procedures
- Use SQL Execute-only Permissions
As Michael points out, only the first, parameterized queries, remedies the problem. The other two provide additional defense.
The good news is that changing your ASP pages to use parameterized queries instead of just dynamically building the query is dead simple. The bad news is that MSDN doesn’t have a lot of samples of how to do parameterized queries in ASP so I thought providing one would be helpful.
As an example, I’m sure that a lot of the websites that have been compromised recently via SQL injection have something 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 =” & strSearch & “‘;”
Set objSearchResults = objConnection.Execute(strSQL)
This code is going to be extremely vulnerable to SQL injection since it’s just taking the user input (which was passed in via a query string from a web form) and pasting it into the SQL statement.
The good thing about parameterization is that it separates the ‘executable’ code (“SELECT name, info…”) from the ‘data’ (strSearch) we’re using. With a few changes, we can make this code use parameters for the query and, with this small change, defend against being exploited in this way.
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()
All that we needed to do was:
- Replace the query string in our SQL squery statement with a ? (which is the placeholder for a parameter).
- Create a new Command object for our command.
- Assign our active connection and command text to the Command object.
- Set the first parameter in the parameters collection to our dynamic string.
- Execute the command.
If we needed to use multiple parameters in our query, we’d add additional question marks to strSQL and additional parameters to the Parameters collection. For example:
…
strSql = “SELECT name, info FROM [companies] WHERE name = ?” _
& “AND info = ?;”
…
objCommand.Parameters(0).value = strName
objCommand.Parameters(1).value = strInfo
…
There is a BIG caveat on this — the method I show above has a performance hit because I haven’t specified the types of the parameters. This means that ADO has to make a roundtrip to the SQL server to figure out the type before actually using it. You can fix this by creating parameters objects with the appropriate type which would have the added bonus of doing simple input validation as well. If there’s interest, I’ll write a followup in the next few weeks with some samples of typed, parameterized queries. (EDIT: Written, it’s here.)
Additional info is available on MSDN here. NomadPete has a fuller walkthrough here that covers parameterized queries and stored procedures.
As always, this is only part of the job in securing against SQL injection; however, it is probably the single most useful change you could make.
(Big thanks to Bala Neerumalla for tech reviewing this for me.)
(Edit: Fixed two minor issues with the code examples. Thanks, Steve!)
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).
包子猜您可能还喜欢下列文章：
最近评论