October 17, 2008 at 5:51 pm
We have two scripts that provide the same basic functionality, but one is for clients who still use SQL 2000 and the other is for clients running SQL 2005. Is there some way to put those two scripts into a single script that has some sort of SQL version directive that I could use so that only the correct part of the script would be seen?
Something like a VB6 compiler directive ...
#if SQLVersion = 2000#
-- execute the SQL Server 2000 script
#else if SQLVersion = 2005#
-- execute the SQL Server 2005 script
#end#
Thanks,
Tracy
October 18, 2008 at 7:15 pm
SELECT @@VERSION will return the current version of the SQL server it is run against.
That along with an IF by your VB app should cover it. Note that when you say script, I assume you don't mean Stored Procedure. If you do, you're probably not going to be able to put them both into the same SP, as the new commands won't parse on the old server without dynamic SQL (which I would not use here)
October 18, 2008 at 8:02 pm
Actually, you're still going to have problems, even if it is a script.
You see, SQL tries to compile everything in a batch. For a script, a batch is everything between GO statements. Even if you try to use IF @@VERSION... it won't work if you have incompatible syntax because it is still going to try to compile both sides of the IF branches.
Now, you might think that "I'll just use IF to branch around the batches" or "I'll put GO in the branches to break it into batches", but No, that won't work either: GO will terminate the IF also.
The only effective way around this is to use Dynamic SQL to construct your batch boundaries and to be able to explicitly control what actually gets compiled and to be able to use IF to branch around batches that you do not want to execute or even compile. (This is actually why Microsoft uses Dynamic SQL so much in their generated scripts).
So, not only is Dynamic SQL a good idea for this, it's pretty much your only choice (unless you want to write your own client code to control your scripts).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 18, 2008 at 8:30 pm
Is it bad practice to hard code the SQL into the front end in cases such as these? I wouldn't do it, but I know a lot of people do.
October 18, 2008 at 8:57 pm
Garadin (10/18/2008)
Is it bad practice to hard code the SQL into the front end in cases such as these? I wouldn't do it, but I know a lot of people do.
I am not sure what this means?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 19, 2008 at 12:19 am
In ASP for example, you can do:
sql="SELECT * FROM TABLE WHERE Field ='" & MyVariable & "'"
rs.open sql conn2
Instead of
sql ="Exec dbo.MySP @Variable =" & MyVariable
rs.open sql conn2
So she could have the IF in her front end application, and only try to exec the sql statement based on what @@Version returned. Like I said, I prefer not to do things this way, but I've seen a lot of developers do it. Of course, I might be confused by the term "script" as well.
October 19, 2008 at 2:26 am
Garadin (10/19/2008)
In ASP for example, you can do:sql="SELECT * FROM TABLE WHERE Field ='" & MyVariable & "'"
rs.open sql conn2
Instead of
sql ="Exec dbo.MySP @Variable =" & MyVariable
rs.open sql conn2
So she could have the IF in her front end application, and only try to exec the sql statement based on what @@Version returned. Like I said, I prefer not to do things this way, but I've seen a lot of developers do it. Of course, I might be confused by the term "script" as well.
You can do it that way, depends on what the client app is. That's what RBarry was suggesting when he said "unless you want to write your own client code to control your scripts"
Oh, and you may want to look at the code you've posted. Both have textbook SQL injection vulnerabilities.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2008 at 11:14 am
Right. Gail got it for me there. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 19, 2008 at 11:23 am
K, so we were basically saying the same thing, I misunderstood as it seemed like your post was in response to mine. The code posted was purely for explanation's sake. That said, I see how the hard coded one has injection vulnerabilities, but how is the SP vulnerable if that variable is used within a structured select statement?
October 19, 2008 at 12:03 pm
Well, let's say that MyVariable was set to " '; DROP TABLE USERS --", then the sql string in your client becomes:SELECT * FROM TABLE WHERE Field =' '; DROP TABLE USERS --, which might be a problem :w00t:. Effecitvely, the injection happened in the client code, instead of in the SQL Server, but the result is the same.
The question that has to be asked in cases like these is "Where is MyVariable coming from?" If it is from a user who shouldn't be allowed to drop the Users table then you (both client & server) should take steps to prevent this. If on the other hand, it is coming from you or another DBA or Admin who could do this anyway, then you do not normally have to be concerned. The threat with injection is that an unprivliged user can play "stupid string tricks" to hijack the SQL Server rights of the server process, either directly or indirectly (through the client).
That's why protecting against injection is really a two tier defense;
1) The client should never allow user-supplied text to become part of a SQL command that it executes, and...
2) The server should never allow client-supplied text to become part of a SQL command that it executes
#2 is most easily accomplished by forcing the client to use stored procedures and then in those stored procedures, either never use dynamic SQL or by being extremely careful in how you construct the dynamic sql, again, following the rule that client-supplied text (parameters) must never become part of the actual SQL command. (additionally, you should use different users/schemas to firewall the access rights of each piece of the execution chain).
If you do this, then #1 can be accomplished on the client by never trying to "compose" SQL commands (effectively, dynamic SQL) but rather always calling stored procedures and passing the user-supplied text as parameters.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 19, 2008 at 12:05 pm
Garadin (10/19/2008)
The code posted was purely for explanation's sake.
Sure, but people are going to see it and possibly take it as an example of how things should be done.
That said, I see how the hard coded one has injection vulnerabilities, but how is the SP vulnerable if that variable is used within a structured select statement?
Because you're concatenating together the call for it and that leaves a possibility that a user can add a second command to the end. It's got nothing to do with the proc itself, but rather how you're calling it. Consider the following.
MyVariable = "sysobjects" // assume this is the value that a user's entered in a text box
sql ="Exec sp_help @objname = '" & MyVariable & "'"
rs.open sql conn2
All well and good. We get the results of sp_help for sysobjects. Now, how about this one?
MyVariable = "sysobjects'; DROP TABLE Errorlog --" // assume this is the value that a user's entered in a text box
sql ="Exec sp_help @objname = '" & MyVariable & "'"
rs.open sql conn2
The results of the concatenation, and what will be passed as a command to rs.open is
Exec sp_help @objname = 'sysobjects'; DROP TABLE Errorlog --'
Anyone want to run that on Adventureworks? (with a backup taken first, of course)
The way it should be done is to use the parameter collection. Something (roughly) like this (pseudocode follows)
cmd = new sqlcommand(conn2);
cmd.commandtype = "StoredProcedure"
cmd.commandtext = "sp_help";
cmd.parameters.add("@objname","sysobjects")
rs = cmd.execute
That ensures that even if malicious values are entered in, they will be considered part of the parameter value, not part of the command. It will be the equivalent of running
SET @paramValue = 'sysobjects''; DROP TABLE Errorlog --'
Exec sp_help @objname = @paramValue
And that is safe to run. Running that in adventureworks results in
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'sysobjects'; DROP TABLE Errorlog --' does not exist in database 'AdventureWorks' or is invalid for this operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2008 at 2:57 pm
Makes perfect sense, I just thought about it like that. Thanks much for both explanations, that'll close a whole lot of holes on our new website build.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply