.net Dapper 和 SQL 注入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13653461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dapper and SQL Injections
提问by cdub
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.
Dapper 如何帮助防止 SQL 注入？我正在测试不同的 DAL 技术，必须选择一种技术来保护我们的网站。我倾向于 Dapper (http://code.google.com/p/dapper-dot-net/)，但需要一些帮助来了解安全性。
回答by Marc Gravell
How does Dapper help protect against SQL injections?
Dapper 如何帮助防止 SQL 注入？
It makes it really, reallyeasy 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 stupidlyconvenient. It also makes turning rows into objects really easy, avoiding the temptation to use DataTable... everyone wins.
它使得完全参数化数据访问变得非常非常容易，而无需连接输入。特别是，因为你并不需要通过大量的跳跃“添加参数，设置参数类型，检查空，因为ADO.NET有苏茨基空处理的20个参数，清洗/重复”，通过使参数处理愣神方便. 它还使将行变成对象变得非常容易，避免使用的诱惑DataTable......每个人都赢了。
From comments:
来自评论：
One more...what does dapper actually help do then?
再来一个……那么 dapper 实际上有什么帮助呢？
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:
要回答，让我们以 marc_s 的回复中的示例为例，并按照旧方式编写，假设我们必须从connection. 这就是：
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:
AddWithValuerarely 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
- 参数的空处理
- 结果列的空处理
- 使用序数列索引
- 适应底层表和类型的结构变化
- 结果列的数据转换（在各种原语、字符串、枚举等之间）
- 对非常常见的“在此列表中”场景的特殊处理
- 对于“执行”，“将其单独应用于输入列表”的特殊处理
- 避免愚蠢的错别字
- 减少代码维护
- 处理多个网格
- 处理在单个网格中水平返回的多个对象
- 使用任意 ADO.NET 提供程序（提示：
AddWithValue很少存在）- 包括对 Oracle 等需要额外配置的特定支持
- 与 ADO.NET 装饰器（例如“迷你分析器”）配合得很好
- 对缓冲（适用于中小数据；最小化命令持续时间）和非缓冲（适用于大数据；最小化内存使用）访问的内置支持
- 由关心性能并且对数据访问和元编程“相当了解”的人进行优化
- 允许您使用您选择的 POCO / DTO / anon-type / 无论参数和输出
dynamic当输出不保证生成 POCO / DTO 时，允许使用（对于多列）或原语等（对于单列）- 避免复杂的全类型 ORM 的开销，如 EF
- 避免弱类型层的开销，如
DataTable - 根据需要打开和关闭连接
- 以及大量其他常见问题
回答by marc_s
You just need to use parameterized querieslike 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.
您只需要像往常一样使用参数化查询。由于 Dapper 只是“原始”SQL 和 ADO.NET 的“微小”（并且非常薄）扩展 - 只需使用参数化的 ADO.NET 查询并提供参数。
See this sample from the Dapper-Dot-Net site:
从 Dapper-Dot-Net 站点查看此示例：
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.
SQL 查询使用参数 - 您将这些参数提供给“Dapper”查询。
To summarize: using Dapper in itself doesn't help protect against SQL injections per se - using parameterizedADO.NET/SQL queries however does (and those queries are absolutely supported by Dapper, no issues at all)
总而言之：使用 Dapper 本身并不能帮助防止 SQL 注入本身 - 使用参数化的ADO.NET/SQL 查询却可以（而且这些查询完全受 Dapper 支持，完全没有问题）
