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.
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).