小巧玲珑和SQL注入 [英] Dapper and SQL Injections

问题描述

如何小巧玲珑帮助防止SQL注入？我测试了不同的DAL技术，必须选择一个作为保护我们的网站。我倾向于小巧精致。（HTTP：//$c$c.google.com/p/dapper-dot-net/），但需要一些帮助，学习有关安全

解决方案

  

如何小巧玲珑帮助防止SQL注入？

这使得它的真的，真的容易做到完全参数化的数据访问，甚至无需要么串连输入。特别是，因为你并不需要跳转通过大量的添加参数，设置参数类型，检查空的因为ADO.NET有苏茨基空处理的，冲洗/重复20参数 ，通过参数处理的愚蠢方便。这也使得转向行为对象很容易，避免了诱惑，使用数据表 ...每个人都赢。

从评论：

  

还有一个......是什么短小精悍的实际帮助怎么办呢？

要回答，让我们从marc_s的答复的例子，并写旧的方式，假设我们要开始为连接。这才是：

 名单，其中，狗＆GT;狗=新的名单，其中，狗＆GT;（）;
使用（VAR CMD = connection.CreateCommand（））{
    cmd.CommandText =选择年龄= @Age，ID = @Id;
    cmd.Parameters.AddWithValue（时代，的DBNull.Value）;
    cmd.Parameters.AddWithValue（ID，GUID）;
    使用（VAR读卡器= cmd.ExecuteReader（））{
        而（reader.Read（））{
            INT年龄= reader.ReadInt32（时代）;
            INT ID = reader.ReadInt32（ID）;
            dog.Add（新狗{年龄=年龄，ID = ID}）;
        }
    }
}
 

但我已经过simplfied严重，因为它也涉及广泛的问题，如：

• 参数的空值处理
• 在结果列的空值处理
• 使用顺序列索引
• 适应基础表和类型结构变化
• 在结果列的数据转换（各种原语，字符串，枚举等之间）
• 在在此列表中哦，所以，常见的情况的特殊处理
• 为执行，进行特殊处理的单独申请这对输入的名单
• 避免愚蠢的错别字
• 在减少code维护
• 在处理多个网格
• 在处理多个对象在一个网格返回水平
• 在任意ADO.NET提供商合作（提示： AddWithValue 很少存在）
  • 包括像甲骨文，它需要额外配置的具体支持
  • 在很好地扮演与ADO.NET decoratos如迷你分析器
• 为缓冲的内置支持（适合小到中等的数据，最大限度地减少指令长）和非bufferesd（适合大数据;最小的内存使用情况）accesss
• 将谁关心性能，知道颇有些关于这两个数据访问和元编程人员优化
• 允许您使用您所选择的POCO / DTO /匿名类型/不管这两个参数和输出
• 允许使用的任动态（多列）或基元等（单柱），当输出不保证一代POCO / DTO
• 避免复杂的全类型奥姆斯喜欢EF的开销
• 避免弱类型层的如数据表
的开销
• 打开和关闭连接的，需要
• 和其他常见的问题一个浩大的范围

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.

解决方案

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> dog = 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");
            dog.Add(new Dog { Age = age, Id = id });
        }
    }
}

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

这篇关于小巧玲珑和SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！