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]

Re: Checking for username...

>How do I check in a mySQL table called 'Users' on column user_name when[color=blue]
>registering a new user_name to make sure the new 'user_name' does not
>already exist in that column?[/color]

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]

Re: Checking for username...

*** Domestos wrote/escribió (Mon, 13 Jun 2005 20:50:51 GMT):[color=blue]
> 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?[/color]

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]

Re: Checking for username...

Alvaro G Vicario пишет:[color=blue]
> *** Domestos wrote/escribió (Mon, 13 Jun 2005 20:50:51 GMT):
>[color=green]
>>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?[/color]
>
>
> I normally do something like this:
>
> SELECT id FROM users WHERE username='john'
>
> If zero rows are returned, the username is available.
>[/color]
I guess better use not ='john' but like 'john', because of case-sensitive

[Kenneth Downs]

Re: Checking for username...

Domestos wrote:
[color=blue]
> 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[/color]

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]

Re: Checking for username...

Kenneth Downs пишет:[color=blue]
> Domestos wrote:
>
>[color=green]
>>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[/color]
>
>
> 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.[/color]
checking for the SQL error is not a good idea, actually.
Well known programing prupose to use select before insert.

[Gordon Burditt]

Re: Checking for username...

>> The database can do this by implementing a unique constraint on the column[color=blue][color=green]
>> 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.[/color]
>checking for the SQL error is not a good idea, actually.
>Well known programing prupose to use select before insert.[/color]

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]

Re: Checking for username...

Ivan Omelchenko 608308824 wrote:
[color=blue]
> Kenneth Downs ?????:[color=green]
>> Domestos wrote:
>>
>>[color=darkred]
>>>Hi all,
>>>
>>>How do I check in a mySQL table called 'Users' on column user_name when
>>>registerin g a new user_name to make sure the new 'user_name' does not
>>>already exist in that column?
>>>
>>>TIA[/color]
>>
>>
>> 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.[/color]
> checking for the SQL error is not a good idea, actually.
> Well known programing prupose to use select before insert.[/color]

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]

Re: Checking for username...

*** Gordon Burditt wrote/escribió (Tue, 14 Jun 2005 13:20:31 -0000):[color=blue]
> 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.[/color]

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]

Re: Checking for username...

>> This has the well-known problem that you may find the name not[color=blue][color=green]
>> in use, insert it, and find the name has been taken before you
>> got to it - unless you've gotten a lock first.[/color]
>
>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[/color]

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).
[color=blue]
>cute but, what's the probability of such a race condition if both queries
>(SELECT and INSERT) and consecutive in your code?[/color]

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]

Re: Checking for username...

*** Gordon Burditt wrote/escribió (Tue, 14 Jun 2005 17:55:24 -0000):[color=blue][color=green]
>>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[/color]
>
> That assumes that you CHECK for errors on the insert, which "obviously"
> can't happen since you already checked with SELECT, right? Wrong.[/color]

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.
[color=blue]
> 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).[/color]

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 ;-)

[color=blue]
> If your site is popular enough, and lasts long enough, it's near
> 100% to happen eventually.[/color]

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.
[color=blue]
> 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.[/color]

Yeah, sure :)
[color=blue]
> 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.[/color]

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]

Re: Checking for username...

>>>If username field is defined as unique it shouldn't be a great issue. User[color=blue][color=green][color=darkred]
>>>will get a generic error rather than a 'user not available' message. Not so[/color]
>>
>> That assumes that you CHECK for errors on the insert, which "obviously"
>> can't happen since you already checked with SELECT, right? Wrong.[/color]
>
>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.[/color]

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.
[color=blue][color=green]
>> 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).[/color]
>
>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 ;-)[/color]

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.
[color=blue][color=green]
>> 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.[/color]
>
>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 ;-)[/color]
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]

Re: Checking for username...

*** Gordon Burditt wrote/escribió (Tue, 14 Jun 2005 19:32:23 -0000):[color=blue][color=green]
>>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.[/color]
>
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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

[color=blue][color=green][color=darkred]
>>> 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.[/color]
>>
>>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 ;-)[/color]
> It doesn't take nearly that much to generate intelligent error
> messages or use correct locking.[/color]

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]

Re: Checking for username...

Alvaro G Vicario <alvaro_QUITAR_ REMOVE@telecomp uteronline.com> wrote:[color=blue]
> 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.[/color]

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]

Re: Checking for username...

*** Daniel Tryba wrote/escribió (15 Jun 2005 07:46:58 GMT):[color=blue]
> So if you are checking the INSERT for failures why prepend a SELECT?[/color]

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