Best practice to confirm unique username for user creation in JSP and JDBC?

Question

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!

Answer 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.