php 如何在数据库中插入特殊字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2584066/
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 insert special characters into a database?
提问by Joey Morani
Can anyone tell me how to insert special characters into a MySQL database? I've made a PHP script which is meant to insert some words into a database, although if the word contains a ' then it wont be inserted.
谁能告诉我如何将特殊字符插入到 MySQL 数据库中？我制作了一个 PHP 脚本，旨在将一些单词插入到数据库中，但如果该单词包含一个 ' 则它不会被插入。
I can insert the special characters fine when using PHPmyAdmin, but it just doesn't work when inserting them via PHP. Could it be that PHP is changing the special characters into something else? If so, is there a way to make them insert properly?
我可以在使用 PHPmyAdmin 时很好地插入特殊字符，但是通过 PHP 插入它们时它不起作用。难道是 PHP 正在将特殊字符更改为其他字符？如果是这样，有没有办法让它们正确插入？
回答by Anthony
$insert_data = mysql_real_escape_string($input_data);
Assuming that you have the data stored as $input_data
假设您将数据存储为 $input_data
回答by zaf
Are you escaping? Try the mysql_real_escape_string() function and it will handle the special characters.
你在逃吗？试试 mysql_real_escape_string() 函数，它会处理特殊字符。
回答by Paul Lammertsma
You are most likely escaping the SQL string, similar to:
您很可能会转义 SQL 字符串，类似于：
SELECT * FROM `table` WHERE `column` = 'Here's a syntax error!'
You need to escape quotes, like follows:
您需要转义引号，如下所示：
SELECT * FROM `table` WHERE `column` = 'Here\'s a syntax error!'
mysql_real_escape_string()handles this for you.
mysql_real_escape_string()为你处理这个。
回答by Peter Parker
use mysql_real_escape_string
使用mysql_real_escape_string
So what does mysql_real_escape_stringdo?
那么mysql_real_escape_string 有什么作用呢？
This PHP library function prepends backslashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. The important part is that the single and double quotes are escaped, because these are the characters most likely to open up vulnerabilities.
此 PHP 库函数在以下字符前添加反斜杠：\n、\r、\、\x00、\x1a、' 和“。重要的部分是单引号和双引号被转义，因为这些是最有可能打开漏洞的字符。
Please inform yourself about sql_injection. You can use this linkas a start
请自行了解 sql_injection。您可以使用此链接作为开始
回答by Krzysztof Sikorski
You are propably pasting them directly into a query. Istead you should "escape" them, using appriopriate function - mysql_real_escape_string, mysqli_real_escape_stringor PDO::quotedepending on extension you are using.
您可以将它们直接粘贴到查询中。相反，您应该“转义”它们，使用适当的函数 - mysql_real_escape_string、mysqli_real_escape_string或PDO::quote ，具体取决于您使用的扩展名。
回答by symcbean
Note that as others have pointed out mysql_real_escape_string() will solve the problem (as will addslashes), however you should always use mysql_real_escape_string()for security reasons - consider:
请注意，正如其他人指出的 mysql_real_escape_string() 将解决该问题（就像addslashes 一样），但是出于安全原因，您应该始终使用 mysql_real_escape_string()- 考虑：
SELECT * FROM valid_users WHERE username='$user' AND password='$password'
What if the browser sends
如果浏览器发送
user="admin' OR (user=''"
password="') AND ''='"
The query becomes:
查询变为：
SELECT * FROM valid_users
WHERE username='admin' OR (user='' AND password='') AND ''=''
i.e. the security checks are completely bypassed.
即安全检查被完全绕过。
C.
C。
回答by OM The Eternity
Probably "mysql_real_escape_string()" will work for u
可能“ mysql_real_escape_string()”会为你工作
回答by amarjit singh
$var = mysql_real_escape_string("data & the base");
$result = mysql_query('SELECT * FROM php_bugs WHERE php_bugs_category like "%' .$var.'%"');
回答by Soyab Badi
Use this: htmlentities($_POST['field']);
用这个： htmlentities($_POST['field']);
Enough only. This for special character:
够了而已。这是特殊字符：
Use for CI htmlentities($this->input->post('control_name'));
用来 CI htmlentities($this->input->post('control_name'));
回答by N?s????? ?
For Example:
$parent_category_name = Men's Clothing
Consider here the SQL query
例如：
$parent_category_name = Men's Clothing
这里考虑 SQL 查询
$sql_category_name = "SELECT c.*, cd.name, cd.category_id as iid FROM ". DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = '" . $this->db->escape($parent_category_name) . "' AND c.parent_id =0 ";
Error:
错误：
Static analysis:
1 errors were found during analysis.
Ending quote ' was expected. (near "" at position 198)
SQL query: Documentation
SELECT c.*, cd.name, cd.category_id as iid FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = 'Men's Clothing' AND c.parent_id =0 LIMIT 0, 25
MySQL said: Documentation
#1064 - 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 Clothing' AND c.parent_id =0 LIMIT 0, 25' at line 1
Try to implement:
尝试实现：
$this->db->escape($parent_category_name)
The above method works on OpenCart framework. Find your framework & implement OR
mysql_real_escape_string()in Core PHP
上述方法适用于 OpenCart 框架。找到你的框架并mysql_real_escape_string()在 Core PHP 中实现 OR
This will become Men\'s Clothing i.e, In my case
这将成为男装，即，就我而言
$sql_category_name = "SELECT c.*, cd.name, cd.category_id as iid FROM ". DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = '" . $this->db->escape($parent_category_name) . "' AND c.parent_id =0 ";
So you'll get the clear picture of the query by implementing escape() as
因此，您将通过将 escape() 实现为
SELECT c.*, cd.name, cd.category_id as iid FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = 'Men\'s Clothing' AND c.parent_id =0
