将具有 '（撇号）的文本插入 SQL 表的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8336812/
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
Way to insert text having ' (apostrophe) into a SQL table
提问by Nazmul
While I was trying the following SQL command , I got sql error.
当我尝试以下 SQL 命令时，出现 sql 错误。
INSERT INTO exampleTbl VALUES('he doesn't work for me')
where doesn't contain the apostrophe.
where 不包含撇号。
What is the way to insert text having ' (apostrophe) into a SQL table.
将具有 '（撇号）的文本插入 SQL 表的方法是什么。
回答by Laurence Gonsalves
In SQL, the way to do this is to double the apostrophe:
在 SQL 中，这样做的方法是将撇号加倍：
'he doesn''t work for me'
If you are doing this programmatically, you should use an API that accepts parameters and escapes them for you, like prepared statements or similar, rather that escaping and using string concatenation to assemble a query.
如果您以编程方式执行此操作，您应该使用接受参数并为您转义它们的 API，如准备好的语句或类似的，而不是转义并使用字符串连接来组装查询。
回答by Ed B
INSERT INTO exampleTbl VALUES('he doesn''t work for me')
If you're adding a record through ASP.NET, you can use the SqlParameter object to pass in values so you don't have to worry about the apostrophe's that users enter in.
如果您通过 ASP.NET 添加记录，您可以使用 SqlParameter 对象来传递值，这样您就不必担心用户输入的撇号。
回答by FosterZ
try this
尝试这个
INSERT INTO exampleTbl VALUES('he doesn''t work for me')
回答by Daniel Draganov
$value = "he doesn't work for me";
$new_value = str_replace("'", "''", "$value"); // it looks like " ' " , " ' ' "
INSERT INTO exampleTbl (`column`) VALUES('$new_value')
回答by sunil guragol
insert into table1 values("sunil''s book",123,99382932938);
use double apostrophe inside of single apostrophe, it will work
在单撇号内使用双撇号，它会起作用
回答by Kirk
I know the question is aimed at the direct escaping of the apostrophe character but I assume that usually this is going to be triggered by some sort of program providing the input.
我知道这个问题是针对撇号字符的直接转义，但我认为这通常是由某种提供输入的程序触发的。
What I have done universally in the scripts and programs I have worked with is to substitute it with a ` character when processing the formatting of the text being input.
我在我使用过的脚本和程序中普遍做的是在处理输入文本的格式时用 ` 字符替换它。
Now I know that in some cases, the backtick character may in fact be part of what you might be trying to save (such as on a forum like this) but if you're simply saving text input from users it's a possible solution.
现在我知道在某些情况下，反引号字符实际上可能是您可能尝试保存的内容的一部分（例如在这样的论坛上），但如果您只是保存用户输入的文本，这是一个可能的解决方案。
Going into the SQL database
进入SQL数据库
$newval=~s/\'/`/g;
Then, when coming back out for display, filtered again like this:
然后，当回来显示时，再次过滤如下：
$showval=~s/`/\'/g;
This example was when PERL/CGI is being used but it can apply to PHP and other bases as well. I have found it works well because I think it helps prevent possible injection attempts, because all ' are removed prior to attempting an insertion of a record.
这个例子是在使用 PERL/CGI 时，但它也适用于 PHP 和其他基础。我发现它运行良好，因为我认为它有助于防止可能的注入尝试，因为在尝试插入记录之前所有 ' 都被删除。
回答by tika
yes, sql server doesn't allow to insert single quote in table field due to the sql injection attack. so we must replace single appostrophe by double while saving.
是的，由于 sql 注入攻击，sql server 不允许在表字段中插入单引号。所以我们必须在保存时用双引号替换单引号。
(he doesn't work for me) must be => (he doesn''t work for me)
（他不对我来说有效）一定是 =>（他不对我来说有效）
回答by Muhammad Asif Mahmood
you can use backslash '\' if you want to display a single quote in your text.
如果要在文本中显示单引号，可以使用反斜杠 '\'。
INSERT INTO exampleTbl VALUES('He doesn(\')t') ;
INSERT INTO exampleTbl VALUES('He does(\')t') ;
