When I have looked at ORM tools, specifically NHibernate, the one thing that has always stopped me from using them is the fact that they dynamically build the SQL to access the database. For years it has been drummed in that you should always use Stored Procedures to access your database. The key reasons are:
- Performance: Stored Procedures are fasted than un-compiled statements
- Security: If you use Stored Procedures you don’t need to give permissions to the underlying tables
- Vulnerability:Stored Procedures aren’t suseptable to SQL injection attacks
I was very pleased to hear via Dot Net Rocks that there is a ORM tool, iBATIS that allows you to use Stored Procedures. The configuration would look like this:
<procedure id="SwapEmailAddresses" parameterMap="swap-params">
ps_swap_email_address
</procedure>
...
<parameterMap id="swap-params">
<parameter property="email1" column="First_Email" />
<parameter property="email2" column="Second_Email" />
</parameterMap>
ORM has been discussed on pretty much every episode of Dot Net Rocks since they interviewed Oren Eini from the NHibernate project. It is interesting to get Richard Campbell‘s perspective given his DBA slant on things. The impression I get from Richard’s comments is that he wants the SQL generated by these tools to be as good as any SQL that would be hand buit by a developer (Stored Procedures included).
Sounds like all of this discussion is going to culminate in a passionate ORM panel discussion, or a Carl refers to it ORM Smack-down. I will definitely have to blog about that when it happens.
Tags: .Net
12 April 2007 at 5:44 am |
* SP have no perf advantage over parameterized queries
* See here why security is less of an issue that you make of it:
http://www.ayende.com/Blog/archive/7698.aspx
* I don’t know of a way you could do a successul SQL Injection into an ORM. There are contrived examples, of course, but they are as valid for SP
12 April 2007 at 9:30 pm |
[…] to SQL Injection Got a well deserved comment from Oren Eini (aka Ayende) on my post about ORM tools and their support for Stored Procedures. I sort of expected the performance comment as it is just becoming less and less an issue with each […]
26 June 2008 at 7:12 am |
Sprocs may be precompiled but when the parameters change it must be recompiled thus negating performance increase.
See why I reconsidered this as a best practice: http://scottwhite.blogspot.com/2008/04/stored-procedures-reconsidered.html
12 June 2009 at 1:52 pm |
[…] Well I might use both… […]
10 September 2009 at 11:17 pm |
Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.
5 October 2013 at 11:17 pm |
Great content…!!! Waiting for more good posts from you.
14 May 2014 at 10:44 pm |
Thanks for sharing this valuable information,it is very useful for us and also for who provides
Online Reputation Management Services .