February 18, 2010 at 9:34 am
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.
Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
[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]
February 18, 2010 at 9:37 am
athornicroft (2/18/2010)
Hi there is a good thread here about eliminating cursors without the use of sub queries.
It is a myth that subqueries are necessarily a bad thing.
[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]
February 18, 2010 at 9:47 am
Josep (2/18/2010)
Kevin and Andy,I wouldn't like to be missunderstood: I absolutely agree with you. I only use dynamic sql when I cannot avoid it, as a last option. It has so many cons as you've said.
I wrote it as "another way to do it" but I also recommended to use the loop script with a @table (table variable) instead of dynamic sql.
Regards,
Josep
Completely understand. I suspected that was the case (I could have mentioned that a little better than I did) 😀
This was more a warning for those who are not in the know on this. I have seen many people get away with dynamic sql in their applications and having it work with no problems because they connected to the database as a dbo or as an sa user and not realise why it worked and that what they were doing was very bad.
February 18, 2010 at 9:54 am
RBarryYoung (2/18/2010)
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
Ok I will admit I was a little strong in that wording. I should say you should be very careful if you do have to use it.
February 18, 2010 at 9:57 am
Kevin Rathgeber (2/18/2010)
Completely understand. I suspected that was the case (I could have mentioned that a little better than I did) 😀This was more a warning for those who are not in the know on this. I have seen many people get away with dynamic sql in their applications and having it work with no problems because they connected to the database as a dbo or as an sa user and not realise why it worked and that what they were doing was very bad.
No sorry, you are incorrect. Although there is plenty of bad dynamic SQL out there, it is perfectly easy to write good, safe, and secure dynamic sql, and in many cases it is easily the best performing solution.
If there is an acceptable declarative static SQL way to do something, that is certainly preferred, but correctly written Dynamic SQL, is usually the next preferred option. Cursors and Loops are dead last.
[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]
February 18, 2010 at 10:01 am
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
Ok I will admit I was a little strong in that wording. I should say you should be very careful if you do have to use it.
SQL Injection anyone?
SQL Injection is not caused by Dynamic SQL, it is caused by executing user input as part of the SQL command stream, usually via poorly written dynamic sql, and/or poorly written Client code.
[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]
February 18, 2010 at 10:04 am
RBarryYoung (2/18/2010)
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
Ok I will admit I was a little strong in that wording. I should say you should be very careful if you do have to use it.
SQL Injection anyone?
SQL Injection is not caused by Dynamic SQL, it is caused by poorly written Dynamic SQL, and/or poorly written Client code.
While you are correct that poorly written Client Code is a cause of SQL Injection, you are assuming that the hacker could never have access to the SQL Server directly. If they ever find a way to get access, a poorly written Dynamic SQL statement can have a similar affect.
EDIT: Reworded the above
February 18, 2010 at 10:19 am
RBarryYoung (2/18/2010)
...Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
All costs? Sounds like trolling, to me. I know my boss would not be pleased if I announced I spent the morning shaving 20 milliseconds off a back-up job that still takes 10 minutes to run.
A nested cursor loop query that pegs the CPU for hours is an excellent candidate for re-factoring into a set-based solution, but why waste effort on a script where cursor overhead is an insignificant fraction of the total execution time?
If you're presented with requirements that dictate calling a stored procedure multiple times with a changing input parameter, (eg. vendor provided interface like BACKUP) you will wind up with a looping solution whether it's explicit or hidden. Of course the proper set-based solution is to create a new set-based stored procedure, but that's not always an option.
Dynamic SQL has it's pros and cons and there are many articles that explore them. I like this one by Erland Sommarskog: http://www.sommarskog.se/dynamic_sql.html
February 18, 2010 at 10:41 am
Andy DBA (2/18/2010)
RBarryYoung (2/18/2010)
...Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
All costs? Sounds like trolling, to me. I know my boss would not be pleased if I announced I spent the morning shaving 20 milliseconds off a back-up job that still takes 10 minutes to run.
A nested cursor loop query that pegs the CPU for hours is an excellent candidate for re-factoring into a set-based solution, but why waste effort on a script where cursor overhead is an insignificant fraction of the total execution time?
If you're presented with requirements that dictate calling a stored procedure multiple times with a changing input parameter, (eg. vendor provided interface like BACKUP) you will wind up with a looping solution whether it's explicit or hidden. Of course the proper set-based solution is to create a new set-based stored procedure, but that's not always an option.
Dynamic SQL has it's pros and cons and there are many articles that explore them. I like this one by Erland Sommarskog: http://www.sommarskog.se/dynamic_sql.html
At all costs implies that it's under your control, if it's not, then obviously you must do what you must.
aside, the thought of RBarryYoung being marked a troll made me chuckle, he's had some experience comparing cursors to loops to set-based code here at SSC.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 18, 2010 at 10:53 am
Andy DBA (2/18/2010)
RBarryYoung (2/18/2010)
...Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
All costs? Sounds like trolling, to me.
I'm trolling? Heh, if so then this must be my permanent lair.
Here are some articles elaborating my some of replies to your comments wrt Cursors:
http://www.sqlservercentral.com/articles/T-SQL/66097/
http://www.sqlservercentral.com/articles/T-SQL/66494/
In my experience, trolls don't usually go to the time and trouble of writing technical position articles that then have to be defended.
[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]
February 18, 2010 at 10:58 am
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
Ok I will admit I was a little strong in that wording. I should say you should be very careful if you do have to use it.
SQL Injection anyone?
SQL Injection is not caused by Dynamic SQL, it is caused by poorly written Dynamic SQL, and/or poorly written Client code.
While you are correct that poorly written Client Code is a cause of SQL Injection, you are assuming that the hacker could never have access to the SQL Server directly. If they ever find a way to get access, a poorly written Dynamic SQL statement can have a similar affect.
EDIT: Reworded the above
I am assuming nothing of the sort. And look again, I already included poorly written Dynamic SQL along with poorly written Client Code.
Correctly written dynamic SQL is every bit as safe as static SQL and is not that hard to write it correctly. Much easier to write, IMHO, than correctly written client code.
[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]
February 18, 2010 at 11:06 am
Whoa! RBarryYoung, I did not mean to call you or anybody else a troll. I respect your work and posts here at SSC. My point was that stating absolutes in a forum has a tendency to bring forth posts listing exceptions. After re-reading the thread, I realized you were repeating a phrase ("...at all costs") from a prior post. My humble apologies.
I spend many hours a week re-writing cursor based code inherited from other vendors and other developers and have, no doubt, used set based solutions presented by you and others here at SSC to great advantage. Eliminating cursors simply for the sake of eliminating cursors is not always cost effective though and I think the question about calling BACKUP multiple times is an example where it's not.
February 18, 2010 at 11:09 am
RBarryYoung (2/18/2010)
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
Ok I will admit I was a little strong in that wording. I should say you should be very careful if you do have to use it.
SQL Injection anyone?
SQL Injection is not caused by Dynamic SQL, it is caused by poorly written Dynamic SQL, and/or poorly written Client code.
While you are correct that poorly written Client Code is a cause of SQL Injection, you are assuming that the hacker could never have access to the SQL Server directly. If they ever find a way to get access, a poorly written Dynamic SQL statement can have a similar affect.
EDIT: Reworded the above
I am assuming nothing of the sort. And look again, I already included poorly written Dynamic SQL along with poorly written Client Code.
Correctly written dynamic SQL is every bit as safe as static SQL and is not that hard to write it correctly. Much easier to write, IMHO, than correctly written client code.
Then why did you say "SQL Injection is not caused by Dynamic SQL". I see you tried to qualify it later with the word poorly, but poorly also fits into Dynamic SQL as whole.
Can you show me small example of where "correctly written dynamic SQL is every bit as safe as static SQL"?
I just want to know what I am missing here, because almost every case I have run into, opens up more data to the user.
February 18, 2010 at 2:02 pm
what is being argued about? The tools were created to fulfill certain needs and are there to be used. Some tools are be better suited to certain situations than others.
Those who state absolutes about technique ("I fire anyone who uses a cursor","I despise anyone who uses Dynamic SQL","Temp tables are a product of the dark side","I never talk to anyone who capitalizes the letter b on Thursday's") are usually just displaying their lack of understanding of the use of the technique or tool.
FYI IMHO dynamic SQL rocks!!!!!! Have been using it for decades......
February 18, 2010 at 2:25 pm
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Kevin Rathgeber (2/18/2010)
RBarryYoung (2/18/2010)
Dynamic SQL is not preferred, but saying that it should be "avoided at all costs" is far too strong and just is not correct. There are many, many situations for which Dynamic SQL is not only OK, it is the best solution by far.Cursors are much, much more undesirable than Dynamic SQL, and should be avoided at all costs.
Ok I will admit I was a little strong in that wording. I should say you should be very careful if you do have to use it.
SQL Injection anyone?
SQL Injection is not caused by Dynamic SQL, it is caused by poorly written Dynamic SQL, and/or poorly written Client code.
While you are correct that poorly written Client Code is a cause of SQL Injection, you are assuming that the hacker could never have access to the SQL Server directly. If they ever find a way to get access, a poorly written Dynamic SQL statement can have a similar affect.
EDIT: Reworded the above
I am assuming nothing of the sort. And look again, I already included poorly written Dynamic SQL along with poorly written Client Code.
Correctly written dynamic SQL is every bit as safe as static SQL and is not that hard to write it correctly. Much easier to write, IMHO, than correctly written client code.
Then why did you say "SQL Injection is not caused by Dynamic SQL".
Because it is NOT caused by Dynamic SQL anymore than it is caused by client application code. This is a widespread myth based on widespread lack of understanding about what SQL Injection actually is and what actually causes it.
SQL Injection is caused by a very specific bad programming practice, that typically requires both client code and Dynamic SQL in order to pull it off. This "worst" practice is "injecting" user-supplied text into SQL command text, instead of keeping it safely encapsulated in parameters and variables where it belongs, and then using dynamic SQL to execute this now compromised command text. Dynamic SQL is not the cause, it is just one of the facilities that gets utilized by the programmers writing the Injection (that can later be exploited by hackers). Client code is another facility that gets utilised by injection, but I don't hear anyone suggesting that Client code should be avoided at all costs in order to prevent Injection.
Can you show me small example of where "correctly written dynamic SQL is every bit as safe as static SQL"?
Sure here's a great example of finding the exact current rowcount of any table whose name is passed to a stored proc as a string parameter. I very rarely see this common use of dynamic SQL written correctly and safely, so in another thread on another site, having a very similar discussion, I wrote this example to demonstrate it.
I just want to know what I am missing here, because almost every case I have run into, opens up more data to the user.
Here's what I think most people are missing on this matter:
1. Dynamic SQL is not the problem, Injection IS.
2. SQL Injection uses Dynamic SQL, but the two are far from synonymous.
3. Dynamic SQL is just a facility used for Injection, Client code is another.
4. It IS possible to write most needed dynamic SQL without Injection, and usually this is not hard.
5. Dynamic SQL is as safe as anything else in SQL, *IF*, you never Inject user-supplied text into the SQL command.
6. The principal means of doing 4 & 5 is to validate user-supplied text by replacing it with server-supplied text using some concrete table or function to generate valid-only text (in the context for which it is to be used).
7. The permissions problem with dynamic SQL that you alluded to earlier is only a problem for owner-chaining permissions. The stronger and safer privilege elevation techniques of either Impersonation or Certificates do not suffer from this problem and are preferred for Dynamic SQL in any event.
[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]
Viewing 15 posts - 271 through 285 (of 296 total)
You must be logged in to reply to this topic. Login to reply