I've been researching for weeks and looking for a solution, but I can't find a solution to my specific problem.
I premise: I use NodeJS as a back-end server and I manage the data through Prisma (https://www.prisma.io)
My client requested data encryption for some sensitive data in the database. I tried several solutions, such as (the most important):
- Encrypt / decrypt data using middleware;
- Use the MySQL Functions.
Step 1: Data encryption and decryption works. I used an AES encryption algorithm, but I have a problem when I have to query the database with LIKE. For example, if I have to search through the title of a product, while the user types, it is impossible for me.
Point 2: MySQL Functions are great! But unfortunately I can't recall them via Prisma. The only way we can write QUERY via Prism is with the "$ queryRaw" or "$ executeRaw" function. If I write a classic QUERY inside in which I call a MySQL Function I get errors!
I'll write you an example:
"const products = await prism. $ queryRaw`SELECT getAes (title, 'AU34chje933q') as TITLE FROM oncoga_dh2.test where getAes (title, 'AU34chje933q') LIKE 'Son%'` "
Is it possible that Prisma does not provide a method for encrypting and decrypting data in the database?