In asp classic is it possible to pass a parameter to plain sql?

I've seen the other questions...

Is this possible

    SQLStmt.CommandText = "select * from table where id=@id"
     SQLStmt.Parameters.Append SQLStmt.CreateParameter("id", adInteger, _
      adParamReturnValue)
     ' Set value of Param1 of the default collection to 22
     SQL开发者_如何学编程Stmt("id") = 22

Set SQLStmt.ActiveConnection = PropConnection
RSProp.Open SQLStmt

---

An approach I've recommended before is to declare variables and bind those to parameters, then refer to the variables in the actual SQL statement.

DECLARE @id = ?;
select * from table where id=@id

You still have to bind the parameters positionally, but you can keep using named variables in your SQL statements, and as a plus, if you use the same variable more than once, you only have to bind it once.

---

The answer is NO. As usual stupid old school VB and asp is full of hole, glitches, and non-sense.

So I whipped up this little Class to act as a wrapper and allow me to pass simple strings with @ prefixed variable names and generate non named parameter sql to send back to sql server.

This may seem silly, but to me being able to write my sql statments plainly such as Select field1,field2,field3 from table where field1 =@field1 and field2 = @field2 was extremely valuable to me. I've included an example of me using this code at the bottom.

Maybe I'm providing my self a false sense of security, but it seems to me not only did I block sql injection attacks by using parameterized queries, I also added another level of lock down, since I have the sql strings set as constants.

Class SQLBuilder

Private Sub Class_Initialize(  )
    Set internal_commandObject = Server.CreateObject("ADODB.Command")
End Sub

Private internal_sql
Private internal_parameters
private internal_commandObject

Public Property Let CommandType(ByVal value)
    internal_commandObject.CommandType = value
End Property

Public Property Get CommandType
    CommandType = internal_commandObject.CommandType
End Property

Public Property Let Prepared(ByVal value)
    internal_commandObject.Prepared = value
End Property

Public Property Get Prepared
    Prepared = internal_commandObject.Prepared
End Property

Public Property Get SQLCommandObject
Set SQLCommandObject = internal_commandObject
End Property

Public Property Let SQLCommandObject(ByVal value)
Set internal_commandObject = value
End Property

Public Property Get CommandText
    CommandText = internal_commandObject.CommandText
End Property

Public Property Let CommandText(ByVal sqlStatement)
    GetSQLParameters sqlStatement
    internal_commandObject.CommandText =  internal_sql
End Property

Public Property Get Parameters
    Set Parameters = internal_parameters
End Property

Private matches

Public Function SetParameter(name,datatype,direction,size,value)
 internal_commandObject.Parameters.Append internal_commandObject.CreateParameter(name,datatype,direction,size,value)
End Function 

Private Sub GetSQLParameters(sql)

    Set RegExObj = New RegExp

    With RegExObj
        .Global = true
        .Multiline = true
        .Pattern = "@\S+"
        .IgnoreCase = true
    End With

    Set internal_parameters =  CreateObject("Scripting.Dictionary")

    set matches = RegExObj.Execute(sql)

    With internal_parameters
        For each item in matches
        if Not .Exists(item.value) then  
            .Add item.value,item.value
        end if
        Next

    End With
    internal_sql = RegExObj.Replace(sql,"?")
End Sub
End Class

---

Public Const GET_PROPERTY_INFO = "select AccountNumber, ParcelID, UserAccount, LocationLookup, StreetNumber, Unit, OwnerLookup, LUC, NBC, isnull(TotalLand,0) as TotalLand, UpdtDate from STAGE.DataProperty where FISCAL_YEAR = @FISCAL_YEAR AND AccountNumber = @ACCOUNT_NUMBER"
Dim Temp
Dim mySqlBuilder
set mySqlBuilder = new SQLBuilder

With mySqlBuilder
    set .SQLCommandObject.ActiveConnection = PropConnection
    .CommandType = adCmdText
    .CommandText = GET_PROPERTY_INFO
    .Prepared = true
    .SetParameter "@FISCAL_YEAR",adInteger,adParamInput,4,Cint(Year)
    .SetParameter "@ACCOUNT_NUMBER",adVarChar,adParamInput,13,AccountNumber
End With

    RSProp.Open mySqlBuilder.SQLCommandObject