> 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
