在C＃中安全地生成SQL查询

What's the safest way of generating SQL queries in C#, including cleansing user input so it's safe from injection? I'm looking to use a simple solution that doesn't need external libraries.

在C＃中生成SQL查询最安全的方法是什么，包括清理用户输入以便注入安全？我希望使用一个不需要外部库的简单解决方案。

7 个解决方案

#1

Use Sql Parameters:

使用Sql参数：

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.80).aspx

Here's an example in C#

这是C＃中的一个例子

SqlCommand tCommand = new SqlCommand();
tCommand.Connection = new SqlConnection("YourConnectionString");
tCommand.CommandText = "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum";

tCommand.Parameters.Add(new SqlParameter("@name", System.Data.SqlDbType.VarChar).Value = "Smith, Steve");
tCommand.Parameters.Add(new SqlParameter("@score", System.Data.SqlDbType.Int).Value = "42");
tCommand.Parameters.Add(new SqlParameter("@active", System.Data.SqlDbType.Bit).Value = true);
tCommand.Parameters.Add(new SqlParameter("@jerseyNum", System.Data.SqlDbType.Int).Value = "99");

tCommand.ExecuteNonQuery();

#2

In essence don't do this

本质上不要这样做

SqlCommand command = new SqlCommand(MyConnection);
command.CommandText = "Select * From MyTable Where MyColumn = '" + TextBox1.Text + "'"
...

做

SqlCommand command = new SqlCommand(MyConnection);
command.CommandText = "Select * From MyTable Where MyColumn = @MyValue";
command.Parameters.AddWithValue("MyValue",TextBox1.Text);
...

Basically never build your sql command directly from user input.

基本上永远不会直接从用户输入构建您的sql命令。

If you use an ORM, such as EntityFrameworks / POCO all queries are done in the latter form.

如果您使用ORM，例如EntityFrameworks / POCO，则所有查询都以后一种形式完成。

#3

The first rule of thumb is to make sure you use parameterized queries/commands. Basically don't dynamically build a sql string that includes something that the user has input into the page.

第一个经验法则是确保使用参数化查询/命令。基本上不要动态构建包含用户输入到页面中的内容的sql字符串。

If you use on ORM (EF, L2S, Nhib), this is typically handled in most cases because most all of them run parameterized queries.

如果您在ORM（EF，L2S，Nhib）上使用，通常会在大多数情况下处理，因为大多数情况下都会运行参数化查询。

#4

Parametrize your queries.

参数化您的查询。

In case if you build some TSQL which builds some other dynamic TSQL - then use some described technique

如果您构建了一些构建其他动态TSQL的TSQL，那么请使用一些描述的技术

What does "parametrizing means?

“参数化意味着什么？

See, not use something like this:

看，不要使用这样的东西：

sqlCommand.CommandText = "select * from mytable where id = "+someVariable;

use this:

用这个：

sqlCommand.CommandText = "select * from mytable where id = @id";
sqlCommand.Parameters.AddWithValue("@id", someVariable);

#5

Make use of Parametrized Queries.

使用参数化查询。

Simple Example.

简单的例子。

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();
}

More Detailed Example.

更详细的例子。

protected void btnGoodAddShipper_Click(object sender, EventArgs e)
{
   string connStr = c
      "Server=(local);Database=Northwind;Integrated Security=SSPI";

   // this is good because all input becomes a
   // parameter and not part of the SQL statement
   string cmdStr =
      "insert into Shippers (CompanyName, Phone) values (" +
      "@CompanyName, @Phone)";

   using (SqlConnection conn = new SqlConnection(connStr))
      using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
         {
             // add parameters
             cmd.Parameters.AddWithValue
                ("@CompanyName", txtCompanyName.Text);
             cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);

             conn.Open();
             cmd.ExecuteNonQuery();
         }
}

#6

Using DBML and LINQ to handle it for you. Many people have worked on those to ensure those issues are well mitigated.

使用DBML和LINQ为您处理它。很多人都在努力确保这些问题得到很好的缓解。

And if not than at least parametrize your queries.

如果不是至少参数化您的查询。

#7

A proper name for DBML is linq2sql or an advanced version is called entity framework. These technologies are provided by Microsoft and well integrated with visual studio. Does not require additional libraries.

DBML的正确名称是linq2sql，或者高级版本称为实体框架。这些技术由Microsoft提供，并与visual studio完美集成。不需要额外的库。

Pretty stable products..

非常稳定的产品..

#1