Mysql：常规错误：1366字符串值不正确

Today I got an error while I was developing an app based on PHP, MySql and the Zend Framework. Moreover, I'm using phpseclib to encrypt the data using the AES algorithm and here came the problem. The output of the AES algorithm is in a form that seems MySql doesn't like. Infact when I try to insert the data into the database a got an Sql Exception. The error is:

今天我在开发基于PHP，MySql和Zend Framework的应用程序时遇到了错误。此外，我正在使用phpseclib使用AES算法加密数据，这就出现了问题。 AES算法的输出形式似乎是MySql不喜欢的形式。事实上，当我尝试将数据插入数据库时​​，得到了一个Sql异常。错误是：

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE4\xD5\xABtZM...' for column 'Name'

I've already read all the answers posted on * and have also Googled the problem but all the proposed solution were already in my code. Database, tables and all the cols have Collation utf8_general_ci. Below you can see the relevant code:

我已经阅读了*上发布的所有答案，并且还搜索了问题，但所有提议的解决方案都已经在我的代码中了。数据库，表和所有列都有Collat​​ion utf8_general_ci。您可以在下面看到相关代码：

1. Application.ini to see how is set up the connection
2. Application.ini看看如何设置连接
3. Database.php to see how I retrieve the database connection
4. Database.php看看我如何检索数据库连接
5. Model.php to see how I try to insert the data in the database
6. Model.php看看我是如何尝试在数据库中插入数据的
7. encrypt() to see how I use the AES class to encrypt the data
8. encrypt（）看看我如何使用AES类加密数据
9. Table definition (If know that all are in utf8 isn't enough)
10. 表定义（如果知道所有都在utf8中是不够的）

application.ini

的application.ini

resources.db.adapter = "Pdo_Mysql"
resources.db.params.charset = "utf8"
resources.db.params.host = "localhost"
resources.db.params.username = "********"
resources.db.params.password = "********"
resources.db.params.dbname = "dbname"

database.php

为database.php

public static function getDb()
{
   if (self::$Db === NULL)
      self::$Db = Zend_Db_Table::getDefaultAdapter();
   return self::$Db;
}

model.php

model.php

$Values = array(
   'Id' => $this->Id,
   'Name' => $this->Name,
   'CreationDate' => $this->CreationDate,
);
$RowChanged = $Db->insert('TABLENAME', $Values);

encrypt()

加密（）

public static function encrypt($Data, $EncryptionKey)
{
   $AES = new Crypt_AES();
   $AES->setKey($EncryptionKey);
   return $AES->encrypt($Data);
}

table

表

CREATE TABLE IF NOT EXISTS `table` (
  `Id` mediumint(8) unsigned NOT NULL,
  `Name` varchar(200) DEFAULT NULL,
  `CreationDate` date NOT NULL,
  PRIMARY KEY (`Id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Question: How can I solve the problem and store the data into the database?

问题：如何解决问题并将数据存储到数据库中？

1 个解决方案

#1

I realize that this is a reference for AES_ENCRYPT for MySQL, however it looks like you may need to change your varchar(200) to a varbinary(200) (or larger) as AES seems to return a binary string.

我意识到这是针对MySQL的AES_ENCRYPT的参考，但是看起来您可能需要将varchar（200）更改为varbinary（200）（或更大），因为AES似乎返回二进制字符串。

There is a less clear explanation on the MySQL site.

在MySQL网站上有一个不太清楚的解释。

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

许多加密和压缩函数返回结果可能包含任意字节值的字符串。如果要存储这些结果，请使用具有VARBINARY或BLOB二进制字符串数据类型的列。这将避免可能会更改数据值的尾随空格删除或字符集转换的潜在问题，例如，如果使用非二进制字符串数据类型（CHAR，VARCHAR，TEXT），则可能会出现此问题。