1

How do i handle multiple signups with the same email, while email confirmation is still pending?

Here are my tables

user
uid     - bigint primary key
uname   - varchar 255 unique
uemail  - varchar 255 unique
upass   - varchar 255
ustatus - int default 0

confirm
cid - bigint primary key
uid - bigint
utokenhash - text 

when a user registers using an email, ustatus is set to 0, to indicate that the email is pending confirmation. A token is sent to the user's email via a link to confirm email

example.com/confirm/?token=32874$kbkddbskb..

A hash of the token is stored in the confirm table with uid to indicate the user. When the user clicks on the link, the token is taken, compared with the hash and the user email is verified. The entry in the confirm table is deleted. ustatus is changed to 1 to indicate that the user is valid.

How can i handle situations like the one mentioned below?

User A signs up with the email usera@example.com and is awaiting email confirmation to verify his email. Meanwhile user B comes and attempts a signup with the same email usera@example.com.

One solution that i found is to remove unique from uemail, when either one confirms the email, change ustatus to 1 for that user and delete the other user. Make sure that email is unique from the application side (which is already being checked). But i don't know if it is a good practice to let application side be solely responsible for this. If something goes wrong on the application side, someone can send multiple email confirmation to a single already registered user.

Notes

  • Recaptcha is added to the signup forms.
  • Username is also unique

Is there any other way to handle this? Is my proposed solution "okay"?

  • Most sites these days use email address as the primary key for the user instead of having a separate user name field. You are asked to log in using your email address and password instead of your user name and password. Doing so avoids this problem entirely. There can only be one user per email address. – Stephen Ostermiller Mar 4 '18 at 12:00
  • Why would a user sign up with someone else's email address? In all likelihood it'd be the same user, so give them the option of resending the confirmation email in case they didn't receive it. You also want to expire unvalidated accounts after a set time period. – Tim Fountain Mar 4 '18 at 12:08
  • @StephenOstermiller I wouldn't have though you should have the email address as the "primary key", since that will only complicate matters if the user should want to change it. The OP currently has the email as a unique index, which already solves this - but it potentially creates problems when more than one user signs up and uses the same email address. Before it is confirmed, either one could be correct, but only one can be correct. – DocRoot Mar 4 '18 at 12:27
  • @TimFountain users can resend the confirmation mail without signing up again. A form is given immediately after confirmation mail is sent to the email (Did not receive mail? resend). If they try to log in with their unconfirmed username/email + password they are asked to confirm their email address and a resend from is given there also. Since the username is unique it is reserved even for unconfirmed users. If a legitimate user tries to signup again with the same username it will be unavailable this time around. – nandu kk Mar 4 '18 at 12:49
  • @StephenOstermiller The site is a community driven aggregator(think reddit). Username is necessary to identify the the user who posted a link or comment. Even if the username is not unique, the field has to be present. Users can login with their username or email at the moment, as the username is unique. – nandu kk Mar 4 '18 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.