September 18, 2006 at 11:58 pm
IMO if you have a "that should never" attitude towards dynamic sql or most other design decisions, you are doing your clients a disservice. Technology should be used to solve problems and often stored procedures creates another layer of debugging and specialty knowledge inside a company rather than solving a core problem. If you need your SQL Server to run at peak performance, you can't ignore the advantages of stored procedures however don't write off dynamic sql it does have advantages and they aren't minor in some instances.
In our company, our middle tier generates obscene amounts of SQL for us. Given the size of our application and limited resources, spending that time coding and maintaining stored procedures is man power better spent elsewhere. Do our SQL Servers run at optimum speed, no. However, that wasn't a criteria for the project and hasn't been a problem.
Our design doesn't fit all scenarios, but more often than not before DBAs even hear how the project is supposed to work they just jump on the Stored Proc bandwagon and think a company is retarded for not using them. They both have their place.
September 19, 2006 at 2:48 am
Interesting the idea but I've some point unclear...
You can do SQL injection using EXEC, but I think there's no injection possibility using the sp_executesql store procedure.
Is sp_executesql inneficient? Well, it reuses the execution plans because you pass to it the parameters to change.
Josep.
September 19, 2006 at 4:04 am
I am one of the developers who works from the basis that Dynamic SQL is "bad". Having said that, it is not gospel, simply a starting point. If there are alternatives, I feel that they should be used, however, often it is the only viable approach, in which case it is done as a conscious decision and appropriate precaustions can be taken. I am working with an application at present where, while I can read the data, I am not able to add any objects to the database itself, and I require input from the user. I agree with Stephen's point, that purely internal mitigates much of the risk. I still work on the assumption that there is some risk and therefore validate the user input.
A good article, IMO, which places the context of the code at the forefront and identifies the decisions made within the context.
September 19, 2006 at 4:23 am
Thanks. Just a note, I am pretty much an Oracle guy, so I can't answer any SQL specific questions.
September 19, 2006 at 5:17 am
Beauty is a tenuous term at best. Solving the problem and providing a solution are in a sense - beauty. Take a datagrid and plug in sortable columns custom user-selected page sizes and have it display a fair amout of data in 10 or more columns and you are "bad" because you have "dynamic SQL" and, heaven forbid, you can read it in the code-behind. I would suggest that for most, this solution is "beauty".
As a rule, the simplistic answer is that to defeat SQL injection, we must use stored procedures. Okay, so when that is done, what is the next crisis that will be created by the ne'er do well hackers of the world? I support Stephen's theory and thank him for some insight.
September 19, 2006 at 6:24 am
As with most things in life, dynamic SQL is great within context. Are GOTO's bad? Is VB bad? Is religion bad? Are guns bad? There are those who would exclaim "Yes" to each of those statements, but within context each of these are very useful and even elegant.
A little philosophical, maybe, but I'm just sayin' I agree with you. Use the best tool at your disposal when you need it.
September 19, 2006 at 6:25 am
So, to sum up the article... it depends.
That should be answer that DBA's use in most situations.
The thing is, there really are solutions (your example being one) where dynamic SQL is not only acceptable, it's preferred. Unfortunately, usually, when you see this religious debate going on, it's not between reasonable people. It's between code zealots, who don't/can't/won't deal in set based logic, treating TSQL as just another part of the coding architecture to let it do what it does best in ways that improve performance and elminate code reuse, versus DBA zealots, who don't/can't/won't deal in speed and flexibility over control and stability, treating all applications as interlopers into the sanctum sanctorum of the clean-room database environment who'd better wipe their muddy-assed boots at the stored procedure door. These two camps don't want to change.
The zealots aside, most of the time when I read about (or deal with) developers that are insistent that they MUST have dynamic SQL, it's because of a lack of knowledge. They can't understand set-based logic so they try to treat databases like flat files, writing out one line/row at a time. They don't have a good grasp of their own data access mechanisms, for example, they don't know how to pass parameters to stored procedures through ADO.NET. In these cases, while it's a pain the ass, taking the time to walk them through why stored procs are good things, how to use them, how to call them, reaps long term benefits.
Of course, I can just take out the hickory stick & go all Buford Pusser on their heads too. While that doesn't always help the developers, I feel better afterwards.
Nice article. I'm sure it's going to wake the zealots up again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 19, 2006 at 6:29 am
When choosing dynamic, parameterized SQL statements versus stored procedures, there are no technical advantages of one over the other.
Performance was mentioned. However, dynamic, parameterized SQL statements are just as efficient as the same code running in a stored procedure, especially with MSSQL. Execution plans are cached for parameterized SQL and stored procedures.
SQL injection was mentioned. Dynamic, parameterized SQL statements are no more susceptible to this than a stored procedure.
The key is to use parameterized SQL. That is...
BAD BAD BAD
"Select * From MyTable Where ID = " & userID
GOOD GOOD GOOD
"Select * From MyTable Where ID = @user-id"
If you use the latter form, that will perform the same and be just as safe as using a stored procedure. Don't allow performance and injection attacks to be a factor in the decision on which technique to use.
September 19, 2006 at 7:09 am
That was a very well written article and presents a great example of making the right choice for the given situation. Hurcane does provide a solution that meets the requirements while still protecting from SQL Injection attacks and may even provide a little bit better performance. Since the solution may not be using SQL Server, it is possible that this option was not available. The solution that Stephen described was definitely an elegant, cross-platform option and I appreciate his sharing it with us. I like anything that makes me double-check my knee-jerk reactions!
Thanks Stephen!
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
September 19, 2006 at 7:23 am
My pleasure Bryant! That's why I wrote the article. For all of our religious arguments, programming really isn't theology, it's a means to an end. If I get people to step back and question their assumptions (just to question, not to change), I would be very happy.
September 19, 2006 at 7:34 am
Good article. I agree that there are times when one has to use dynamic sql. Further, in the many cases that I have used it, the run time even with the compile time has been faster than the optimized code that doesn't use it, usually where variables are needed in the join clauses.
September 19, 2006 at 11:15 am
I agree with Stephen too. I also agree with you.
GOTO's served a purpose for a time but got misused and the misuse was what was actually bad. Same thing for religion and guns. Pizza and beer for that matter. Anything can be misused and it is that misuse that is bad.
Then you people who elevate things to the level of religion (Object Oriented Programming, Non-dynamic SQL, Oracle, C++, etc.) and treat the rest of us like errent dogs. We get told "Bad, bad." and swatted on the nose with a rolled up paper. Usually it's a "white paper" that they are trying to get paid for writing. Tisk, tisk, tisk. How sad.
Store procedures are fine things. SQL server implements them well. The jury is still out on CLR integration in 2005. As for VB, don't get me started. I admit that I love it but nothing lower than VB-6. Any project that gets re-written or major upgrade gets converted to 2005 instantly. We write handheld apps, desktop apps, and services in VB 2005. All high performance. The key is "don't use the slow objects" unless nothing else will work.
Just like anything can be misused that same thing can also be used well. Know the good and the bad. Know when something is good or bad in the particlular circumstances and then use something that you are paid for. Your own good judgement.
ATBCharles Kincaid
October 25, 2006 at 12:06 pm
Dynamic SQL has given me the ability to convert 1000s of lines of old Sybase SQL code to 100s of lines of code that are lookup file/table driven.
September 19, 2007 at 2:20 am
As a programmer heavily involved in database development, I normally avoid dynamic SQL within any code, but I've used plenty of it within stored procedures, which seem to give you the best of both worlds.
Of course, the actual sin of dynamic SQL is passing in un-verified text from the end user into the generated statement(s), as that's where injection occurs. If you simply generate the SQL within the stored proc, (or wherever), based on a few options passed by the user, then all they can do is pick from a limited set of SQL statements that you've already made sure aren't going to break the system.
The only thing I'd say that's "BAD, BAD, BAD" is discounting any option or technique available, so that's why I agree with the article's basic sentiments.
Paul
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply