vb.net 如何将用户提供的输入添加到 SQL 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35163361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I add user-supplied input to an SQL statement?
提问by Heinzi
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), - I cannot seem to get the format right when inserting DateTime values and
- people keep telling me that I should not do this because of "SQL injection".
- 当用户输入包含单引号（例如
O'Brien）时，这会失败， - 插入 DateTime 值时，我似乎无法正确获取格式，并且
- 人们一直告诉我，我不应该因为“SQL 注入”而这样做。
How do I do it "the right way"?
我该如何“以正确的方式”做到这一点？
回答by Heinzi
Use parameterized SQL.
使用参数化 SQL。
Examples
例子
(These examples are in C#, see belowfor 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: AddWithValueis 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, Addwith 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.)
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'Brienwon'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 ofAddWithValueis 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 也是如此。
回答by Igor
VB.NET Example Code
VB.NET 示例代码
This is the example code for the wiki answerin vb.net, assuming Option Strict Onand Option Infer On.
这是示例代码维基答案在vb.net，假设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
更新
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
