Its good design practice to factor out SQL
statements/queries out of your middle tier business
logic objects/components. Improves the testability and
optimization of the SQL statements among other
benefits that others have alluded to:)
Shapiro...
"The best way to predict the future is to invent it."
- Unknown
--- Marc Brooks <[EMAIL PROTECTED]> wrote:
> > 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
>
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
