graphile / graphile/starter

username sanitization is case sensitive; column is citext

Open
#284 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
1.8k
Forks
227
PR merge metrics
No merged PRs in 30d

Description

### Summary

Creating a new user using oauth fails in case there is already the same username with different case (ie. existing: peter, newly registering: Peter)

### Steps to reproduce

1. create an account with same username as your github username, but in different case (in my case, HYDRANDT would do)
2. sign out
3. attempt to sign in using github
4. enjoy blue screen "An unknown error occurred"

### Expected results

Username sanitization should be case insensitive and username should be sanitized to hydrandt1

### Actual results

HYDRANDT and hydrandt are deemed non identical, number is not appended, and inserting a new user fails, as `username` column on `app_public.users` is type `citext`.

### Additional context

- problem is in the initial mutation, https://github.com/graphile/starter/blob/main/%40app/db/migrations/committed/000001.sql, in `app_private.register_user`:

```
-- Sanitise the username, and make it unique if necessary.
...
select (
case
when i = 0 then v_username
else v_username || i::text
end
) into v_username from generate_series(0, 1000) i
where not exists(
select 1
from app_public.users
where users.username = (
case
when i = 0 then v_username
else v_username || i::text
end
)
)
limit 1;
```

### Possible Solution

Convert username using lower() while checking for uniqueness:

```
select (
case
when i = 0 then v_username
else v_username || i::text
end
) into v_username from generate_series(0, 1000) i
where not exists(
select 1
from app_public.users
-- comparing using lowercase to make sure the username is unique (username column is citext -> constraint is not case sensitive)
where lower(users.username) = (
case
when i = 0 then lower(v_username)
else lower(v_username) || i::text
end
)
)
limit 1;
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.