ORM isn’t an excuse for not using Stored Procedures

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).
Smackdown
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.

About these ads

Tags:

7 Responses to “ORM isn’t an excuse for not using Stored Procedures”

  1. Ayende Rahien Says:

    * 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

  2. and Parameterised Queries aren't susceptible to SQL Injection « What is an Architect? Says:

    [...] 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 [...]

  3. Scott White Says:

    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

  4. You’re Not Your Data Access - Rob Conery - CodeBetter.Com - Stuff you need to Code Better! Says:

    [...] Well I might use both… [...]

  5. sandrar Says:

    Hi! I was surfing and found your blog post… nice! I love your blog. :) Cheers! Sandra. R.

  6. orm services india Says:

    Great content…!!! Waiting for more good posts from you.

  7. david maxwell Says:

    Thanks for sharing this valuable information,it is very useful for us and also for who provides
    Online Reputation Management Services
    .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: