Dapper and SQL Injections

Question

How does Dapper help protect against SQL injections? I am testing out different DAL technologies and have to choose one to be secure our site. I'm leaning towards Dapper (http://code.google.com/p/dapper-dot-net/), but need some help learning about security.

Answer 1

You just need to use parameterized queries like you always should. Since Dapper is just a "tiny" (and pretty thin) extension to "raw" SQL and ADO.NET - just use parameterized ADO.NET queries and supply parameters.

See this sample from the Dapper-Dot-Net site:

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", 
                                new { Age = (int?)null, Id = guid });

The SQL query uses parameters - and you supply those to the "Dapper" query.

To summarize: using Dapper in itself doesn't help protect against SQL injections per se - using parameterized ADO.NET/SQL queries however does (and those queries are absolutely supported by Dapper, no issues at all)

Answer 2

How does Dapper help protect against SQL injections?

It makes it really, really easy to do fully parameterized data access, without ever needing to either concatenate input. In particular, because you don't need to jump through lots of "add parameter, set the parameter type, check for null because ADO.NET has sucky null-handling, rinse/repeat for 20 parameters", by making parameter handling stupidly convenient. It also makes turning rows into objects really easy, avoiding the temptation to use DataTable... everyone wins.

From comments:

One more...what does dapper actually help do then?

To answer, let's take the example from marc_s's reply, and write it the old way, assuming all we have to start with is connection. This is then:

List<Dog> dogs = new List<Dog>();
using(var cmd = connection.CreateCommand()) {
    cmd.CommandText = "select Age = @Age, Id = @Id";
    cmd.Parameters.AddWithValue("Age", DBNull.Value);
    cmd.Parameters.AddWithValue("Id", guid);
    using(var reader = cmd.ExecuteReader()) {
        while(reader.Read()) {
            int age = reader.ReadInt32("Age");
            int id = reader.ReadInt32("Id");
            dogs.Add(new Dog { Age = age, Id = id });
        }
        while(reader.NextResult()) {}
    }
}

except I've over-simplfied grossly, as it also deals with a wide range of issues such as:

• null handling of parameters
• null handling of result columns
• using the ordinal column indices
• adapting to structural changes of the underlying table and type
• data conversion of result columns (between various primitives, strings, enums, etc)
• special handling of the oh-so-common "in this list" scenario
• for "execute", special handling of the "apply this separately to a list of inputs"
• avoiding silly typos
• reducing code maintenance
• handling multiple grids
• handling multiple objects returned horizontally in a single grid
• working with arbitrary ADO.NET providers (hint: AddWithValue rarely exists)
  • including specific support for things like Oracle, which needs additional configuration
  • plays nicely with ADO.NET decoratos such as "mini-profiler"
• inbuilt support for both buffered (suitable for small-to-moderate data; minimises command duration) and non-bufferesd (suitable for large data; minimised memory usage) accesss
• optimized by people who care about performance and know "quite a bit" about both data-access and meta-programming
• allows you to use your choice of POCO / DTO / anon-type / whatever for both the parameter and output
• allows use of either dynamic (for multi-column) or primitives etc (for single column) when the output doesn't warrant generation a POCO / DTO
• avoid the overhead of complex fully-typed ORMs like EF
• avoid the overhead of weak-typed layers like DataTable
• opening and closing connections as-necessary
• and a vast range of other common gotchas