在asp classic中是否可以将参数传递给纯sql？

Question

我看过其他问题...

这可能吗

    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
     SQLStmt("id") = 22

Set SQLStmt.ActiveConnection = PropConnection
RSProp.Open SQLStmt

Answer 1

我之前推荐的一种方法是声明变量并将它们绑定到参数，然后在实际的 SQL 语句中引用变量。

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

您仍然必须按位置绑定参数，但您可以继续在 SQL 语句中使用命名变量，而且如果您多次使用同一变量，则只需绑定一次。

Answer 2

答案是否。像往常一样，愚蠢的老式 VB 和 asp 充满了漏洞、故障和废话。

因此，我创建了这个小类来充当包装器，并允许我传递带有 @ 前缀的变量名的简单字符串，并生成非命名参数 sql 发送回 sql server。

这可能看起来很愚蠢，但对我来说，能够简单地编写 sql 语句，例如 Select field1,field2,field3 from table where field1 =@field1 and field2 = @field2 对我来说非常有价值。我在底部提供了一个使用此代码的示例。

也许我给自己提供了一种错误的安全感，但在我看来，我不仅通过使用参数化查询来阻止 sql 注入攻击，而且还添加了另一级锁定，因为我将 sql 字符串设置为常量。

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