Sure it does since the developers have to submit their stored procs to me to be applied ;-) I have no control what they do in their crappy code but I have almost total control over what gets applied to the database. If I allowed dynamic SQL in my stored procs (which requires permissions to the underlying tables) then why would I care if they put it in their business objects, front end or whatever. Where there is no separation of duties and no control you are just about guaranteed to have crap regardless of your beliefs on where the business logic goes and the value of stored procedures.
Yes, while you are technically correct that it is parameterization that prevents SQL injection I am pretty sure you understand the point I was making. It is great that there are many intelligent people on this list who can argue so well, but I have found some of the hair splitting and nit picking that goes on here detracts from the value of the discussion. Tim Flory Sr Programmer Analyst Phone: 216.464.2244 ext. 229 www.medquist.com This electronic mail transmission contains confidential information intended only for the person(s) named. Any use, distribution, copying or disclosure by another person is strictly prohibited. If you are not the intended recipient of this e-mail, promptly delete it and all attachments. "Unmoderated discussion of advanced .NET topics." <[email protected]> wrote on 09/14/2005 04:37:08 PM: > > Just because you can accomplish some of these through parameterized sql > > does not invalidate the reasons for using stored procedures. > > You proposed SQL Injection as a reason for using stored procedures > OVER dynamic SQL. I've stated that parameterized dynamic SQL is just > as safe as stored procedures for preventing that. > > To say that stored procedures are safe from SQL Injection > automatically would be wrong anyway. I'm sure you've seen things like > assembling a dynamic query in the stored procedure and using EXEC. > That's bad practice, of course, but SO is doing dynamic SQL without > parameters. My point is that stored procedures don't give you the > safety any more than dynamic SQL; it's the parameterization that does > it. You can do injection even in the presence of all-stored > procedures by doing stupid things like this if you've got id10ts > working on your system (and yes, I've seen this exact thing done!): > > string userName = " ');DROP TABLE foo;"; > command.CommandText = string.Format("EXEC crud_InsertBang('{0}');", userName); > > > Additionally, if I limit access to my data through stored procedures > > I am guaranteed these things. > > Not if the stored procedures are executed or coded incorrectly! > > > If not I have to trust others to do the right thing, > > especially in a loosely controlled environment. > > Yes, you do... you have to trust that the stored procedures are coded > correctly. You have to trust that they are called with parameters and > not via dynamic SQL construction. > > > If trust was effective there would be no need for security. > > Security and trust are NOT exchangeable. This IS the one attribute of > the stored procedures that you were 100% on. I can grant rights to a > stored procedure that accesses table the user doesn't have rights to, > and things work safely. > > > Additionally, the use of stored procedures increases the chance that > > the stored procedure code can used in multiple applications. > > True. If a procedure is useful in multiple applications that cannot > share code libraries, you have a gain... in the .Net world and SOA > business layers, I'm less worried about that that I used to be. > > > Also, as another poster mentioned, you have some > > users who are savvy enough to use Access and linked > > tables. > > That is again where security comes into the picture. What the heck is > going on when your "users" have access to the database. If you're > doing SOA / BLL systems, they have an independant login should not be > known to the outside world! > > -- > "Under capitalism, man exploits man. Under communism, it's just the > opposite." –John Kenneth Gailbraith > > Marc C. Brooks > mailto:[EMAIL PROTECTED] > http://musingmarc.blogspot.com
