Guaranteeing unique username/email

[cmccully]

Hi

I have a PHP/MYSQL driven site. When a user creates a new account or updates their existing username or email address I do a quick check to make sure the requested entries are not already in use. I am concerned that in the few milliseconds between the SELECT and INSERT/UPDATE statements someone may grab that username/email resulting in duplicate entries. This would obviously be very bad.

First off, am I panicking for no reason? Should I put my hair out and go on my merry way or this a real possibility that I need to code against?

Second, what is the best way to guard against this? I am thinking about starting a transaction, inserting the data then doing a search to see if more than one row is returned. If not then I would commit the transaction.

Any thoughts on this would be appreciated.

cmccully

[pritaeas]

Make the user and email columns in the database unique. If the query returns with an error, it is already in use. The database will take care of the concurrency problem.

[smantscheff]

Pritaeas is right about unique fields. You could bracket your select and insert query into a transaction, but that would not exclude the remote possibility that someone with direct access to the database might enter duplicate names and email addresses - for example an admin who is not using your web interface but another database client. Therefore the database design should exclude duplicates where this is needed.

[cmccully]

Thank you both for your responses.
Marking the fields as unique is such a simple solution, thanks.

cmccully