MySQL 如何在MySql中转义撇号（'）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9596652/
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 to escape apostrophe (') in MySql?
提问by user4951
The MySQL documentationsays that it should be \'. However, both scite and mysql shows that ''works. I saw that and it works. What should I do?
在MySQL文档说，它应该是\'。但是，scite 和 mysql 都显示''有效。我看到了，它的工作原理。我该怎么办？
回答by Jim DeLaHunt
The MySQL documentation you cite actually says a little bit more than you mention. It also says,
您引用的 MySQL 文档实际上比您提到的要多一些。它还说，
A “
'” inside a string quoted with “'” may be written as “''”.
A“
'”加引号的字符串内“'”可以写为“''”。
(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequenceslooks pretty similar.)
（此外，您链接到MySQL 5.0 版本的 Table 8.1. Special Character Escape Sequences，当前版本是 5.6 — 但当前的Table 8.1. Special Character Escape Sequences看起来非常相似。）
I think the Postgres note on the backslash_quote (string)parameteris informative:
我认为关于backslash_quote (string)参数的Postgres 注释是有用的：
This controls whether a quote mark can be represented by
\'in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted\'. However, use of\'creates security risks...
这控制是否可以
\'在字符串文字中表示引号。表示引号的首选 SQL 标准方式是将它加倍 ('')，但 PostgreSQL 历来也接受\'. 但是，使用\'会产生安全风险......
That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.
这对我说，与使用反斜杠转义单引号相比，使用双引号字符是更好的整体和长期选择。
Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.
现在，如果您还想在等式中添加语言选择、SQL 数据库及其非标准特性的选择以及查询框架的选择，那么您最终可能会有不同的选择。你没有提供太多关于你的约束的信息。
回答by Jonathan Leffler
Standard SQL uses doubled-up quotes; MySQL has to accept that to be reasonably compliant.
标准 SQL 使用双引号；MySQL 必须接受这一点才能合理地合规。
'He said, "Don''t!"'
回答by user3169788
What I believe user2087510 meant was:
我相信 user2087510 的意思是：
name = 'something'
name = name.replace("'", "\'")
I have also used this with success.
我也成功地使用了它。
回答by MRRaja
just write ''in place of 'i mean two times '
只是写''代替'我的意思两次'
回答by Overkillica
Here's an example:
下面是一个例子：
SELECT * FROM pubs WHERE name LIKE "%John's%"
Just use double quotes to enclose the single quote.
只需使用双引号将单引号括起来即可。
If you insist in using single quotes (and the need to escape the character):
如果您坚持使用单引号（并且需要对字符进行转义）：
SELECT * FROM pubs WHERE name LIKE '%John\'s%'
回答by Robert Rocha
There are three ways I am aware of. The first not being the prettiest and the second being the common way in most programming languages:
我知道有三种方式。第一个不是最漂亮的，第二个是大多数编程语言中的常用方法：
- Use another single quote:
'I mustn''t sin!' - Use the escape character
\before the single quote':'I mustn\'t sin!' - Use double quotes to enclose string instead of single quotes:
"I mustn't sin!"
- 使用另一个单引号：
'I mustn''t sin!' \在单引号前使用转义字符'：'I mustn\'t sin!'- 使用双引号将字符串而不是单引号括起来：
"I mustn't sin!"
回答by Ashutosh Jha
Replace the string
替换字符串
value = value.replace(/'/g, "\'");
where value is your string which is going to store in your Database.
其中 value 是将要存储在数据库中的字符串。
Further,
NPM package for this, you can have look into it
更远，
用于此的 NPM 包，您可以查看它
回答by Priyanka Pandhi
I think if you have any data point with apostrophe you can add one apostrophe before the apostrophe
我认为如果您有任何带撇号的数据点，您可以在撇号前添加一个撇号
eg. 'This is John's place'
例如。“这是约翰的地方”
Here MYSQL assumes two sentence 'This is John' 's place'
这里 MYSQL 假设两个句子 'This is John''s place'
You can put 'This is John''s place'. I think it should work that way.
你可以输入“这是约翰的地方”。我认为它应该这样工作。
回答by will
In PHP I like using mysqli_real_escape_string() which escapes special characters in a string for use in an SQL statement.
在 PHP 中，我喜欢使用 mysqli_real_escape_string() 来转义字符串中的特殊字符以用于 SQL 语句。
see https://www.php.net/manual/en/mysqli.real-escape-string.php
见https://www.php.net/manual/en/mysqli.real-escape-string.php
回答by Grindlay
Possibly off-topic, but maybe you came here looking for a way to sanitise text input from an HTML form, so that when a user inputs the apostrophe character, it doesn't throw an error when you try to write the text to a SQL-based table in a DB. There are a couple of ways to do this, and you might want to read about SQL injection too, but a simple option in PHP is to use the htmlspecialchars()function which will convert all your apostrophes into 'which is possibly what you want to store anyway.
可能偏离主题，但也许您来到这里是为了寻找一种方法来清理从 HTML 表单输入的文本，以便当用户输入撇号字符时，当您尝试将文本写入 SQL 时不会抛出错误基于数据库中的表。有几种方法可以做到这一点，您可能也想了解 SQL 注入，但 PHP 中的一个简单选项是使用htmlspecialchars()函数，该函数会将您所有的撇号转换成您'可能想要存储的内容反正。
