I am facing a challenge in implementing field-level encryption for the "rating" column in my MySQL InnoDB database. The codebase is distributed across various repositories, and I am unable to make changes at the application code level. Therefore, I am exploring options to achieve this encryption at the database level.
Given these constraints, I am seeking advice on alternative approaches or strategies to implement field-level encryption for the "rating" column in MySQL InnoDB without modifying the application code as there are more than 70 rating columns. Are there any database-level solutions or configurations that could help achieve this goal seamlessly, ensuring that the rating data remains encrypted at rest?
Thank you in advance for any insights or recommendations.
Here's what I've tried so far:
Triggers: I attempted to use triggers to handle encryption and decryption during insert and update operations. However, triggers do not seem to work seamlessly with select statements, making this approach unsuitable for my use case.
AST (Abstract Syntax Tree) for Query Modification: I experimented with AST to convert MySQL queries and perform encryption/decryption. I am using aes_encrypt() and aes_decrypt() for the same. While this approach worked for some scenarios, it is not viable for my PHP application, as it heavily relies on raw queries. Modifying these raw queries would be a substantial undertaking.