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?

Answer 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:

1. If you pass the correct/maximally specified parameter values to the function you can map VarType(value) constants to parameter type constants
2. 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.