Create a random token, check for a duplicate.

Question

I have a php script that makes a random token (A-Z, a-z, 0-9):

    function token($length) {

    $characters = array(
    "A","B","C","D","E","F","G","H","J","K","L","M",
    "N","P","Q","R","S","T","U","V","W","X","Y","Z",
    "a","b","c","d","e","f","g","h","i","j","k","m",
    "n","o","p","q","r","s","t","u","v","w","x","y","z",
    "1","2","3","4","5","6","7","8","9");

    //make an "empty container" or array for our keys
    $keys = array();

    //first count of $keys is empty so "1", remaining count is 1-6 = total 7 times
    while(count($keys) < $length) {
        //"0" because we use this to FIND ARRAY KEYS which has a 0 value
        //"-1" because were only concerned of number of keys which is 32 not 33
        //count($characters) = 33
        $x = mt_rand(0, count($characters)-1);
        if(!in_array($x, $keys)) {
           $keys[] = $x;
        }
    }

    foreach($keys as $key){
        $random .= $characters[$key];
    }

    return $random;

}

Works perfect, but I want to be able to check a certain database to make sure the same token has never been used before. And if it has it will instantly recreate a fresh token before outputing it.

I know I can use this script to check for a duplicate:

$check = mysqli_query($mysqli, "SELECT ".$table.".token FROM ".$table." WHERE ".$table.".token = '".$random."'");

        if(mysqli_num_rows($check) > 0){

        //ERROR: DUPLICATE TOKEN, CREATE A NEW TOKEN NOW...

        }

I just need help to add it all together, so that if a duplicate is found in the database, it will loop back and try again.

Answer 1

Put the token creation and check into a do … while loop:

do {
    $random = token(10);
    $check = mysqli_query($mysqli, "SELECT ".$table.".token FROM ".$table." WHERE ".$table.".token = '".$random."'");
} while (mysqli_num_rows($check) > 0);

By the way: You can simplify your token function as follows:

function token($length) {
    $characters = array(
        "A","B","C","D","E","F","G","H","J","K","L","M",
        "N","P","Q","R","S","T","U","V","W","X","Y","Z",
        "a","b","c","d","e","f","g","h","i","j","k","m",
        "n","o","p","q","r","s","t","u","v","w","x","y","z",
        "1","2","3","4","5","6","7","8","9");
    if ($length < 0 || $length > count($characters)) return null;
    shuffle($characters);
    return implode("", array_slice($characters, 0, $length));
}

Answer 2

Use a do while loop:

do {
    $random = token(LENGTH);
} while (mysqli_num_rows(mysqli_query(YOUR_LOOKUP_QUERY)));

PS: You may simplify your token code:

function token($length) {
    $characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz123456789';
    return substr(str_shuffle($characters), 0, $length);
}

Answer 3

You can wrap all of your code in a while loop that will repeat the code until you find one that is valid (isn't repeated). For instance;

do { 
  $nextToken = token(32);
  $check = mysqli_query($mysqli, "SELECT ".$table.".token FROM ".$table." WHERE ".$table.".token = '".$nextToken."'");
} while ( mysql_num_rows($check) > 0 );

This will load up a random token repeatedly, until you find a token that doesn't generate any rows from the mysql_num_rows function (ie. has not been repeated before).

Answer 4

Its a lot more efficient to setup your database table using a unique key on the token - then try inserting the new value - if the query succeeds you've got a new value, if it returns error code 1022 the key already exists - if it returns a different error - something else went wrong.

C.

Answer 5

A do-while loop would be perfect for this, because you want to do 'generation of a new token' while the generated token is not unique.

do {
    $token = token(...);
} while (token_exists($token));

As you've done with your token function, you should put the database lookup code into a new function, so that the main algorithm (keep generating a new token until it's unique) is clearly stated. I've assumed in the above code that the function would be called token_exists, and would look something like this:

function token_exists($random) {
    $check = mysqli_query($mysqli, "SELECT ".$table.".token FROM ".$table." WHERE ".$table.".token = '".$random."'");
    return (mysqli_num_rows($check) > 0);
}

Answer 6

 mysql> CREATE TABLE tokens (id integer primary key auto_increment, token varchar(36), UNIQUE(token));

<?php
  mysqli_query('INSERT INTO tokens (token) SELECT UUID();');
  $r = mysqli_query('SELECT token FROM tokens WHERE id = '.mysqli_insert_id());
  $result = mysqli_fetch_row($r);
  echo $result[0];

Answer 7

I don't know what the context of your scenario is, but it might be worth looking into using a more pre-fabiracted method to generate your unique tokens (Unique ID on the table for instancE?)

That being said, if you want the fine-tuning control over the format of your token, it might be worth looking into wrapping the uniqueness-generation routine into SQL procedure, so that the determination is made in one transaction, rather than kicing off a separate communication request for every iteration.

Something like this generates 1000 "unique" tokens, but it could be easily adjusted to loop until the code is truly unique rather than a fixed number of loops (and it can probably be cleaned up a bit :) ):

DECLARE @token as varchar(6),
        @numberOfLoops int,
        @currentLoop int,
        @pool varchar(50)       
-- Initialize 
SET @token = ''
SET @numberOfLoops = 1000
SET @currentLoop = 0
SET @pool = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890';

WHILE (@currentLoop < @numberOfLoops)
BEGIN
    SET @token = 
        SUBSTRING(@pool, CAST((RAND() * LEN(@pool) + 1) as int), 1) + 
        SUBSTRING(@pool, CAST((RAND() * LEN(@pool) + 1) as int), 1) + 
        SUBSTRING(@pool, CAST((RAND() * LEN(@pool) + 1) as int), 1) + 
        SUBSTRING(@pool, CAST((RAND() * LEN(@pool) + 1) as int), 1) + 
        SUBSTRING(@pool, CAST((RAND() * LEN(@pool) + 1) as int), 1) + 
        SUBSTRING(@pool, CAST((RAND() * LEN(@pool) + 1) as int), 1);
    print @token;


    SET @currentLoop = @currentLoop + 1;
END

There are some major caveats to consider here, though. The biggest concern I can think of with tokens you roll yourself is the problem of temporary uniqueness. Depending on when you're assigning the token, it may be unique w/r/t your data source one moment, but then --- potentially -- claimed the next if you're not careful.