Checking for username...

[Domestos]

Hi all,

How do I check in a mySQL table called 'Users' on column user_name when
registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

TIA

[Gordon Burditt]

>How do I check in a mySQL table called 'Users' on column user_name when

registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

An appropriate way is to insert a row with the username, and it will
fail if the username is in use because of the unique index you have
on the user_name column.

Gordon L. Burditt

[Alvaro G Vicario]

*** Domestos wrote/escribió (Mon, 13 Jun 2005 20:50:51 GMT):

How do I check in a mySQL table called 'Users' on column user_name when
registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

I normally do something like this:

SELECT id FROM users WHERE username='john'

If zero rows are returned, the username is available.

--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Ivan Omelchenko 608308824]

Alvaro G Vicario пишет:

*** Domestos wrote/escribió (Mon, 13 Jun 2005 20:50:51 GMT):

How do I check in a mySQL table called 'Users' on column user_name when
registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

I normally do something like this:

SELECT id FROM users WHERE username='john'

If zero rows are returned, the username is available.

I guess better use not ='john' but like 'john', because of case-sensitive

[Kenneth Downs]

Domestos wrote:

Hi all,

How do I check in a mySQL table called 'Users' on column user_name when
registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

TIA

The database can do this by implementing a unique constraint on the column
user_name. After issuing the INSERT, (or any SQL command), check for an
error, and report it. This then solves your general problem of enforcing
data integrity and reporting errors.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[Ivan Omelchenko 608308824]

Kenneth Downs пишет:

Domestos wrote:

Hi all,

How do I check in a mySQL table called 'Users' on column user_name when
registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

TIA

The database can do this by implementing a unique constraint on the column
user_name. After issuing the INSERT, (or any SQL command), check for an
error, and report it. This then solves your general problem of enforcing
data integrity and reporting errors.

checking for the SQL error is not a good idea, actually.
Well known programing prupose to use select before insert.

[Gordon Burditt]

>> The database can do this by implementing a unique constraint on the column

user_name. After issuing the INSERT, (or any SQL command), check for an
error, and report it. This then solves your general problem of enforcing
data integrity and reporting errors.

checking for the SQL error is not a good idea, actually.
Well known programing prupose to use select before insert.

This has the well-known problem that you may find the name not
in use, insert it, and find the name has been taken before you
got to it - unless you've gotten a lock first.

Gordon L. Burditt

[Kenneth Downs]

Ivan Omelchenko 608308824 wrote:

Kenneth Downs ?????:

Domestos wrote:

Hi all,

How do I check in a mySQL table called 'Users' on column user_name when
registering a new user_name to make sure the new 'user_name' does not
already exist in that column?

TIA

The database can do this by implementing a unique constraint on the
column
user_name. After issuing the INSERT, (or any SQL command), check for an
error, and report it. This then solves your general problem of enforcing
data integrity and reporting errors.

checking for the SQL error is not a good idea, actually.
Well known programing prupose to use select before insert.

Well known on what planet?

The Server maintains integrity, that's why they were invented. This
protects the database against a buggy or intentionally misbehaving
application. Not surprisingly, it is also much faster.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[Alvaro G Vicario]

*** Gordon Burditt wrote/escribió (Tue, 14 Jun 2005 13:20:31 -0000):

This has the well-known problem that you may find the name not
in use, insert it, and find the name has been taken before you
got to it - unless you've gotten a lock first.

If username field is defined as unique it shouldn't be a great issue. User
will get a generic error rather than a 'user not available' message. Not so
cute but, what's the probability of such a race condition if both queries
(SELECT and INSERT) and consecutive in your code?
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Gordon Burditt]

>> This has the well-known problem that you may find the name not

in use, insert it, and find the name has been taken before you
got to it - unless you've gotten a lock first.
If username field is defined as unique it shouldn't be a great issue. User
will get a generic error rather than a 'user not available' message. Not so

That assumes that you CHECK for errors on the insert, which "obviously"
can't happen since you already checked with SELECT, right? Wrong.
If you fail to check for errors on the insert, the user will think
he GOT the username he asked for, and then it won't work (since
presumably the password is different).

Queries ALWAYS take at least two moments and at least one query
from something else can always get in between them unless you've
done something to prevent that (e.g. locking, or ensuring that only
one client exists at a time).

Oh, yes, there's no reason why you have to give a "generic error"
if the insert fails, except, of course, for Bad Web Design(tm).
cute but, what's the probability of such a race condition if both queries
(SELECT and INSERT) and consecutive in your code?

If your site is popular enough, and lasts long enough, it's near
100% to happen eventually. And it will probably happen right when
your prospective client is about to decide to let you design his
web site at a generous rate, or when he's about to approve your
final check. Murphy's Law can be quite nasty.

The fact that you ask such a question means I don't want you any
where near the software design for airplanes, nuclear reactors, air
traffic control systems, weapons systems, automobile on-board
software, or cellular phone firmware. I don't really want you
around the design of any web sites I use to buy stuff, either.

Gordon L. Burditt

[Alvaro G Vicario]

*** Gordon Burditt wrote/escribió (Tue, 14 Jun 2005 17:55:24 -0000):

If username field is defined as unique it shouldn't be a great issue. User
will get a generic error rather than a 'user not available' message. Not so
That assumes that you CHECK for errors on the insert, which "obviously"
can't happen since you already checked with SELECT, right? Wrong.

UNIQUE indexes are handled by database server itself. Even old versions of
MySQL have such. If you insert a duplicate value the insertion fails and
mysql_query() returns an error code. You cannot insert duplicates, even if
you try.
Oh, yes, there's no reason why you have to give a "generic error"
if the insert fails, except, of course, for Bad Web Design(tm).
Absolutely Perfect Web Design may not be adequate for all purposes. Most
web applications out there serve to pretty simple purposes and have limited
resources (time, stuff or money). If my generic error will be displayed
once in 5 years, I won't waste my time coding. My customer won't appreciate
it ;-)

If your site is popular enough, and lasts long enough, it's near
100% to happen eventually.
As I said above: how popular? how often? The answer to these questions does
matter. We must handle pretty big figures to have two simultaneous
registrations with the same username; if we do, then we must obviously use
a different design, I agree with that. Not otherwise.
And it will probably happen right when
your prospective client is about to decide to let you design his
web site at a generous rate, or when he's about to approve your
final check. Murphy's Law can be quite nasty.
Yeah, sure :)
The fact that you ask such a question means I don't want you any
where near the software design for airplanes, nuclear reactors, air
traffic control systems, weapons systems, automobile on-board
software, or cellular phone firmware. I don't really want you
around the design of any web sites I use to buy stuff, either.

You won't see me there. However, I don't you to code my guestbook if you're
taking 15 years of development, 100 engineers and 3000 million euros ;-)
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Gordon Burditt]

>>>If username field is defined as unique it shouldn't be a great issue. User

will get a generic error rather than a 'user not available' message. Not so

That assumes that you CHECK for errors on the insert, which "obviously"
can't happen since you already checked with SELECT, right? Wrong.

UNIQUE indexes are handled by database server itself. Even old versions of
MySQL have such. If you insert a duplicate value the insertion fails and
mysql_query() returns an error code. You cannot insert duplicates, even if
you try.

And if you do not *CHECK* for such an error, you will tell the user
that the registration succeeded when it did not. The server cannot
force you to do that.

Oh, yes, there's no reason why you have to give a "generic error"
if the insert fails, except, of course, for Bad Web Design(tm).

Absolutely Perfect Web Design may not be adequate for all purposes. Most
web applications out there serve to pretty simple purposes and have limited
resources (time, stuff or money). If my generic error will be displayed
once in 5 years, I won't waste my time coding. My customer won't appreciate
it ;-)

You do not have to display a "generic error". You can display a
*USEFUL* error message that is informative. For example: "Either
someone just grabbed your user name while you were filling in the
form or something's broke. Please try again with a different user
name". You don't have to use some odd server error code (printing
raw server error codes may be a security problem anyway), and you
can use a message more informative than "Whoops!". You need to
check that the insert worked correctly ANYWAY. And composing a
message like the above shouldn't take that much time.

Since you're into probabilities, you might as well assume that ANY
error on the insert is due to a user name conflict and leave off
the "or something's broke" part of the above message. Just do an
INSERT and if it failed, ask the user to choose a different username.
(Don't do this if other user data entered can cause the insert to
fail, such as a credit card number "NONE" or leaving the email
address field blank, and the server will be the first check
to catch these.)

Since the server was presumably working when it sent the form,
that's a very narrow time window for the server to crash.

The fact that you ask such a question means I don't want you any
where near the software design for airplanes, nuclear reactors, air
traffic control systems, weapons systems, automobile on-board
software, or cellular phone firmware. I don't really want you
around the design of any web sites I use to buy stuff, either.

You won't see me there. However, I don't you to code my guestbook if you're
taking 15 years of development, 100 engineers and 3000 million euros ;-)

It doesn't take nearly that much to generate intelligent error
messages or use correct locking. For a guestbook it may not be
important, but for a shopping basket, or any application where
real money is involved, it probably is. It's amazing how a little
opportunity to steal will bring on the attacks.

Gordon L. Burditt

[Alvaro G Vicario]

*** Gordon Burditt wrote/escribió (Tue, 14 Jun 2005 19:32:23 -0000):

UNIQUE indexes are handled by database server itself. Even old versions of
MySQL have such. If you insert a duplicate value the insertion fails and
mysql_query() returns an error code. You cannot insert duplicates, even if
you try.
And if you do not *CHECK* for such an error, you will tell the user
that the registration succeeded when it did not. The server cannot
force you to do that.

All the time I'be been considering that checking return values from
functions you use is such a basic practice that you don't need to mention.
Sorry if I didn't make it clear.
You do not have to display a "generic error". You can display a
*USEFUL* error message that is informative. For example: "Either
someone just grabbed your user name while you were filling in the
form or something's broke.

So "Something's broke" is *not* a generic error for you?

The fact that you ask such a question means I don't want you any
where near the software design for airplanes, nuclear reactors, air
traffic control systems, weapons systems, automobile on-board
software, or cellular phone firmware. I don't really want you
around the design of any web sites I use to buy stuff, either.

You won't see me there. However, I don't you to code my guestbook if you're
taking 15 years of development, 100 engineers and 3000 million euros ;-)

It doesn't take nearly that much to generate intelligent error
messages or use correct locking.

You use _that_ example and I cannot do the same? :^)

--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Daniel Tryba]

Alvaro G Vicario <al******************@telecomputeronline.com> wrote:

All the time I'be been considering that checking return values from
functions you use is such a basic practice that you don't need to mention.
Sorry if I didn't make it clear.

So if you are checking the INSERT for failures why prepend a SELECT?
The INSERT after the SELECT can still fail for the same reason a
standalone INSERT can fail.

Skipping the SELECT halves the number of operations the database has to
do for this function. Which in this case might not be that important but
a more common situation where you have to do either a INSERT or an
UPDATE (eg storing session data in a database) is more obvious. There is
only 1 INSERT at the beginning of the session, all other writes will be
UPDATEs. So instead of using a SELECT in the write method of the
sessionhandler before an INSERT/UPDATE always is 2 operations, simply
trying to UPDATE and on failure (since there is no such id in the table)
INSERT drastically reduces the rdbms' overhead.

[Alvaro G Vicario]

*** Daniel Tryba wrote/escribió (15 Jun 2005 07:46:58 GMT):

So if you are checking the INSERT for failures why prepend a SELECT?

In order to distinguish a SQL error from an unavailable username. I hate
apps that tell you your data is incorrect when DB server is down.
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Kenneth Downs]

Alvaro G Vicario wrote:

*** Daniel Tryba wrote/escribió (15 Jun 2005 07:46:58 GMT):

So if you are checking the INSERT for failures why prepend a SELECT?

In order to distinguish a SQL error from an unavailable username. I hate
apps that tell you your data is incorrect when DB server is down.

How did you connect to a down server to do the INSERT, and why didn't you
report to the user that you could not connect?

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[Alvaro G Vicario]

*** Kenneth Downs wrote/escribió (Wed, 15 Jun 2005 06:42:03 -0400):

How did you connect to a down server to do the INSERT, and why didn't you
report to the user that you could not connect?

So... We are worried about a second script getting in the middle of our two
consecutive queries trying to insert the very same username but we consider
DB server failures are totally impossible once the script begins
successfully?
--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Kenneth Downs]

Alvaro G Vicario wrote:

*** Kenneth Downs wrote/escribió (Wed, 15 Jun 2005 06:42:03 -0400):

How did you connect to a down server to do the INSERT, and why didn't you
report to the user that you could not connect?

So... We are worried about a second script getting in the middle of our
two consecutive queries trying to insert the very same username but we
consider DB server failures are totally impossible once the script begins
successfully?

geez.

<plonk>

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[Andy Hassall]

On Wed, 15 Jun 2005 10:41:39 +0200, Alvaro G Vicario
<al******************@telecomputeronline.com> wrote:

*** Daniel Tryba wrote/escribió (15 Jun 2005 07:46:58 GMT):

So if you are checking the INSERT for failures why prepend a SELECT?

In order to distinguish a SQL error from an unavailable username. I hate
apps that tell you your data is incorrect when DB server is down.

You distinguish between the two using the error code returned from the
database.

A key violation will produce a completely different error code than other
failures. Code for expected errors, producing suitable messages for those, and
handle unexpected errors in a more generic fashion.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool