1

Hello to this great community,

I have written the following lines of code.

mysql_query("DELETE FROM token_db WHERE date < ".strtotime('-1 day'));

$token = mt_rand(1000, 9999);                                                                      
$result = mysql_query("SELECT token FROM token_db WHERE token = $token");

while (mysql_num_rows($result) == 1)
{
$token = mt_rand(1000, 9999);                                                                      
$result = mysql_query("SELECT token FROM token_db WHERE token = $token");
}

mysql_query("INSERT INTO token_db (date,token) VALUES('$date','$token')");

This script generates me tokens which exist max. 24 hours in the database. How can I add the possibilty to extend the token by one digit (e.g. 5-digit) if all tokens (e.g. 4-digit) are in use? THANK YOU!

flix
  • 15
  • 2

2 Answers2

1

You need to perform a count on the token db and count token entries.

 mysql_query("SELECT count(token) FROM token_db");

If they reach a predefined limit like say 9000 or your 9999 then just

 $token = mt_rand(10000, 99999);

But I would rather suggest to increase your token size if you do expect larger numbers to avoid handling of scenarios which are more or less common for your situation.

What is your typical use case? Do you already expect larger sizes?

Anyway your entropy is quite small. With a full database (say 9000 entries) you will have a very hard time finding a valid token. The application would hang, the user would have to wait while your script hammers the database.

If you expect many tokens, you could think about pre-filling the database with tokens without a date. Then you could retrieve a randomized set of tokens (LIMIT 1 or 10) where no date has been set and use this.

You try to achieve scalability by this but I doubt that it will work that well, except you increase the token digit size when your DB has still 50% "space"

Edit: Another problem might be worth mentioning: Do you protect any important resource by such a token system? You have chosen a token which is very small, the token collision has been discussed but what has been left out is the ability of a user to guess a token. If you protect sensitive information by this way and you read the token from an url (e.g. you sent the generated token to the user via mail, etc) than a malicious user may impersonate another by guess a token. This is very likely to occur if you have your 4-digit tokens used up. That's where uniqid and other large entropy algorithms kick in.

Samuel
  • 6,126
  • 35
  • 70
  • +1 for the concept. I think that would still be a bit overkill compared to a simple `uniqid()`, but I like alternative solutions – Martijn Feb 04 '14 at 10:11
  • Thank you for your long answer. Its a good idea to prefill the database to increase the speed. And if the all tokens from 1000 to 9999 are in use, the database is filled up again with 10000 to 99999, right? The scenario is an easy communication between users via that token. To keep things simple I would like to use only digits. I would expect more tokens if I reduce the lifetime of a token, therefore the automation is important. – flix Feb 04 '14 at 10:26
  • @flix, Well you could pre-fill them but filling another 90000 seems like a large step. I would suggest an offline process, like a cron (requires access to the cron facility, more info: http://stackoverflow.com/questions/18737407/how-to-create-cron-job-using-php ) that checks how many free tokens are in a database, if there are less than 100, 1000, or what ever suits you, then the script should generate another set of 100 or 1000, etc. You will leverage the db size and access to new tokens by this. – Samuel Feb 04 '14 at 10:30
  • @flix, I've edited my post and added some possible problem about user guessing the token. – Samuel Feb 04 '14 at 10:36
  • Yes, I was thinking of that aswell. The token only protects a simple system to rate comments and it is given directly (they read it on the display and enter) to the users. And if I reduce the lifetime of a token, I think i would be fine. So the bad user would have to guess the token and that token should be valid for that certain time, the bad user tries to log in. – flix Feb 04 '14 at 10:44
1

Something along these lines I guess:

$result = mysql_query("SELECT count(id) as numRows FROM token_db"); // credit for the edit to Samuel
$fetch = mysql_fetch_assoc($result);
$foundRows = $fetch['numRows'];

$token = $foundRows===(9999-1000) ? mt_rand(10000, 99999) : mt_rand(1000, 9999);

I've added the braces to clarify what is happening, and used 9999-1000 to show you how I got to the number (the max-min of the mt_rand). If set it to the column id because those are often index, integers and fast :)

You do the test before the while.

And the usual: Start using mysql*i* functions

An better, more permanent solution would be uniqid():

$token = uniqid();

That will always be an unique id (surprise). No need for check that might get complicated and no maximum tokens (at least not for a long while),

Martijn
  • 15,791
  • 4
  • 36
  • 68
  • Imho this is a bad advice. Your query would theoretically fetch 9999 unnecessary tokens where you only need a number. Rely on mysql functionality instead and use `count()` – Samuel Feb 04 '14 at 10:06
  • I was just updating it, but thanks for the notice :) – Martijn Feb 04 '14 at 10:07
  • I second uniqid as it reduces probability of a collision but then the concept must be revised anyway as it will require a string type, which also need to be indexed as it is used for retrieval (which should be done anyway here, I guess) – Samuel Feb 04 '14 at 10:12
  • Thanks for your answer! Of course it would be perfect to use uniqid, but to keep things simple for the user I would like to use only digits. – flix Feb 04 '14 at 10:19
  • I still think uniqid is the way to go, people can copy/paste, but otherwise I recommend Samuel's solution. In the long run its saver and its a lot faster in large datasets – Martijn Feb 04 '14 at 10:23