Hello all -

I have a table with a column that has a UNIQUE constraint. This column
is a code number that a user uses to log into a website.

The value is only good for 2 days, so I figure 5 characters is long
enough to keep someone from guessing a code. Also, since we don't want
someone guessing codes from a sequence, I'm using the RAND() function
( along with a few others to create a zero-padded five digit number )
to get a random number:

INSERT INTO Cards ( code_number ) VALUES ( LPAD( FLOOR( RAND() *
100000 ), 5, '0' ) )

My concern is that I could get a failed insert if the RAND() guesses a
number that happens to exist already in the table. Is this a concern?
How could I fix it without using a pattern, or referencing existing
codes, so that the user couldn't guess the pattern?

Bonus question: Is there a control statement that I could use in PHP
to continuously re-try my existing statement on failure, until RAND
comes up with an unused code number?

>I have a table with a column that has a UNIQUE constraint. This column
>is a code number that a user uses to log into a website.
>
> The value is only good for 2 days, so I figure 5 characters is long
>enough to keep someone from guessing a code.

I presume, then, that you periodically (e.g. hourly) remove old
records, because if you leave them around, you'll violate the UNIQUE
constraint.

>Also, since we don't want
>someone guessing codes from a sequence, I'm using the RAND() function
>( along with a few others to create a zero-padded five digit number )
>to get a random number:

So how many users do you expect to sign up in 2 days? 5? You're
very safe (but a screwup is still not impossible). 20000? You're
virtually guaranteed to screw up at least every 2 days. 200000?
You're absolutely guaranteed to screw up at least half of them.


This is similar to the "birthday problem". The chances of at least
two people having the same birthday (month and day) in a group of
25 is about 50%, as I recall, even though you're using less than
7% of the days of the year.

>INSERT INTO Cards ( code_number ) VALUES ( LPAD( FLOOR( RAND() *
>100000 ), 5, '0' ) )
>
>My concern is that I could get a failed insert if the RAND() guesses a
>number that happens to exist already in the table. Is this a concern?

It depends. Is occasionally blowing up acceptable? For a web site
intended to look professional, the answer is certainly no. What
are the consequences of a breakin? Someone posts some SPAM in a
message? Not that big a deal (and real users might do that anyway).
Does this site offer gambling games with real money? You'll go
broke very quickly.

>How could I fix it without using a pattern, or referencing existing
>codes, so that the user couldn't guess the pattern?

RAND() is not cryptographically secure. This means it may not be that
hard to guess a generated number given a previous one (Joe Evil signs up
himself to get a number, then uses it to guess others).

>Bonus question: Is there a control statement that I could use in PHP
>to continuously re-try my existing statement on failure, until RAND
>comes up with an unused code number?

Look at something like mysql_affected_rows() after your query. If
it's 0, it failed, try again. Either that or you've got other
problems, like a spelling error in the query, or you're out of disk
space, or something else is broke. Perhaps if you fail more than a
dozen times, you should declare the page broken.