How to Make a Parametrized SQL Query on Classic Asp

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

How to make a parametrized SQL Query on Classic ASP?

Use the adodb.command object.

with createobject("adodb.command")
.activeConnection = application("connectionstring")
.commandText = "select * from sometable where id=?"
set rs = .execute( ,array(123))
end with

I would also advise to use a custom db access object instead of using adodb directly. This allows you to build a nicer api, improves testability and add hooks for debuging/logging/profiling. Secondly you can add request scoped transactions with implicit rollback's on errors using the class_terminiate event. Oure db access object offers the following query api

call db.execute("update some_table set column=? where id=?", array(value, id))
set rs = db.fetch_rs("select * from some_table where id=?", array(id))
count = db.fetch_scalar("select count(*) from some_table where column > ?", array(value))

How do I run a parameterized SQL query in classic ASP? And is it secure?

There are ADODB Objects which do basically the same thing.
ADODB.Command object is the equivalent to SqlCommand. From there it is basically doing the same as in .NET.

set cmd = Server.CreateOject("ADODB.Command")
cmd.CommandText = "select From Table where ID = @id")
set param = cmd.CreateParameter("@id", adInteger, adInput,0,0)

I frequently use w3schools for help about ADO objects.

MySQL / Classic ASP - Parameterized Queries

The code in your second snippet is correct, but should be applied to a new ADODB.Command object, not to the Connection object:

username = Trim(Request("username"))

'-----Added this-----
Dim cmdContent
Set cmdContent = Server.CreateObject("ADODB.Command")

' Use this line to associate the Command with your previously opened connection
Set cmdContent.ActiveConnection = connContent
'--------------------

cmdContent.Prepared = True

Const ad_nVarChar = 202
Const ad_ParamInput = 1

SQL = " SELECT * FROM users WHERE (username=?) ; "

Set newParameter = cmdContent.CreateParameter("@username", ad_nVarChar, ad_ParamInput, 20, username)
cmdContent.Parameters.Append newParameter

cmdContent.CommandText = SQL
Set rs = cmdContent.Execute

If NOT rs.EOF Then
' Do something...
End If

rs.Close

By the way, there was a typo with the spelling of adParamInput instead of ad_ParamInput (corrected in my example).

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

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);"


Related Topics



Leave a reply



Submit