How do I insert a value in MySQL that consist of single or double quotes. i.e
如何在MySQL中插入由单引号或双引号组成的值。即
This is Ashok's Pen.
The single quote will create problems. There might be other escape characters.
单引号会产生问题。可能还有其他的转义字符。
How do you insert the data properly?
如何正确插入数据?
16 个解决方案
#1
92
Put quite simply:
很简单:
SELECT 'This is Ashok''s Pen.';
选择“这是Ashok的钢笔”;
So inside the string, replace each single quote with two of them.
在字符串内部，用两个引号替换每个引号。
Or:
或者:
SELECT 'This is Ashok\'s Pen.'
选择“这是Ashok\'s Pen”。
Escape it =)
逃避=)
#2
9
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_string or PDO::quote
无论您使用哪个库与MySQL对话，都将有一个内置函数，例如在PHP中，您可以使用mysqli_real_escape_string或PDO::quote。
#3
8
' is the escape character. So your string should be: This is Ashok''s Pen
是转义字符。所以你的线应该是:这是Ashok的笔
Edit:
编辑:
If you are using some front-end code, you need to do a string replace before sending the data to the SP.
如果正在使用前端代码，则需要在将数据发送到SP之前进行字符串替换。
For eg. in C# you can do
如。在c#中你可以做到
value = value.Replace("'","''");
and then pass value to SP.
然后将值传递给SP。
#4
6
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();
#5
5
You should escape the special characters using the \ character.
您应该使用\字符转义特殊字符。
This is Ashok's Pen.
Becomes:
就变成:
This is Ashok\'s Pen.
#6
4
Use this code :
使用这段代码:
<?php $var = "This is Ashok's Pen.";
mysql_real_escape_string($var);?>
This will solve your problem cause database can't detect special character of string.
这将解决您的问题，因为数据库不能检测到字符串的特殊字符。
#7
3
You can use this code
您可以使用此代码
<?php
$var = "This is Ashok's Pen.";
addslashes($var);?>
if mysqli_real_escape_string not work
如果mysqli_real_escape_string不工作
#8
3
This is a really old question, but 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.
这是一个很老的问题，但是有另一种方法可以解决这个问题，它可能更安全，也可能不安全，这取决于你的观点。由于使用了特定的字符串函数:FROM_BASE64，所以需要MySQL 5.6或更高的时间。
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编码，这样就有了一个“clean”字符串:
SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD 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). - base64编码是一种二进制编码，所以在进行编码时最好确保字符集是正确的，因为MySQL将把base64编码的字符串解码为字节，然后解释这些字符串。请确保base64和MySQL都同意什么是字符编码(我推荐UTF-8)。
- I've wrapped the string to 50 columns for readability on SO. You can wrap it to any number of columns you want (or not wrap at all) and it will still work.
- 为了便于阅读，我将字符串封装到50列中。您可以将它封装到任意数量的列中(或者根本不封装)，它仍然可以工作。
Now, to load this into MySQL:
现在，将它加载到MySQL中:
INSERT INTO my_table (text) VALUES (FROM_BASE64(' SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K '));
在表中插入值(FROM_BASE64)
This will insert without any complaints, and you didn't have to manually-escape any text inside the string.
这将毫无怨言地插入，并且您不必手动转义字符串中的任何文本。
#9
2
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);
?>
#10
2
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可以存储在数据库中。
#11
1
$var = mysqli_real_escape_string($conn, $_POST['varfield']);
#12
0
For programmatic access, you can use placeholders to automatically escape unsafe characters for you.
对于编程访问，可以使用占位符自动转义不安全字符。
In Perl DBI, for example, you can use:
例如，在Perl DBI中，您可以使用:
my $string = "This is Ashok's pen";
$dbh->do("insert into my_table(my_string) values(?)",undef,($string));
#13
0
if you are using php just use addslashes() function
如果您正在使用php，请使用addslashes()函数。
PHP手册addslashes
#14
0
Maybe you could take a look at function Quote in the Mysql manual.
也许你可以看看Mysql手册中的函数报价。
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html function_quote
#15
0
The way I do, by using Delphi:
我用德尔菲的方法
TheString to "escape":
TheString“逃离”:
TheString=" bla bla bla 'em some more apo:S 'em and so on ";
Solution:
解决方案:
StringReplace(TheString, #39,'\'+#39, [rfReplaceAll, rfIgnoreCase]);
Result:
结果:
TheString=" bla bla bla \'em some more apo:S \'em and so on ";
This function will replace all Char(39) with "\'" allowing you to insert or update text fields in MySQL without any problem.
该函数将用“\”替换所有Char(39)，允许您在MySQL中插入或更新文本字段，而不会出现任何问题。
Similar functions are found in all prog-languages!
在所有prog语言中都可以找到类似的函数!
#16
0
use either addslahes(), or mysql_real_escape_string
使用addslahes()或mysql_real_escape_string。
#1
92
Put quite simply:
很简单:
SELECT 'This is Ashok''s Pen.';
选择“这是Ashok的钢笔”;
So inside the string, replace each single quote with two of them.
在字符串内部，用两个引号替换每个引号。
Or:
或者:
SELECT 'This is Ashok\'s Pen.'
选择“这是Ashok\'s Pen”。
Escape it =)
逃避=)
#2
9
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_string or PDO::quote
无论您使用哪个库与MySQL对话，都将有一个内置函数，例如在PHP中，您可以使用mysqli_real_escape_string或PDO::quote。
#3
8
' is the escape character. So your string should be: This is Ashok''s Pen
是转义字符。所以你的线应该是:这是Ashok的笔
Edit:
编辑:
If you are using some front-end code, you need to do a string replace before sending the data to the SP.
如果正在使用前端代码，则需要在将数据发送到SP之前进行字符串替换。
For eg. in C# you can do
如。在c#中你可以做到
value = value.Replace("'","''");
and then pass value to SP.
然后将值传递给SP。
#4
6
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();
#5
5
You should escape the special characters using the \ character.
您应该使用\字符转义特殊字符。
This is Ashok's Pen.
Becomes:
就变成:
This is Ashok\'s Pen.
#6
4
Use this code :
使用这段代码:
<?php $var = "This is Ashok's Pen.";
mysql_real_escape_string($var);?>
This will solve your problem cause database can't detect special character of string.
这将解决您的问题，因为数据库不能检测到字符串的特殊字符。
#7
3
You can use this code
您可以使用此代码
<?php
$var = "This is Ashok's Pen.";
addslashes($var);?>
if mysqli_real_escape_string not work
如果mysqli_real_escape_string不工作
#8
3
This is a really old question, but 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.
这是一个很老的问题，但是有另一种方法可以解决这个问题，它可能更安全，也可能不安全，这取决于你的观点。由于使用了特定的字符串函数:FROM_BASE64，所以需要MySQL 5.6或更高的时间。
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编码，这样就有了一个“clean”字符串:
SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD 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). - base64编码是一种二进制编码，所以在进行编码时最好确保字符集是正确的，因为MySQL将把base64编码的字符串解码为字节，然后解释这些字符串。请确保base64和MySQL都同意什么是字符编码(我推荐UTF-8)。
- I've wrapped the string to 50 columns for readability on SO. You can wrap it to any number of columns you want (or not wrap at all) and it will still work.
- 为了便于阅读，我将字符串封装到50列中。您可以将它封装到任意数量的列中(或者根本不封装)，它仍然可以工作。
Now, to load this into MySQL:
现在，将它加载到MySQL中:
INSERT INTO my_table (text) VALUES (FROM_BASE64(' SWtGb0xDSWdUbVZoY214NUlFaGxZV1JzWlhOeklFNXBZMnNnZD JGMlpXUWdZVzRnWld4bFoyRnVkQ0JvWVc1a0xDQWlZU0J0WVhS MFpYCklnYjJZZ2JtOGdhVzF3YjNKMFlXNWpaUzRnTGlBdUlDNG dTWFFuY3lCdWIzUWdZWE1nZEdodmRXZG9JRWtnY21WaGJHeDVJ SGRoYm5SbApaQ0IwYnlCcWIybHVMaUF1SUM0Z0xpQlVhRzkxWj JoMElFa25aQ0JoY0hCc2VTd2dZblYwSUdGd2NHRnlaVzUwYkhr Z1NTQW5aRzl1SjMKUWdablZzWm1sc2JDQnlaWEYxYVhKbGJXVn VkSE1uSUMwaUlBPT0K '));
在表中插入值(FROM_BASE64)
This will insert without any complaints, and you didn't have to manually-escape any text inside the string.
这将毫无怨言地插入，并且您不必手动转义字符串中的任何文本。
#9
2
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);
?>
#10
2
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可以存储在数据库中。
#11
1
$var = mysqli_real_escape_string($conn, $_POST['varfield']);
#12
0
For programmatic access, you can use placeholders to automatically escape unsafe characters for you.
对于编程访问，可以使用占位符自动转义不安全字符。
In Perl DBI, for example, you can use:
例如，在Perl DBI中，您可以使用:
my $string = "This is Ashok's pen";
$dbh->do("insert into my_table(my_string) values(?)",undef,($string));
#13
0
if you are using php just use addslashes() function
如果您正在使用php，请使用addslashes()函数。
PHP手册addslashes
#14
0
Maybe you could take a look at function Quote in the Mysql manual.
也许你可以看看Mysql手册中的函数报价。
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote
http://dev.mysql.com/doc/refman/5.7/en/string-functions.html function_quote
#15
0
The way I do, by using Delphi:
我用德尔菲的方法
TheString to "escape":
TheString“逃离”:
TheString=" bla bla bla 'em some more apo:S 'em and so on ";
Solution:
解决方案:
StringReplace(TheString, #39,'\'+#39, [rfReplaceAll, rfIgnoreCase]);
Result:
结果:
TheString=" bla bla bla \'em some more apo:S \'em and so on ";
This function will replace all Char(39) with "\'" allowing you to insert or update text fields in MySQL without any problem.
该函数将用“\”替换所有Char(39)，允许您在MySQL中插入或更新文本字段，而不会出现任何问题。
Similar functions are found in all prog-languages!
在所有prog语言中都可以找到类似的函数!
#16
0
use either addslahes(), or mysql_real_escape_string
使用addslahes()或mysql_real_escape_string。