asked on

Using QueryTable with SQL Server stored procedures from code - Including parameters

Experts,

I am trying to automatically import some data generated by a SQL Server stored procedure into Excel, from code. It kind of works, but the problem I am running into is that I can't seem to get the parameters to work like they should (in my mind). The code is actually executed on the webserver (javascript), but, I can replicate the exact behaviour in Excel VBA itself so I will use VBA as an example:

Dim objTable As QueryTable

objTable = ActiveSheet.QueryTables.Add(strConnectionString,
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 1))

objTable.CommandText = "MyProcedure " & lngSomeValue & ", " & lngSomeOtherValue
objTable.Refresh True

This in itself works fine. However, given my situation, the values given to the SP are from user input and, well, SQL injection is a thing to worry about. So, I would rather do something like:

objTable = ActiveSheet.QueryTables.Add(strConnectionString,
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, 1))

objTable.CommandText = "MyProcedure"
objTable.Parameters.Add("MyParam").SetParam 1, lngSomeValue
objTable.Parameters.Add("MySecondParam").SetParam 1, lngSomeOtherValue
objTable.Refresh True

...Which I can't get to work. How can I get the QueryTable to work while giving up individual parameters?

(I'm sorry if this is unclear - It feels kind of hard to explain)

Rory Archibald

How are you vulnerable to SQL injection the first way and not the second? Using an SP with parameters, which you are doing either way, should avoid the issue.
I'm not sure you can achieve your second goal using QueryTables (would be interested to be proven wrong though!) - you might have to look at using ADO and the CopyFromRecordset method instead.
Regards,
Rory

NeoTeq

ASKER

>> Using an SP with parameters, which you are doing either way, should avoid the issue.
Well, not quite. In the first example it would be quite possible to inject all sorts of nasty stuff into the query. In the second, this is a *lot* harder to accomplish, since I'll set the parameter types as they should be (int, bit, etc) with only minimal exceptions.

>> You might have to look at using ADO and the CopyFromRecordset method instead
Since, for the most part in my application, I use the ADO Command object, this may very well work too (not to mention it would keep my data access methods used at one :)). Please, give me a short example so I can try it.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NeoTeq

ASKER

>>Surely your parameters are typed in the SP?
Yeah, but that doesn't stop anything if I call it in a simple query:

intSomeKey = 5
strSomeString = "value'; DELETE SomethingElse"

objTable.CommandText = "MyProcedure " + intSomeKey + ", '" + strSomeString "'"

Anyway... I'm quite certain your idea with CopyFromRecordset will work:

Set objCommand = New ADODB.Command
objCommand.CommandType = 4
objCommand.CommandText = "MyProcedure"
objCommand.Parameters("@SomeKey") = intSomeKey
objCommand.Parameters("@SomeString") = strSomeString

objRS = objCommand.Execute
ActiveSheet.Range("A2").CopyFromRecordset objRS

I'll have to test it on tuesday before awarding points, just to be sure, but I expect no problems.

Thanks!

Rory Archibald

I'm curious - wouldn't those parameters have the same effect either way you use them? (I'm not a dba!)

NeoTeq

ASKER

No - They don't. I'm no DBA either, but when giving up the parameters in a command object, the values are *attempted* to be converted to their appropriate types. So if in a varchar or nvarchar, I would enter a string like "something'; DELETE FROM SomeTable; --", it would escape the single quote.

(I just tested this to confirm)

Anyway, your idea with the copyfromrecordset method works like a charm. Much faster than programatically looping through the rs and fields, and it has the added advantage of keeping my way of accessing the db uniform across everything.

I thank you, sir!