Let's admit this situation: i have a project that involves critical data. Server side is managed by Laravel/Symfony to retrieve, process and store these data.
Data are sent to the server through the API, they are encrypted and finally stored in the database.
My question is: if data are encrypted in my database, can i still retrieve these using a WHERE clause ? I'm thinking about something like On The Fly decryption, but i've found nothing about these terms on Google. What is the best way to encrypt data in a database to improve data protection ?
The trick is to index the encrypted values, but this does limit what you can search for. You can improve things a bit by normalising the data beforehand, for example by forcing it to lower case before encryption to make matches more likely.
However, this is all academic because rather than reinventing the (potentially complicated and difficult as it may be) wheel, the best way to do this is to use a library that does it for you, and the library you need is CipherSweet by Scott Arciszewski.