Today Jeremy linked to a post with some amusing comics but ended with a statement that came off to me as anti-stored procedures. I can already smell the debate of ad-hoc sql in your data layer versus stored procedures so I thought I'd write up a quick post describing why neither party is right, and that the pragmatic developer walks the middle of the road.
Stored Procedures Are More Secure
This is the strongest argument for using stored procedures versus ad-hoc sql. First, you can be sure that no sql injection attacks will ever occur. In the ad-hoc world you're depending on all developers and accounts that access the database to use parameterized queries.
A bigger reason is that in the ad-hoc world you actually have to grant the user/application the rights to change data in the server directly on the table. In my very humble opinion this is unacceptable. Should an account become compromised, the hacker now has rights to do basically whatever they want to your data, which is a bad situation to be in.
Stored Procedures (and views) Protect Your Application From Changes
As people jump onto the Agile/Unit Testing bandwagon, I see this common theme about insulating your application from damage from changes. So when I see people in the TDD crowd slamming stored procedures and views it greatly confuses me. If you run things though views and stored procedures, you have the power to completely rip out the underlaying data structure and just ensure that the stored procs and views return the new structure's data in the expected format for the application. Your DBA team can work completely independently of your programming team and do pretty much whatever they want and your application doesn't care! *gasp* a stored procedure or view is like a contract or interface.
In addition, you can make minor changes to a stored procedure without a recompile/redeploy, unlike ad-hoc.
Stored Procedures aren't portable
The biggest weakness. But only a weakness if you actually do support multiple databases. Unless you're an ISV or software vendor this is unlikely. I would wager that the vast majority of developers work with a single database platform.
Dynamic Where Clauses Are A Pain in the Ass
Dynamic queries are difficult for programmers to write, and not all that fun to look at when compared to ad-hoc querying.
So what do I do?
If I'm designing a system, or you're asking me what I recommend, here's what I do: Both. Here are my rules of thumb:
- As much business logic as possible goes into the code layer, it's job is to scrub the data for the database server. The database server only enforces ACID.
- Anything that changes data is a stored procedure. Accounts are granted execute on a need be basis to the stored procs. This way if something is compromised, they still have to play by your rules, limiting the damage.
- Reporting, filtering, and otherwise dynamic queries are done with in line/ad-hoc sql but must be executed against views with only select rights granted to the accounts.
- The database is designed to filter, sort, and index things far more efficiently than you could ever do on the programming side... let the database do what it does well.
The biggest thing to take away from the above is reducing your attack surface and jealously guarding your data integrity. Stored procedures and views are the best tools we have for this task.