如何从c#代码中避免MSSQL服务器上的SQL注入?

What would be the best way to avoid SQL injection on the C#.net platform.

在c# .net平台上避免SQL注入的最佳方法是什么?

Please post an C# implementation if you have any.

如果您有c#实现，请发布。

2 个解决方案

#1

Top 10 things we can do to be safe (No one of these will do it all.)

为了安全，我们可以做的10件事(没有一件能做到这一切)。

Adopt the notion that, "All data is evil." All data, even the data stored in the database or on our file system is suspect. Not just data input from apps outside our firewall like query strings, form fields, cookies, etc. Anything can be used to compromise a system.

采纳“所有数据都是邪恶的”这一观点。所有数据，甚至存储在数据库或文件系统中的数据都是可疑的。不只是来自防火墙之外的应用程序的数据输入，比如查询字符串、表单字段、cookie等等。任何东西都可以用来破坏系统。
Don't rely on client-side validation of javascript or html field lengths or even server-side web APIs that use client-side validation. Use it to improve usability, but don't rely on it as the sole guard. Know how validators provided by APIs like NET work. Don't take them for granted. There are ways around them.

不要依赖于javascript或html字段长度的客户端验证，甚至使用客户端验证的服务器端web api。使用它来提高可用性，但是不要把它作为唯一的保护。了解诸如NET之类的api如何提供验证器。不要想当然。有很多方法可以绕过它们。
Do positive matching to catch all data as it goes in. If the Data matches character ranges of a regular expression, then it's okay. This disallows weird unicode characters into our database that might accidentally delimit something in sql or create other problems like Homographic XSS/Phishing Attacks. In contrast, Negative matching requires lists of all the bad characters, which seem to grow all the time. This is a bad approach. Positive matching is better. We reject bad data, don't sanitize or escape it.

在数据输入时进行正匹配以捕获所有数据。如果数据匹配正则表达式的字符范围，那么就没有问题。这使得我们的数据库中不允许使用奇怪的unicode字符，这可能会意外地破坏sql中的某些内容，或者产生其他问题，比如Homographic XSS/钓鱼攻击。相反，负匹配要求列出所有的坏字符，这些字符似乎一直在增长。这是一个糟糕的做法。积极的匹配是更好的。我们拒绝坏数据，不进行消毒或转义。
When possible, consider filtering, flagging, or catching string data with "update", "delete", "drop", "select", "alter", etc. This may not be possible given the nature of the string. "1212 Lemondrop Ln", "Waltersburg, PA", and "Table Rock, NE" are valid address fields. Running a daily scan of all table data for fields that match any of these could reveal delayed attacks or vulnerabilties. Also logging, ip banning, email alerts, etc etc could be used as the data comes inbound.

如果可能，考虑使用“update”、“delete”、“drop”、“select”、“alter”等进行过滤、标记或捕获字符串数据。“1212柠檬汁Ln”、“Waltersburg, PA”和“Table Rock, NE”都是有效的地址字段。对所有表数据进行每日扫描，以查找与这些数据匹配的字段，可以发现延迟的攻击或漏洞。当数据进入时，还可以使用日志记录、ip禁止、电子邮件警报等。
Use stored procedures and/or parameterized queries as much as possible. Avoid dynamic sql both in db client code and in sql. (Avoid exec statements with dynamic code with external sections in your stored procedures!!!) Parameterization will escape string terminators like the apostrophe, catch field lengths, and type check. We can't always rely on the APIs that provide parameterization to be perfect, but they are written by people much more aware of database idiosyncracies than most of us.

尽可能多地使用存储过程和/或参数化查询。避免在db客户机代码和sql中使用动态sql。(避免在存储过程中使用带有外部部分的动态代码的exec语句!!)参数化将转义字符串终止符，如撇号、捕获字段长度和类型检查。我们不能总是依赖于提供参数化的api来实现完美，但它们是由比我们大多数人更了解数据库特性的人编写的。
Be sure that no stray code is sitting around in a world readable/executable web directory. If it's not part of the active site, archive it somewhere secure and delete it from public view. Same goes for unused stored procedures.

确保在世界可读/可执行的web目录中没有零星的代码。如果它不是活动站点的一部分，将它归档到某个安全的地方，并从公共视图中删除它。对于未使用的存储过程也是如此。
Stay up to date on the database APIs. Some ways of executing SQL statements in some APIs are not as secure as others.

在数据库api上保持最新。在一些api中执行SQL语句的某些方法不如其他方法安全。
Store passwords securely with one-way encryption. This way, a table dump of usernames and passwords should still keep people out.

使用单向加密安全地存储密码。这样，用户名和密码的表转储仍然会将用户排除在外。
Harden the server in all the usual ways. For example, when possible, give least privilege on database tables. Limit access of web server database accounts strictly to the tables in question. Use read only as much as possible. Create multiple accounts that create a divide between the access rights of public and in-house/trusted traffic.

以所有通常的方式加强服务器。例如，在可能的情况下，在数据库表上提供最少的特权。严格限制对所涉表的web服务器数据库帐户的访问。尽可能多地使用read。创建多个帐户，在公共访问权限和内部/受信任流量之间划分界限。
Catch errors gracefully. This goes for all code, not just code that uses the database. Sql injection attacks specifically do rely on error messages however and so it's desirable to hide as much as is possible about the database from the public. Always write code that handles exceptions or empty datasets in a vanilla fashion as to reveal as little as possible about what type of database we're using, what the fields are in our tables, or how what kind of queries we're running. Log errors on the server. Even in the non-database code, it's best to keep quiet about third party components, file folder structures, other services we may be running, etc. Giving malacious users as little information as possible is key to keeping them clueless.

优雅地捕获错误。这适用于所有代码，而不仅仅是使用数据库的代码。但是，Sql注入攻击特别依赖于错误消息，所以最好尽可能地向公众隐藏数据库。总是以一种普通的方式编写处理异常或空数据集的代码，以尽可能少地揭示我们正在使用的数据库类型、表中的字段以及我们正在运行的查询类型。服务器上的日志错误。即使是在非数据库代码中，最好对第三方组件、文件文件夹结构、我们可能正在运行的其他服务等保持沉默。尽可能少地向恶意用户提供信息是保持它们不可见的关键。

And #11, always revisit/revise this list. Always be up to date. Be proactive. Make it an upfront priority and requirement, not an after thought.

第11点，经常回顾/修改这个列表。永远保持最新。要积极主动。把它作为一项首要任务和要求，而不是事后的想法。

#2

There's no algorithm needed - just don't use string concatenation to build SQL statements. Use the SqlCommand.Parameters collection instead. This does all the necessary escaping of values (such as replacing ' with '') and ensures the command will be safe because somebody else (i.e. Microsoft) has done all the testing.

不需要算法——只是不要使用字符串连接来构建SQL语句。使用SqlCommand。参数集合。这就完成了所有值的转义(例如用“替换”)，并确保命令是安全的，因为其他人(例如微软)已经完成了所有的测试。

e.g. calling a stored procedure:

例如，调用存储过程:

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("MySprocName", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Param1", param1Value);
    return command.ExecuteReader();
}

This technique also works for inline SQL statements, e.g.

这种技术也适用于内联SQL语句，例如。

var sql = "SELECT * FROM MyTable WHERE MyColumn = @Param1";
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@Param1", param1Value);
    return command.ExecuteReader();
}

#1