如何将用户提供的输入添加到SQL语句中？

I am trying to create an SQL statement using user-supplied data. I use code similar to this in C#:

我试图使用用户提供的数据创建一个SQL语句。我在C＃中使用与此类似的代码：

var sql = "INSERT INTO myTable (myField1, myField2) " +
          "VALUES ('" + someVariable + "', '" + someTextBox.Text + "');";

var cmd = new SqlCommand(sql, myDbConnection);
cmd.ExecuteNonQuery();

and this in VB.NET:

这在VB.NET中：

Dim sql = "INSERT INTO myTable (myField1, myField2) " &
          "VALUES ('" & someVariable & "', '" & someTextBox.Text & "');"

Dim cmd As New SqlCommand(sql, myDbConnection)
cmd.ExecuteNonQuery()

However,

然而，

• this fails when the user input contains single quotes (e.g. O'Brien),
• 当用户输入包含单引号（例如O'Brien）时，这会失败，
• I cannot seem to get the format right when inserting DateTime values and
• 插入DateTime值时，我似乎无法正确获取格式
• people keep telling me that I should not do this because of "SQL injection".
• 人们一直告诉我，由于“SQL注入”，我不应该这样做。

How do I do it "the right way"?

我怎么做“正确的方式”？

2 个解决方案

#1

Use parameterized SQL.

使用参数化SQL。

Examples

例子

(These examples are in C#, see below for the VB.NET version.)

（这些示例在C＃中，请参阅下面的VB.NET版本。）

Replace your string concatenations with @... placeholders and, afterwards, add the values to your SqlCommand. You can choose the name of the placeholders freely, just make sure that they start with the @ sign. Your example would look like this:

用@ ...占位符替换字符串连接，然后将值添加到SqlCommand。您可以*选择占位符的名称，只需确保它们以@符号开头即可。你的例子看起来像这样：

var sql = "INSERT INTO myTable (myField1, myField2) " +
          "VALUES (@someValue, @someOtherValue);";

using (var cmd = new SqlCommand(sql, myDbConnection))
{
    cmd.Parameters.AddWithValue("@someValue", someVariable);
    cmd.Parameters.AddWithValue("@someOtherValue", someTextBox.Text);
    cmd.ExecuteNonQuery();
}

The same pattern is used for other kinds of SQL statements:

相同的模式用于其他类型的SQL语句：

var sql = "UPDATE myTable SET myField1 = @newValue WHERE myField2 = @someValue;";

// see above, same as INSERT

or

要么

var sql = "SELECT myField1, myField2 FROM myTable WHERE myField3 = @someValue;";

using (var cmd = new SqlCommand(sql, myDbConnection))
{
    cmd.Parameters.AddWithValue("@someValue", someVariable);
    using (var reader = cmd.ExecuteReader())
    {
        ...
    }
    // Alternatively: object result = cmd.ExecuteScalar();
    // if you are only interested in one value of one row.
}

A word of caution: AddWithValue is a good starting point and works fine in most cases. However, that the value you pass in needs to exactly match the data type of the corresponding database field. Otherwise, you might end up in a situation where the conversion prevents your query from using an index. Note that some SQL Server data types, such as char/varchar (without preceding "n") or date do not have a corresponding .NET data type. In those cases, Add with the correct data type should be used instead.

需要注意的是：AddWithValue是一个很好的起点，在大多数情况下都能正常工作。但是，传入的值需要与相应数据库字段的数据类型完全匹配。否则，您可能最终会遇到转换阻止查询使用索引的情况。请注意，某些SQL Server数据类型（如char / varchar（不带前面的“n”）或日期）没有相应的.NET数据类型。在这些情况下，应使用具有正确数据类型的Add。

Why should I do that?

我为什么要那么做？

• It's more secure: It stops SQL injection. _{(Bobby Tables won't delete your student records.)}

  它更安全：它会停止SQL注入。 （Bobby Tables不会删除您的学生记录。）

• It's easier: No need to fiddle around with single and double quotes or to look up the correct string representation of date literals.

  它更容易：无需使用单引号和双引号来查找或查找日期文字的正确字符串表示。

• It's more stable: O'Brien won't crash your application just because he insists on keeping his strange name.

  它更稳定：O'Brien不会因为他坚持保留他的陌生名字而使你的应用程序崩溃。

Other database access libraries

其他数据库访问库

• If you use an OleDbCommand instead of an SqlCommand (e.g., if you are using an MS Access database), use ? instead of @... as the placeholder in the SQL. In that case, the first parameter of AddWithValue is irrelevant; instead, you need to add the parameters in the correct order. The same is true for OdbcCommand.

  如果您使用OleDbCommand而不是SqlCommand（例如，如果您使用的是MS Access数据库），请使用？而不是@ ...作为SQL中的占位符。在这种情况下，AddWithValue的第一个参数是无关紧要的;相反，您需要以正确的顺序添加参数。 OdbcCommand也是如此。

• Entity Framework also supports parameterized queries.

  实体框架还支持参数化查询。

#2

VB.NET Example Code

This is the example code for the wiki answer in vb.net, assuming Option Strict On and Option Infer On.

这是vb.net中wiki答案的示例代码，假设Option Strict On和Option Infer On。

INSERT

插

Dim sql = "INSERT INTO myTable (myField1, myField2) " & 
          "VALUES (@someValue, @someOtherValue);"

Using cmd As New SqlCommand(sql, myDbConnection)
    cmd.Parameters.AddWithValue("@someValue", someVariable)
    cmd.Parameters.AddWithValue("@someOtherValue", someTextBox.Text)
    cmd.ExecuteNonQuery()
End Using

UPDATE

UPDATE

Dim sql = "UPDATE myTable SET myField1 = @newValue WHERE myField2 = @someValue;"

' see above, same as INSERT

SELECT

选择

Dim sql = "SELECT myField1, myField2 FROM myTable WHERE myField3 = @someValue;"

Using cmd As New SqlCommand(sql, myDbConnection)
    cmd.Parameters.AddWithValue("@someValue", someVariable)
    Using reader = cmd.ExecuteReader()
        ' ...
    End Using
    ' Alternatively: Dim result = cmd.ExecuteScalar()
    ' if you are only interested in one value of one row.
End Using