Avatar of NeoTeq
 asked on

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


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)
Microsoft SQL ServerMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
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.

>> 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.
Rory Archibald

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Rory Archibald

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

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!