AddWithValue 到 SQL 语句？VB.Net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22782079/
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
AddWithValue to SQL Statements? VB.Net
提问by Aegelis
I have a bunch of AddWithValue statements. I have been racking my brain to figure out a way to change them into SQL Statements.
我有一堆 AddWithValue 语句。我一直在绞尽脑汁想办法把它们改成SQL语句。
This is what it currently looks like;
这是它目前的样子；
Dim updateStatement As String =
"UPDATE Customers SET " &
"Name = @NewName, "
"WHERE Name = @OldName "
Dim updateCommand As New OleDbCommand(updateStatement, connection)
updateCommand.Parameters.AddWithValue("@NewName", newCustomer.Name)
updateCommand.Parameters.AddWithValue("@OldName", oldCustomer.Name)
'Connection Open blah blah blah Connection Closed.
'连接打开等等等等连接关闭。
I was told a different way of doing it was;
有人告诉我一种不同的做法是；
Dim SQL as String = "UPDATE Customers SET" &
"Name = '" Customer.Name "', "
But it refuses to work this way saying that just the two first double quotations is it.
但它拒绝以这种方式工作，说只有前两个双引号就是它。
"Name = '" Customer.Name "',"In italics the code is black and not red which refuses to work.
"Name = '" Customer.Name "',"斜体代码是黑色而不是红色，它拒绝工作。
I am not using a database, I am simply pulling it from the file itself (As if it were in Access)
我没有使用数据库，我只是从文件本身中提取它（就像它在 Access 中一样）
Help me with whatever you can! I wish I could just leave it because if it's not broken don't fix it. But my teacher has a funny way of messing with people.
尽你所能帮助我！我希望我可以离开它，因为如果它没有损坏就不要修理它。但是我的老师有一种与人相处的有趣方式。
回答by Joel Coehoorn
Do NOT do this. EVER. It leaves you vulnerable to a very serious security issue known as Sql Injection.
不要这样做。曾经。它使您容易受到称为 Sql 注入的非常严重的安全问题的影响。
If you want a different way to add a parameter, I can give you one that will actually improveyour code:
如果您想要一种不同的方式来添加参数，我可以为您提供一种实际上可以改进您的代码的方式：
Dim updateStatement As String =
"UPDATE Customers SET " &
"Name = @NewName, "
"WHERE Name = @OldName "
'You should also create your connection this way
Using updateCommand As New OleDbCommand(updateStatement, connection)
'I have to guess at your DB types and lengths
updateCommand.Parameters.Add("@NewName", OleDbType.VarChar, 50).Value = newCustomer.Name
updateCommand.Parameters.Add("@OldName", OleDbType.VarChar, 50).Value = oldCustomer.Name
updateCommand.ExecuteNonQuery()
End Using
There are a certain issues you can run into when using .AddWithValue(), because it forces .Net to guesswhat the database type of your parameter will be. Sometimes, .Net will guess wrong. Even when it guesses wrong, things usually still work most of the time, but what can happen is that performance will suffer dramatically, because the type mismatch forces per-row conversions or breaks the use of an index.
使用 时可能会遇到某些问题.AddWithValue()，因为它会强制 .Net猜测参数的数据库类型。有时，.Net 会猜错。即使它猜错了，大多数时候事情通常仍然有效，但可能发生的是性能会受到严重影响，因为类型不匹配会强制每行转换或破坏索引的使用。
回答by Szymon
I'm not going to give you an answer on how to concatenate queries (i.e. concatenate literal values into the query) correctly because concatenating queries is not the correct way.
我不会就如何正确地连接查询（即将文字值连接到查询中）给出答案，因为连接查询不是正确的方法。
The correct way is the way you originally used: using parameters.
正确的方式是你最初使用的方式：使用参数。
If you put literal values into your queries, you have the following problems:
如果将文字值放入查询中，则会遇到以下问题：
your code is vulnerable to SQL injection. That reason is enough to never recommend it.
you may have problems with types conversion, e.g. for dates, times, numbers as you pass values as strings which have to be converted into proper data types by the database system.
您的代码容易受到SQL 注入的影响。这个理由足以永远不推荐它。
当您将值作为字符串传递时，您可能会遇到类型转换问题，例如日期、时间、数字，这些值必须由数据库系统转换为正确的数据类型。
回答by HAJJAJ
' Add CustomerID parameter for WHERE clause.
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)
回答by Oran D. Lord
You're missing some well-placed string concatenation operators and spaces to get the syntax right:
您缺少一些位置合适的字符串连接运算符和空格来获得正确的语法：
Dim SQL as String = "UPDATE Customers SET " & "Name = '" & newCustomer.Name & "'"
BUT, and perhaps you might already notice the security issue with this, if I set Customer.Name to "maliciousUser'; DROP TABLE Customers; -- "what could happen? Different is not necessarily better.
但是，如果我将 Customer.Name 设置为"maliciousUser'; DROP TABLE Customers; -- "会发生什么？不同不一定更好。
Check this out: From what do sql parameters protect you?
看看这个：sql 参数从什么方面保护你？
