Doctrine Encrypted Query Laravel Package

Technical Evaluation

Architecture Fit

• Use Case Alignment: Fits systems requiring field-level encryption (e.g., GDPR compliance, PII protection) while enabling searchable encrypted data via cryptographic SQL functions (e.g., AES_ENCRYPT, AES_DECRYPT, custom hash-based queries).
• ORM Integration: Designed for Doctrine ORM, making it ideal for Laravel applications using Doctrine as a primary or secondary ORM (e.g., legacy systems, hybrid stacks).
• Query Abstraction: Extends Doctrine’s QueryBuilder to support encrypted predicates (e.g., WHERE encrypted_column LIKE '%value%'), reducing custom SQL injection risks.
• Limitation: Not a standalone solution—requires aeliot/doctrine-encrypted-bundle for full functionality (e.g., entity field encryption). Standalone may lack critical features like key management or schema migrations.

Integration Feasibility

• Laravel Compatibility:
  • Doctrine ORM: Works with Laravel via packages like doctrine/orm or illuminate/database (if using Doctrine as a secondary ORM).
  • Eloquent vs. Doctrine: Low feasibility for pure Eloquent apps without Doctrine. Requires hybrid setup or migration to Doctrine.
  • Database Support: Relies on native SQL encryption functions (e.g., MySQL’s AES_ENCRYPT, PostgreSQL’s pgp_sym_encrypt). Compatibility must be validated per DB.
• Dependency Risks:
  • Tight coupling with doctrine-encrypted-bundle may introduce versioning conflicts or unmaintained dependencies (0 stars, recent but unproven release).
  • No Laravel-specific documentation; assumes Doctrine expertise.

Technical Risk

Key Questions

1. Why Doctrine?
   • Is Doctrine already in use, or is this a new requirement? If the latter, justify the migration effort vs. alternatives (e.g., Laravel’s native encryption + application-layer filtering).
2. Database Support
   • Which databases are in scope? Are native encryption functions available?
3. Encryption Strategy
   • Will this replace existing encryption (e.g., Laravel’s encrypt()) or supplement it? Define scope (e.g., "only for searchable PII").
4. Key Management
   • How will encryption keys be stored/rotated? Will this integrate with existing secrets management?
5. Query Patterns
   • What percentage of queries will use encrypted fields? Are there performance bottlenecks in the current stack?
6. Compliance
   • Does this meet regulatory requirements (e.g., "data at rest" encryption)? Are there gaps (e.g., no column-level access control)?

Integration Approach

Stack Fit

• Primary Fit:
  • Laravel apps using Doctrine ORM (e.g., hybrid Eloquent/Doctrine, legacy systems).
  • Systems requiring searchable encrypted data (e.g., customer search on encrypted names).
• Secondary Fit:
  • Apps using Doctrine DBAL (without full ORM) for direct SQL encryption.
• Non-Fit:
  • Pure Eloquent apps (without Doctrine).
  • Apps relying on application-layer encryption (e.g., Laravel’s encrypt()) for all sensitive data.

Migration Path

1. Assessment Phase:
   • Audit current encryption strategy (e.g., where encrypt() is used).
   • Identify fields requiring searchable encryption (e.g., email, phone).
2. Pilot Integration:
   • Set up doctrine-encrypted-bundle in a staging environment.
   • Migrate a single entity (e.g., User) with 1–2 encrypted fields.
   • Test with:
     • Basic WHERE queries on encrypted fields.
     • Complex queries (e.g., JOIN, ORDER BY).
3. Full Rollout:
   • Gradually encrypt additional fields/entities.
   • Replace custom SQL encryption logic with Doctrine’s QueryBuilder methods.
4. Deprecation:
   • Phase out legacy encryption methods (e.g., raw AES_ENCRYPT in queries).

Compatibility

• Doctrine Version: Test with the latest LTS version of Doctrine ORM (e.g., ^2.13).
• Database Drivers:
  • MySQL: Supports AES_ENCRYPT/AES_DECRYPT (8.0+).
  • PostgreSQL: Supports pgp_sym_encrypt/pgp_sym_decrypt.
  • SQLite: Not supported (no native encryption functions).
• Laravel Services:
  • Ensure compatibility with Laravel’s connection resolvers (e.g., doctrine/orm config).
  • May require custom Doctrine event listeners for pre/post-query hooks.

Sequencing

1. Prerequisites:
   • Install doctrine/orm and aeliot/doctrine-encrypted-bundle.
   • Configure database connection with encryption function support.
2. Core Setup:
   • Define encrypted fields in entities using bundle annotations/lifecycle callbacks.
   • Example:

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

3. Query Layer:
   • Replace raw SQL with QueryBuilder:

     $qb->andWhere('AES_DECRYPT(encrypted_column, :key) LIKE :pattern')
       ->setParameter('key', $encryptionKey)
       ->setParameter('pattern', '%search%');
     

4. Key Management:
   • Integrate with a secrets manager (e.g., Laravel’s config/services.php for dev, Vault for prod).
5. Testing:
   • Unit tests for encrypted queries.
   • Integration tests with real DB encryption functions.

Operational Impact

Maintenance

• Pros:
  • Centralized encryption logic reduces ad-hoc SQL encryption risks.
  • Bundle updates may include bug fixes (e.g., query optimization).
• Cons:
  • Dependency on external bundle: Risk of abandonment (0 stars, unproven).
  • Doctrine-specific maintenance: Requires ORM expertise for troubleshooting.
• Mitigation:
  • Fork the bundle if critical features are missing.
  • Document customizations for future upgrades.

Support

• Debugging Challenges:
  • Encrypted queries may obscure errors (e.g., "column not found" → AES_DECRYPT(encrypted_column, ...)).
  • Workaround: Log decrypted query plans in dev environments.
• Performance Debugging:
  • Use Doctrine’s query logging to identify slow encrypted predicates.
  • Tooling: Integrate with Laravel Telescope or Blackfire for profiling.
• Key Rotation:
  • Implement a rekeying strategy for encrypted data (e.g., background job to re-encrypt fields with new keys).

Scaling

• Database Load:
  • Encryption functions may increase CPU usage on the DB server. Monitor with:
    • SHOW PROCESSLIST (MySQL) for long-running encrypted queries.
    • pg_stat_activity (PostgreSQL) for decryption bottlenecks.
  • Mitigation: Offload encryption to the application layer for non-searchable fields.
• Query Complexity:
  • Avoid deep JOIN operations on encrypted fields (may prevent query optimization).
  • Alternative: Denormalize encrypted data for read-heavy workloads.
• Horizontal Scaling:
  • Stateless encryption keys must be synchronized across instances (e.g., Redis cache).

Failure Modes