Mysql:一般错误:1366不正确的字符串值 [英] Mysql: General error: 1366 Incorrect string value

问题描述

今天，当我在开发基于PHP，MySql和Zend Framework的应用程序时遇到错误.此外，我正在使用 phpseclib 使用

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:

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

我已经阅读了Stackoverflow上发布的所有答案，并且已经搜索了问题，但是所有建议的解决方案已经在我的代码中.数据库，表和所有列均具有归类utf8_general_ci.在下面，您可以看到相关代码:

I've already read all the answers posted on Stackoverflow 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:

1. Application.ini，以了解如何建立连接
2. Database.php以查看如何检索数据库连接
3. Model.php看看我如何尝试在数据库中插入数据
4. encrypt()了解如何使用AES类加密数据
5. 表定义(如果仅知道utf8中的内容还不够)

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

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

model.php

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

加密()

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

表

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?

推荐答案

我意识到这是

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网站上的解释不太清楚. >

There is a less clear explanation on the MySQL site.

许多加密和压缩功能返回的字符串 结果可能包含任意字节值.如果要存储这些 结果，使用带有VARBINARY或BLOB二进制字符串数据的列 类型.这样可以避免在删除尾随空格时可能出现的问题 或可能会更改数据值的字符集转换，例如 如果使用非二进制字符串数据类型(CHAR，VARCHAR，TEXT)，则会发生这种情况.

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

这篇关于Mysql:一般错误:1366不正确的字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！