Re: LINQ Queries vs Stored Procs

Tech-Archive recommends: Fix windows errors by optimizing your registry

Paul Shapiro <paul@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
I think the security issue is the big one. If ALL data access is via Stored
Procedures, you can limit the user to only executing stored procedures,
without allowing read permissions on any tables. If you have well-defined
application roles, you can group users into role-based security groups and
assign the minimally-necessary permissions on the stored procedures.

True. Where that level of security is actually necessary, stored procs
are great. Where they're not, I think they're an extra overhead.

Stored
procedures eliminate most sql injection attacks, which are more possible
with dynamic sql.

Can you show me a SQL injection attack with LINQ to SQL, which is the
dynamic sql option here?

I personally prefer putting sql into stored procs instead of the application
because I find it a more effective way to organize the code. It separates
the database processing details from the application processing. But I think
that's just personal preference.

That sounds great in theory, but I think it adds an extra overhead if
you need flexibility of search. It can also be a real pain in cases
where you need really dynamic queries, e.g. allowing users to specify
multiple criteria in a flexible way.

--
Jon Skeet - <skeet@xxxxxxxxx>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk
.

Relevant Pages

  • Re: Dynamic SQL Performance
    ... For me it primarily comes down to security concerns: Dynamic SQL leaves ... especially stored procedures that ...
    (microsoft.public.sqlserver.programming)
  • Re: [Info-ingres] RES: [Info-ingres] RES: [Info-ingres] SQL Injection attacks
    ... database server is never culpable; SQL injection attacks are the situations where the DB server does as it's told correctly, but is presented with a query which is not as intended by the app programmers. ... I'd almost agree if it weren't for stored procedures. ... Mike Leo ...
    (comp.databases.ingres)
  • Re: Avoiding SQL Injections
    ... You can also use Dynamic SQL with parameters as described at ... > Is the single act of using stored procedures the ... > only thing you have to do to prevent SQL injection attacks? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Avoiding SQL Injections
    ... SQL injection attacks can also be caused by stored procedures that use ... William Vaughn ... Microsoft MVP ...
    (microsoft.public.dotnet.framework.adonet)
  • Avoiding SQL Injections
    ... Is the single act of using stored procedures (in place of dynamic SQL) the ... only thing you have to do to prevent SQL injection attacks? ...
    (microsoft.public.dotnet.framework.adonet)