Parameterized Queries 1

[cjpettie]

I just inherited a legacy asp app that the client wants to make more secure. I'm having issues changing all the dynamic queries to parameterized queries. The app uses an oracle backend.

So far...
***********
Dim strSql, dcConnection, rsApplications, objCommand,

strSql = "SELECT DISTINCT [field1] FROM [table1] WHERE [field1] = :ID"

Set dcConnection = GetConnection()
Set rsApplications = Server.CreateObject("ADODB.Recordset")
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = dcConnection
objCommand.CommandText = strSql
objCommand.Parameters.Append (objCommand.CreateParameter(":ID", adVarChar, adParamInput, 4, "ABCD"))
Set rsApplications = objCommand.Execute()
***********

When I include the value "ABCD" in the WHERE clause it works as expected within a second. When I use the code above the query times out.

While grasping at straws I tried to swap out the : with @ and ? to no avail.

Any support would be appreciated.

 

[foxbox]

1) use ? in the strSQL
2) use the fieldname in the createparameter

<%
set objCommand = server.CreateObject("adodb.command")
Set objCommand.ActiveConnection = objConn
objCommand.CommandText = "SELECT DISTINCT [field1] FROM [table1] WHERE field1 = ?"

objCommand.CommandType = 1
Set param1 = objCommand.CreateParameter ( "field1", 129,1,4)
param1.value = "ABCD"
objCommand.Parameters.Append param1
Set objRS = objCommand.Execute()
%>

(129 = adChar, 1 = adParamInput)

 

[cjpettie]

Thanks for the solution foxbox! Now I have a ton of work ahead of me but at least I can stop spinning my wheels.

 

[tsuji]

>objCommand.Parameters.Append (objCommand.CreateParameter(":ID", adVarChar, adParamInput, 4, "ABCD"))
The main thing is to have those named constants defined explicit in your script if it is not already. Also the value 4 is not to be take as some magic number, it is the dynamic size to be inputted according to the parameter's value. The naming agreement between the use of ":ID" at two places is of no generic importance, you can name it as such or you can name it differently in the parameter object. Those are the main points to note.
[tt]
const adVarChar=200
const adParamInput=1
dim s
s="ABCD" 'or anything as the application intends to do
objCommand.Parameters.Append objCommand.CreateParameter(":ID", adVarChar, adParamInput, [red]len(s), s[/red])[/tt]