Ayende @ Rahien

It's a girl

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.

 

Funny quote

I'm reading a lot of books recently, and I run into another nice quote:

Let's write a three-tiered application that utilizes this pattern.

 

Tags:

Published at

Background Worker

I just found myself writing this in an email:

It [background worker] allows you to use multi threading with drag & drop, which was the first time many developers used multi threading. Many developers still don't.

Let me repeat this "multi threading with drag & drop"...

I'm going to have a liedown.

Tags:

Published at

NHibernate Query Generator Updates

I just added support for <componet> and <dynamic-component> (in the SVN only, for now).

I'm considerring adding support for projections and the new expressions in NHibernate 1.2, any suggestions? I though about something like:

Projections.Blog.Posts.Count

And:

Projections.Blog.Date.Max

Suggestions?

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.

Tags:

Published at

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?

Tags:

Published at

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.

Published at

NHibernate Generics 1.0.12

I got a couple of complaints about regression in NHibernate Generics 1.0.11, about using an abstract class. I hope that this would fix the issue.

Code and binaries are here

.Net The Spawn Of Satan

I just found the text below on the NUnit forum (removed since then), and I couldn't stop laughing.

Please do not develope components for .NET  It has become required for a few companies apps like Sony and ATI. It installs over 15,000 registry entries!

It is a complete hostile takeover of a system. It does not benefit the user.

It is very similar to a Vampire. A vampire must be invited in to give it power.

The .NET component must be downloaded of your own free will to give it power.

They cannot sell it to you as that would be illegal due to what it does once it is installed. Please study this issue carefully through forensic examination to reveal exactly what .NET is actually doing for both you and for the master controllers. This is a dangerous trend in computing. Please do not support it and especially do not support companies that require it.

I will be going to look for my tinfoil hat now...

Tags:

Published at

NHibernate 1.2 Beta1 Released!

Yesterday the beta for NHibernate 1.2 was released, the new features that interests me are:

  • Native generics (you no longer have to use the NHibernate.Generics compatability library).
  • Batching support (SQL Server only) - huge perforamance increase in some scenarios.
  • Support for using Stored Procedures.
  • Support for named connection strings.
  • SQL Server 2005 and SQL Server 2005 Everywhere dialects
  • Parametrized types and User Collection Types - for the really advanced scenarios :-)
  • Support for projections in criteria queries - This one looks especially interesting
  • IL-based reflection optimizer - another big performance boost.

You can get the new beta release here, and the full release notes are here.

Tags:

Published at

Book Review: Old Man's War


Old Man's War

Only occasionally I get to read a book that make go "Wow!", merely because the strength of the plot and the charm of the charaters. Scalzi creates a full fledge world (universe, actually) very quick, very easily and very convincingly.

The story reminded me strongly of Hienlien's Starship Troopers (although the only common plot idea is war among the stars). As someone who grew up on Hienlien's books, that is a high praise.

There are two other books in the same universe, and I'm looking forward to reading them...

Tags:

Published at

Hanselminutes 33 & Hibernation

I am currently listenining to Hansleminutes #33 and found out that Mac users has an advantage over Windows users in the laptop department. Apperantly Mac user can simply shut the lid, put it in the bag and go on.

I've been doing the exact that with my laptop, whose uptime at the exceeded the 40 days mark before the @#$% Windows Update decided that it is a good time to force a shutdown, with nary a problem.

Binsor on track (and trunk)

IoC 29 - Required to make Binsor work, has moved (with changes from Hammett), to the trunk. I've update Rhino Commons with the new changes, and now you can use it with the Windsor from Castle's trunk.

Have fun...

 

Girls - WTF?!

I just had a converstion with a friend of mine, that started like this:

Her: (accusing) "Why didn't you call to say happy new year*?"
Me: "I don't call anyone to say that."
Her: "It is good that you didn't call, I hate when people do that."

Damned if I do, and damned if I don't. God help me if I could ever understand women.

* It is the Jewish new year.

Upgraded to dasBlog 1.9

The blog was down for about half an hour while I updated to dasBlog 1.9. You should be able to notice a major speedup.

The nice feature that I really want is the tag cloud support, but I can't figure out how to make it work, I added it to the theme, but it isn't outputting anything. You should notice the nice category view, though.

Tags:

Published at

Brail Breaking Change: Removing the code markers

Just read this post, talking about a problem with generating valid XML for IE using Brail. The issue is that Brail considers <? ?> as code blocks markers, thus completely disabling support for xml processing instructions.

This is a breaking change in Brail, but I feel that the reason for removing them is strong enough to do so. If you have got Brail code that uses <? ?>, you need to replace it to either <% %> (not recommended) or <?brail ?> (recommended, valid XML). If this is a really big issue for you, please inform me, and I'll create a compatability mode.

And Ken, I couldn't post any comments to your blog for some reason.

What kind on an idiot?

I just good a Happy New Year email from a company I bought some nonsense from about three years ago. Leaving aside the fact that I contains no memory of why I was in their distribution list, I have posted below all the emails that were in the To field. This is at 40% resolution, and it was enough to fill up a whole page in word.

(Image from clipboard).png

Way to go.

Side benefits of testablity

A while ago I needed to test the output of a class that wrote data to a file. I didn't want to start reading/ writing files in my tests, so I created the following interface:

public interface IOutputFactory
{
  TextWriter Create(string name);
}

This was easy to mock, and I could get the output back very easily. I didn't gave it much thought afterward, except to cuckle a bit at this interface's purpose. The implementation was simple:

return File.CreateText(name);

Then, I got a new requirement from the customer: When I finish writing to a file, I should rename it using a pre-defined pattern. I really have no idea how I would have found all the file I/O operations in this application, but using the approach above, I created a RenamingTextWriter, which would rename the output file when it is closed. That change was a matter of minutes, as was the change to add application wide enconding to the file.

In Working With Legacy Code, Michael talks quite a bit about the seams of the system, where you can insert your mocks and start testing. What I have found is that those seams are very useful for adding functionality, the above case is fairly simple, but I'm using the same approach quite a bit all over the place.

Using NHibernate With Stored Procedures

No, really for real this time. I opened my mailbox today to find a very interesting message from the NHibernate commit logs:

NH-258 - Custom SQL support (including stored procedures)

It may look fairly innocent comment, but it has quite an impact on several nay-sayers that will clutch stored procedures until you pry them out of their cold dead hands. Also, sometimes Stored Procedures (or raw SQL) are useful :-).

So, armed with nothing but a toothpick and some shoes' polish, I turned to the task of finding out how to use this wonderous new feature. I started on the Hibernate documentation in the subject

The table model is very simple:

(Image from clipboard).png

I then create a simple set of stored procedures, all of which were fairly standard, like this one:

ALTER PROCEDURE [dbo].[EmployeesInsert](

      @Name nvarchar(50), @Surname nvarchar(50),

      @Email nvarchar(50), @Type int, @Id int

)

AS

      INSERT INTO [Employees]

      (

            [Id], [Name], [Surname], [Email], [Type]

      )

      VALUES

      (

            @Id, @Name, @Surname, @Email, @Type

      )

 

      RETURN @@Error

I have created those procedures via code generations, but I think that you'll agree that most existing stored procedures will have a similar look. I have the following SPs:

·         EmployeesInsert

·         EmployeesUpdate

·         EmployeesDelete

·         SalariesInsert

·         SalariesUpdate

 ·     SalariesDelete

The only slightly strange thing about these procedures is the position of the ID parameter. While in most SP it is the first parameter, it seems that NHibernate requires it to be the last parameter on the stored procedure. Also note that specifying SET NOCOUNT ON will cause NHibernate to think that it didn't manage to save the row, and throw. The SP must return a count of 1.

So, given those, how am I going to proceed?

First thing that I should do it to update the XSD schemas, this is so I could get intellisense inside VS.Net when I edit the mapping files. Then, let us create our entites:

(Image from clipboard).png

This is a fairly simple model to grasp, but hard to implement. I'll not show you everything, but I'll at least put you on the right path. Let us start with mapping the Employee class:

<class name="Employee"

         table="Employees"

         discriminator-value="1"

         lazy="true">

      <id name="Id"

            unsaved-value="0">

            <generator class="assigned"/>

      </id>

      <discriminator column="Type"/>

      <property name="Name"/>

      <property name="Surname"/>

      <property name="Email"/>

      <set name="Salaries"

             generic="true">

            <key column="EmployeeId"/>

            <one-to-many class="Salary"/>

            <loader query-ref="allSalariesForEmployee"/>

      </set>

 

      <subclass name="Manager"

                    discriminator-value="2">

            <sql-insert>

                  exec EmployeesInsert ?,?,?,2,?

            </sql-insert>

      </subclass>

 

      <sql-insert>

            exec EmployeesInsert ?,?,?,1,?

      </sql-insert>

</class>

This is mostly standard NHibernate mapping, with some interesting additions, notice the generic="true" in the set defination? That means that you can use ISet<T> without using NHibernate.Generics.

Now, for now, let us ignore the <loader> in the set element, and look at the bottom of the code, where we find a <sql-insert> statement, which directs NHibernate to use the text inside when it is inserting an entity to the database. A couple of points about the question marks before we continue:

  • The identifier of this entity is always the last one.
  • The rest of the fields appear in the order that they were defined in the XML.
  • The last two statements are both wrong, in order to know, you need to check the generated SQL that NHibernate generates, and only then you will know. The issue here is that it gets quite complex with inheritance hierarcies, multi column fields, etc.

NHibernate transforms this call to:

exec

EmployeesInsert @p0,@p1,@p2,1,@p3

As I have no real control on the order of the parameters, my SP needs to be changed approprirately (above it appears after the changes). Now, about managers, you noticed that I specified a different statement there? That allows me to pass the different discriminator value (or maybe call ManagersInsert), etc.

Before we get to the set mystery, let us take a look at how I mapped Salary:

<class name="Salary"

         discriminator-value="-1">

      <id name="Id">

            <generator class="assigned"/>

      </id>

      <discriminator column="Type"/>

      <property name="Remark"/>

      <many-to-one name="Employee"

                         class="Employee"

                         column="EmployeeId"/>

      <subclass name="HourlySalary"

                    discriminator-value="1">

            <property name="HourlyRate"

                          column="ParameterOne"/>

            <sql-insert>

                  exec SalariesInsert ?,?,?,1,?

            </sql-insert>

      </subclass>

      <subclass name="GlobalSalary"

                    discriminator-value="2">

            <property name="TotalSalary"

                          column="ParameterOne"/>

            <sql-insert>

                  exec SalariesInsert ?,?,?,2,?

            </sql-insert>

      </subclass>

 

      <sql-insert>

            raiserror ('Salary is an abstract type, can''t insert it!',10,1)

      </sql-insert>

</class>

This is not very different, isn't it? Notice that because salary is an abstract type, I raise an error if an attempt is made to save it (not really neccecary, since this is not possible, but whatever). Then, each subclass defined each own insert statement.

One thing to notice here is the parameter orderring. It appears that in this case the orderring is in the order defined in the mapping, in reverse hierarchal depth. Which means that the members of the subclass appear first, and then the members of the parent class, and last (but not least) the id.

For reference, here is the SalariesInsert procedure:

ALTER PROCEDURE [dbo].[SalariesInsert]

(

      @ParameterOne int, @Remark nvarchar(50),

      @EmployeeId int, @Type int, @Id int

)

AS

      INSERT INTO [Salaries]

      (

            [Id], [Type], [ParameterOne], [Remark], [EmployeeId]

      )

      VALUES

      (

            @Id, @Type, @ParameterOne, @Remark, @EmployeeId

      )

 

      RETURN @@Error

Now, let us check that facinating association between employees and salaries:

<loader query-ref="allSalariesForEmployee"/>

This means that when NHibernate wants to load the employees' salaries, it needs to execute the query (not SP) that is specified. In this case, here is the query:

<sql-query name="allSalariesForEmployee">

      <load-collection alias="salaries"

                               role ="Employee.Salaries"/>

      exec [SalariesByEmployeeId] ?

</sql-query>

Note that we need to specify which collection this query resolves. The SalariesByEmployeeId is simply:

ALTER PROCEDURE [dbo].[SalariesByEmployeeId]

(

      @EmployeeId int

)

AS

      SELECT

            [Id], [Type], [ParameterOne], [Remark], [EmployeeId]

      FROM [Salaries]

      WHERE       [EmployeeId] = @EmployeeId

So, that is all that I have to say so far, you can check Hibernate's documentation for all of the details, and check out the latest NHibernate code to play with it.

This is probably not going to help you map directly to pre-existing stored procedures, but it make it that much easier to work in those envrionment where you just can't get the data from the tables (for various reasons, not the least of which is the 160 columns table), or where Stored Procedures are mandated.

I'm very excited by this because I know that there are several places where this can be very helpful. I love PIVOT, and it was annoying not to be able to use it from NHibernate.

The disadvantages - You take away from NHibernate the ability to comprehend the structure of the tables, this mean that it can't do joins, eager fetching, etc. This is extremely important capability that just vanished. Likewise for queries, NHibernate's abilities to query your objects is being severly limited using this method of operation.

Thinking about APIs: Advance Usages

You may have noticed that I have create some wrappers around NHibernate, to make it easier to work with. NHibernate's API is very rich, and it can get pretty long if you are not careful. Consider the following:

Session.CreateCriteria(typeof(Post))

      .Add(Expression.Gt("PostedAt", DateTime.Now.AddDays(-3)))

      .Add(Expression.Eq("ShowInMainPage", true))

      .AddOrder(Order.Desc("PostedAt"))

      .SetFirstResult(0)

      .SetMaxResults(25)

      .List();

The above is very clear way, if long winded, to say "give me the last 25 posts in the last 3 days that should be shown in the main page". I swim around this API like fish in the water, until it comes to testing.

Trying to mock the above sentence is a PITA of the first order, and I say this as someone who wrote a mocking library :-)

Partly because of this, partly because I liked the API from Active Record, partly because I wanted a mode DDD model to work with, and partly because a client wanted to avoid commitment to a specific OR/M, I created a wrapper around it that gave some very nice features, adding code generation didn't hurt, so the above query now looks like this:

Repository<Post>.FindAll(0,25,Order.Desc("PostedAt"),

                         Where.Post.PostedAt.Gt(DateTime.Now.AddDays(-3)),

                         Where.Post.ShowInMainPage.Eq(true));

Using the Repository<T> is very simple, you can check the API here:

(Image from clipboard).png

Most of the saving are done by removing the fluent interface, and while I feel that this is simpler in most cases, there are some things that it just can't express.

I have talked before about cachable queries, in NHibernate, the are expressed by calling SetCachable(true), and setting the Cache Region, if neccecary. My API doesn't allow for it. Oh, I could add another parameter, but I already have a possible parameter explosion (check out the number of overloads on FindAll() ), and going this route would force me to add a parameter that would force bypassing the cache, etc.

Clearly, NHibernate's approach is much more reasonable now, it is easily extensible to support more options, and it doesn't break down when the complexity goes up.

I really like the fact that I have intellisense at my hands, and that I don't have to scroll through the 50+ methods (I counted) that some of the interfaces in NHibernate have. In addition to that, I have a problem where I want to cache a particular query, part of the time, and only for a very specific part of the time. In the part in question the queryis called roughly 3,650,000 times during the process of executing a certain web request [three points to the first fellow who can tell me what I'm doing]. Obviously going to the database per each query isn't going to be worth my time.

This query is sitting very low in the stack, and I really don't want to have to change / modify it in place, and I don't really like the idea of a caching version vs. non-caching version. After getting up and hitting the wall a couple of time with someone else head (What, you expected me to use mine? I use it for thinking. :-) ), I came up with the following solution:

(Image from clipboard).png

The usage is fairly simple:

using(With.QueryCache())

{

      ICollection<Post> postsToShow = Repository<Post>.FindAll(0, 25, Order.Desc("PostedAt"),

         Where.Post.PostedAt.Gt(DateTime.Now.AddDays(-3)),

         Where.Post.ShowInMainPage.Eq(true));

}

I'll be the first to admit that the "using(With ..." syntax is not the best there is, but in this case, I don't need to special case the exception case, so I don't think that I need to use a function that takes a delegate here.

The really interesting part is the TemporaryQueryCache(), which will enable the query cache for the duration of the using() statement. This means that I can selectively turn it on/off for a part of the code that require caching without actually modifying any code lower in the stack. This enables me to make much better decisions with regard to how and when I can use caching.

I commited my changes, but they are still in the proof of concept stage, and may change.

You can check them out here, or check out the entire tree using:

svn co http://svn.berlios.de/svnroot/repos/rhino-mocks/trunk/rhino-commons

Jeff IDE is Hot

Take a look at this, it certainly looks cool. I'm trying it out at the moment, I had to enlarge the font size a bit, probably because I'm on 1280x1024 on these monitors.

The Case of the not so innocent typo...

Life gets interesting with creative spelling error. I was checking a problem with a page today when I noticed the... interesting name it had... "EmployeeAssassinationValidatorRules.aspx"

No, I don't work on that kind of system.

The name should haved been named "EmployeeAssignationValidatorRules.aspx", or better yet, "EmployeeAssignmentsValidatorRules.aspx". But creative mispelling and taking the first result from Babylon spelling fixer.

I wonder what would happen if I didn't both to actually read the page name, how long it would go unnoticed...

Tags:

Published at