MySQL 因弯引号(智能)而窒息
我正在从表单将一些数据插入数据库。我使用 addslashes
来转义文本(也尝试过 mysql_real_escape_string
得到相同的结果)。
常规引号会被转义,但其他一些引号不会。例如,字符串:
荷马的血液成为莫伊新啤酒的秘密成分。
转换为:
荷马的血液成为莫伊新啤酒的秘密成分。
我认为大引号不转义并不重要,但只有这段文本被插入到数据库中:
荷马的血液成为莫伊的秘密成分
因此 PHP 认为弯引号没问题,但 MySQL 正在丢失字符串。 MySQL 并没有给出任何错误。
I'm inserting some data into a database from a form. I'm using addslashes
to escape the text (have also tried mysql_real_escape_string
with the same result).
Regular quotes are escaped, but some other quotes are not. For example, the string:
Homer's blood becomes the secret ingredient in Moe’s new beer.
is converted to:
Homer\'s blood becomes the secret ingredient in Moe’s new beer.
I didn't think the curly quote would matter unescaped, but only this text is inserted into the database:
Homer's blood becomes the secret ingredient in Moe
So PHP thinks the curly quote is fine, but MySQL is losing the string. MySQL is not giving any errors though.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我会查找 Web 界面中使用的字符编码与数据库级别使用的字符编码之间的不匹配情况。例如,如果您的 Web 界面使用 UTF-8,并且您的数据库使用默认的 MySQL 编码
latin1
,那么您需要使用DEFAULT CHARSET=utf8
设置表>。顺便说一下,使用 mysql_real_escape_string() 或 mysqli。
addslashes()
对于 SQL 注入没有提供足够的保护。I would look for a mismatch between the character encoding used in your Web interface and that used at the database level. If your Web interface uses UTF-8, for example, and your database is using the default MySQL encoding of
latin1
, then you need to set up your tables withDEFAULT CHARSET=utf8
.Use
mysql_real_escape_string()
or mysqli, by the way.addslashes()
is NOT adequate protection against SQL injection.Moe 中的 ' 是示例字符串中唯一无效的字符,如果该字符串是 latin1 编码但您的 mysql 服务器需要 utf8。
简单演示:
打印
因此问题是:您是否以“错误”编码向 mysql 服务器提供某些内容?
每个连接都有一个连接字符集,并且 mysql 服务器期望您的客户端(php 脚本)发送以该字符集编码的数据。 中找到连接字符集是什么
您可以在
This should print things like
,
character_set_connection, utf8
表示“我的”连接字符集是 utf8,即 mysql 服务器期望来自客户端的 utf8 编码字符(php)。 “你的”连接字符集是什么?然后查看参数字符串的实际编码,即是否将
其替换为
并检查输入字符串的实际编码是什么。它打印的是 92 还是 C2 92?
The ’ in Moe’s is the only character in your example string that wouldn't be valid if that string is latin1 encoded but your mysql server expects utf8.
Simple demonstration:
prints
Therefore the question is: Do you feed the mysql server something in a "wrong" encoding?
Each connection has a connection charset and the mysql server expects your client (php script) to send data that is encoded in that character set. You can find out what the connection charset is with
like in
This should print something like
and
character_set_connection, utf8
indicates that "my" connection character set is utf8, i.e. the mysql server expects utf8 encoded characters from the client (php). What's "your" connection charset?Then take a look at the actual encoding of your parameter string, i.e. if you had
replace that by
and check what the actual encoding of your input string is. Does it print 92 or C2 92?