How can I encrypt and decrypt data with Prisma?

Question

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):

1. Encrypt / decrypt data using middleware;
2. 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 prisma.$queryRaw`SELECT getAes(title, 'AU34chje933q') as TITLE FROM title_db.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?

Answer 1

I have some news!

After a long time I managed to find a solution. With the help of $queryRaw it is possible to call MySQL functions. The solution is simpler than expected and I don't know what I was wrong previously! The first step to be faced is to create (if there are none) two functions in MySQL, one of setAes and one of getAes (unfortunately I cannot upload a photo to show you).

From the code in NodeJS it will be sufficient to call these two functions with the following syntax, and that's it. I show you a portion of the code:

const patient = await prisma.$queryRaw`
    INSERT INTO patients(
        name,
        surname,
        sex
    )
    VALUES(
        setAes(${name}, ${process.env.AES_KEY}),
        setAes(${surname}, ${process.env.AES_KEY}),
        setAes(${sex}, ${process.env.AES_KEY})
    )
`

The AES encryption system requires a key to do its job. The wonder of $queryRaw is that it allows you to pass parameters to MySQL functions from your code! In fact, I pass him to the function:

1. The content of the variables;
2. The encryption key saved in my .env file.

Finally, I also show you an example with the use of the getAes function:

const patients = await prisma.$queryRaw`
        SELECT  getAes(name, ${process.env.AES_KEY}) as name
        FROM    name_database.patients
        WHERE   getAes(name, ${process.env.AES_KEY})
        LIKE    'Mal%'
        COLLATE utf8mb4_0900_ai_ci
`

It is important to know that the database and the tables must be of the same type, I had made a mistake and therefore I had to also add the COLLATE utf8mb4_0900_ai_ci clause.