Hey everyone! I need help with encrypting specific columns in my MySQL database. I have about 70 score columns that need encryption but I can’t touch the application code since it’s spread across multiple codebases.
I’m looking for ways to handle this purely at the database level. The main goal is to keep the score data encrypted when stored while making sure everything works without breaking existing functionality.
Has anyone dealt with something similar? I tried a couple things already:
Database triggers - These worked for inserts and updates but caused issues with select queries. Not really practical for what I need.
Query parsing approach - I looked into modifying queries automatically using AES functions like aes_encrypt() and aes_decrypt(). This worked sometimes but our PHP app uses too many raw SQL queries and changing all of them would be a huge project.
Are there any MySQL settings or database-level features that could help me encrypt these columns transparently? I need something that won’t require changes to how the application talks to the database.
tde could help, but it encrypts whole tables, not just cols. If u need col-level encryption w/o app changes, it’s tough - mySQL won’t do that easily. u might want to check out using a proxy like ProxySQL to handle query changes for u.
MySQL 8.0 Enterprise does have transparent data encryption, but getting column-level encryption without touching your code is still a pain. I dealt with this on a financial project - we ended up using database views with stored procedures, which worked if you set them up right. You create updatable views that handle encryption/decryption behind the scenes while your app sees the same interface. But heads up - this breaks with complex joins and some ORMs don’t play nice with it. You could also try MySQL’s keyring plugins with file-per-table encryption, though that’s table-level, not column-level. With 70 score columns, maybe look into MySQL’s plugin architecture if you’ve got dev resources. Performance hit depends on your queries and data size.
Been there with a similar nightmare. Had 100+ sensitive columns across different microservices and zero budget for app rewrites.
What actually works: build an automation layer between your apps and MySQL. Don’t fight with triggers or hack MySQL into doing something it wasn’t designed for. Just intercept queries before they hit the database.
I set this up so the automation layer detects queries hitting your score columns, applies AES encryption/decryption on the fly, and passes clean results back. Your PHP code keeps working exactly as before.
Make it smart enough to handle your raw SQL patterns. Map out the 70 columns once, then let automation handle all the query modification logic. Way cleaner than proxy solutions and performs better than views with encryption functions.
Took me about a week to get running smoothly. Now we encrypt sensitive data in multiple databases without touching a single line of application code.
Latenode makes this kind of database automation straightforward to build and maintain. You can set up query interception and encryption logic without writing complex middleware from scratch.
Had almost the exact same problem two years ago with a legacy system. Ended up using MySQL’s built-in encryption at rest plus a database proxy layer, but it was still pretty complex. Truth is, MySQL doesn’t have native transparent column encryption that just works without the app knowing about it. I tried views with encryption functions but performance tanked hard and our ORM couldn’t deal with it. Here’s what might work better: set up a separate encrypted schema and use MySQL’s federated storage engine to bridge your current setup with the encrypted storage. You can migrate gradually without touching app code at first. Also worth looking into database-level encryption keys with MySQL Enterprise, though you’ll need the right license. Overhead was fine for me but definitely test it hard with your workload first.