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 «««2627282930»»

Eliminating Cursors Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 10:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #868291
Posted Thursday, February 18, 2010 11:06 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:23 PM
Points: 147, Visits: 556
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.



Post #868305
Posted Thursday, February 18, 2010 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 10:21 AM
Points: 36, Visits: 152
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.

Post #868309
Posted Thursday, February 18, 2010 2:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 5, 2010 2:52 PM
Points: 10, Visits: 28
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......




Post #868510
Posted Thursday, February 18, 2010 2:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #868539
Posted Thursday, February 18, 2010 2:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 10:21 AM
Points: 36, Visits: 152
ksh (2/18/2010)
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......



Not denying that dynamic sql works and has it's uses - I have agreed to that now and my original statement I have taken back as it was not correct. The question right now (from me at least) is the security of it. I am maintaining that if not used in the right way it poses a large security risk. I am also trying to understand how you can use dynamic sql without potentially opening more data to the user than one may want to.

One of the standards out there in application development is to use stored procedures for allowing the users to insert, update, delete or select data from a table. Thus you grant the user permissions to the stored procedure and not the table directly. This has many security benefits which can help stop things such as sql injection and data mining to just name a couple. However if I use dynamic sql in that same stored procedure I now have to give the user access to the same tables I was not wanting to give them direct access to in the first place.

Below would be an example of probably the worst possible case of dynamic sql one could ever create and it should be obvious very quick what the security implications are. No one in the right mind should ever do this, but it gives a very distinct example of how dynamic sql can be bad if not used correctly:

CREATE PROCEDURE up_dynamic @SQL varchar(1000) AS
exec(@SQL)

exec up_dynamic 'select * from master.dbo.sysprocesses;select * from sysobjects'
Post #868545
Posted Thursday, February 18, 2010 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 10:21 AM
Points: 36, Visits: 152

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.


Thank you for that information.
Post #868558
Posted Thursday, February 18, 2010 2:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 5, 2010 2:52 PM
Points: 10, Visits: 28
Dynamic SQL allows for the creation of command(s) from a complex set of parameters/variables/conditions. When used properly it enables the centralization, extensibility and re-use of sp code.

The SQL Inject "issue" is being addressed by others. FYI MS has some very good recent articles on the topic.



Post #868573
Posted Thursday, February 18, 2010 2:49 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Kevin Rathgeber (2/18/2010)
...
One of the standards out there in application development is to use stored procedures for allowing the users to insert, update, delete or select data from a table. Thus you grant the user permissions to the stored procedure and not the table directly. This has many security benefits which can help stop things such as sql injection and data mining to just name a couple. However if I use dynamic sql in that same stored procedure I now have to give the user access to the same tables I was not wanting to give them direct access to in the first place.

Below would be an example of probably the worst possible case of dynamic sql one could ever create and it should be obvious very quick what the security implications are. No one in the right mind should ever do this, but it gives a very distinct example of how dynamic sql can be bad if not used correctly:

CREATE PROCEDURE up_dynamic @SQL varchar(1000) AS
exec(@SQL)


I pretty much agree with everything that you are saying here. By way of demonstration, here is how you would write the above procedure to use Impersonation to solve the owner-chaining problem:
CREATE PROCEDURE up_dynamic @SQL varchar(1000) AS
exec(@SQL) AS 'NoPrivUser'

Leaving aside the hideous case of Injection here, that "AS 'NoPrivUser'" will cause the EXEC(..) string to be executed as that user. Of course the owner of the 'up_dynamic' proc has to have sufficient rights to do this too.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #868579
Posted Friday, February 19, 2010 5:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 35,792, Visits: 32,473
ksh (2/18/2010)
Dynamic SQL allows for the creation of command(s) from a complex set of parameters/variables/conditions. When used properly it enables the centralization, extensibility and re-use of sp code.

The SQL Inject "issue" is being addressed by others. FYI MS has some very good recent articles on the topic.


What are the links for those articles?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #868978
« Prev Topic | Next Topic »

Add to briefcase «««2627282930»»

Permissions Expand / Collapse