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).

我正在从表单中将一些数据插入到数据库中。我正在使用addslashes来转义文本(也尝试了mysql_real_escape_string,结果相同)。

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.

荷马的血液成为Moe新啤酒的秘密成分。

is converted to:

转换为:

Homer\'s blood becomes the secret ingredient in Moe’s new beer.

荷马的血液成为Moe新啤酒的秘密成分。

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.

所以PHP认为卷曲的引用很好,但MySQL正在丢失字符串。 MySQL虽然没有给出任何错误。

2 个解决方案

#1

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 with DEFAULT CHARSET=utf8.

我会寻找Web界面中使用的字符编码与数据库级别使用的字符编码之间的不匹配。例如,如果您的Web界面使用UTF-8,并且您的数据库使用latin1的默认MySQL编码,那么您需要使用DEFAULT CHARSET = utf8设置表。

Use mysql_real_escape_string() or mysqli, by the way. addslashes() is NOT adequate protection against SQL injection.

顺便说一下,使用mysql_real_escape_string()或mysqli。 addslashes()不足以防止SQL注入。

#2

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.

'Moe's是你的示例字符串中唯一的字符,如果该字符串是latin1编码但你的mysql服务器需要utf8,那么该字符无效。

Simple demonstration:

<?php
function foo($s) {
    echo 'len=', strlen($s), ' ';
  for($i=0; $i<strlen($s); $i++) {
    printf('%02X ', ord($s[$i]));
  }
  echo "\n";
}

 // my file is latin1 encoded and so is the string literal
foo('Moe’s');
// now try it with an utf8 encoded string
foo( utf8_encode('Moe’s') );

prints

len= 5 4D 6F 65 92 73
len= 6 4D 6F 65 C2 92 73

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

因此问题是:你是否以“错误”编码的方式提供mysql服务器?每个连接都有一个连接字符集,mysql服务器希望你的客户端(php脚本)发送在该字符集中编码的数据。您可以找到连接字符集的内容

SHOW VARIABLES LIKE '%character%'

like in

$mysql = mysql_connect('..', '..', '..') or die(mysql_error());
mysql_select_db('..', $mysql) or die(mysql_error());

$query = "SHOW VARIABLES like '%character%'";
$result = mysql_query($query, $mysql) or die(__LINE__.mysql_error());
while( false!==($row=mysql_fetch_array($result, MYSQL_ASSOC)) ) {
  echo join(', ', $row), "\n";
}

This should print something like

这应该打印出类似的东西

character_set_client, utf8
character_set_connection, utf8
character_set_database, latin1
character_set_filesystem, binary
character_set_results, utf8
character_set_server, utf8
character_set_system, utf8

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?

和character_set_connection,utf8表示“我的”连接字符集是utf8,即mysql服务器期望来自客户端(php)的utf8编码字符。什么是“你的”连接charset?

Then take a look at the actual encoding of your parameter string, i.e. if you had

然后看一下参数字符串的实际编码,即如果有的话

$foo = mysql_real_escape_string($_POST['foo'], $mysql);

replace that by

用它代替

echo '<div>Debug hex($_POST[foo])=';
for($i=0; $i<strlen($s); $i++) {
    printf('%02X ', ord($_POST['foo'][$i]));
}
echo "</div>\n";
$foo = mysql_real_escape_string($_POST['foo'], $mysql);

and check what the actual encoding of your input string is. Does it print 92 or C2 92?

并检查输入字符串的实际编码是什么。它是打印92还是C2 92?

#1