php - 如何使用 PHP Doctrine 2 ORM 在 MySQL varbinary 列上进行选择

Question

我正在尝试使用 Doctrine 2 ORM(版本 2.4.7)针对 MySQL 表中的二进制数据进行选择，但遇到了困难。我的表格布局如下:


创建表`EmailAddresses`(
`EmailAddressId` int(11) NOT NULL AUTO_INCREMENT,
`EmailAddress` varbinary(255) NOT NULL，
主键(`EmailAddressId`)，
唯一键 `EmailAddress_UNIQUE` (`EmailAddress`)
) 引擎=InnoDB 默认字符集=utf8；


我存储在表内的信息是使用 mysql aes_encrypt 的 php 版本加密的。我宁愿在数据库之外以编程方式执行此操作，因为据我所知，您无法让 ORM 为您执行此操作。我要加密的代码是(这只是一个片段):

const MYSQL_ENCRYPTION_CIFER = MCRYPT_RIJNDAEL_128;
const MYSQL_ENCRYPTION_MODE = MCRYPT_MODE_ECB;

/**
 * @param string $valueToEncrypt The value to encrypt. This cannot be empty!
 * @param string $encryptionKey The encryption key. This cannot be empty!
 * @return string
 * @throws EncryptionException
 */
public function mysqlAesEncrypt($valueToEncrypt, $encryptionKey){
    if(IsEmpty::any($valueToEncrypt, $encryptionKey)){
        throw new EncryptionException("Error before encrypting: Encryption value or key cannot be empty!");
    }
    /** @var \Utility\Mcrypt $mcrypt */
    $mcrypt           = $this->getServiceLocator()->get('McryptUtility');
    $paddedValue      = $this->mysqlPad($valueToEncrypt);
    $encryptedValue   = $mcrypt->encrypt(self::MYSQL_ENCRYPTION_CIFER, $encryptionKey, $paddedValue, self::MYSQL_ENCRYPTION_MODE);

    return $encryptedValue;
}

/**
 * Pads a value using PKCS7 padding. This is the padding MySQL uses.
 *
 * @param string $valueToPad
 * @param int $blockSize
 * @return string
 */
private function mysqlPad($valueToPad, $blockSize = 16){
    $length  = $blockSize - (strlen($valueToPad) % $blockSize);
    $padding = str_repeat(chr($length), $length);

    return $valueToPad . $padding;
}

我的问题不在于存储 - 将值存储到数据库时一切正常。我已经通过以编程方式和使用 MySQL aes_decrypt 解密来对此进行了测试。但是，当我尝试选择时，我的服务器崩溃了，实际上遇到了段错误。我猜测二进制数据以某种方式执行此操作。这是我尝试检索电子邮件地址实体的代码，但它出错了。 $email_addresses是一个邮箱地址数组，在传入之前使用上面的加密方式加密过。

/**
 * @param string[] $email_addresses
 * @return \Entity\EmailAddresses[]
 */
public function retrieveEmailAddressEntities(array $email_addresses){
    return $this->repository->findBy(['EmailAddress' => $email_addresses]);
}

EmailAddress 列也在 Doctrine 2 实体中设置为“二进制”:

/**
 * @var string
 *
 * @ORM\Column(name="EmailAddress", type="binary", length=255, nullable=false)
 */
private $EmailAddress;

这是我从错误日志中收到的错误:

[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP Notice:  Array to string conversion in /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 91
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP Stack trace:
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   1. {main}() /var/www/html/public/index.php:0
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   2. Zend\\Mvc\\Application->run() /var/www/html/public/index.php:22
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   3. Zend\\EventManager\\EventManager->trigger() /var/www/html/vendor/zendframework/zendframework/library/Zend/Mvc/Application.php:313
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   4. Zend\\EventManager\\EventManager->triggerListeners() /var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:207
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   5. call_user_func:{/var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:468}() /var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:468
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   6. Zend\\Mvc\\DispatchListener->onDispatch() /var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:468
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   7. Zend\\Mvc\\Controller\\AbstractRestfulController->dispatch() /var/www/html/vendor/zendframework/zendframework/library/Zend/Mvc/DispatchListener.php:113
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   8. Zend\\Mvc\\Controller\\AbstractController->dispatch() /var/www/html/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractRestfulController.php:300
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP   9. Zend\\EventManager\\EventManager->trigger() /var/www/html/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractController.php:116
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  10. Zend\\EventManager\\EventManager->triggerListeners() /var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:207
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  11. call_user_func:{/var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:468}() /var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:468
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  12. Project\\Controller\\ProjectController->onDispatch() /var/www/html/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php:468
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  13. Zend\\Mvc\\Controller\\AbstractRestfulController->onDispatch() /var/www/html/module/Project/src/Project/Controller/ProjectController.php:38
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  14. Zend\\Mvc\\Controller\\AbstractRestfulController->processPostData() /var/www/html/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractRestfulController.php:414
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  15. Project\\Controller\\Email->create() /var/www/html/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractRestfulController.php:456
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  16. Project\\Mailer\\Mailer->send() /var/www/html/module/Project/src/Project/Controller/Email.php:48
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  17. Project\\Service\\EmailLogger->logNewEmailSent() /var/www/html/module/Project/src/Project/Mailer/Mailer.php:35
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  18. Project\\Service\\EmailLogger->convertToEmailAddressEntities() /var/www/html/module/Project/src/Project/Service/EmailLogger.php:45
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  19. Project\\Dao\\EmailAddresses->retrieveEmailAddressEntities() /var/www/html/module/Project/src/Project/Service/EmailLogger.php:108
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  20. Doctrine\\ORM\\EntityRepository->findBy() /var/www/html/module/Project/src/Project/Dao/EmailAddresses.php:14
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  21. Doctrine\\ORM\\Persisters\\BasicEntityPersister->loadAll() /var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php:181
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  22. Doctrine\\DBAL\\Connection->executeQuery() /var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php:930
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  23. Doctrine\\DBAL\\Driver\\PDOStatement->execute() /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:828
[Tue Jan 27 15:11:05 2015] [error] [client 192.168.49.1] PHP  24. PDOStatement->execute() /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91
[Tue Jan 27 15:11:06 2015] [notice] child pid 3478 exit signal Segmentation fault (11)

不确定我可以做什么来使用 Doctrine 2 ORM 以及针对 varbinary 数据进行选择。有人可以提供更多见解吗？我用谷歌搜索，找不到更多信息。谢谢!

附加信息:

• 使用 PHP 5.6
• Zend 框架 2.3
• Doctrine 2 ORM v2.4.7
• 使用 MySQL 5.5
• 中央操作系统 7

Answer 1

我发现错误来自 Doctrine DBAL。在搜索二进制字符串数组时，它导致了错误。我已经为此提交了一个拉取请求，目前正在与 Doctrine 开发人员合作以解决它。谢谢!

拉取请求 here对于那些好奇的人。