php 插入包含撇号（单引号）的数据时出现 MySQL 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7600661/
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
MySQL error when inserting data containing apostrophes (single quotes)?
提问by red23jordan
When I an insert query contains a quote (e.g. Kellog's), it fails to insert a record.
当我插入查询包含引号（例如Kellog's）时，它无法插入记录。
ERROR MSG:
错误消息：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's','Corn Flakes 170g','$ 15.90','$ 15.90','$ 14.10','--')' at line 1MySQL Update Error:
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 's','Corn Flakes 170g','$ 15.90','$ 15.90','$ 14.10','--')' 附近使用的正确语法1MySQL更新错误：
The first 's', should be Kellogg's.
第一个's'，应该是Kellogg's。
Is there any solution?
有什么解决办法吗？
回答by Shef
Escapethe quote with a backslash. Like 'Kellogg\'s'.
用反斜杠转义引号。喜欢'Kellogg\'s'。
Here is your function, using mysql_real_escape_string:
这是您的功能，使用mysql_real_escape_string：
function insert($database, $table, $data_array) {
// Connect to MySQL server and select database
$mysql_connect = connect_to_database();
mysql_select_db ($database, $mysql_connect);
// Create column and data values for SQL command
foreach ($data_array as $key => $value) {
$tmp_col[] = $key;
$tmp_dat[] = "'".mysql_real_escape_string($value)."'"; // <-- escape against SQL injections
}
$columns = join(',', $tmp_col);
$data = join(',', $tmp_dat);
// Create and execute SQL command
$sql = 'INSERT INTO '.$table.'('.$columns.')VALUES('. $data.')';
$result = mysql_query($sql, $mysql_connect);
// Report SQL error, if one occured, otherwise return result
if(!$result) {
echo 'MySQL Update Error: '.mysql_error($mysql_connect);
$result = '';
} else {
return $result;
}
}
回答by user3272729
You should pass the variable ordata inside mysql_real_escape_string(trim($val)), where $valis the data on which you are getting an error.
你应该在里面传递变量或数据mysql_real_escape_string(trim($val))，$val你得到错误的数据在哪里。
If you enter the text, i.e., "I love Kellog's", we have a 'in the string so it will break the query. To avoid it you need to store data in a variable like this $val = "I love Kellog's".
如果您输入文本，即“I love Kellog's”，我们'在字符串中有 a ，因此它会中断查询。为了避免它，您需要将数据存储在这样的变量中$val = "I love Kellog's"。
Now, this should work:
现在，这应该有效：
$goodval = mysql_real_escape_string(trim($val));
回答by Nusrat Robina
Replace mysqlwith mysqli. Use this
替换mysql为mysqli。用这个
mysqli_real_escape_string($connection,$_POST['Description'])
回答by Mindgames
You need to escape the apostrophe (that is, tell SQL that the apostrophe is to be taken literally and not as the beginning or end of a string) using a \.
您需要使用 \ 对撇号进行转义（即，告诉 SQL 撇号应按字面意义使用，而不是作为字符串的开头或结尾）。
Add a \ before the apostrophe in Kellogg's, giving you Kellogg\'s.
在 Kellogg's 中的撇号前添加一个 \，为您提供 Kellogg's。
回答by Jonathan Leffler
In standard SQL, you use two single quotes to indicate one single quote, hence:
在标准 SQL 中，您使用两个单引号表示一个单引号，因此：
INSERT INTO SingleColumn(SingleChar) VALUES('''');
The first quote opens the string; the second and third are a single quote; and the fourth terminates the string. In MySQL, you may also be able to use a backslash instead:
第一个引号打开字符串；第二个和第三个是单引号；第四个终止字符串。在 MySQL 中，您也可以使用反斜杠代替：
INSERT INTO SingleColumn(SingleChar) VALUES('\'');
So, in your example, one or both of these should work:
因此，在您的示例中，其中一个或两个都应该起作用：
INSERT INTO UnidentifiedTable
VALUES('Kellog''s', 'Corn Flakes 170g', '$ 15.90', '$ 15.90', '$ 14.10', '--');
INSERT INTO UnidentifiedTable
VALUES('Kellog\'s', 'Corn Flakes 170g', '$ 15.90', '$ 15.90', '$ 14.10', '--');
In PHP, there is a function to sanitize user data (mysql_real_escape_string) before you embed it into an SQL statement -- or you should use placeholders. Note that if you do not sanitize your data, you expose yourself to SQL Injectionattacks.
在 PHP 中，有一个函数可以在将用户数据 ( mysql_real_escape_string) 嵌入 SQL 语句之前对其进行清理——或者您应该使用占位符。请注意，如果您不清理数据，则会将自己暴露在SQL 注入攻击中。
回答by user2927036
User this one.
用户这个。
mysql_real_escape_string(trim($val));
回答by Heshan Rajapaksha
Optimized for multiple versions of PHP
针对多个版本的 PHP 进行了优化
function mysql_prep($value){
$magic_quotes_active = get_magic_quotes_gpc();
$new_enough_php = function_exists("mysql_real_escape_string");//i.e PHP>=v4.3.0
if($new_enough_php){//php v4.3.o or higher
//undo any magic quote effects so mysql_real_escape_string( can do the work
if($magic_quotes_active){
$value = stripslashes($value);
}
$value = mysql_real_escape_string(trim($value));
}else{//before php v4.3.0
//if magic quotes arn't already on, add slashes
if(!$magic_quotes_active){
$value = addslashes($value);
//if magic quotes are already on, shashes already exists
}
}
return $value;
}
Now just use:
现在只需使用：
mysql_prep($_REQUEST['something'])
回答by cagcak
Escape it by using a helper function like:
使用辅助函数将其转义，例如：
function safeDBname($table_name)
{
$outputText=str_replace("'","",$outputText);
return strtolower($outputText);
}
回答by sanjaya
i did it as below-
我做了如下 -
in my case description field contains apostrophe(').
在我的例子中，描述字段包含撇号（'）。
and here is code:
这是代码：
$description=mysql_real_escape_string($description);
"insert into posts set name='".$name."', address='".$address."', dat='".$dt."', description='".$description."'";
it solved my problem
它解决了我的问题
回答by faz
You can also use the addslashes()function which automatically puts \before 'to avoid error
你也可以使用addslashes()函数，它会自动放在\之前'以避免错误
