1

First let me state the asynchronous approaches:-

  1. The user enters a character into the username form field. I create a connection to the database, use a prepared statement to confirm whether the username already exists, close the connection, and pass results back to initial page. AJAX is at work. All of this happens for every character the user enters or removes from the input field.

  2. I pre-fetch the list of usernames from the database and store them locally. AJAX does username uniqueness checking from this locally stored pool on each character input, rather than from the database.

Now, the synchronous approaches:-

  1. The user enters a username in input field. Clicks submit. I create a connection to the database, use a prepared statement to confirm whether the username already exists, close the connection, and pass results back to initial page. All of this happens every time the user clicks submit.

  2. Basically the same as second approach in asynchronous section, but checking happens on clicking submit, rather than on inputting each character.

Now the second approaches work faster in both cases I believe, but they can lead to concurrency anomalies. Am I correct?

Also, while using a prepared statement to check username uniqueness from database in 1st approach in both categories, should I rely on the exception which would occur if I try to insert the same username (because of unique constraint) or should I use a select query to confirm the uniqueness? Basically is letting MySQL run into an exception considered bad practice?

If there is another better way of doing this, I'm all ears. Thanks!

  • In your asynchronous approaches, what do you do if the user starts to type "A", but the username "A" is already taken (but "Ab" isn't)? – Bart van Ingen Schenau Feb 15 at 11:13
  • I was thinking of making a hashmap of all usernames, and finding the input in that hashmap. "A" will be in that hashmap, so there will be an error message that username isn't available, but as soon as user enters "b", "Ab" isn't in the hashmap, so the error message goes away – Nikhil Feb 15 at 15:19
1

Providing a means to load all users seems like a big security risk. Also the number of users could be 10 or 10 thousand, so beyond a certain number of users, loading preemptively is a strategy that will be impractical to do client-side after a while.

However, asking the server if a given user is taken could be a viable solution. For one, you're not providing usernames to the client so it's secure, and two, it also resolves performance issues with providing potentially large lists of usernames. If anything, the server can cache this list for easy access and checking.

However checking the existence of a username prior to the submit has one obvious flaw. What if another user creates that same username in the meantime? This is why such a check should be superfluous and absolutely not considered to be true upon final submission. It should simply be an aid to the user to know that the username is taken.

So, this leaves the final submission to consider. When you consider the possibility that a username clashes with another one in your database, that possibility is likely going to be very small. Even if you were to check before insertion, you're not guaranteed that in the milliseconds prior to insertion, another user didn't just insert that same username.

So my thoughts are, ensure database integrity by enforcing constraint checking for username field. Databases are very good about enforcing such things, and you will never have a situation where duplicate usernames are allowed. And since checking a priori isn't even a guarantee that once the user is inserted that it won't exist in that moment and since the possibility of said problems is so low, I would argue to simply try to insert into the database. If you receive an exception related to constraint violation, you know the user you tried to insert already exists. Plan for that situation, but also know that it is very unlikely to occur.

So in summary: create a serverside check of user if it already exists but use that information as a guide, not a surefire guarantee that that user won't exist when you insert it. Use constraint checking to ensure consistency in your table, and prepare for the possibility of a duplicate record, however small.

  • I see, so you're saying that I should check for duplicacy before insertion using select clause, and also plan for the exception that could occur (however minute the possibility) due to another duplicate insertion in the database in the milliseconds between the checking through select clause and the actual insertion, right? – Nikhil Feb 15 at 15:27
  • Also, I know this might be trivial (as you said, the probability of usernames clashing is very low), but opening a connection to DB, checking for username, closing the connection and repeating, won't it be costly? Is there a way to optimize this? – Nikhil Feb 15 at 15:28
  • @Nikhil, for usernames, I would consider a "check as you type" indication if the username exists to be a security concern. It makes it very easy for an attacker to get a list of usernames that are in-use and can be targeted for attempting to break the password for. – Bart van Ingen Schenau Feb 15 at 15:53
  • @Nikhil Opening connections, performing single operations, and then closing said connection is one of the most common use-cases for a database, therefore it is often optimized. Even if it is not, you can cache the results in the server so you can return the proper request with no database check (again, the critical point is at the end during insertion, not for getting a list of users which will likely be up-to-date anyway). – Neil Feb 18 at 7:12

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.