Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,971 | Comments: 44,508

filter by tags archive

OR/M and SQL Injections


ScottGu is talking about SQL Injections and he mentions that neither stored procedures nor OR/M are a complete protection from it.

In a way, he is correct. If the stored procedure that you are using is called sp_executesql, then you are not protected from SQL injection. A more problematic approach is when your SP uses sp_executesql to do some of its work (usually because it need to work on a table dynamically, or your precedecor was a sadistic freak that enjoy doing things the really hard way).

Users of ORM are usually working with objects, and most ORMs offer strong OO query capabilities, so this is much less of an issue. Even when string queries are used, ORMs usually make working with parameters so much more easier than working with ADO.Net parameters, that there isn't any drive to use string concntation with most ORMs.

Nevertheless, I got several questions about issues related to just that, creating queries for NHibernate using string concantation. This is an issue all by itself, regardless of security (maintainablity and performance are both directly affected by it). But, following Scott's post, I tried to create an HQL injection using NHibernate.

 Now, NHibernate doesn't use SQL directly, it uses HQL, and object oriented querying language that is translated to SQL at the end of the road. After playing it for about 10 minutes, I was unable to come up with a query with string concantation that will pass both HQL and the translated SQL syntaxes property.

Here is a simple example:

session.Find("from User u where u.Username = '"+username+"' and u.Password = '"+password+"'");

What parameters will cause this query not to fail, but successfully do something that I didn't intend it to?

Note: I am not saying that NHibernate (or any other ORM) is not vulnerable to SQL injections, and the above code is in a very bad style. Just that I couldn't make it work after a few minutes of trying.

PluralizingNamingStrategy for NHibenrate


One of the nicest parts of developing with NHibernate is that you can get NHibernate to generate the table from the mapping. This is extremely useful during development, when changes to the domain model and the data model are fairly common. This is even more important when you want to try something on a different database. (For instnace, you may want to use a SQLite database during testing / development, and SQL Server for production, etc). I intend to post soon about unit testing applications that uses NHibernate, and this functionality is a extremely important in those cases.

This functionality has just one issue, unless you explicitly specify the table name, it uses the name of the class as the name of the table, this tends to give me ticks. Mostly because I am used to thinking about tables in plurals. A table named Employee is an anatema, a table named Employees is all right.

Take a look at this simple model:

(Image from clipboard).png

Creating the mapping for it is not hard, just tedious at times. Creating the mapping and then creating the tables is just boring, and the default NHibernate naming rules are not acceptable for me. I decided to take a leaf from Ruby On Rail (actually, I robbed the MonoRail generator for the source code, and converted it from Boo to Ruby) and create an Inflector and a pluralizing naming strategy. Now, when I generate the table structure from the mapping, I get the following database structure:

(Image from clipboard).png

Now this is much nicer.

 

NHibernate Caching: The Secong Level Cache Space Is Shared!


Let us assume that you have an architecture that demands that you'll have a database per client (quite common, by my experiance), but the application layers are the same. This is usually done for reasons of security and/or scalability. So there is no way a client can get information for another client.

This is fairly simple to handle with NHibernate, you just create a session factory per each client. This is actually an advantage, since you can start playing with the implementations of the classes for each client. (Yes, you are reading me correctly. In this case, it possible to turn NHibernate into an Inversion Of Control container).

The issue turns up when you have caching turned on. Assuming you have an entity of the same type with the same identifier in two of the databases, you may very will mix the data when it is cached. NHibernate maintain a single second level cache per session factory, so you are supposed to be safe from this. The problem is that the cache space is shared (and it has to be shared).

Let us take an example of an Employee object with id of #5, which exist in both IniTech and Fuzbar databases. When the employee instnace is loaded and put in the cache, the cache key that is used looks something like: "NHibernate:Entities.EmployeeManagement.Employee:5@5". Now, when we try to load an employee from the second database, we first lookup in the cache, finding that there is an instance there, because the cache keys matches.

This is happening because the cache space (so to speak) is the same space for all the caching strategies. For SysCache, it is the ASP.Net cache that is used for the cache space. For distributed caching, Memcached is used. In both cases, if the same cache key is generated, the information will be returned even if the key was actually inserted by another session factory.

In this sceanrio, I solved the issue by modifying the SysCacheProvider to be aware of the current database, and insert the current database name to the cache key. This solved all the issues in one stroke.

The JIRA issue for this is here. I was surprised to learn that I was the first to encounter this, and in the comments to the JIRA issue, I found that indeed I wasn't the first. :-)

The issue is solve by specifying cache region prefix in the session factory configuration, but the functionality wasn't ported to NHibernate yet.

Upward compatability: Preparing for next version


Okay, here is an interesting issue. I am currently building a big project using NHibernate 1.0.2, and I need some features on NHibernate 1.2, mostly with regard to the new critieras. At the moment, we can't move to 1.2, there are concerns about it being beta and other changes that will keep us busy with technical stuff rather than building business stuff. Those are valid concerns, but I still feel that it will take more time in the end to move to the new version.

I can solve the problem with 1.0.2, but not cleanly. I can back port the new stuff that I need (basically, a single class) and use it against NHibernate 1.0.2 (without modifications to NHibernate). The issue I wonder about is how to prepare for the eventual move to NHibernate 1.2. The last thing that I want is to have two ways to do something, or using the inferior method to handle it.

I already have a big comment right there:

//TODO: Fix this by XYZ when moving to NHibernate 1.2

 Any other suggestions?

My favoriate piece of code for this week...


I found that I need to group a collection by a certain attribute of its member quite often. Usually because I'm trying to do some crazy things with deeply nested and timed to put some data on the screen.

Anyway, here is the code:

public static IDictionary<T, ICollection<K>> GroupBy<K, T>(ICollection<K> collection, Converter<K, T> converter)

{

       Dictionary<T, ICollection<K>> dic = new Dictionary<T, ICollection<K>>();

       foreach (K k in collection)

       {

              T key = converter(k);

              if (dic.ContainsKey(key) == false)

              {

                     dic[key] = new List<K>();

              }

              dic[key].Add(k);

       }

       return dic;

}

It is not earth shattering or anything like it, but it is going to make a lot of tasks a lot easier now.

(BTW, this is now a part of Rhino Commons).

Rude installer


For a while, I was sure that I had a spyware on the machine, but it turned out to be just VS2005 SP1 Beta1 installer (from now on, known as SP1, because it is quite a mouthful).

(Image from clipboard).png

It kept pegging the CPU for about 20 minutes!

At least I managed to uninstall it and got back my Web Application Projects.

FUTURE POSTS

  1. Paying the rent online - about one day from now

There are posts all the way to Aug 03, 2015

RECENT SERIES

  1. Production postmortem (5):
    29 Jul 2015 - The evil licensing code
  2. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
  3. API Design (7):
    20 Jul 2015 - We’ll let the users sort it out
  4. What is new in RavenDB 3.5 (3):
    15 Jul 2015 - Exploring data in the dark
  5. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats