Mysql + php 带有特殊字符，如'（撇号）和“（引号）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4659879/
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 + php with special characters like '(Apostrophe) and " (Quotation mark)
提问by Daniel Rufus Kaldheim
I have been struggling with a small problem for a while. It's been there for years but it's just been an irritating problem and not a serious one, and I have just worked around it. But now I want to find out if anyone can help me. I have done some google'ing but no success.
我一直在为一个小问题而苦苦挣扎。它已经存在多年，但它只是一个令人恼火的问题而不是一个严重的问题，我刚刚解决了它。但是现在我想知道是否有人可以帮助我。我已经做了一些 google'ing 但没有成功。
If I do a form post from a html textarea in a php file like this:
如果我从 php 文件中的 html textarea 进行表单发布，如下所示：
<form action="http://action.com" method="post">
<textarea name="text"><a href="http://google.com">google's site</a></textarea>
</form>
and of course there is a submit button and so on.
当然还有一个提交按钮等等。
The value is the problem: <a href="http://google.com">google's site</a>The value of the textarea have both "(Quotation mark) and '(Apostrophe).
值是问题：<a href="http://google.com">google's site</a>textarea的值有"(引号)和'(撇号)。
To save this in a mysql_database I do this:
要将其保存在 mysql_database 中，我这样做：
$result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".$_POST['text']."') ") or die(mysql_error());
And now I get the mysql error:
现在我收到了 mysql 错误：
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 site'' at line 1
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“站点”附近使用的正确语法
回答by Peter Porfy
Your sql string will be:
您的 sql 字符串将是：
INSERT INTO `table` (`row1`) VALUES ('google's site')
Which is not a valid statement. As Nanne wrote, escape the string at least with mysql_real_escape_string : http://php.net/manual/en/function.mysql-real-escape-string.php
这不是一个有效的陈述。正如 Nanne 所写，至少使用 mysql_real_escape_string 转义字符串：http://php.net/manual/en/function.mysql-real-escape-string.php
And read about sql injection http://en.wikipedia.org/wiki/SQL_injection
并阅读关于 sql 注入 http://en.wikipedia.org/wiki/SQL_injection
Think a bit: if someone posts this: $_POST['text']with value: ');delete from table;....
想一想：如果有人发布这个：$_POST['text']有价值：');delete from table;....
Your can say good bye to your data :)
你可以和你的数据说再见了 :)
Always filter/escape input!
总是过滤/转义输入！
EDIT: As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. Please use mysqli extension and mysqli::escape_string function instead
编辑：自 PHP 5.5.0 起 mysql_real_escape_string 和 mysql 扩展已被弃用。请改用 mysqli 扩展和 mysqli::escape_string 函数
回答by methodin
Always at least use mysql_real_escape_string when adding user-provided values into the Database. You should look into binding parameters or mysqli so your query would become:
将用户提供的值添加到数据库时，始终至少使用 mysql_real_escape_string。您应该查看绑定参数或 mysqli，以便您的查询变为：
INSERT INTO `table` (`row1`) VALUES (?)
And ? would be replaced by the actual value after sanitizing the input.
和 ？将在清理输入后由实际值替换。
In your case use:
在你的情况下使用：
$result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".mysql_real_escape_string($_POST['text'])."') ") or die(mysql_error());
Read up on SQL Injection. It's worth doing right ASAP!
阅读 SQL 注入。值得尽快做对！
回答by Nanne
回答by Hemi
you can use addslashes() function. It Quote string with slashes. so, it will be very useful to you when you are adding any apostrophe in your field.
您可以使用 addlashes() 函数。它引用带斜杠的字符串。因此，当您在您的领域中添加任何撇号时，这对您非常有用。
$result = mysql_query("INSERT INTO `table` (`row1`) VALUES ('".addslashes($_POST['text'])."') ") or die(mysql_error());
回答by Stephen
instead of using the old mysql* functions, use PDO and write parameterized queries - http://php.net/pdo
而不是使用旧的 mysql* 函数，使用 PDO 并编写参数化查询 - http://php.net/pdo
回答by Micheal P.
I was also Struggling about characters when I was updating data in mysql.
当我在 mysql 中更新数据时，我也在为字符而苦苦挣扎。
But I finally came to a better answer, Here is:
但我终于找到了一个更好的答案，这里是：
$lastname = "$_POST["lastname"]"; //lastname is : O'Brian, Bran'storm
And When you are going to update your database, the system will not update it unless you use the MySQL REAL Escape String. Here:
并且当您要更新数据库时，除非您使用 MySQL REAL Escape String，否则系统不会更新它。这里：
$lastname = mysql_real_escape_string($_POST["lastname"]); // This Works Always.
Then you query will update certainly.
那么你的查询肯定会更新。
Example: mysql_query("UPDATE client SET lastname = '$lastname' where clientID = '%"); //This will update your data and provide you with security.
For More Information, please check MYSQL_REAL_ESCAPE_STRING
有关更多信息，请检查MYSQL_REAL_ESCAPE_STRING
Hope This Helps
希望这可以帮助
回答by Vaibhav Tomar
Just use prepared statements and you wouldn't have to worry about escaping or sql injection.
只需使用准备好的语句，您就不必担心转义或 sql 注入。
$con = <"Your database connection">;
$input = "What's up?";
$stmt = $con->prepare("insert into `tablename` (`field`)values(?)");
$stmt->bind_param("s",$input);
$stmt->execute();
