Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Beauty is in the Eye of the Beholder Expand / Collapse
Author
Message
Posted Monday, September 11, 2006 3:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 132, Visits: 113
Comments posted to this topic are about the content posted at temp
Post #307782
Posted Monday, September 18, 2006 11:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2008 6:43 PM
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.




Post #309533
Posted Tuesday, September 19, 2006 2:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:51 AM
Points: 139, Visits: 4,615

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.

Post #309549
Posted Tuesday, September 19, 2006 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:28 PM
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.
Post #309558
Posted Tuesday, September 19, 2006 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 132, Visits: 113
Thanks. Just a note, I am pretty much an Oracle guy, so I can't answer any SQL specific questions.
Post #309560
Posted Tuesday, September 19, 2006 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:38 AM
Points: 6, Visits: 119

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.

 

Post #309580
Posted Tuesday, September 19, 2006 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 23, 2011 9:44 AM
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.

Post #309599
Posted Tuesday, September 19, 2006 6:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 15,518, Visits: 27,900

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #309600
Posted Tuesday, September 19, 2006 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 11, 2014 7:04 AM
Points: 144, Visits: 211
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.



Post #309602
Posted Tuesday, September 19, 2006 7:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:50 PM
Points: 292, Visits: 264
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
Post #309618
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse