Been doing a bit of classic ASP again lately, so thought I'd write up one of my opinions on a best practice for ADO.
Contrary to what seems like popular belief, classic ASP provides objects to allow building of parameterized SQL statements. Thats right, you don't HAVE to concatenate a SQL statement in a string, you can use the ADODB.command object instead.
Be sure to #include adovbs.inc to have access to the constants referenced below.
Returning a Recordset:
With oSQLCommand
.ActiveConnection = oSQLConn
.CommandText = "usp_storedProcName"
.CommandType = adCmdStoredProc
'add input parameters
.Parameters.Append .CreateParameter("@TableId", adInteger, adParamInput, , iTableIdValue)
End With
'run the stored procedure
oRS.Open oSQLCommand
Returning OUTPUT parameters:
With oSQLCommand
.ActiveConnection = oSQLConn
.CommandText = "usp_storedProcName"
.CommandType = adCmdStoredProc
'add input parameters
.Parameters.Append .CreateParameter("@sFieldName", adVarChar, adParamInput, 20, sFieldValue)
'add output paramters
.Parameters.Append .CreateParameter("@iOutput", adInteger, adParamOutput, , 0)
'run the procedure and save the output
.Execute, , adExecuteNoRecords
iReturnedValue = .Parameters("@iOutput")
End With
Not using a stored procedure? Remove the CommandType, and put parameters in the WHERE clause:
With oSQLCommand
.ActiveConnection = oSQLConn
.CommandText = "SELECT * FROM whatever WHERE field1=@field1"
'add input parameters
.Parameters.Append .CreateParameter("field1", adInteger, adParamInput, , iTableIdValue)
End With
'run the stored procedure
oRS.Open oSQLCommand
Using the Command object may introduce it's own downsides, but it is certainly better than the way that some people still do data access in ASP:
"SELECT * FROM whatever WHERE field1='" & Replace(sField1Value, "'", "''") & '"
Aside from the fact that a list of named Parameter objects inside of a Command object being much easier to debug than a long concatenated string, a parameterized Command object will also be safer. Many people will argue that simply replacing single quotes will solve all SQL injection problems, but there are other vulnerabilities left open by bad coding practices. Yes, Yes I'm sure the code you write is perfect, but for the sake of argument lets say you inherited a project that someone else wrote with the following code and slack database permissions:
'hiddenId is assumed to be an integer, and is posted to the current page in a hidden form field.
iIntIdValue = Request.Form("hiddenId")
'previous developer did not put single quotes around the identifier since it should always be an integer
"SELECT * FROM whatever WHERE intIdentifier=" & Replace(iIntIdValue, "'", "''") & " Order by orderField"
Simply changing the hidden field's value to "1;TRUNCATE TABLE whatever--" before post will cause some nasty results if database permissions are not properly set. Of course nobody would be using a login that has TRUNCATE permission on a table with a production server right? Even if the database permissions are locked down fairly tightly then the user could still write an UPDATE or INSERT statement to change information in open tables.
Now lets assume you always use parameterized stored procedures AND replace single quotes. The above problem can still exist assuming the LAST parameter in the stored procedure call is a numeric value that is not wrapped in single quotes:
'hiddenId is assumed to be an integer, and is posted to the current page in a hidden form field.
iIntIdValue = Request.Form("hiddenId")
'previous developer did not put single quotes around the identifier since it should always be an integer
"usp_DoStuffToWhatever '" & Replace(sSomeVarCharValue, "'", "''") & "', " & Replace(iIntIdValue, "'", "''")
Changing the hidden field's value before post to "1;TRUNCATE TABLE whatever" will cause the stored procedure to run, then the second query will run.
The above examples are far from being advanced injection tactics, but I have seen the problem in practice, and thats more than enough to wreak havoc on the production databases.
Some good references for the Command and Parameter objects: (forget MSDN)
http://www.w3schools.com/ado/ado_ref_command.asp
http://www.w3schools.com/ado/ado_ref_parameter.asp