如何在 MySQL 中转义单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/887036/
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 single quotes in MySQL
提问by Ashok Gupta
How do I insert a value in MySQL that consist of single or double quotes. i.e
如何在 MySQL 中插入由单引号或双引号组成的值。IE
This is Ashok's Pen.
The single quote will create problems. There might be other escape characters.
单引号会产生问题。可能还有其他转义字符。
How do you insert the data properly?
你如何正确插入数据？
回答by Rob
Put quite simply:
简单地说：
SELECT 'This is Ashok''s Pen.';
SELECT 'This is Ashok''s Pen.';
So inside the string, replace each single quote with two of them.
所以在字符串内部，用其中的两个替换每个单引号。
Or:
或者：
SELECT 'This is Ashok\'s Pen.'
SELECT 'This is Ashok\'s Pen.'
Escape it =)
逃脱它 =)
回答by SO User
' is the escape character. So your string should be:
' 是转义字符。所以你的字符串应该是：
This is Ashok''s Pen
这是阿肖克的钢笔
If you are using some front-end code, you need to do a string replace before sending the data to the stored procedure.
如果您使用的是一些前端代码，则需要在将数据发送到存储过程之前进行字符串替换。
For example, in C# you can do
例如，在 C# 中你可以这样做
value = value.Replace("'", "''");
and then pass value to the stored procedure.
然后将值传递给存储过程。
回答by Paul Dixon
See my answer to "How to escape characters in MySQL"
请参阅我对“如何在 MySQL 中转义字符”的回答
Whatever library you are using to talk to MySQL will have an escaping function built in, e.g. in PHP you could use mysqli_real_escape_stringor PDO::quote
无论您使用什么库与 MySQL 对话，都会有一个内置的转义函数，例如在 PHP 中，您可以使用mysqli_real_escape_string或PDO::quote
回答by hd1
If you use prepared statements, the driver will handle any escaping. For example (Java):
如果您使用准备好的语句，驱动程序将处理任何转义。例如（Java）：
Connection conn = DriverManager.getConnection(driverUrl);
conn.setAutoCommit(false);
PreparedStatement prepped = conn.prepareStatement("INSERT INTO tbl(fileinfo) VALUES(?)");
String line = null;
while ((line = br.readLine()) != null) {
prepped.setString(1, line);
prepped.executeQuery();
}
conn.commit();
conn.close();
回答by user3818708
Use this code:
使用此代码：
<?php
$var = "This is Ashok's Pen.";
mysql_real_escape_string($var);
?>
This will solve your problem, because the database can't detect the special characters of a string.
这将解决您的问题，因为数据库无法检测字符串的特殊字符。
回答by Christopher Schultz
There is another way to do this which may or may not be safer, depending upon your perspective. It requires MySQL 5.6 or later because of the use of a specific string function: FROM_BASE64.
还有另一种方法可以做到这一点，这可能更安全，也可能不安全，这取决于您的观点。由于使用了特定的字符串函数，它需要 MySQL 5.6 或更高版本：FROM_BASE64.
Let's say you have this message you'd like to insert:
假设您有要插入的消息：
"Ah," Nearly Headless Nick waved an elegant hand, "a matter of no importance. . . . It's not as though I really wanted to join. . . . Thought I'd apply, but apparently I 'don't fulfill requirements' -"
“啊，”差点没头的尼克优雅地挥了挥手，“无关紧要的事……我也不是真的想加入……我以为我会申请，但显然我不符合要求’——”
That quote has a bunch of single- and double-quotes and would be a real pain to insert into MySQL. If you are inserting that from a program, it's easy to escape the quotes, etc. But, if you have to put that into a SQL script, you'll have to edit the text (to escape the quotes) which could be error prone or sensitive to word-wrapping, etc.
那个引用有一堆单引号和双引号，插入 MySQL 真的很痛苦。如果您从程序中插入它，很容易转义引号等。但是，如果您必须将其放入 SQL 脚本中，则必须编辑文本（以转义引号），这可能容易出错或对自动换行等敏感。
Instead, you can Base64-encode the text, so you have a "clean" string:
相反，您可以对文本进行 Base64 编码，这样您就有了一个“干净”的字符串：
JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS
MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG
dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ
SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj
JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr
Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn
VkSE1uSUMwaUlBPT0K
Some notes about Base64-encoding:
关于 Base64 编码的一些注意事项：
- Base64-encoding is a binary encoding, so you'd better make sure that you get your character set correct when you do the encoding, because MySQL is going to decode the Base64-encoded string into bytes and then interpret those. Be sure
base64and MySQL agree on what the character encoding is (I recommend UTF-8). - I've wrapped the string to 50 columns for readability on Stack Overflow. You can wrap it to any number of columns you want (or not wrap at all) and it will still work.
- Base64 编码是一种二进制编码，因此在进行编码时最好确保字符集正确无误，因为 MySQL 会将 Base64 编码的字符串解码为字节，然后对其进行解释。确保
base64和 MySQL 就字符编码是什么达成一致（我推荐 UTF-8）。 - 为了在 Stack Overflow 上可读，我已将字符串包装为 50 列。您可以将它包装到您想要的任意数量的列（或根本不包装），它仍然可以工作。
Now, to load this into MySQL:
现在，将其加载到 MySQL 中：
SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD
JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS
MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG
dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ
SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj
JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr
Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn
VkSE1uSUMwaUlBPT0K
'));
This will insert without any complaints, and you didn't have to manually-escape any text inside the string.
这将插入而没有任何抱怨，并且您不必手动转义字符串中的任何文本。
回答by simon622
You should escape the special characters using the \character.
您应该使用\字符转义特殊字符。
This is Ashok's Pen.
Becomes:
变成：
This is Ashok\'s Pen.
回答by Marcel Verwey
In PHP, use mysqli_real_escape_string.
在 PHP 中，使用mysqli_real_escape_string。
Example from the PHP Manual:
PHP 手册中的示例：
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");
$city = "'s Hertogenbosch";
/* this query will fail, cause we didn't escape $city */
if (!mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
printf("Error: %s\n", mysqli_sqlstate($link));
}
$city = mysqli_real_escape_string($link, $city);
/* this query with escaped $city will work */
if (mysqli_query($link, "INSERT into myCity (Name) VALUES ('$city')")) {
printf("%d Row inserted.\n", mysqli_affected_rows($link));
}
mysqli_close($link);
?>
回答by Devil
You can use this code,
您可以使用此代码，
<?php
$var = "This is Ashok's Pen.";
addslashes($var);
?>
if mysqli_real_escape_string() does not work.
如果 mysqli_real_escape_string() 不起作用。
回答by Dheeraj singh
If you want to keep (') apostrophe in the database use this below code
如果您想在数据库中保留 (') 撇号，请使用以下代码
$new_value = str_replace("'","\'", $value);
$new_value can store in database.
$new_value 可以存储在数据库中。
