C# 什么是防止 SQL 注入的好方法？

What are good ways to prevent SQL injection?

提问by LeonidasFett

I have to program an application management system for my OJT company. The front end will be done in C# and the back end in SQL.

我必须为我的 OJT 公司编写一个应用程序管理系统。前端将在 C# 中完成，后端将在 SQL 中完成。

Now I have never done a project of this scope before; in school we had only basic lessons about SQL. Somehow our teacher completely failed to discuss SQL injections, something which I have only now come in contact with by reading about it on the net.

现在我从来没有做过这个范围的项目；在学校，我们只有关于 SQL 的基本课程。不知何故，我们的老师完全没有讨论 SQL 注入，这是我现在通过在网上阅读它才接触到的。

So anyway my question is: how do you prevent SQL injections in C#? I vaguely think that it can be done by properly masking the text fields of the application so that it only accepts input in a specified format. For example: an e-mail textbox should be of the format "[email protected]". Would this approach be sufficient? Or does .NET have pre-defined methods that handle stuff like this? Can I apply a filter to a textbox so it only accepts email-address format or a name textbox so it doesn't accept special chars?

所以无论如何我的问题是：你如何防止 C# 中的 SQL 注入？我模糊地认为可以通过正确屏蔽应用程序的文本字段来完成，以便它只接受指定格式的输入。例如：电子邮件文本框的格式应为“[email protected]”。这种方法是否足够？或者 .NET 是否具有处理此类内容的预定义方法？我可以将过滤器应用于文本框，使其只接受电子邮件地址格式或名称文本框，因此它不接受特殊字符吗？

采纳答案by Oliver

By using the SqlCommandand its child collection of parametersall the pain of checking for sql injection is taken away from you and will be handled by these classes.

通过使用SqlCommand和它的子参数集合，检查 sql 注入的所有痛苦都从您身上消失了，并将由这些类处理。

Here is an example, taken from one of the articles above:

这是一个示例，取自上述文章之一：

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored  
    // in an xml column.  
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics. 
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

回答by ?yvind Br?then

My answer is quite easy:

我的回答很简单：

Use Entity Framework for communication between C# and your SQL database. That will make parameterized SQL strings that isn't vulnerable to SQL injection.

使用实体框架在 C# 和 SQL 数据库之间进行通信。这将使参数化的 SQL 字符串不易受到 SQL 注入的影响。

As a bonus, it's very easy to work with as well.

作为奖励，它也很容易使用。

回答by Jon

SQL injection should not be prevented by trying to validate your input; instead, that input should be properly escaped before being passed to the database.

不应通过尝试验证您的输入来阻止 SQL 注入；相反，该输入应该在传递到数据库之前正确转义。

How to escape input totally depends on what technology you are using to interface with the database. In most cases and unless you are writing bare SQL (which you should avoid as hard as you can) it will be taken care of automatically by the framework so you get bulletproof protection for free.

如何转义输入完全取决于您使用什么技术与数据库交互。在大多数情况下，除非您正在编写裸 SQL（您应该尽可能避免），否则框架会自动处理它，因此您可以免费获得防弹保护。

You should explore this question further after you have decided exactly what your interfacing technology will be.

在您确切地确定您的接口技术将是什么之后，您应该进一步探讨这个问题。

回答by nerdybeardo

SQL injection can be a tricky problem but there are ways around it. Your risk is reduced your risk simply by using an ORM like Linq2Entities, Linq2SQL, NHibrenate. However you can have SQL injection problems even with them.

SQL 注入可能是一个棘手的问题，但有一些方法可以解决它。只需使用像 Linq2Entities、Linq2SQL、NHibrenate 这样的 ORM，您的风险就会降低。但是，即使使用它们，您也可能遇到 SQL 注入问题。

The main thing with SQL injection is user controlled input (as is with XSS). In the most simple example if you have a login form (I hope you never have one that just does this) that takes a username and password.

SQL 注入的主要内容是用户控制的输入（与 XSS 一样）。在最简单的示例中，如果您有一个需要用户名和密码的登录表单（我希望您永远不会有这样的表单）。

SELECT * FROM Users WHERE Username = '" + username + "' AND password = '" + password + "'"

If a user were to input the following for the username Admin' --the SQL Statement would look like this when executing against the database.

如果用户要为用户名Admin'输入以下内容——当对数据库执行时，SQL 语句将如下所示。

SELECT * FROM Users WHERE Username = 'Admin' --' AND password = ''

In this simple case using a paramaterized query (which is what an ORM does) would remove your risk. You also have a the issue of a lesser known SQL injection attack vector and that's with stored procedures. In this case even if you use a paramaterized query or an ORM you would still have a SQL injection problem. Stored procedures can contain execute commands, and those commands themselves may be suceptable to SQL injection attacks.

在这个简单的情况下，使用参数化查询（这是 ORM 所做的）将消除您的风险。您还有一个鲜为人知的 SQL 注入攻击向量的问题，这与存储过程有关。在这种情况下，即使您使用参数化查询或 ORM，您仍然会遇到 SQL 注入问题。存储过程可以包含执行命令，这些命令本身可能容易受到 SQL 注入攻击。

CREATE PROCEDURE SP_GetLogin @username varchar(100), @password varchar(100) AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT * FROM users' +
              ' FROM Product Where username = ''' + @username + ''' AND password = '''+@password+''''

EXECUTE sp_executesql @sql

So this example would have the same SQL injection problem as the previous one even if you use paramaterized queries or an ORM. And although the example seems silly you'd be surprised as to how often something like this is written.

因此，即使您使用参数化查询或 ORM，此示例也将具有与前一个相同的 SQL 注入问题。虽然这个例子看起来很傻，但你会惊讶于写这样的东西的频率。

My recommendations would be to use an ORM to immediately reduce your chances of having a SQL injection problem, and then learn to spot code and stored procedures which can have the problem and work to fix them. I don't recommend using ADO.NET (SqlClient, SqlCommand etc...) directly unless you have to, not because it's somehow not safe to use it with parameters but because it's that much easier to get lazy and just start writing a SQL query using strings and just ignoring the parameters. ORMS do a great job of forcing you to use parameters because it's just what they do.

我的建议是使用 ORM 立即减少出现 SQL 注入问题的机会，然后学习发现可能存在问题的代码和存储过程并努力修复它们。我不建议直接使用 ADO.NET（SqlClient、SqlCommand 等...），除非你必须这样做，不是因为将它与参数一起使用是不安全的，而是因为它更容易变得懒惰并开始编写 SQL使用字符串查询并忽略参数。ORMS 在强制您使用参数方面做得很好，因为这正是它们所做的。

Next Visit the OWASP site on SQL injection https://www.owasp.org/index.php/SQL_Injectionand use the SQL injection cheat sheet to make sure you can spot and take out any issues that will arise in your code. https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheetfinally I would say put in place a good code review between you and other developers at your company where you can review each others code for things like SQL injection and XSS. A lot of times programmers miss this stuff because they're trying to rush out some feature and don't spend too much time on reviewing their code.

接下来访问有关 SQL 注入的 OWASP 站点https://www.owasp.org/index.php/SQL_Injection并使用 SQL 注入备忘单来确保您可以发现并解决代码中将出现的任何问题。 https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet最后，我想说的是在您和您公司的其他开发人员之间进行良好的代码，您可以在其中彼此的代码，例如 SQL 注入和 XSS。很多时候程序员会错过这些东西，因为他们试图匆忙推出某些功能并且没有花太多时间他们的代码。