Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Beauty is in the Eye of the Beholder


Beauty is in the Eye of the Beholder

Author
Message
Stephen Hirsch
Stephen Hirsch
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 161
Comments posted to this topic are about the content posted at temp
Straegen
Straegen
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 3

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.





Josep
Josep
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 4666
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.
Brent Challis
Brent Challis
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
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.
Stephen Hirsch
Stephen Hirsch
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 161
Thanks. Just a note, I am pretty much an Oracle guy, so I can't answer any SQL specific questions.
Brian Hickey
Brian Hickey
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 129

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.


Dougie H
Dougie H
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 13

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.


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17580 Visits: 32260

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
hurcane
hurcane
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 254
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 = @UserID"

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.



Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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!

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search