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
How to Create a Multi-Tenant Database with Shared Table Structures
Finding the Next Available Id in MySQL
SQL Server 2008 Paging Methods
Split One Column Value into Multiple Column Values
How to See the Values of a Table Variable at Debug Time in T-Sql
Built-In Function to Capitalise the First Letter of Each Word
Coldfusion - Variable Field Name When Looping Through Database Query Results
Delete Parent If It's Not Referenced by Any Other Child
Best Way to Do Nested Case Statement Logic in SQL Server
Alternatives to Replace on a Text or Ntext Datatype
Cte Error: "Types Don't Match Between the Anchor and the Recursive Part"
How to Alter This Computed Column in SQL Server 2008
When to Denormalize a Database Design