Getting ciphertext back from the database

[franky47]

This issue somehow got turned into a thread about ciphertext not being decrypted and returned as-is from the database.

Such issues usually indicate:

1. That the setup is correct (encryption works, which means fields are correctly interpreted, and the encryption key is supplied correctly)
2. That there is something wrong with the decryption process.

Decryption will throw an error when in strict mode (using /// @encrypted?mode=strict), but will log a warning to the console in other modes. This could be the sign that you are missing the right key to decrypt data.

If there are no errors, it usually means that the ciphertext has been corrupted somehow, and failed to be detected by the middleware (so is passed through as any other data). Make sure your field maximum length is high enough to contain the largest expected ciphertext, which is larger than the clear-text. Calculator available here.

---

Original issue content:

Adding a global strict decryption mode that throws errors when decryption fails (missing key) should help avoid building layers of encryption in migrations.

Use-case:

1. The decryption key is missing, encrypted data is returned in ciphertext (A)
2. The data migration re-encrypts ciphertext A with encryption key, producing ciphertext B
3. Decryption of re-encrypted records yield ciphertext A

[aortukgp]

Hi Franklyn, I can't decrypt the data. I use the .env file for setting the keys.

I entered the key to encrypt in PRISMA_FIELD_ENCRYPTION_KEY and the key to decrypt in PRISMA_FIELD_DECRYPTION_KEYS but the middleware always returns the encrypted data to me.

As you wrote, the decryption key must be the encryption key, so I copied and pasted the key from PRISMA_FIELD_ENCRYPTION_KEY to PRISMA_FIELD_DECRYPTION_KEYS but I always get the encrypted data.

I have done many tests, but I cannot decrypt the data, please help :)

[franky47]

The encryption key is automatically added to the list of decryption keys, so you should not need to add it yourself (this list is only for key rotation). Are you sure your environment variables are correctly loaded at the time when the middleware is initialised?

[aortukgp]

Thank you for your answer! I have removed the key PRISMA_FIELD_DECRYPTION_KEYS from the .env file. Yes I am sure that the environment file is loaded when the middleware is initialized, because if I comment out the key PRISMA_FIELD_ENCRYPTION_KEY I get the following error:

Error: [prisma-field-encryption] Error: no encryption key provided.

I can explain how the file is structured. It is a javascript file with several APIs.

At the beginning of the file I write the following code.

const {
    PrismaClient
} = require("@prisma/client")

const { 
    fieldEncryptionMiddleware
} = require('prisma-field-encryption')

const prisma = new PrismaClient()

prisma.$use(
    fieldEncryptionMiddleware()
)

Then follow 2 APIs

First

router.post('/create', [passport.authenticate('jwt', {session: false}), middleware.session.isValid], async (req, res) => {

    const {
        name,
        surname,
        CF
    } = req.body.patient

    patient = await prisma.patients.create({
        data: {
            name,
            surname,
            CF
        }
    })

    return res.status(200).json({
        msg: `Patient created`,
        patient
    })

})

Second

router.get('/read/detail/:CF', [passport.authenticate('jwt', {session: false}), middleware.session.isValid], async (req, res) => {

    const {
        CF
    } = req.params

    const patient = await prisma.patients.findUnique({
        where: {
            CF
        },
        include: {
            patients_pathologies: {
                include: {
                    therapy: true
                }
            }
        }
    })

    if(!patient) return res.status(404).json({
        msg: `Patient '${CF}' not found`
    })

    return res.status(200).json(patient)

})

Unfortunately in the second API I get the patient's name and surname encrypted :(

I also share the file .prisma for you

model patients {

  id Int @id @default(autoincrement())

  CF         String    @unique
  name       String    @db.VarChar(50) /// @encrypted
  surname    String    @db.VarChar(50) /// @encrypted
  email      String?   @db.VarChar(50)
  phone      String?   @db.VarChar(50)
  date_birth DateTime?

  patients_pathologies patients_pathologies[]

}

If I do the "create" the data is correctly encrypted, but when I do the "findUnique" the file is not decrypted!

[franky47]

Ok, I see nothing weird about your usage. By any chance, is the ciphertext returned by Prisma the same as the one stored in the database for a given cell? Just want to rule out double-encryption as mentioned in the original post.

[aortukgp]

Yes, i show you directly:

Client

---

Database

In the two input fields there are uppercase()

(Sorry if I'm asking for help with this issue)

[franky47]

The ciphertext is surprisingly short, do you know if the underlying clear-text data was short too (only a few characters long I'd wager) ? This rules out double-encryption anyway.

If you import your encryption key here, does it show the same fingerprint (8 hex characters next to "Use for encryption"), ie d9ca6d57 ? If not, it means the data has been encrypted using a different key.

[aortukgp]

Franky,

If I enter my encryption key in the Keychain I get this result d9ca6d57

The data I encrypted (name and surname) are both seven characters long!

[franky47]

Ok, let's see if we can get some errors showing: could you do the following change please?

model patients {
  ...
- name       String    @db.VarChar(50) /// @encrypted
+ name       String    @db.VarChar(50) /// @encrypted?strict
- surname    String    @db.VarChar(50) /// @encrypted
+ surname    String    @db.VarChar(50) /// @encrypted?strict
  ...
}

With strict mode enabled, decryption errors will throw rather than pass the ciphertext through. Maybe it will shed more light as to what went wrong, although it should already show errors in the console (admittedly using the string "encryption error", I just noticed a copy/pasta typo there, it should be "decryption error").

One last thing you could try if there are no errors, is to disable the early bailout for decryption, where we try to detect if there's anything to actually decrypt in the data coming from the database. You can do so by commenting out the return in node_modules/prisma-field-encryption/dist/encryption.js:80:

function decryptOnRead(params, result, keys, models, operation, decryptFn) {
    var _a;
    // Analyse the query to see if there's anything to decrypt.
    const model = models[params.model];
    if (Object.keys(model.fields).length === 0 && !((_a = params.args) === null || _a === void 0 ? void 0 : _a.include)) {
        // The queried model doesn't have any encrypted field,
        // and there are no included connections.
        // We can safely skip decryption for the returned data.
        // todo: Walk the include/select tree for a better decision.
-       return;
+       // return; <- comment this out
    }
    const decryptionErrors = [];
    const fatalDecryptionErrors = [];

[franky47]

I've released 1.4.0-beta.3, which includes debugging printouts. You can set the DEBUG environment variable to prisma-field-encryption:* to print everything, hopefully that will tell you more about what's going on.

[aortukgp]

Ok, let's see if we can get some errors showing: could you do the following change please?

model patients {
  ...
- name       String    @db.VarChar(50) /// @encrypted
+ name       String    @db.VarChar(50) /// @encrypted?strict
- surname    String    @db.VarChar(50) /// @encrypted
+ surname    String    @db.VarChar(50) /// @encrypted?strict

}

With strict mode enabled, decryption errors will throw rather than pass....

Hi franky, thanks for your availability!

The module does not print errors in the console and does not create log files.

I have tried to comment out the line of code (in my module it was in line 78) and I have also edited the schema.prisma file doing this job:

model patients {

  id Int @id @default(autoincrement())

  CF         String    @unique
  name       String    @db.VarChar(50) /// @encrypted?strict
  surname    String    @db.VarChar(50) /// @encrypted?strict
  ..

  patients_pathologies patients_pathologies[]

}

But the data is still not decrypted :(

I can tell you that the text in the patients.ts file has changed:

/**
 * Internal model:
 * {
 *   "cursor": "id",
 *   "fields": {
 *     "name": {
 *       "encrypt": true,
 *       "strictDecryption": true <-- changed
 *     },
 *     "surname": {
 *       "encrypt": true,
 *       "strictDecryption": true <-- changed
 *     }
 *   },
 *   "connections": {
 *     "user": {
 *       "modelName": "users",
 *       "isList": false
 *     },
 *     "patients_pathologies": {
 *       "modelName": "patients_pathologies",
 *       "isList": true
 *     }
 *   }
 * }
 */

I add: Every time I make a change I run both commands

npx prisma migrate dev --name fix
npx prisma generate

Then I delete the data from the database and recreate it.

[aortukgp]

I've released 1.4.0-beta.3, which includes debugging printouts. You can set the DEBUG environment variable to prisma-field-encryption:* to print everything, hopefully that will tell you more about what's going on.

I checked your edit. I have read the README file, but I don't understand where I need to change the variable.

In the .env file?

In the .env file I created a special section for PRISMA, like this:

PRISMA_FIELD_ENCRYPTION_KEY=k1.aesgcm256.-j7zibF7[...]
DEBUG=prisma-field-encryption:* node ./index.js

[franky47]

You're finding me stumped with this issue, but we'll get to the bottom of this!

What does the debug logs say when reading out the data (that comes out still encrypted)? You can log only those by setting DEBUG to prisma-field-encryption:decryption.

You can either set it in the .env file, as long as it's loaded somehow when running your app (before importing the lib):

DEBUG=prisma-field-encryption:*

But the better way would be to set it as part of the env of the terminal running your server:

# On macOS/Unix:
DEBUG="prisma-field-encryption:*" npm run my-server-start-script

# On Windows (CMD):
set DEBUG=prisma-field-encryption:* & npm run my-server-start-script

# On Windows (VSCode terminal):
$env:DEBUG="prisma-field-encryption:*"; npm run my-server-start-script

You might want to also set the debug depth as well:

# On macOS/Unix:
DEBUG="prisma-field-encryption:*" DEBUG_DEPTH=100 npm run my-server-start-script

# On Windows (CMD):
set DEBUG=prisma-field-encryption:* & set DEBUG_DEPTH=100 & npm run my-server-start-script

# On Windows (VSCode terminal):
$env:DEBUG="prisma-field-encryption:*"; $env:DEBUG_DEPTH=100; npm run my-server-start-script

[franky47]

Ahhh search no longer, I found the issue..

I use a regexp to try and detect a ciphertext in the @47ng/cloak format, and it was wrong somehow to assume the ciphertext part of the string was 22 characters long at least (yours is 11).

I'll run some more tests on this with various short clear-text lengths, fix the regexp and release a fix for you to try, sorry about that!

[franky47]

Now my tests in @47ng/cloak seem to be correct on ciphertext minimum length (an empty string encrypts into a 63-character encrypted string), which brings me to the obvious thing I did not notice in your Prisma schema: the maximum length of 50 characters for your encrypted fields.

What happened is that the ciphertext was truncated, passing under the threshold of the regexp detection, and passed through as-is.

Since ciphertext is quite a bit larger than clear-text, I'd suggest raising this limit (for reference, a 50-character clear-text input encrypts into a 127-characters long encrypted string, so better set it to 130 for safety), and enforcing the actual 50 character length limit in your API before passing it to Prisma. You can calculate other lengths here: https://cloak.47ng.com/ciphertext-length-calculator

Sorry I got you into a wild goose chase, when the answer was actually right in your second message. 😅

[aortukgp]

waah, now it's working properly!! 😂😂😂 damn length! 😂😂😂 i am very happy now, because your package is very comfortable!!

The data I would like to encrypt is longer, the name and surname were a test. The important thing is that we managed to reach the goal! You are very good, thank you very much Franky!

[Serdans]

I seem to be experiencing this issue where my data is encrypted in the database, but it is returned without decrypting. I'm using MongoDB and assume the character length is not the cause of this?

Using ///@encrypted?mode=strict is not throwing an error.

[franky47]

@serdans could you check that you get the same ciphertext from Prisma that the one stored in the database? Just want to rule out a double encryption case.

[Serdans]

Yup it's the same ciphertext.

[franky47]

If you turn on debugging, does it reveal anything useful? Mind that some values may be printed in clear text there.

[Serdans]

I'm using NextJS and setting the DEBUG value in my next.config.js gives me the following error when running it:

Server Error
Error: Invalid left-hand side in assignment

This error happened while generating the page. Any console logs will be displayed in the terminal window.

 at __webpack_require__ (/home/andres/Documents/development/web/subpanel_v3/.next/server/webpack-runtime.js:33:43)
    at eval (webpack-internal:///(rsc)/./node_modules/.pnpm/prisma-field-encryption@1.5.0_@prisma+client@5.5.2/node_modules/prisma-field-encryption/dist/index.js:6:19)
    at (rsc)/./node_modules/.pnpm/prisma-field-encryption@1.5.0_@prisma+client@5.5.2/node_modules/prisma-field-encryption/dist/index.js (/home/andres/Documents/development/web/subpanel_v3/.next/server/vendor-chunks/prisma-field-encryption@1.5.0_@prisma+client@5.5.2.js:80:1)

[franky47]

Looks like an import error, anyway you probably don't need to set it in the Next.js config, a DEBUG="prisma-field-encryption:*" next dev should work.

[Serdans]

I see it's successfully decrypting it if I'm selecting the data directly, e.g. prisma.user.findFirst, but doing a

prisma.order.findMany({
  include: {
     user: {
       select: {
          myDecryptedField: true
       } 
    }
  }

does not decrypt the field.

[franky47]

Ah, that's a different issue then, could you open a separate issue please? I'll try and replicate from your example query, but a concise Prisma schema might help a lot with reproduction. Thanks!