Exclude user's password from query with Prisma 2

Question

Recently I started working on a new project to learn some new technologies (Prisma 2, REST api with Express, etc.). Tho, I faced a problem.

My app has a user authentication system and the user model has a password column. So, when the client requests a user, the backend selects all the columns from the database including the password (that's hashed by the way).

I tried to not select the password column on the prisma findMany, like this:

await prisma.user.findUnique({
  where: {
    ...
  },
  select: {
    password: false
  }
});

But I got an error by prisma saying that the select should contain at least one truly value. Thus, I added id: true to the select. I made an api request and I saw that only the id was returning for the user.

By my understanding, prisma expects me to add all the columns I care to the select object. But, I need a lot of columns from the user and I am making a lot of queries to fetch users and I cannot just write all the field I need everytime.

So, I wanted to ask you if there is a legit way to do that.

PS: I don't take "use rawQuery instead" as a solution.

score 6 · Answer 1 · answered Jun 28 '21 at 05:11

The only legit way is adding column: true to the columns you want to include. There are requests for excluding columns here so it would be great if you could add a to the request relevant to you so that we can look at the priority.

https://github.com/prisma/prisma/issues/5042 https://github.com/prisma/prisma/issues/7380 https://github.com/prisma/prisma/issues/3796

score 6 · Answer 2 · answered Jun 21 '22 at 04:38

I've been wondering about how to implement this as well, and bafflingly the issues linked in @Ryan's post are over two years old, and still unresolved. I came up with a temporary workaround, which is to implement a middleware function for the Prisma client which removes the password field manually after each call.

import { PrismaClient } from '@prisma/client'

async function excludePasswordMiddleware(params, next) {
  const result = await next(params)
  if (params?.model === 'User' && params?.args?.select?.password !== true) {
    delete result.password
  }
  return result
}

const prisma = new PrismaClient()
prisma.$use(excludePasswordMiddlware)

This will check if the model being queried is a User, and it will not delete the field if you explicitly include the password using a select query. This should allow you to still get the password when needed, like when you need to authenticate a user who is signing in:

async validateUser(email: string, password: string) {
  const user = await this.prisma.user.findUnique({
    where: { email },
    select: {
      emailVerified: true,
      password: true,
    },
  })
  // Continue to validate user, compare passwords, etc.
  return isValid
}

Actually, I think the most efficient way to do it (especially in case you are using this as an API response) is to use [class-transformer](https://github.com/typestack/class-transformer). With it, you can create a DTO class for the user, that doesn't expose, for example, the password field. Then, use the `plainToClass` function to convert the user entity (that comes from Prisma and includes the password) to the user DTO class (that doesn't include the user's password). That's how you get rid of the password. Check the library it's pretty cool, it made things a lot simpler for me. — Vasilis Voyiadjis, Jun 24 '22 at 14:55
Useful. Only thing I would add to this is that is doesn't cover cases where more than one user object is returned from methods like findMany(). Checking if the result is an array would then cover most use cases: if(Array.isArray(result)) { result = result.map(user => { delete user.password; return user; }); } — Jacob, Jul 29 '22 at 12:13

score 1 · Answer 3 · edited Sep 04 '22 at 06:40

1

Check out the following code Exclude keys from user

function exclude(user, ...keys) {
  for (let key of keys) {
    delete user[key]
  }
  return user
}

function main() {
  const user = await prisma.user.findUnique({ where: 1 })
  const userWithoutPassword = exclude(user, 'password')
}

reference prima official Website

edited Sep 04 '22 at 06:40

Harsh Patel

6,334
10
40
73

answered Aug 28 '22 at 17:34

Adarsh Hatkar

21
6

6

Just a side note for new dev who read this answer: this is technically a really bad idea though it's the most straight forward way to do it, and from the official website. You have to always remember to exclude the password column all the time, so it's a huge potential data leak issue as well as for all the relation query cases. Using for loop also causes performance issue unnecessarily. The ideal way to solve it is actually exclude it in the SQL level or Prisma model level. That's what the original question is looking for. In this discussion: https://github.com/prisma/prisma/issues/5042 – Yunhai Sep 11 '22 at 07:01

Exclude user's password from query with Prisma 2

3 Answers3