Half-truths and sleight of hand

Tweet by Brad Wilson:

“The Simple.Data blog post is built upon half-truths and sleight of hand. Microsoft.Data supports parameters. Your stuff is vuln to concat.”

Apology by me:

I didn’t know that Microsoft.Data supported parameters. Sorry.

And yes, Simple.Data is also vulnerable to SQL injection through concatenated SQL, as is anything that lets developers execute text SQL statements against a database.

However, what I’m trying to do with this project is explore other, non-text-based ways of interacting with a database that are not as complicated as full-blown ORMs.

Simple.Data with proper types

One of the comments on my previous post asked about working with “real types”. It was something I intended to try, and I had a couple of ideas about how it might work. But one of the issues is that any value returned from a method or property on a dynamic object is also dynamic. So to get, for example, a User object back, the developer would need to use a cast or “as” conversion.

While I was working on the DynamicTable class in the Simple.Data code, I noticed one of the available overrides from the DynamicObject subclass was “TryConvert”. It turns out that this method is called when an implicit or explicit cast is attempted on the object.

ExpandoObject, which is what was being returned by the Find methods, doesn’t implement this method, so I created my own ExpandoObject called DynamicRecord and added the TryConvert override. So now, using the Simple.Data.Database class, you can do this:

var db = Database.Open(); // Connection string in config.
User user = db.Users.FindByName("Bob");

And the dynamic object will be magically implicitly cast to the User type, with compile-time checking and IntelliSense intact.

I’ve only proved this with happy-path tests so far (exact matches for all properties), and obviously it’s going to rely on the database table having column names which match the type, but it’s a very neat solution and extremely new-developer-friendly.

Updated code at http://github.com/markrendle/Simple.Data

Introducing Simple.Data

Update – 06-Aug-2010

This post was inaccurate, in that it failed to acknowledge that Microsoft.Data supports parameters in its text querying. I have made changes to address this inaccuracy.

What is it?

It’s a very lightweight data access framework that I wouldn’t dream of calling an ORM. It uses the dynamic features of Microsoft .NET 4 to provide very expressive ways of retrieving data. It provides non-SQL ways of doing things, inspired by the Ruby ActiveRecord library. It also provides SQL ways of doing things, but with features to protect against SQL injection.

Why?

Because Microsoft have identified a gap in the data access toolkit ecosystem which they think scares casual web developers who are used to environments like PHP. These developers neither want nor need a full-blown ORM like NHibernate or Entity Framework, and they don’t want to deal with the complexity of ADO.NET connections, commands and readers.

Fair enough.

Microsoft have recently released a preview of a new library, Microsoft.Data.dll, which aims to serve these developers. It provides various simple ways of opening up a database, and then lets you run SQL against it, which is how casual developers are used to working with MySQL in the PHP world. They build up their SQL strings and then they execute them.

The problem is, that’s wrong. And I believe that attempting to attract people to your stack by giving them a really easy way to carry on doing things wrong is also wrong. The right thing to do is give them a way to do things right that is as easy as, if not easier than, what they have been using.

Simple.Data is my attempt at that.

Example?

Using Microsoft.Data, you would should query a Users table like this:

var db = Database.Open(); // Connection specified in config.
string sql = "select * from users where name = @0 and password = @1";
var user = db.Query(sql, name, password).FirstOrDefault();

But you could query it like this:

var db = Database.Open(); // Connection specified in config.
string sql = "select * from users where name = '" + name
+ "' and password = '" password + "'";
var user = db.Query(sql).FirstOrDefault();

A lot of people are quite cross about this. One of the main problems they have is that building query strings in this way opens your application up to SQL injection attacks. This is a common pattern in PHP applications written by new developers.

To achieve the same task with Simple.Data, you can do this:

var db = Database.Open(); // Connection specified in config.
var user = db.Users.FindByNameAndPassword(name, password);

That’s pretty neat, right? So, did we have to generate the Database class and a bunch of table classes to make this work?

No.

In this example, the type returned by Database.Open() is dynamic. It doesn’t have a Users property, but when that property is referenced on it, it returns a new instance of a DynamicTable type, again as dynamic. That instance doesn’t actually have a method called FindByNameAndPassword, but when it’s called, it sees “FindBy” at the start of the method, so it pulls apart the rest of the method name, combines it with the arguments, and builds an ADO.NET command which safely encapsulates the name and password values inside parameters. The FindBy* methods will only return one record; there are FindAllBy* methods which return result sets. This approach is used by the Ruby/Rails ActiveRecord library; Ruby’s metaprogramming nature encourages stuff like this.

[A section which wrongly implied that Microsoft.Data did not support parameterized queries has been removed here.]

More examples…

Inserting using named parameters:

db.Users.Insert(Name: "Steve", Password: "Secret", Age: 21);

Executing an update:

db.Execute("update Stock set Current = Current - 1 where ProductId = ?", productId);

(Planned) inserting/updating using an object:

db.Stock.Insert(newStockItem);
db.Stock.UpdateByProductId(stockItem);

Roadmap

This project is more of a constructive criticism of Microsoft’s preview than anything else. I’m going to develop it a bit further (for example, the “planned” syntax support above), mainly as an exercise. One thing I’d maybe like to explore further is whether it can be used as a layer over NoSQL stores as well as RDBMS.

The project is hosted at http://github.com/markrendle/Simple.Data for anybody who wants to download and play with it.

I’d really appreciate feedback, so please do use the comments if you’ve got any criticisms, suggestions or encouragement to express.

Tuple.Do

Another example of using extension methods to improve the readability of your code.


Version 4 of the .NET Framework introduces a new Tuple type; or rather, a set of Tuple types with different numbers of generic parameters. Tuples represent an ordered set of values, and are first-class features in some languages, particularly functional ones such as F#, which can return multiple values from a function, as in this example (which requires the PowerPack DLL):

let success, value = TryParseInt("42")

This is considerably more elegant than using an "out" parameter, which requires explicit variable declarations prior to the call.

In .NET 4.0, the BCL Tuple type is compatible with F#'s native Tuples, but can also be used from C# (and other CLI languages). C# lacks the syntactic sugar of F#, so the call above would return an instance of Tuple<bool, int> in which the "success" value was referred to by the Item1 property, and the value by the Item2 property.

For example:

var tuple = TryParseInt("42");
if (tuple.Item1)
{
  Console.WriteLine("Success: {0}", tuple.Item2);
}
else
{
  Console.WriteLine("Fail");
}

To me, this is less than ideal, and in thinking about it I was reminded of a common pattern from Ruby, where functions often take a "block" (Ruby's version of an Action or Func) and call it with the value(s) that would be returned from the method.

It's simple to reproduce this feature in C#, using an extension method on the Tuple<T1,T2> type:

using System;

namespace System.Linq
{
  public static class TupleDoExtensions
  {
    public static void Do<T1, T2>(this Tuple<T1, T2> tuple,

Action<T1, T2> action)
    {
      action(tuple.Item1, tuple.Item2);
    }
  }
}

With this extension method, we can specify meaningful names for the return values as the parameters to an anonymous method, thus:

TryParseInt("42").Do((success, value) =>
{
  if (success)
  {
    Console.WriteLine("Success: {0}", value);
  }
  else
  {
    Console.WriteLine("Fail");
  }
}

I think this code is a lot more readable. Another victory for extension methods.