Classic ASP - help using parameters

[AppalachiaAl]

I have some old code that I inherited and I need to update it to use parameters to prevent SQL injection attacks. I've tried the below but I get error that I "Must declare the variable '@fName'." Does some one see what's wrong. Also, if someone has a an example to update and insert using parameters I would appreciate it. I've tried googoling but you get about a million hits and everything is for .Net
Below is the code I have.

set objCmd = Server.CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = conn
.CommandText = "select userID from tblUser where fname = @fName and lname = @lName and company = @Company and email = @eMail"
.Parameters.Append .CreateParameter("@fName", adVarChar, adParamInput, 50, strfname)
.Parameters.Append .CreateParameter("@lName", adVarChar , adParamInput, 50, strlname)
.Parameters.Append .CreateParameter("@Company", adVarChar , adParamInput, 75, strcompany)
.Parameters.Append .CreateParameter("@eMail", adVarChar , adParamInput, 100, stremail)
Set rs2 = .Execute
End With
THanks
Allen

[Chikara]

I'm not the best with ASP but I looks like you are trying to call a stored procedure right?

Why exactly do you need parameters to prevent SQL injection attacks? Really all you need to do is trim and validate input before it goes into SQL. I wrote up a few subs that do this nicely.

[AppalachiaAl]

This is using a SQL string, not a stroed procedure, not sure if there is a difference in syntax. There is some cleansing of variables before concatenating, I was told that using parameters is a best practice but I haven't really found any good examples that show it in use. Can you share your subs for cleansing?

Thanks

[Chikara]

Yeah I can show some subs. Basically they do things like trim input, handle 's, and other SQL attacks. Give me a moment to consolidate them all.

I'm just surprised that you are using parameters and not calling a stored prod. To be honest, I didn't think you could use parameters without using stored procedures.

[AppalachiaAl]

Thanks Chikara. I know you do the parameters in .Net but not sure about classic asp. I can do the stored procedures in teh classic but I didn't want to have to go thru and change everything, if i did that, I might as well switch it to .net (which is not an option right now). It sounds like the subs you are talking about should fit the bill.:)

Allen

[Nandem]

I'd never see execute an query with a ado.command...

why dont u use an stored procedure ?
then call with command passing those parameters... =P


CREATE PROCEDURE dbo.myProc1
@myVarChar VARCHAR(100),
@myInt INT
AS
SELECT
COLUMN1,
COLUMN2
FROM
SomeTable
WHERE
Column4 = @myVarChar
AND
Column5 = @myInt

[AppalachiaAl]

I wasn't allocated much time for this project so I thought if I could do the query, that would be the quickest. I agree, using stored procs would be the best and I may still go that way, I can do those.

Thanks

[russell]

cant pass params that way to a SQL string. either use an SP or need to replace the params with the actual values in your SQL string -- or use sp_executeSql.

if u are paramaterizing for security purposes as your original post states, then a stored procedure is the correct choice.