2

Quick question... I have to work with quite a bit of legacy ASP code that I am cleaning up, and it all uses queries that are vulnerable to SQL injection. I have a library that I put together to replace them with parameterized queries, and I'm wondering if there is a difference from a security standpoint between the following approaches.

Approach 1: This is the approach shown on most examples where parameter objects are individually built and added to the Command object. Here's an example from another question.

Approach 2: Use the Command.Execute method with an array of parameter values. Example:

Command.CommandText = "select foo, bar from baz where a = ? and b = ?"
Command.Execute , Array(1, "BBB")

Yes, the first parameter to Execute is ignored.

The first approach has each parameter built with its type, size, etc all specified, and it needs to match the database. But I've always had trouble with that approach, weird errors and the like if everything isn't "just" perfect. So I prefer the latter, and it in fact works with my coding style much better because I can encapsulate the DB logic into a class and pass around arrays as needed without having to litter my code with tons of DB calls.

Example of approach #2 using my wrapper DB.Query method:

set rs = DB.Query("select foo, bar from baz where a = ? and b = ?", Array(1, "BBB")

Or:

set rs = DB.Query("select foo, bar from baz", empty)

(passing keyword empty to denote the parameter is not used)

Given that, I'm wondering: Is approach #2 still safe from SQL injection attacks?

Thanks.

Edit The call to Execute was wrong and written from memory, it has been corrected.

Community
  • 1
  • 1
Dave
  • 1,057
  • 2
  • 12
  • 18
  • taken from here (http://msdn.microsoft.com/en-us/library/ms808187.aspx) it seems as the Parameter Array is packed into the Parameters object of the command. but it seems that you cannot use the SQL Statement as the first Parameter of Execute? or does this work? – ulluoink Aug 22 '13 at 07:36
  • No you are absolutely right, I was tired when I wrote that from memory. My wrapper does that, i.e. DB.Query("select ...", Array(1, "BBB")) and it translates it into the Command.Execute call properly. I'll update the question to reflect the correct syntax. Thanks for pointing that out. – Dave Aug 23 '13 at 00:08

1 Answers1

2

From my sight: yes it is.

i wrote a quick example and then debugged it with Visual Studio. After the call to 

Command.Execute , Array(1, "BBB")

the Parameters object of the ADODB.Command is properly filled with the given values from the Array. The datatype and length of the parameters is correctly set.

So in my opinion this approach is as safe as the approach #1 (with a manually created Parameters object).

ulluoink
  • 2,775
  • 2
  • 17
  • 22
  • Thanks. When you say you debugged with VS was that debugging a classic ASP script? If so how did you do that? I have VS 2010 at work and can't create a classic ASP project and when I dragged a script into the IDE there was no option to run it locally or debug it. If I could run it locally that would change everything about how I work with it now. Right now I work on the server using a beefed up text editor. Thanks! – Dave Aug 24 '13 at 14:58
  • @Dave yes i run it locally on my machine in a IIS. i am using the "stop" keyword in my asp scripts to set breakpoints. there are some prerequisites though. you have to enable server side debugging for asp in your iis and so on. – ulluoink Aug 26 '13 at 05:52