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.

Durr

Answer 1

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

Wickiup

@niico where does it do something that isn't a Dog? – Wickiup

@niico ah, right, now with you. I think my eye auto-corrected that when I read it! Yes, that would be clearer. Note, though, that (as stated) I was taking the example from another answer which used dog as the variable name. – Wickiup

Now, do you use Dapper for parameterized queries ? – Beckett

Answer 2

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)

Lotze

thx for this question answer and the other one. One more...what does dapper actually help do then? – Durr

@Chris: it turns the result of the SQL query (see above) into a nice, user-friendly .NET object (like the Dog class) - instead of leaving you with a pile of rows/columns that you have to wade through to figure out what you got back from the SQL .... – Lotze

I will have to change my sql queries anyway in my code then too, to make it go from new SqlParameter(...) to the dapper version then? – Durr

@chris: yes, you will have to change your queries - since the Dapper extension method "lives" on the SqlConnection - so it doesn't see any of your parameters defined on the SqlCommand . – Lotze

does this count as a DAL (#13654432)? – Durr

@chris "dapper" would usually be called a library; generally, what people mean by a DAL is: you've separated the UI code from the code that communicates in some way to the database. Your DAL would generally use a library to reduce code maintenance, but your DAL is the bit that knows nothing about the UI, but knows what the tables / columns / procs / views / etc are called. Which the UI shouldn't need to: the UI only needs to know about the entity-model, or (preferable) the view-model. Feel free to substitute "web-service API" or similar for "UI" in all of the above. – Wickiup