Is it a best practice to use SP (store Proc) for every single SQL call in .NET applications?
Is it encouraged for performance reasons and to reduce surface area for SQL injection attacks (in web applications)?
+4
A:
No.
If you send your queries to SQL Server as parameterized queries, SQL Server will cache the execution plan AND will sanitize your parameter inputs properly to avoid SQL injection attacks.
Dave Markle
2008-12-04 13:32:44
Is it "avoid" or reduce the risk of sql injection? I thought elimination will require strong input validation, No?
Shaw
2008-12-04 13:36:07
As long as you always use parameters, rather than use concatenation, SQL injection is impossible. The reason is that parameters seperate the syntax from the values - anything contained in the parameters cannot affect how the SQL is parsed, because that has already been done.
Michael Madsen
2008-12-04 13:42:22
Parameterized queries are immune to sql injection AFAIK, unless the code is calling sp_executesql or something like that.
Barry Fandango
2008-12-04 13:43:06
Thanks! It's very helpful :)
Shaw
2008-12-04 13:49:46
Used properly, parameterized queries are immune to SQL injection. I've seen spocs that use paraters to pass parts of a dynamically created SQL statement that are wide open to SQL injection.
TGnat
2008-12-04 14:51:50
While this answer is technically correct (it's not a best practice, given the same query in an sp or parameterized query there's no performance advantage to the sp), there ARE some good reasons to use stored procedures. See my answer as well.
Joel Coehoorn
2008-12-04 15:32:46
There are additional means to prevent SQL injection attacks. I personally find inline SQL to be extreme and a step toward removing the database functionality from the DBAs, whom it rightfully belongs to.
joseph.ferris
2008-12-04 15:42:13
SQL injection is not just a problem on the web. Any time you take input from a client and use that input in a SQl statement you are at risk for sql injection. You could easily have SQL injection problems in a windows form app.
Jim
2008-12-04 15:46:15
@Joseph -- Separating DB development by giving it to a so-called "development DBA" isn't good for the schedule or budget of a project. Better to have *informed developers* than teams split by layer. CRUD queries aren't that hard. (Not that anyone should even be writing them in the first place!)
Dave Markle
2008-12-07 00:49:28
+2
A:
It's just one way of doing things. Upsides include keeping all your SQL code in one place, procs being verified for syntax at creation time, and being able to set permissions on procs, which usually represent some kind of "action" and are well suited to a conceptual security model.
Downsides include massive numbers of procs for any medium or larger application, and all the housekeeping that comes with that.
My employer's product uses procs for everything, and I must say with the right practices in place it's quite bearable.
Barry Fandango
2008-12-04 13:39:19
+3
A:
I prefer stored procs over inline SQL, because this way the SQL is one consolidated place; however, I prefer using a tool like nHibernate which will auto generate the SQL for me, then you have no SQL to worry about!
JoshBerke
2008-12-04 14:02:18
Is it good to recommend an open source stack in MS world considering MS is not a well known supporter of open source? My worry is what if an application breaks an upgrade to a higher .NET version due to lock-in to an open source? I'm new so, I could be talking non-sense.
Shaw
2008-12-04 14:09:44
Microsoft has been very quietly expanding largely into open source projects. A growing number of the projects on Codeplex, for example, are actually written by Microsoft and/or respective developers from their product teams.
joseph.ferris
2008-12-04 15:40:15
But, MS is actually behind Codeplex. Also, I'm thinking about open source frameworks like what we see in java communities.
Shaw
2008-12-04 16:02:19
MS's attitude has changed. This is evident by the inclusion of JQUERY in visual studio. The concern about framework breaking open source frameworks well that can occur if you use any non-ms solution (and even if you use ms solution)
JoshBerke
2008-12-04 20:34:29
+8
A:
Stored procedures have a few advantages over parameterized queries:
When used exclusively, you can turn off CREATE, INSERT, SELECT, UPDATE, ALTER, DROP, DELETE, etc access for your application accounts, and this way add a small amount of security.
They provide a consistent, manageable interface when you have multiple applications using the same database.
Using procedures allows a DBA to manage and tune queries even after an application is deployed.
Deploying small changes and bug fixes is much simpler.
They also have a few disadvantages:
The number of procedures can quickly grow to the point where maintaining them is difficult, and current tools don't provide a simple method for adequate documentation.
Parameterized queries put the database code next to the place where it's used. Stored procedures keep it far separated, making finding related code more difficult.
Stored procedures are harder to version.
You'll need to weigh those costs/benefits for your system.
Joel Coehoorn
2008-12-04 14:32:28
One advantage that you didn't mention is that it is easier to deploy a small change to the SQL code to fix a bug because you don't have to redeploy the application, just the one proc.I personally find it easier to determine the possible effect of proposed database structure changes as well.
HLGEM
2008-12-04 15:10:51
This is easily the best answer, but I'd like to add one thing based on the wording of the question. The number of procs shouldn't be 1-1 with the number of places you need to run a query in the code. If you find this is true, you need to write them to be more general purpose or modularize your code.
JohnFx
2008-12-04 15:35:17
+1 - Developers often fail to realize that they are not DBAs. Putting queries in code exclusively is, IMHO, no different than hard-coding. The maintenance of database functionality should not live with the developers.
joseph.ferris
2008-12-04 15:38:30
@Joseph - is ORM a bad idea as it keeps DBA away? A product company that delivers applications to be used by multiple databases will certainly benefit with a database agnostic architecture.However, ORM is usually considered a good solution - I have never understood. DB's are always a bigger asset.
Shaw
2008-12-04 15:50:54
It depends on the system: a DB that will definitely only ever serve a single application benefits from ORM, especially as some ORM's can be wired to use stored procedures. A DB that could serve several apps may want to reconsider.
Joel Coehoorn
2008-12-04 16:05:38
They can become trouble when Stored Procedures are used by multiple applications - updating a stored proc then impacts multiple applications. It's a design consideration at the end of the day.
RobS
2008-12-04 16:11:07
@Shaw - I don't think so. ORM is good, as it can reliably produce results. Of course, it varies with which ORM is being used, but I don't think that they are bad, per se. My objection is specifically in the form of developers writing production SQL.
joseph.ferris
2008-12-04 17:10:19
+3
A:
There is one more advantage - when it comes to tuning, especially per customer, it can be easily done with SP (by adding hints or even rewriting the code). With embedded SQL it is practically impossible.
Dmitry Khalatov
2008-12-04 14:54:35