Setting CursorType with ADODB.Command.Execute

adodb, asp-classic, sql, sql-injection, vbscript

Solution

The answer, as far as I've been able to determine is that this is impossible with `ADODB.Command.Execute`, but it's possible with `ADODB.RecordSet.Open` using `ADODB.Command.Parameters`:

function CreateSQLParameter(arg)
    set param = Server.CreateObject("ADODB.Parameter")

    select TypeName(arg)
        case "String"
            param.Type = adVarChar
            param.Size = Len(CStr(arg))
            param.Value = CStr(arg)
        case "Integer"
            param.Type = adInteger
            param.Value = CLng(arg)
        case "Double"
            param.Type = adDouble
            param.Value = CDbl(arg)
        case else
            ' 13 is the "Type Mismatch" error code
            Err.Raise(13,,, "Type '" & TypeName(arg) "' is not handled. Please add support for it to CreateSQLParameter")
    end select

    set CreateSQLParameter = param
end function

function CreateSQLCommand(sql, args)
    set cmd = Server.CreateObject("ADODB.Command")
    'From http://www.w3schools.com/asp/prop_comm_commandtype.asp.
    'adCmdText is for some reason undefined in our scope.
    cmd.CommandType = 1
    cmd.CommandText = sql

    for i = Lbound(args) to Ubound(args)
        set param = CreateSQLParameter(args(i))
        cmd.Parameters.Append(param)
    next

    set CreateSQLCommand = cmd
end function

function ExecuteSQL(conn, sql, args)
    set cmd = CreateSQLCommand(sql, args)
    set rs = Server.CreateObject("ADODB.RecordSet")
    rs.Open(cmd, conn)

    set ExecuteSQL = rs
end function

Problem

Is there any way to set the `CursorType` for an `ADODB.RecordSet` which I obtain from `ADODB.Command.Execute`? I know that it is possible if I do: ``` rs = Server.CreateObject("ADODB.RecordSet") rs.Open(cmd) ``` However, I currently use `Command.Execute` with the `Parameters` parameter, which automatically handles variant-arrays of `?` parameters for safe interpolation. Therefore using `RecordSet.Open` does not appear to be an option. Specifically, my code currently looks like: ``` function ExecuteSQL(conn, sql, args) set ExecuteSQL_CmdObj = Server.CreateObject("ADODB.Command") ExecuteSQL_CmdObj.CommandType = adCmdText ExecuteSQL_CmdObj.CommandText = sql ExecuteSQL_CmdObj.ActiveConnection = conn if Ubound(args) = -1 then set ExecuteSQL = ExecuteSQL_CmdObj.Execute else set ExecuteSQL = ExecuteSQL_CmdObj.Execute(,args) end if end function ``` If I want to maintain this same API, but also control `CursorType`, how can this be accomplished?

Original source