How would I go about generating this... I want to keep my primary key sequential and have a 12 digit unique pin generated for each new object added to the database.
The reason it cant just be autoincrement is i don't want the sequential numbers to be easily guessable.
It needs to be integer numbers, because I'm going to have verification codes that need to be dialed on a phone pad.
Use a concatenation of a unique incremented number and a randomly generated number.
The unique incremented number ensures that the result is unique, and the randomly generated number makes it hardly guessable.
This is simple and guaranteed to have no collision (1). The result is incremental, partly random, and non-predictable (provided that the random number part is generated with a good PRNG).
(1): You have to either pad id and random with zeros, or to separate them with some non-digit character.
With a MySQL db, this translates to:
CREATE TABLE foo (
id int not null auto_increment,
random int not null,
...
primary key (id)
);
Maybe you can use UUID_SHORT(). Not 12 digits long, but still could be a viable option:
mysql> select uuid_short();
+-------------------+
| uuid_short() |
+-------------------+
| 22048742962102272 |
+-------------------+
So:
INSERT INTO `table` (`id`, `text`) VALUES (UUID_SHORT(), 'hello world!');
Note: If you really want to have exactly 12 digits, then don't even try to substring the result, if would not ensure the uniqueness of the identifier and may cause collisions.
<?php
$allowed_characters = array(1,2,3,4,5,6,7,8,9,0);
for($i = 1;$i <= 12; $i++){
$pass .= $allowed_characters[rand(0, count($allowed_characters) - 1)];
}
echo $pass;
?>
Generally, I will prefer to do something a little bit more low tech. I obscure the values in PHP and leave them as auto-incrementing in JS.
$seeds = array( /*series 100 of very large >= 10-digit numbers*/ );
$seedID = rand( count( $seeds ) ); // randomly choose one of those.
// a string combination which represents the ID + some hash.
$id = bcadd( $seeds[ $seedID ], /* id retrieved from database */ );
// make sure we've not accidentally passed the 10^12 point
$id = bcmod( $id, 1000000000000 );
// make sure to pad
$id = str_pad('' . $id, 3, "0", STR_PAD_LEFT);
$outID = substr( $id, 0, 5 ) . $seedID . substr( $id, 6 );
Then, when receiving the ID from the user:
$seedID = substr( $outID, 6, 2 );
$tmpID = substr( $outID, 0, 5 ) . substr( $outID, 8 );
$id = bcsub( $tmpID, $seeds[ $seedID ] );
// we passed the modulus se we need to add this back in.
if( $id < 0 ) $id = bcmod( bcadd( $id, 1000000000000 ), 1000000000000 );
This will basically mean that you're simply obscuring whatever number you want -- you can use auto_increment with impunity!
One method would be to take your primary key value, salt it with a few other random-ish bits of data (username, current time, process ID, fixed string, etc...) and hash it with md5 or sha1. You then take the hash string and convert it into digits via basic string operations. That'll give you a relatively unique numeric code.
of course, with only 12 digits, you're far more likely to end up with a collision than by using the raw string hash - but since you're requiring this to be dialed on a keypad, it's an acceptable tradeoff.
If the pins are invalidated/deleted after usage, then the collision chances will be much reduced.
You want two things
If you want both the things from same sequence you will run out of luck (literally) Uniqueness is guaranteed by having large sample space + random + check-unique. Which means, the actual number could be anywhere in between the sample space.
But if you want unique + incremental property, you are dividing sample space by 2. In 64 tries you would have reduced a 64 bit int sample space to 1 bit sample space.
Good luck !
All solutions so far lack one thing essential to your application: Security!
You said you will be using these numbers as a (product) verification code - so you really, really want this to be unpredictable, otherwise it will get exploited.
Neither MySQL's built-in RANDOM function nor any of the random functions PHP provides today are secure random functions. They behave pseudo-randomly, alright, but they all are predictable!
Your only chance is to whip up something of your own using /dev/urandom on a *nix machine or leveraging the Crypto API on Windows. OpenSSL does provide secure random numbers based on these mechanisms - you could reuse this either in a C extension for PHP or by reading the output from a command line script called from PHP. See also this answer.
About your requirement for the numbers to be sequential - is this really so important? It does complicate things enormously. Otherwise you would be good to go with a simple secure 6 byte random number encoded to a string using hex encoding (yielding a 12 character string). Although I would recommend making it 10 bytes and 20 characters to be safer.
But if you want to be sequential, which I interpret as monotonously increasing (because a simple +1 would be trivially predictable), this makes things just so much more complicated. And you don't gain anything from this complexity, the only thing that might happen is that you break the security by inventing some obscure scheme that is easily exploitable.
My suggestion: Add another column that acts as a plain old auto-incremented ID and add the code as a random number constructed as above as a separate column. As far as I see, there's no need to require the product activation code to be the ID at the same time.
