C# INSERT 上的 SQL 注入

SQL injection on INSERT

提问by Brad

I have created a small survey web page on our company Intranet. This web page is not accessible from the outside.

我在我们公司的 Intranet 上创建了一个小型调查网页。此网页无法从外部访问。

The form is simply a couple of radio buttons and a comments box.

该表单只是几个单选按钮和一个评论框。

I would like to maintain good coding practices and would like to guard against SQL Injections.

我想保持良好的编码习惯，并想防范 SQL 注入。

Can SQL injections happen on a insert statement with comments from the textbox? If so, how can I guard against it using .NET 2.0?

SQL 注入可以发生在带有文本框注释的插入语句上吗？如果是这样，我如何使用 .NET 2.0 防范它？

采纳答案by Dave Webb

Injection can happen on any SQL statement not run properly.

任何未正常运行的 SQL 语句都可能发生注入。

For example, let's pretend your comment table has two fields, an integer ID and the comment string. So you'd INSERTas follows:

例如，假设您的评论表有两个字段，一个整数 ID 和评论字符串。所以你会INSERT如下：

 INSERT INTO COMMENTS VALUES(122,'I like this website');

Consider someone entering the following comment:

考虑有人输入以下评论：

'); DELETE FROM users; --

If you just put the comment string into the SQL without any processesing this could turn your single INSERTin to the following two statements followed by a comment:

如果您只是将注释字符串放入 SQL 中而不进行任何处理，这可能会将您的单曲INSERT变成以下两个语句，后跟注释：

INSERT INTO COMMENTS VALUES(123,''); DELETE FROM users; -- ');

This would delete everything from your userstable. And there are people willing to spend all day finding the right tablename to empty using trial and error and various tricks. Here's a description of how you could perform an SQL Injection attack.

这将从您的users表中删除所有内容。有些人愿意花一整天的时间使用反复试验和各种技巧来寻找合适的表名来清空。 这里描述了如何执行 SQL 注入攻击。

You need to use parameterized SQL statementsto prevent this.

您需要使用参数化的 SQL 语句来防止这种情况。

And this isn't just for security reasons. For example, if you're creating your SQL statements naively the following comment:

这不仅仅是出于安全原因。例如，如果您天真地创建 SQL 语句，请使用以下注释：

I'm just loving this website

would cause an SQL syntax error because of the apostrophe being interpreted by SQL as a closing quote.

将导致 SQL 语法错误，因为撇号被 SQL 解释为结束引号。

回答by tvanfosson

Use parameterized queries so that the text is automatically quoted for you.

使用参数化查询，以便为您自动引用文本。

SqlCommand command = connection.CreateCommand();
command.CommandText = "insert into dbo.Table (val1,val2,txt) values (@val1,@val2,@txt)";
command.AddParameterWithValue( "val1", value1 );
command.AddParameterWithValue( "val2", value2 );
command.AddParameterWithValue( "txt", text );

...

回答by Bravax

The easiest way to guard against that form of SQL injection, is to use parameters and stored procedures rather then building sql statements to run. (In C# or internally to SQL Server).

防止这种形式的 SQL 注入的最简单方法是使用参数和存储过程，而不是构建要运行的 sql 语句。（在 C# 中或在 SQL Server 内部）。

However I'm not entirely sure you should be spending time on this, unless of course it's your corporate policy, as the chances of it ever occuring internally are minimal at best, and if it did occur, I would hope you would know immediately who it is.

但是，我不完全确定您是否应该花时间在这上面，当然除非这是您的公司政策，因为它在内部发生的可能性充其量是微乎其微的，如果确实发生了，我希望您能立即知道是谁这是。

回答by boj

Yes, it can. Let's say the client sends this:

是的，它可以。假设客户端发送这个：

OR 1 = 1

That can be very painfull for your

这对你来说可能是非常痛苦的

  SELECT * FROM admin WHERE name = @name AND password = @password

You can prevent this with

你可以用

• using Parameter class from ADO.NET
• using regexp
• reading, reading: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx

• 使用 ADO.NET 中的 Parameter 类
• 使用正则表达式
• 阅读、阅读：http: //www.codeproject.com/KB/database/SqlInjectionAttacks.aspx

回答by Eric Petroelje

Yes, they can happen. The easiest way to guard against this is to use prepared statements rather than building the SQL manually.

是的，它们可能发生。防止这种情况的最简单方法是使用准备好的语句而不是手动构建 SQL。

So, rather than this:

所以，而不是这样：

String sql = 
 String.Format("INSERT INTO mytable (text_column) VALUES ( '{0}' )",
   myTextBox.Text); // Unsafe!

You would do something like this:

你会做这样的事情：

String sql = "INSERT INTO mytable (text_column) VALUES ( ? )"; // Much safer

Then add the text of the text box as a parameter to your DbCommand which will cause it to be automatically escaped and replace the "?" in the SQL.

然后将文本框的文本作为参数添加到 DbCommand 中，这将导致它自动转义并替换“？” 在 SQL 中。

回答by Gary.Ray

In addition to using prepared statements and parameters rather than concatenating strings into your SQL you should also do the following:

除了使用准备好的语句和参数而不是将字符串连接到 SQL 之外，您还应该执行以下操作：

1. Validate and format user input on the server side. Client side validation and limits can easily be bypasses with tools like WebScarab, or by spoofing your form.

2. Configure appropriate permissions for the database user account. Web application should use a separate account or role in your database with permissions restricted to only the tables, views and procedures required to run your application. Make sure that user does not have select rights on the system tables

3. Hide detailed error messages from users, and use less common names for your objects. It amazes me how often you can determine the server type (oracle, mysql, sqlserver) and find basic schema information in an error message and then get information from tables called 'user(s)', 'employee(s)'. If you haven't set your permissions as in (2) and I can determine your server type you are open to statements like this for SQL Server

   SELECT table_name FROM information_schema.table

   EXECUTE sp_help foundTableName

1. 在服务器端验证和格式化用户输入。可以使用 WebScarab 等工具或通过欺骗您的表单轻松绕过客户端验证和限制。

2. 为数据库用户帐户配置适当的权限。Web 应用程序应在数据库中使用单独的帐户或角色，其权限仅限于运行应用程序所需的表、视图和过程。确保用户对系统表没有选择权限

3. 向用户隐藏详细的错误消息，并为您的对象使用不太常见的名称。令我惊讶的是，您可以确定服务器类型（oracle、mysql、sqlserver）并在错误消息中找到基本架构信息，然后从名为“user(s)”、“employee(s)”的表中获取信息。如果您没有像 (2) 中那样设置您的权限并且我可以确定您的服务器类型，那么您对 ​​SQL Server 这样的语句持开放态度

   SELECT table_name FROM information_schema.table

   EXECUTE sp_help foundTableName

回答by K. Brian Kelley

SQL injection can happen any time you pass a query back to the database. Here's a simple demonstration:

任何时候您将查询传递回数据库时，都可能发生 SQL 注入。这是一个简单的演示：

SQL Injection Explained

SQL注入解释

The key, within .NET, is to do as Dave Webb has given. It will prevent the injection attempt by encompassing the entire string as one parameter to be submitted, handling all characters that might be interpreted by SQL Server to change the query or append additional commands.

在 .NET 中，关键是按照 Dave Webb 的要求去做。它将通过将整个字符串包含为一个要提交的参数来防止注入尝试，处理可能由 SQL Server 解释以更改查询或附加其他命令的所有字符。

And it should be pointed out that SQL injection can occur on any application, not just web applications. And that an internal attack is usually the most costly to an organization. One cannot safely assume that an attack won't originate from within.

需要指出的是，SQL 注入可以发生在任何应用程序上，而不仅仅是 Web 应用程序。并且内部攻击通常对组织来说是最昂贵的。不能安全地假设攻击不会来自内部。

回答by Vivek Sharma

Prevent SQL Injection by using prepared statement. The use of placehoder(?) totally eliminates sql Injection Vulnerability. example String sql=Select * from user_table where username='+request.getparameter("username")+'; statement.executeQuery(sql);

使用预准备语句防止 SQL 注入。placehoder(?)的使用彻底消除了sql注入漏洞。示例 String sql=Select * from user_table where username='+request.getparameter("username")+'; 语句.executeQuery(sql);

the above statement is vulnerable to sql injection.

上面的语句容易受到 sql 注入的影响。

To make it safe against sql injection. Use following the snippet

使其免受sql注入的影响。使用以下代码段

String sql=Select * from user_table where username=?; statement.setString(1,username);

String sql=Select * from user_table where username=?; statement.setString(1,username);