避免从 C# 代码向 MSSQL Server 注入 SQL 的算法? [英] Algorithm to avoid SQL injection on MSSQL Server from C# code?
问题描述
在 C#.net 平台上避免 SQL 注入的最佳方法是什么.
What would be the best way to avoid SQL injection on the C#.net platform.
如果您有 C# 实现，请发布.
Please post an C# implementation if you have any.
推荐答案
为了确保安全，我们可以做的前 10 件事(没有一个能做到.)
Top 10 things we can do to be safe (No one of these will do it all.)
接受所有数据都是邪恶的"的观念.所有数据，甚至存储在数据库或我们文件系统中的数据都是可疑的.不仅仅是从我们防火墙外的应用程序输入的数据，如查询字符串、表单字段、cookies 等.任何东西都可以用来破坏系统.
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.
不要依赖客户端验证 javascript 或 html 字段长度，甚至使用客户端验证的服务器端 Web API.使用它来提高可用性，但不要依赖它作为唯一的保护措施.了解 NET 等 API 提供的验证器如何工作.不要把它们视为理所当然.有办法绕过它们.
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.
做正匹配来捕捉所有进入的数据.如果数据匹配正则表达式的字符范围，那么没关系.这不允许奇怪的 unicode 字符进入我们的数据库，这些字符可能会意外地在 sql 中分隔某些内容或造成其他问题，如同形 XSS/网络钓鱼攻击.相比之下，否定匹配需要所有坏字符的列表，这些列表似乎一直在增长.这是一个糟糕的方法.正匹配更好.我们拒绝不良数据，不清理或逃避它.
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.
如果可能，请考虑使用update"、delete"、drop"、select"、alter"等过滤、标记或捕获字符串数据.鉴于其性质，这可能是不可能的字符串.1212 Lemondrop Ln"、Waltersburg, PA"和Table Rock, NE"是有效的地址字段.每天扫描所有表数据以查找与其中任何一个匹配的字段，可能会发现延迟攻击或漏洞.当数据传入时，还可以使用日志记录、IP 禁止、电子邮件警报等.
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.
尽可能使用存储过程和/或参数化查询.避免在 db 客户端代码和 sql 中使用动态 sql.(避免在存储过程中使用带有外部部分的动态代码的 exec 语句！！！)参数化将转义字符串终止符，如撇号、捕获字段长度和类型检查.我们不能总是依赖提供参数化的 API 来做到完美，但它们是由比我们大多数人更了解数据库特性的人编写的.
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.
确保在世界可读/可执行的网络目录中没有杂散的代码.如果它不是活动站点的一部分，请将其存档在安全的地方并从公共视图中删除它.未使用的存储过程也是如此.
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.
及时了解数据库 API.在某些 API 中执行 SQL 语句的某些方式不如其他方式安全.
Stay up to date on the database APIs. Some ways of executing SQL statements in some APIs are not as secure as others.
使用单向加密安全地存储密码.这样，用户名和密码的表转储仍应将人们拒之门外.
Store passwords securely with one-way encryption. This way, a table dump of usernames and passwords should still keep people out.
以所有常用方式强化服务器.例如，在可能的情况下，对数据库表给予最低权限.将 Web 服务器数据库帐户的访问权限严格限制为相关表.尽可能使用只读.创建多个帐户，在公共和内部/受信任流量的访问权限之间创建鸿沟.
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.
优雅地捕捉错误.这适用于所有代码，而不仅仅是使用数据库的代码.然而，Sql 注入攻击确实依赖于错误消息，因此需要尽可能多地向公众隐藏有关数据库的信息.始终编写以普通方式处理异常或空数据集的代码，以尽可能少地揭示我们使用的数据库类型、表中的字段或我们正在运行的查询类型.在服务器上记录错误.即使在非数据库代码中，最好对第三方组件、文件夹结构、我们可能正在运行的其他服务等保持沉默.向恶意用户提供尽可能少的信息是让他们保持无知的关键.
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.
和#11，总是重新访问/修改这个列表.始终保持最新状态.主动.让它成为一个预先的优先事项和要求，而不是事后的想法.
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.
这篇关于避免从 C# 代码向 MSSQL Server 注入 SQL 的算法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
