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.Ad
ActiveSheet.Range(ActiveSh
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.Ad
ActiveSheet.Range(ActiveSh
objTable.CommandText = "MyProcedure"
objTable.Parameters.Add("M
objTable.Parameters.Add("M
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)
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.Ad
ActiveSheet.Range(ActiveSh
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.Ad
ActiveSheet.Range(ActiveSh
objTable.CommandText = "MyProcedure"
objTable.Parameters.Add("M
objTable.Parameters.Add("M
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
Last Comment
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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("@So
objCommand.Parameters("@So
objRS = objCommand.Execute
ActiveSheet.Range("A2").Co
I'll have to test it on tuesday before awarding points, just to be sure, but I expect no problems.
Thanks!
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("@So
objCommand.Parameters("@So
objRS = objCommand.Execute
ActiveSheet.Range("A2").Co
I'll have to test it on tuesday before awarding points, just to be sure, but I expect no problems.
Thanks!
I'm curious - wouldn't those parameters have the same effect either way you use them? (I'm not a dba!)
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!
(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!
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