Doctrine Encrypted Query Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require aeliot/doctrine-encrypted-query
   

   Ensure doctrine/orm (≥2.10) and doctrine/dbal (≥3.6) are installed.

2. Register the Query Language Add to your config/packages/doctrine.yaml (or equivalent):

   doctrine:
       orm:
           query_language:
               - Aeliot\DoctrineEncryptedQuery\Query\AST\EncryptedQueryLanguage
   

3. First Use Case: Encrypted Field Filtering

   use Aeliot\DoctrineEncryptedQuery\Query\AST\Functions\Encrypt;
   
   $qb = $entityManager->createQueryBuilder();
   $qb->andWhere(
       $qb->expr()->eq(
           new Encrypt('u.username'), // Encrypted field
           $qb->expr()->literal('encrypted_username_value')
       )
   );
   

Implementation Patterns

Core Workflows

1. Field-Level Encryption in Queries Use Encrypt/Decrypt functions for DQL queries:

   $qb->andWhere(
       $qb->expr()->eq(
           new \Aeliot\DoctrineEncryptedQuery\Query\AST\Functions\Encrypt('u.encrypted_email'),
           $qb->expr()->literal('aes_encrypt_value')
       )
   );
   

2. Integration with Doctrine Encrypted Bundle Pair with aeliot/doctrine-encrypted-bundle for seamless field encryption:

   // In Entity
   use Aeliot\DoctrineEncryptedBundle\Annotation\Encrypted;
   /**
    * @Encrypted
    */
   private $ssn;
   

3. Custom Encryption Logic Extend EncryptedQueryLanguage to support custom algorithms:

   class CustomEncryptedQueryLanguage extends EncryptedQueryLanguage {
       protected function getEncryptionFunction(): string {
           return 'custom_encrypt_function';
       }
   }
   

4. Hybrid Queries Combine encrypted and plain queries:

   $qb->where('u.status = :status')
      ->andWhere(
          new Encrypt('u.encrypted_data'),
          $qb->expr()->literal('encrypted_value')
      )
      ->setParameter('status', 'active');
   

Gotchas and Tips

Pitfalls

1. SQL Function Compatibility

   • Ensure your DBMS (PostgreSQL/MySQL) supports AES_ENCRYPT/AES_DECRYPT.
   • Test with raw SQL first:

     SELECT * FROM users WHERE AES_DECRYPT(encrypted_email, 'key') = 'user@example.com';
     

2. Key Management

   • Hardcoded keys in queries are insecure. Use environment variables or a key management system.
   • Example:

     $qb->expr()->literal('AES_ENCRYPT(?, ?)', ['plaintext', $_ENV['ENCRYPTION_KEY']]);
     

3. Performance Overhead

   • Encrypted queries add latency. Index encrypted fields if filtering frequently:

     CREATE INDEX idx_encrypted_email ON users (AES_DECRYPT(encrypted_email, 'key'));
     

4. DQL vs. Native SQL

   • Avoid mixing Encrypt functions with raw SQL in the same query (may cause parsing conflicts).

Debugging Tips

• Enable SQL Logging

  $entityManager->getConnection()->getConfiguration()->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());
  

  Verify generated SQL uses AES_DECRYPT/AES_ENCRYPT.

• Check Query Language Registration If queries fail silently, confirm EncryptedQueryLanguage is registered in Doctrine’s query_language list.

Extension Points

1. Custom Encryption Functions Override getEncryptionFunction() and getDecryptionFunction() in a subclass of EncryptedQueryLanguage.

2. Parameter Binding For dynamic keys, extend EncryptedExpression to support parameterized encryption:

   class ParameterizedEncrypt extends EncryptedExpression {
       public function __construct(string $field, string $keyParameter) {
           // Implement dynamic key binding
       }
   }
   

3. Query Builder Shortcuts Add a trait to QueryBuilder for fluent syntax:

   trait EncryptedQueryBuilder {
       public function whereEncrypted(string $field, $value, string $key): self {
           return $this->andWhere(
               new Encrypt($field),
               $this->expr()->literal($value)
           );
       }
   }