php MySQL 插入特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14976863/
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 Insert special characters
提问by conmen
I had a registration form which will insert into a member table, the collation is in utf8_general_ci, and I use SET NAMES utf8in php script, OK here is the problem, I can't insert a string other than pure alphabet, I try input 'Hélène' in a form field, after the sql query run, I check with my db table, the field is inserted as 'H', the rest of the alphabet cannot be insert whenever the string come with special alphabet such as é, ?on behind.
我有一个注册表单，它将插入到成员表中，排序规则在utf8_general_ci，我SET NAMES utf8在 php 脚本中使用，好的，这里是问题，我不能插入纯字母以外的字符串，我尝试输入 'Hélène'一个表单字段，在 sql 查询运行后，我检查了我的 db 表，该字段被插入为“H”，每当字符串带有特殊字母表（例如é, ?后面）时，其余的字母表无法插入。
Can someone please help? thanks.
有人可以帮忙吗？谢谢。
SOLUTION:
解决方案：
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
mysql_query("SET NAMES utf8");
above two line is crucial part to accept input into database and output on a webpage.
以上两行是接受数据库输入和网页输出的关键部分。
Thanks everyone for the advise.
谢谢大家的建议。
回答by Ripa Saha
try to insert data after encoding. try mysql_real_escape_string()to encode. then execute insert query.
尝试在编码后插入数据。尝试mysql_real_escape_string()编码。然后执行插入查询。
EDIT:-
编辑：-
This answer was posted one year ago. Now mysql_real_escape_string()for php 5 will work in mysqli::real_escape_stringthis format. please check here
这个答案是一年前发布的。现在mysql_real_escape_string()for php 5 将在mysqli::real_escape_string这种格式下工作。请检查这里
回答by KarlosFontana
And if you don't want to worry about so many different charset codings or if htmlentities doesn't work for you, here the alternative: I used mysqli DB connection (and PHPV5) Form post for writing/inserting to MySQl DB.
如果您不想担心这么多不同的字符集编码，或者如果 htmlentities 对您不起作用，那么这里的替代方法是：我使用 mysqli DB 连接（和 PHPV5）表单帖子来写入/插入到 MySQl DB。
$Notes = $_POST['Notes']; //can be text input or textarea.
$charset = mysqli_character_set_name($link); //only works for mysqli DB connection
printf ("To check your character set but not necessary %s\n",$charset);
$Notes = str_replace('"', '"', $Notes); //double quotes for mailto: emails.
$von = array("?","?","ü","?","?","?","ü"," ","é"); //to correct double whitepaces as well
$zu = array("ä","ö","ü","ß","Ä","Ö","Ü"," ","é");
$Notes = str_replace($von, $zu, $Notes);
echo " Notes:".$Notes."<br>" ;
$Notes = mysqli_real_escape_string($link, $Notes); //for recommended mysqli DB connection.
//$Notes = mysql_real_escape_string($Notes); //for old deprecated mysql DB connection.
// mysqli_real_escape_string Escapes special characters in a string for use in an SQL statement
echo " Notes:".$Notes ; //ready for inserting
回答by Vaibhav Tomar
The reason you are not able to do so is because PHP prevents a set of special characters to be sent to database to avoid things like sql injection. You can use the escape string function if you wish to escape but the best way to do so is by using the prepared statements.
您无法这样做的原因是因为 PHP 会阻止将一组特殊字符发送到数据库以避免诸如 sql 注入之类的事情。如果您想转义，您可以使用转义字符串函数，但最好的方法是使用准备好的语句。
$connection = <"Your database connection">;
$userinput = "Special characters Hélène";
$stmt = $con->prepare("insert into `table` (`fieldname`)values(?)");
$stmt->bind_param("s",$input);
$stmt->execute();
