How can I use an ADODB.CommandObject with ADODB.RecordSet?

Question

I am trying to make a Classic ASP/VBScript website more secure by making SQL statements parameterized.

I have the following function:

Function OpenUpdateableRS(strSQL)
    Dim rs
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQL, cnDZ, adOpenKeyset, adLockPessimistic, adCmdText
    Set OpenUpdateableRS = rs
    Set rs = Nothing
End Function

I intend to convert it to something like:

Function SecureOpenUpdateableRS(strSQL, strParam1, strParam2)
    Dim rs
    Dim cmdOB
    Set cmdOB = Server.CreateObject("ADODB.CommandObject")
    With cmdOB
        .ActiveConnection = cnDZ
        .CommandText = strSQL
        .Parameters(0).value = strParam1
        .Parameters(0).value = strParam2
    End With
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open cmdOB.Execute, , adOpenKeyset, adLockPessimistic
    Set SecureOpenUpdateableRS = rs
    Set rs = Nothing
End Function

When I call the function with:

Set rs = SecureOpenUpdateableRS("SELECT CustID, LastActive, LoggedIn, SessionID FROM tblLogins WHERE EMail = ? AND PWord = ?", strEMail, strPassword)

I get a "500 - Internal Server Error" which is probably because I disabled debugging on the server.
Any ideas on how I could make the original function more secure without breaking it?

Ekkehard.Horner · Accepted Answer · 2013-03-28T11:39:42.247

1

You'll have to create the parameters and append them to the command's parameter collection. Just assigning the values to (the same!) parameter can't possibly work. Google for a sample; perhaps this VB! sample will get you started.

Added:

I can think of two strategies to derive the parameter types:

If you pass the correct/maximally specified parameter values to the function you can map VarType(value) constants to parameter type constants
If you do a SELECT based on the fieldnames in the command text, you can map the recordset's field .Types to parameter type constants

It won't be trivial to get this right for all possible cases. I would pass pairs of value and desired type to the function.

edited Mar 28 '13 at 11:39

answered Mar 28 '13 at 09:53

Ekkehard.Horner

38,498
2
45
96

Thank you for the quick response. Is there any way I can use parameterized statements without being sure about the type of the input parameters? – Vinayak Mar 28 '13 at 11:02
Like the code on this StackOverflow page: http://stackoverflow.com/questions/11020846/classic-asp-protection-against-sql-injection – Vinayak Mar 28 '13 at 11:03

How can I use an ADODB.CommandObject with ADODB.RecordSet?

1 Answers1