How can I make a prepared statement in classic asp that prevents sql injection?

Question

I have this which works:

sqlString = "SELECT * FROM employees WHERE lastname = '" & last_name & "'"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = dbConn
cmd.CommandText = sqlString
cmd.Prepared = True
Set recs = cmd.Execute

The problem I have is that above the dynamic part of sqlString is before the prepared statement command. I don't think what I have above is protecting me.

Don't I have to fix this sqlString before I do the prepared statement? Reading this made me think that: How can prepared statements protect from SQL injection attacks?:

"While in case of prepared statements we don't alter our program, it remains intact That's the point.

We are sending program to the server first

 $db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called "placeholder" and then we're sending the data separately:

 $db->execute($data);

so, it can't alter our program and do any harm. Quite simple - isn't it?"

But I don't know how to make my query correct. I also don't know how he got from prepare to $data. Was hoping for guidance. Thanks.

score 10 · Answer 1 · answered Dec 16 '11 at 19:14

10

Why not use ADO command parameters?

var oCmd = Server.CreateObject("ADODB.Command");
oCmd.CommandText = "SELECT * FROM employees WHERE lastname = ?";
oCmd.Parameters.Append(oCmd.CreateParameter(undefined,202, 1, 50,"last name"))//adVarWChar

answered Dec 16 '11 at 19:14

aepheus

6,747
7
32
50

I will try it. Thanks. What is undefined,202, 1, 50,? – johnny Dec 16 '11 at 19:19
oCmd.CreateParameter([name],[type], [direction], [size],[value]) – aepheus Dec 16 '11 at 19:43
This would be an excellent place for somebody to link a good tutorial. – Stephen R Jun 09 '17 at 16:39
See also [Simple fetch ASP prepared statement](https://stackoverflow.com/questions/25259434/simple-fetch-asp-prepared-statement) for VBS example. – Vadzim Nov 19 '18 at 21:39

score 0 · Answer 2 · answered Dec 16 '11 at 19:13

0

Here's a good blog on how to prevent sql injection using classic asp.

http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx

answered Dec 16 '11 at 19:13

Robert

3,072
3
21
32

score 0 · Answer 3 · answered Dec 17 '11 at 03:29

0

The easiest is using stored procedures in SQL and using Commands that way.. Otherwise, you have to escape out certain characters being gathered from the Request object, like single quotes and double hyphens, etc.

answered Dec 17 '11 at 03:29

Control Freak

12,107
25
83
138

How can I make a prepared statement in classic asp that prevents sql injection?

3 Answers3

Linked