[Bug?]: Standard dbAuth username is case sensitive causing inconsistencies

[ched-dev]

What's not working?

Using the standard dbAuth strategy, username's are stored as they type in, causing possible duplicate accounts or failed logins because of mismatch.

Note: It is possible to implement some additional logic within my app to solve the issue for most cases. It will not be fixed for the duplicate user check which is handled by redwood - which could cause user to create duplicate users.

How do we reproduce the bug?

Using a redwoodjs project with standard dbAuth flow installed:

• Sign up for an account with a case sensitive username (Ex: demoUser)
• Log out, then log back in trying to use the username in all lowercase (Ex: demouser)
• Error: Could not find account with matching username

What's your environment? (If it applies)

System:
    OS: macOS 12.0.1
    Shell: 5.8 - /bin/zsh
  Binaries:
    Node: 16.13.2 - ../node
    Yarn: 3.2.0 - ../yarn
  Databases:
    SQLite: 3.36.0 - /usr/bin/sqlite3
  Browsers:
    Chrome: 110.0.5481.177
    Firefox: 110.0.1
    Safari: 15.1
  npmPackages:
    @redwoodjs/auth-dbauth-setup: 4.0.1 => 4.0.1
    @redwoodjs/core: ^4.0.1 => 4.0.1

Are you interested in working on this?

• I'm interested in working on this

[Tobbe]

usernames should probably be stored as the user types them in (i.e. case sensitive), but matched case insensitive, both for duplication checks and when logging in. @ched-dev if I can confirm that's the behavior we want, would you be interesting in trying to implement it?

[ched-dev]

@Tobbe yes, I can work on this. I can probably put together a PR in the next couple weeks.

[Tobbe]

Perfect! @cannikin, what do you think?

[cannikin]

usernames should probably be stored as the user types them in (i.e. case sensitive), but matched case insensitive

Yep, agree!

[Tobbe]

Great! @ched-dev please go ahead with that PR whenever you have time 🙂

[ched-dev]

Looking into this and having some trouble.

If I try to add "case-insensitive" mode to the prisma query:

user = await this.dbAccessor.findUnique({
  where: {
    [this.options.authFields.username]: {
      equals: username,
      mode: 'insensitive'
    }
  },
})

I get an error when trying to run it:

Argument email: Got invalid value
{
equals: 'rob@redwoodjs.com',
mode: 'insensitive'
}
on prisma.findUniqueUser. Provided Json, expected String.

If I try to change the findUnique() to a findFirst() I get another error:

Unknown arg mode in where.email.mode for type StringFilter.

While prisma says it supports case-insensitive filtering, it doesn't seem to be working in this scenario.

Curious if you have any advice on how to proceed. I am a beginner prisma user, so not sure if I am approaching it wrong.

[ched-dev]

I've done some more digging and it seems the mode: 'insensitive' is only available if the database allows it since some databases are case-insensitive by default. So if I was to write code to handle this, I would have to write it based on the database being used. I believe I'm getting the errors now because I was using the default setup of SQLite.

Also, mode: 'insensitive' is only available on .findFirst() so I'd have to switch it from .findUnique(). Will that cause a noticeable performance hit?

[ageddesi]

As you say this depends on what db the user has chosen and its best to avoid logic based on different dbs.
As such, I think we should consider adding another prop to the User Db Object eg 'usernameInsensitive'
Then when we are doing the check-in '_createUser' we can do it on the 'usernameInsensitive' instead.

[thedavidprice]

FYI that @Tobbe and @cannikin are traveling this week. Give them (me) a nudge if there's no reply by early next week.

[ched-dev]

I will be AFK (away from keyboard) through Apr 6 too. @ageddesi if you have time try out a fix, feel free. otherwise, I will look at it again when I get back.

[ageddesi]

@ched-dev Hey, I will try to take a look before your back and let you know if I get somewhere.

[ageddesi]

@ched-dev @thedavidprice
I managed to get some time to look into the code changes, which I have done here ageddesi#1
I need to just test this in a locally running project. The tests all pass.
Let me know if you think I am missing anything.
Once I have confirmed that I will point the PR to this branch.

[thedavidprice]

@ageddesi Please open the PR! That's the best context for review and I can make sure the right people are looped in.

Huge thanks 🙏

[dennemark]

Hi,
the PR by @ageddesi applies case insensitivity check on signup flow, but not on login. Is it possible to add it easily to login, too?

[ageddesi]

This definitely could be a factor as well. If someone assigns a ticket to me i can add this as i know that area now.

[cannikin]

@dennemark Can you create an issue for this and I'll assign to @ageddesi !

[Tobbe]

Closed by #7979 and #8045