'asc' and 'desc' are encrypted when using orderBy on String values

Question

'asc' and 'desc' are encrypted when using orderBy on String values

forbesgillikin opened this issue a year ago · 7 comments

prisma.user.findMany({
        where: {
            archived: false
        },
        take: 25,
        skip: 0,
        orderBy: {
           lastName: 'asc'
        }
    })

lastName is not encrypted in the Prisma model.

'asc' becomes encrypted when passed into Prisma

Argument lastName: Provided value '*encryptedvalue*' of type String on prisma.findManyUser is not a SortOrder.

Using SortOrders on other types works as expected.

Answer 1 · 2023-01-26T06:21:57.000Z

May I see the relevant Prisma model in your schema please, with the @encrypted annotations?

Answer 2 · 2023-01-27T04:09:53.000Z

In my OP I referenced a different model but this is the one of concern in my project

model Card {
  id               String         @id @default(uuid())
  createdAt        DateTime       @default(now())
  updatedAt        DateTime       @updatedAt
  cardID           String         @unique /// @encrypted
  cardIDHash       String?        @unique /// @encrypted:hash(cardID)
  cardNumber       String         @unique /// @encrypted
  cardNumberHash   String?        @unique /// @encrypted:hash(cardNumber)
  date             DateTime       @default(now())
  firstName        String
  lastName         String
  email            String?
  lastPaid         DateTime?      @default(now())
  lastPaidAmount   Float?
  archived         Boolean        @default(false)
  cardHolderTypeId String
  cardHolderType   CardHolderType @relation(fields: [cardHolderTypeId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  transactions     Transaction[]
}

const cards = await prisma.card.findMany({
        where: {
            archived: false,
            cardHolderType: cardHolderType
                ? { name: cardHolderType }
                : { isNot: undefined }
        },
        include: {
            cardHolderType: true,
            transactions: true
        },
        take: 25,
        skip: page > 1 ? (page - 1) * 25 : 0,
        orderBy: {
             lastName: 'asc'
        }
    })

'asc' gets converted into an encrypted string. When using orderBy with non-supported types like ID, Float, DateTime, 'asc' is not converted to an encrypted string and Prisma resolves as expected.

Answer 3 · 2023-02-10T10:18:26.000Z

What do the debugging logs say? I'd be curious to see what the schema analysis looks like, to understand how this path ends up being encrypted, especially on a read-only query.

Answer 4 · 2023-03-30T03:56:43.000Z

Hi @franky47

I got the same error.

Prisma schema:

model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique /// @encrypted
  emailHash     String?    @unique /// @encryption:hash(email)
  name      String?
  password  String
  salt      String
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  expiredAt DateTime  @updatedAt
  products  Product[]
}

The error is:

{
    "statusCode": 500,
    "error": "Internal Server Error",
    "message": "\nInvalid `prisma.user.findMany()` invocation in\nC:\\projects\\fastify-test\\src\\modules\\user\\user.service.ts:30:22\n\n  27 }\n  28 \n  29 export async function findUsers() {\n→ 30   return prisma.user.findMany({\n         select: {\n           email: true,\n           name: true,\n           id: true,\n           createdAt: true,\n           expiredAt: true\n         },\n         orderBy: {\n           email: 'v1.aesgcm256.d2a16aa0.uYZkVsBGJYUxarPL.MlYUnc9DVYnZN1FQ4mU3Js-wDd0=',\n                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n           emailHash: 'c3f46fc280c4ac3e5724798b684c86b04d0bc217adcfcb1dade3c1ef56fd8e6b'\n                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n         }\n         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n       })\n\nArgument orderBy of type UserOrderByWithRelationInput needs exactly one argument, but you provided email and emailHash. Please choose one. Available args: \ntype UserOrderByWithRelationInput {\n  id?: SortOrder\n  email?: SortOrder\n  emailHash?: SortOrder\n  name?: SortOrder\n  password?: SortOrder\n  salt?: SortOrder\n  createdAt?: SortOrder\n  updatedAt?: SortOrder\n  expiredAt?: SortOrder\n  products?: ProductOrderByRelationAggregateInput\n}\nArgument email: Provided value 'v1.aesgcm256.d2a16aa0.uYZkVsBGJYUxarPL.MlYUnc9DVYnZN1FQ4mU3Js-wDd0=' of type String on prisma.findManyUser is not a SortOrder.\n→ Possible values: SortOrder.asc, SortOrder.desc\nArgument emailHash: Provided value 'c3f46fc280c4ac3e5724798b684c86b04d0bc217adcfcb1dade3c1ef56fd8e6b' of type String on prisma.findManyUser is not a SortOrder.\n→ Possible values: SortOrder.asc, SortOrder.desc\n\n"
}

Answer 5 · 2023-04-11T19:36:37.000Z

Hi,

Same Error:

invalid `prisma.tbl_user.findMany()` invocation:
{
  select: {
    id: true,
    email: true,
    first_name: true,
    last_name: true,
    user_title: true,
    user_roles: true
  },
  take: 25,
  skip: 0,
  orderBy: {
    first_name: 'v1.aesgcm256.23ba68b8.pkemoldaauMZo5nQ.vt1RxX8FGGHG8IYIQ6J0vdjRAw=='
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  }
}

Argument first_name: Provided value 'v1.aesgcm256.23ba68b8.pkemoldaauMZo5nQ.vt1RxX8FGGHG8IYIQ6J0vdjRAw==' of type String on prisma.findManytbl_user is not a SortOrder.
→ Possible values: SortOrder.asc, SortOrder.desc

Here's the debug log:

 prisma-field-encryption:decryption Raw result from database: {
  prisma-field-encryption:decryption   id: 1,
  prisma-field-encryption:decryption   email: 'v1.aesgcm256.23ba68b8.bbmyY0Q5BsE305dW.d9_gD7LhCfbK7kC1IQNqKg7AHCDZSa6uxL1uaeGm8V68X1I4TdHsPgekq59SdQ==',
  prisma-field-encryption:decryption   email_Hash: '6cb2b703ca2003f860d7de38ff0e9561aad24f028ef0367fec0f005b655f98f3',
  prisma-field-encryption:decryption   title_id: 1,
  prisma-field-encryption:decryption   first_name: 'v1.aesgcm256.23ba68b8.k9WII-p_LfhiMiAQ.yPzxr49mHck-Qx_-UgSWShgI3fjwoA==',
  prisma-field-encryption:decryption   last_name: 'v1.aesgcm256.23ba68b8.85oljSoT_aAH026W.gly8ICZWPZ2bIaj52Ro6P9WElMyPPbEJDTeg40s=',
  prisma-field-encryption:decryption   ininitals: 'v1.aesgcm256.23ba68b8.aL4yxVFK4Fizk2Bx.piZxYLjpneocspIEh1TQL_sN',
  prisma-field-encryption:decryption   password_hash: '$2b$10$zH7enIi7Vkpoayqzhl/J3uYR8IqjY.SBQNcMfqI.2Hvi9FYeEhT.i',
  prisma-field-encryption:decryption   user_auth_token: '45bbc49e-3855-4f81-a48e-18ec44a72e1a',
  prisma-field-encryption:decryption   theme_id: 1,
  prisma-field-encryption:decryption   country_flag: '🇩🇪',
  prisma-field-encryption:decryption   is_creator: true,
  prisma-field-encryption:decryption   is_light_theme: false,
  prisma-field-encryption:decryption   created: 2023-04-11T19:16:01.567Z,
  prisma-field-encryption:decryption   updated: null,
  prisma-field-encryption:decryption   deleted: null,
  prisma-field-encryption:decryption   user_theme: {
  prisma-field-encryption:decryption     id: 1,
  prisma-field-encryption:decryption     name: 'skeleton',
  prisma-field-encryption:decryption     symbol: '💀',
  prisma-field-encryption:decryption     created: 2023-04-11T19:16:01.510Z,
  prisma-field-encryption:decryption     updated: null,
  prisma-field-encryption:decryption     deleted: null
  prisma-field-encryption:decryption   },
  prisma-field-encryption:decryption   user_roles: [
  prisma-field-encryption:decryption     {
  prisma-field-encryption:decryption       id: 1,
  prisma-field-encryption:decryption       user_id: 1,
  prisma-field-encryption:decryption       role_name: 'Superadmin',
  prisma-field-encryption:decryption       created: 2023-04-11T19:16:01.567Z,
  prisma-field-encryption:decryption       updated: null,
  prisma-field-encryption:decryption       deleted: null
  prisma-field-encryption:decryption     }
  prisma-field-encryption:decryption   ],
  prisma-field-encryption:decryption   country: {
  prisma-field-encryption:decryption     id: 2,
  prisma-field-encryption:decryption     country_name: 'Germany',
  prisma-field-encryption:decryption     country_code: 'DE',
  prisma-field-encryption:decryption     flag: '🇩🇪',
  prisma-field-encryption:decryption     language: 'de',
  prisma-field-encryption:decryption     created: 2023-04-11T19:16:01.537Z,
  prisma-field-encryption:decryption     updated: null,
  prisma-field-encryption:decryption     deleted: null
  prisma-field-encryption:decryption   },
  prisma-field-encryption:decryption   user_title: { id: 1, country_id: 2, title: '' }
  prisma-field-encryption:decryption } +25ms
  prisma-field-encryption:decryption Visiting {
  prisma-field-encryption:decryption   field: 'email',
  prisma-field-encryption:decryption   model: 'tbl_user',
  prisma-field-encryption:decryption   fieldConfig: {
  prisma-field-encryption:decryption     encrypt: true,
  prisma-field-encryption:decryption     strictDecryption: false,
  prisma-field-encryption:decryption     hash: {
  prisma-field-encryption:decryption       targetField: 'email_Hash',
  prisma-field-encryption:decryption       algorithm: 'sha256',
  prisma-field-encryption:decryption       salt: '=L3eIcvVPL)!',
  prisma-field-encryption:decryption       inputEncoding: 'utf8',
  prisma-field-encryption:decryption       outputEncoding: 'hex'
  prisma-field-encryption:decryption     }
  prisma-field-encryption:decryption   },
  prisma-field-encryption:decryption   path: 'email',
  prisma-field-encryption:decryption   value: 'v1.aesgcm256.23ba68b8.bbmyY0Q5BsE305dW.d9_gD7LhCfbK7kC1IQNqKg7AHCDZSa6uxL1uaeGm8V68X1I4TdHsPgekq59SdQ=='
  prisma-field-encryption:decryption } +1ms
  prisma-field-encryption:decryption Decrypted tbl_user.email at path `email` using key fingerprint 23ba68b8 +1ms
  prisma-field-encryption:decryption Visiting {
  prisma-field-encryption:decryption   field: 'first_name',
  prisma-field-encryption:decryption   model: 'tbl_user',
  prisma-field-encryption:decryption   fieldConfig: { encrypt: true, strictDecryption: false },
  prisma-field-encryption:decryption   path: 'first_name',
  prisma-field-encryption:decryption   value: 'v1.aesgcm256.23ba68b8.k9WII-p_LfhiMiAQ.yPzxr49mHck-Qx_-UgSWShgI3fjwoA=='
  prisma-field-encryption:decryption } +0ms
  prisma-field-encryption:decryption Decrypted tbl_user.first_name at path `first_name` using key fingerprint 23ba68b8 +0ms
  prisma-field-encryption:decryption Visiting {
  prisma-field-encryption:decryption   field: 'last_name',
  prisma-field-encryption:decryption   model: 'tbl_user',
  prisma-field-encryption:decryption   fieldConfig: { encrypt: true, strictDecryption: false },
  prisma-field-encryption:decryption   path: 'last_name',
  prisma-field-encryption:decryption   value: 'v1.aesgcm256.23ba68b8.85oljSoT_aAH026W.gly8ICZWPZ2bIaj52Ro6P9WElMyPPbEJDTeg40s='
  prisma-field-encryption:decryption } +0ms
  prisma-field-encryption:decryption Decrypted tbl_user.last_name at path `last_name` using key fingerprint 23ba68b8 +1ms
  prisma-field-encryption:decryption Visiting {
  prisma-field-encryption:decryption   field: 'ininitals',
  prisma-field-encryption:decryption   model: 'tbl_user',
  prisma-field-encryption:decryption   fieldConfig: { encrypt: true, strictDecryption: false },
  prisma-field-encryption:decryption   path: 'ininitals',
  prisma-field-encryption:decryption   value: 'v1.aesgcm256.23ba68b8.aL4yxVFK4Fizk2Bx.piZxYLjpneocspIEh1TQL_sN'
  prisma-field-encryption:decryption } +0ms
  prisma-field-encryption:decryption Decrypted tbl_user.ininitals at path `ininitals` using key fingerprint 23ba68b8 +0ms
  prisma-field-encryption:decryption Changing model: following connection tbl_user.user_theme to model tbl_theme +0ms
  prisma-field-encryption:decryption Changing model: following connection tbl_user.user_roles to model tbl_user_role +0ms
  prisma-field-encryption:decryption Changing model: following connection tbl_user.country to model tbl_country +1ms
  prisma-field-encryption:decryption Changing model: following connection tbl_user.user_title to model tbl_title +0ms
  prisma-field-encryption:decryption Decrypted result: {
  prisma-field-encryption:decryption   id: 1,
  prisma-field-encryption:decryption   email: 'ichangedthis@change.com',
  prisma-field-encryption:decryption   email_Hash: '6cb2b703ca2003f860d7de38ff0e9561aad24f028ef0367fec0f005b655f98f3',
  prisma-field-encryption:decryption   title_id: 1,
  prisma-field-encryption:decryption   first_name: 'Changed',
  prisma-field-encryption:decryption   last_name: 'ChangedChanged',
  prisma-field-encryption:decryption   ininitals: 'MS',
  prisma-field-encryption:decryption   password_hash: '$2b$10$zH7enIi7Vkpoayqzhl/J3uYR8IqjY.SBQNcMfqI.2Hvi9FYeEhT.i',
  prisma-field-encryption:decryption   user_auth_token: '45bbc49e-3855-4f81-a48e-18ec44a72e1a',
  prisma-field-encryption:decryption   theme_id: 1,
  prisma-field-encryption:decryption   country_flag: '🇩🇪',
  prisma-field-encryption:decryption   is_creator: true,
  prisma-field-encryption:decryption   is_light_theme: false,
  prisma-field-encryption:decryption   created: 2023-04-11T19:16:01.567Z,
  prisma-field-encryption:decryption   updated: null,
  prisma-field-encryption:decryption   deleted: null,
  prisma-field-encryption:decryption   user_theme: {
  prisma-field-encryption:decryption     id: 1,
  prisma-field-encryption:decryption     name: 'skeleton',
  prisma-field-encryption:decryption     symbol: '💀',
  prisma-field-encryption:decryption     created: 2023-04-11T19:16:01.510Z,
  prisma-field-encryption:decryption     updated: null,
  prisma-field-encryption:decryption     deleted: null
  prisma-field-encryption:decryption   },
  prisma-field-encryption:decryption   user_roles: [
  prisma-field-encryption:decryption     {
  prisma-field-encryption:decryption       id: 1,
  prisma-field-encryption:decryption       user_id: 1,
  prisma-field-encryption:decryption       role_name: 'Superadmin',
  prisma-field-encryption:decryption       created: 2023-04-11T19:16:01.567Z,
  prisma-field-encryption:decryption       updated: null,
  prisma-field-encryption:decryption       deleted: null
  prisma-field-encryption:decryption     }
  prisma-field-encryption:decryption   ],
  prisma-field-encryption:decryption   country: {
  prisma-field-encryption:decryption     id: 2,
  prisma-field-encryption:decryption     country_name: 'Germany',
  prisma-field-encryption:decryption     country_code: 'DE',
  prisma-field-encryption:decryption     flag: '🇩🇪',
  prisma-field-encryption:decryption     language: 'de',
  prisma-field-encryption:decryption     created: 2023-04-11T19:16:01.537Z,
  prisma-field-encryption:decryption     updated: null,
  prisma-field-encryption:decryption     deleted: null
  prisma-field-encryption:decryption   },
  prisma-field-encryption:decryption   user_title: { id: 1, country_id: 2, title: '' }
  prisma-field-encryption:decryption } +0ms
  prisma-field-encryption:encryption Clear-text input: {
  prisma-field-encryption:encryption   args: {
  prisma-field-encryption:encryption     select: {
  prisma-field-encryption:encryption       id: true,
  prisma-field-encryption:encryption       email: true,
  prisma-field-encryption:encryption       first_name: true,
  prisma-field-encryption:encryption       last_name: true,
  prisma-field-encryption:encryption       user_title: true,
  prisma-field-encryption:encryption       user_roles: true
  prisma-field-encryption:encryption     },
  prisma-field-encryption:encryption     take: 25,
  prisma-field-encryption:encryption     skip: 0,
  prisma-field-encryption:encryption     orderBy: { first_name: 'asc' }
  prisma-field-encryption:encryption   },
  prisma-field-encryption:encryption   dataPath: [],
  prisma-field-encryption:encryption   runInTransaction: false,
  prisma-field-encryption:encryption   action: 'findMany',
  prisma-field-encryption:encryption   model: 'tbl_user'
  prisma-field-encryption:encryption } +11ms
  prisma-field-encryption:encryption Visiting {
  prisma-field-encryption:encryption   field: 'first_name',
  prisma-field-encryption:encryption   model: 'tbl_user',
  prisma-field-encryption:encryption   fieldConfig: { encrypt: true, strictDecryption: false },
  prisma-field-encryption:encryption   path: 'orderBy.first_name',
  prisma-field-encryption:encryption   value: 'asc'
  prisma-field-encryption:encryption } +4ms
  prisma-field-encryption:encryption Encrypted tbl_user.first_name at path `orderBy.first_name` +1ms
  prisma-field-encryption:encryption Encrypted input: {
  prisma-field-encryption:encryption   args: {
  prisma-field-encryption:encryption     select: {
  prisma-field-encryption:encryption       id: true,
  prisma-field-encryption:encryption       email: true,
  prisma-field-encryption:encryption       first_name: true,
  prisma-field-encryption:encryption       last_name: true,
  prisma-field-encryption:encryption       user_title: true,
  prisma-field-encryption:encryption       user_roles: true
  prisma-field-encryption:encryption     },
  prisma-field-encryption:encryption     take: 25,
  prisma-field-encryption:encryption     skip: 0,
  prisma-field-encryption:encryption     orderBy: {
  prisma-field-encryption:encryption       first_name: 'v1.aesgcm256.23ba68b8.pkemoldaauMZo5nQ.vt1RxX8FGGHG8IYIQ6J0vdjRAw=='
  prisma-field-encryption:encryption     }
  prisma-field-encryption:encryption   },
  prisma-field-encryption:encryption   dataPath: [],
  prisma-field-encryption:encryption   runInTransaction: false,
  prisma-field-encryption:encryption   action: 'findMany',
  prisma-field-encryption:encryption   model: 'tbl_user'
  prisma-field-encryption:encryption } +0ms
ERR query users.userTable - 11ms

Answer 6 · 2023-04-12T09:26:30.000Z

Thanks for the debug logs, I can see where the problem is now. I'll have to add a special case for the presence of orderBy in the query, but it will also need to apply this sorting logic not at the database level (which makes no sense as we'd be sorting the ciphertext), but at runtime after decryption.

Answer 7 · 2023-04-13T08:33:27.000Z

I'm closing this as "won't fix", as sorting on encrypted values is not something I'd recommend: since the database cannot do the job itself (sorting random ciphertext won't do much good for sorting the underlying clear text data), sorting would have to be done at runtime after decryption, which is another whole can of worms of parsing the query to know what to work on, and play nicely with pagination.

If anyone wishes to tackle this problem, I'll happily review PRs.

In the mean time, attempting to orderBy on an encrypted field will log an error, and fix the encrypted 'asc' | 'desc' issue so that Prisma doesn't complain (it will actually drop the orderBy clause).

As for @forbesgillikin and @codewithmecoder, if you're indeed sorting on non-encrypted fields and see this error, please provide me with debug logs so I can have a better idea of what is going on.