vb.net 在 SQL 中插入包含单引号 ' 的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21137290/
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
Inserting string which contain single quotes ' in SQL
提问by Cal
I've seen so many solutions to this that I don't know which one to follow. I thought that what I have would work but upon further testing I have discovered that this is not true. I'm using VB to pick up MS Excel worksheets from a given file directory, extract the data, and insert into SQL data tables.
我看到了很多解决方案，我不知道该遵循哪一个。我认为我所拥有的会起作用，但经过进一步的测试，我发现这不是真的。我正在使用 VB 从给定的文件目录中提取 MS Excel 工作表，提取数据，并插入到 SQL 数据表中。
here's the part I need some help with:
这是我需要帮助的部分：
If saRet(linex, 11) <> "" Then
IntDesc = (saRet(linex, 11).ToString.Replace("'", "''"))
Echo("Internal description: " & IntDesc)
Else
Echo("No internal description given")
IntDesc = ""
End If
After tampering around with some test insert statements in SQL server studio I thought that replacing ' with '' worked. Sadly, not.
在 SQL Server Studio 中篡改了一些测试插入语句后，我认为将 ' 替换为 '' 有效。可悲的是，不是。
Here's an example of a string which makes the insert fail:
这是一个使插入失败的字符串示例：
Set-up and Config of New Button on BP and UDF's for Despatch Process
and after my string manipulation, here's the Insert statement(I've blanked out some data which my company probably doesn't want to share, it's insignificant anyway):
在我的字符串操作之后，这里是 Insert 语句（我已经清除了一些我的公司可能不想共享的数据，无论如何它是微不足道的）：
NSERT INTO <tablename> VALUES ('2013-12-10', '12', '2013', 'AAAA', 'AAAA', '10668', 'JBT', 'Project - Config & System Build', 'CSB', '2', 'Y', 'N', '0', 'Set-up and Config of New Button on BP and UDF's for Despatch Process', 'Set-up and Config of New Button on BP and UDF''s for Despatch Process', '0', 'NULL')
Very grateful for any help! Thanks.
非常感谢任何帮助！谢谢。
回答by Luaan
The best way is always using parameters. Those handle everything for you and you don't need to do any escaping.
最好的方法是始终使用参数。那些为你处理一切，你不需要做任何逃避。
If you can't use parameters, you have to do the encoding yourself, and that's very tricky. One way would be to use a format you can encode safely - for example, instead of inserting as a string literal, you might use binary encoding (eg. cast(0xAABBCCDD as varchar(max))). This is perfectly safe, since you can be sure that there's no invalid character that would break it. Of course, it also has its problems.
如果不能使用参数，则必须自己进行编码，这非常棘手。一种方法是使用可以安全编码的格式 - 例如，您可以使用二进制编码（例如cast(0xAABBCCDD as varchar(max))），而不是作为字符串文字插入。这是完全安全的，因为您可以确定没有无效字符会破坏它。当然，它也有它的问题。
As for your example, replacing 'with ''works fine (although of course you'd have to watch out for other invalid characters, such as endlines). Your problem is that you didn't do the encoding on allthe strings. In your sample, the last string has the proper encoding, the one before it does not. This also beautifully illustrates the pain of making sure you're encoding properly - and everything you miss means an error, or even a way to exploit the code and cause harm. For example, what if the description was ', ''0'', ''NULL''); delete from users --?
至于您的示例，替换'为''工作正常（尽管您当然必须注意其他无效字符，例如结束线）。您的问题是您没有对所有字符串进行编码。在您的示例中，最后一个字符串具有正确的编码，前一个则没有。这也很好地说明了确保正确编码的痛苦 - 而你错过的一切都意味着错误，甚至是一种利用代码并造成伤害的方法。例如，如果描述是', ''0'', ''NULL''); delete from users --？
