Rick Cable's Tech Blog / RickCable.com

Web Development ~ SEO ~ InfoSec



Below are working code examples of basic parametrized queries done in Classic ASP VBScript along with and links to references I used to piece this article together


Extremely important to note that the first code example won't work without translation of the adCmdText" constant. You can find it in the adovbs.inc (include file) that contains all the ADO Constants we use for commands like the "adCmdText".  None of the other sources mentioned that at all. 


I've added a second code example that should allow you to ditch the need for the include file and just enter an enumeration of the CommandType. 


ADOVBS.INC Example:

'---- CommandTypeEnum Values ----

Const adCmdUnknown = &H0008

Const adCmdText = &H0001

Const adCmdTable = &H0002

Const adCmdStoredProc = &H0004

<%
 set rs = Server.CReateObject("ADODB.Recordset")
 set cmd1  = Server.CreateObject("ADODB.Command")
 Set conn = Server.CreateObject("ADODB.Connection")
 conn.Open [Connection String Value]
 cmd1.ActiveConnection = conn //connection object already created
 cmd1.CommandText = "SELECT * FROM [table] where ID = ?"
 cmd1.CommandType = adCmdText
 'cmd1.Prepared = True ' only needed if u plan to reuse this command often
 cmd1.Parameters.Refresh
 cmd1.Parameters(0).Value = "55"
 set rs = cmd1.Execute
 While NOT rs.eof
  Response.Write(rs("ID") & "
")
  rs.MoveNext
 Wend
 Set rs = Nothing
 Set conn = Nothing
%>

Can also be written replacing constant adCmdText with acceptable enumeration of 1 for the CommandType.


<%
set rs = Server.CReateObject("ADODB.Recordset")
set cmd1  = Server.CreateObject("ADODB.Command")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open [Connection String Value]
cmd1.ActiveConnection = conn //connection object already created
cmd1.CommandText = "SELECT * FROM [table] where ID = ?"
cmd1.CommandType = 1
'cmd1.Prepared = True ' only needed if u plan to reuse this command often
cmd1.Parameters.Refresh
cmd1.Parameters(0).Value = "55"
set rs = cmd1.Execute
While NOT rs.eof
    Response.Write(rs("ID") & "
")
    rs.MoveNext
Wend
Set rs = Nothing
Set conn = Nothing
%>


Technical References:


ADO CommandType Properties & Enumeration


https://www.w3schools.com/asp/prop_comm_commandtype.asp

Parameters Collection (ADO)


https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/parameters-collection-ado?view=sql-server-2017

https://blogs.technet.microsoft.com/neilcar/2008/05/23/sql-injection-mitigation-using-parameterized-queries-part-2-types-and-recordsets/ 

https://stackoverflow.com/questions/7654446/parameterized-query-in-classic-asp/9226886#9226886

https://vikaskanani.wordpress.com/2012/05/07/classic-asp-sql-injection-prevention-by-using-query-parameter/