Do Dapper is safe from SQL Injections?

.net .net-3.5 dapper orm

Question

How can Dapper assist in providing SQL injection protection? I am evaluating several DAL technologies and must choose one to safeguard our website. Dapper (http://code.google.com/p/dapper-dot-net/) is what I'm leaning toward, however I need some assistance with security.

1
39
11/30/2012 9:28:51 PM

Accepted Answer

How does Dapper help protect against SQL injections?

It makes fully parameterized data access simple and eliminates the need for input concatenation. In particular, by making parameter handling stupidly easy, you don't need to hop through heaps of "add parameter, change the parameter type, check for null due to ADO.NET's poor null-handling, rinse/repeat for 20 parameters." It also removes the desire to utilize objects by making it incredibly simple to convert rows into objects.DataTable Everyone gains.

Based on comments

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

To respond, let's use the example from marc s's response and format it traditionally, assuming that all we have to work with at the outset isconnection . So, this is:

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 that I've severely oversimplified since it also addresses a variety of concerns, like:

  • handling of arguments for null
  • handling of result columns for nulls
  • the use of ordinal column indices
  • adjusting to underlying table and type structure changes
  • conversion of the result columns' data (between various primitives, strings, enums, etc)
  • unique treatment of the very frequent "in this list" situation
  • unique handling of the "apply this independently to a list of inputs" for "execute"
  • preventing clumsy typos
  • cutting down on code maintenance
  • managing many grids
  • managing several items returned in a single grid horizontally
  • use any ADO.NET provider at will (hint:AddWithValue seldom occurs)
    • Including special Oracle support, which requires extra setting
    • ADO.NET decoratos like "mini-profiler" play well with it.
  • both buffered (suited for small-to-moderate data; reduces command length) and non-bufferesd (ideal for huge data; reduces memory use) accesses are built-in support
  • optimized by those who are concerned with efficiency and have "some" knowledge of both data-access and meta-programming
  • permits you to utilize any POCO, DTO, anon-type, or other form of output for the parameter.
  • permits for either usedynamic When the output doesn't justify creating a POCO or DTO, use primitives, etc. (for multi-column output), or primitives, etc. (for single column output).
  • evade the burden of sophisticated fully-typed ORMs like EF
  • stay away from weak-typed layers' overheadDataTable
  • whenever it's required, open and close connections
  • with a plethora of more common gotchas
51
5/15/2016 4:24:45 PM

Popular Answer

Simply do query parameterization as you normally should. Use parameterized ADO.NET queries and provide parameters instead of Dapper, which is merely a "small" (and rather thin) addition to "raw" SQL and ADO.NET.

Check out this example from the website Dapper-Dot-Net:

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

You provide the "Dapper" query the arguments that the SQL query requires.

In conclusion, utilizing Dapper doesn't necessarily assist against SQL injections; instead, using parameterized ADO.NET/SQL queries does (and those queries are absolutely supported by Dapper, no issues at all)



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow