Examples of O/R Mapping vs Stored Procedures

O/R Mapping (Object/Relational Mapping)

rbfigueira — Mon, 29 Jun 2009 22:19:11 GMT

Quem é que ainda não conhece o NHibernate , LLBLGen Pro entre outros ? Como sabem estas "tools"

O/R Mapping (Object/Relational Mapping)

rbfigueira — Sun, 28 Jun 2009 20:09:55 GMT

Quem é que ainda não conhece o NHibernate , LLBLGen Pro entre outros ? Como sabem estas "tools"

O/R Mapping (Object/Relational Mapping)

rbfigueira — Sat, 13 Jun 2009 20:13:41 GMT

Quem é que ainda não conhece o NHibernate , LLBLGen Pro entre outros ? Como sabem estas "tools"

O/R Mapping (Object/Relational Mapping)

TrackBack — Thu, 13 Jan 2005 16:25:00 GMT

re: Examples of O/R Mapping vs Stored Procedures

Christian Braun — Thu, 14 Oct 2004 08:16:00 GMT

Wow that was pretty fast, thx a lot Paul!

re: Examples of O/R Mapping vs Stored Procedures

Paul Wilson — Thu, 14 Oct 2004 00:34:00 GMT

Martin Fowler's "Patterns of Enterprise Application Architecture" is good background.

Christian Bauer and Gavin King's "Hibernate in Action" is the best practical example book.

Scott Ambler at "http://ambysoft.com/mappingObjects.html" has a fair amount of information online.

Other online resources are "http://www.objectarchitects.de/ObjectArchitects/orpatterns/index.htm"
and "http://www.joeyoder.com/Research/objectmappings/"

re: Examples of O/R Mapping vs Stored Procedures

Christian Braun — Wed, 13 Oct 2004 23:48:00 GMT

Hi there,

just yesterday i started my master thesis about o/r mappers. Can anyone of u give me a hint for good theories about that stuff? I really need 2 learn that stuff from the beginning, any good books would be great.
My main thesis will be about the pros and cons of O/R Mapping, perfomance and testing/describing some tools...
For any information, links and book tips i will be most grateful.

Best regards Christian

Excuse my bad english, i am german and not that used to it, sorry.

re: Examples of O/R Mapping vs Stored Procedures

Dennis v/d Stelt — Wed, 06 Oct 2004 08:27:00 GMT

Also another good reason NOT to use SP's is that most people put _so_ much functionality in SP's that they most of the time would not do in dynamiq-inline-sql statements (parameterised of course). I just came on a project where sp's are calling sp's and have lots of if's and way more.

Some freezing is done with a datetime as parameter. SQL functionality is used to get the last day of the previous month and that date is used as parameter for other SP's. But what if something goes wrong and you have to get the last day-of-the-month for 3 months ago? Then you have to rewrite the SP's or do everything manually. Just a simple example of things that can go very wrong with SP's because you're possible to do. I can only fear of what to expect once we can use .NET code in SQL2005!

re: Examples of O/R Mapping vs Stored Procedures

Jeff Gonzalez — Tue, 21 Sep 2004 22:15:00 GMT

Thanks for the information, we are using method #2 ourselves, but relying on cleaning data always makes me feel...dirty.

I guess you can probably account for 99.999% of attacks that way and that small 0.001% chance isn't enough benefit for the cost it takes to secure it.

Security and Flexibility always seem to be in direct contradiction of each other.

Thanks for the information

re: Examples of O/R Mapping vs Stored Procedures

Paul Wilson — Tue, 21 Sep 2004 20:59:00 GMT

First, all of my mapper's persistence is parameterized. Next, there are several ways that you can retrieve data -- and the option you choose can certainly impact whether there is any/much potential for sql injection.

(1) The simplest method is to just create all your own where-clauses -- I try NOT to do this in any of my examples since it obviously leaves the developer entirely responsible for "cleaning" all entered data.

(2) The next method, which is what I almost always use, is to build your where-clauses with my QueryHelper methods. This makes it easy to build "clean" equality expressions, as well as allowing you to not have to know the table and field names, or your databases delimiters. There is also a very good OPath syntax parser that can help you build other "clean" expressions in most cases. I find using the QueryHelper prevents nearly all sql injection, if not all, but I can't of course make any such 100% guarantee either, especially when multiple types of databases are involved.

(3) The final technique is to build the entire sql select statement with paramaters, and then use the overrides with the SelectProcedure class instead. This allows you to do all retrieval with parameterized sql, but it does require more work, so I don't typically do this since I believe the other solution good enough. Note that you can still even in this case use the QueryHelper to get your table and field names, although the rest would be up to the developer.

As for "Order By", the QueryHelper can again be used to validate field names, but certainly there is room for developers to not "clean" sort parameters entered by users.

In the end, I think my mapper has all the capabilities of being safe against sql injection, but there are certainly also capabilities for developers to do things poorly. So developers should still make sure that anything entered by the user (including querystrings) is properly "cleaned", and its also true that your databases should still be properly secured. Yes, its true that you need to allow read and write roles in your database for your application, but you can (1) not use sa or give ddl permissions and (2) secure your application correctly from unauthorized attacks. An ORMapper is a very good tool, but that's all it is -- one tool of hopefully many others in your toolbelt -- it should not be an excuse to be ignorant and/or lazy.