Using Variables in Classic Asp Parameterized SQL

Using variables in Classic ASP parameterized SQL

If you want to avoid repetition, you can continue to DECLARE your variables and set their value once:

var sqlReview = "DECLARE @UserID AS Int = ?, @PgID AS Int = ?, @Rating AS TinyInt = ?;"
sqlReview += "DELETE FROM PGrating WHERE (UserID = @UserID) AND (PgID = @PgID);"
sqlReview += "INSERT INTO PGrating (InsertDate, PgID, UserID, Rating) VALUES (GETDATE(), @PgID, @UserID, @Rating);"

The above is assuming SQL Server 2008 or higher. On lower versions, you'd need a separate line for assignment:

var sqlReview = "DECLARE @UserID AS Int, @PgID AS Int, @Rating AS TinyInt;"
sqlReview += "SELECT @UserID = ?, @PgID = ?, @Rating = ?;"
sqlReview += "DELETE FROM PGrating WHERE (UserID = @UserID) AND (PgID = @PgID);"
sqlReview += "INSERT INTO PGrating (InsertDate, PgID, UserID, Rating) VALUES (GETDATE(), @PgID, @UserID, @Rating);"

How to use ASP variables in SQL statement

Add a parameter to the SQL:

delCmd.CommandText="DELETE * FROM post WHERE (pos_ID = ?)"
delCmd.Parameters.Append delCmd.CreateParameter("posid", adInteger, adParamInput)   ' input parameter
delCmd.Parameters("posid").Value = postit

Parameterized query in Classic Asp

In my code, this is how I get a recordset from a command:

Set rs = server.createobject("ADODB.Recordset")
Set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = Conn //connection object already created
cmd.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd.CommandType = adCmdText
cmd.CommandTimeout = 900 

set prm = cmd.CreateParameter("@prm", 200, 1, 200, "development")
cmd.Parameters.Append prm

' Execute the query for readonly
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

Hope it helps

Classic ASP / Parameterized Full Text Query

"@columnN" is the name of that parameter, and isn't related to the column columnN. This field is optional, so it could be unspecified for all of your parameters if you are never going to use the name when referring to it.

It can be used for retrieving the value of output and input/output parameters from the Command object, instead of referring to the parameter by the order in which it was appended to the Parameters collection. I believe that some DBMSs will also support using named parameters in the query string instead of ? (easier to read, presumably).

To answer your specific question,

Set newParameter = cmdConn.CreateParameter(, adInteger, adParamInput, Len(input), input)
cmdConn.Parameters.Append newParameter

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

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

---

Related Topics