(Part 1 is here <http://blogs.technet.com/neilcar/archive/2008/05/21/sql-injection-mitigatio n-using-parameterized-queries.aspx> )
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
<http://msdn.microsoft.com/en-us/library/ms681010%28VS.85%29.aspx> , more
info about the possible types is here
<http://msdn.microsoft.com/en-us/library/ms675318%28VS.85%29.aspx> .
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
<http://msdn.microsoft.com/en-us/library/ms676529%28VS.85%29.aspx> ).
Published Friday, May 23, 2008 12:18 PM by neilcar
<http://blogs.technet.com/user/Profile.aspx?UserID=3663>
[Ph4nt0m] <http://www.ph4nt0m.org/>
[Ph4nt0m Security Team]
<http://blog.ph4nt0m.org/> [EMAIL PROTECTED]
Email: [EMAIL PROTECTED]
PingMe:
<http://cn.pingme.messenger.yahoo.com/webchat/ajax_webchat.php?yid=hanqin_wu
hq&sig=9ae1bbb1ae99009d8859e88e899ab2d1c2a17724>
=== V3ry G00d, V3ry Str0ng ===
=== Ultim4te H4cking ===
=== XPLOITZ ! ===
=== #_# ===
#If you brave,there is nothing you cannot achieve.#
--~--~---------~--~----~------------~-------~--~----~
要向邮件组发送邮件，请发到 [email protected]
要退订此邮件，请发邮件至 [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
<<inline: image001.gif>>
