如何使用PHPDoctrine2ORM在MySQL变量列上进行选择

Question

我试图使用Doctrine2ORM(Version2.4.7)针对MySQL表中的二进制数据进行选择，并且遇到了困难。我的桌子布局如下：

CREATE TABLE `EmailAddresses` ( `EmailAddressId` int(11) NOT NULL AUTO_INCREMENT, `EmailAddress` varbinary(255) NOT NULL, PRIMARY KEY (`EmailAddressId`), UNIQUE KEY `EmailAddress_UNIQUE` (`EmailAddress`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我要存储在表中的信息是使用php版本的mysql aes_encrypt加密的。我宁愿以编程的方式在数据库之外这样做，因为据我所知，您不能让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]);
}

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

/**
 * @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)

不确定我能做些什么来使用Doctrine2ORM，也可以针对varbinary进行选择。有人能提供更多的洞察力吗？我在谷歌上搜索过，再也找不到更多的信息了。谢谢!

附加信息：

• 使用PHP 5.6
• Zend框架2.3
• 原则2 ORM v2.4.7
• 使用MySQL 5.5
• CentOS 7

Answer 1

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

为那些好奇的人拉出请求这里。