1

I have a table with an auto-incrementing primary key, but I now need to change this so that the primary keys aren't as guessable by someone typing in consecutive numbers. To create the random primary key I think I'm going to create a table which contains all 6 digit numbers shuffled into a random order and keyed against 1 to n.

My question is, without changing all of my existing queries, how can I use make the link between the auto-incremented key and the 6 digit number at the corresponding index in the second table?

As there is only one query to insert to this table I'd like to change this to keep things simple. I'd like to query the next number in the second table and insert this into the primary key column in the first table. The problem I forsee is that two queries may hit the server at the same time and may return the same 6 digit number from the second table. What are my options for getting around this issue in MySQL?

Josh
  • 3,445
  • 5
  • 37
  • 55

3 Answers3

1

Rather than doing all that, why not use a UUID? You can modify your tables adding a column of this type to your tables and you do not need to worry about generating anything or keeping track of it.

https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

Schleis
  • 41,516
  • 7
  • 68
  • 87
  • The problem with UUID is that it's not user friendly. The primary key is being used as a way for real humans to navigate through the app. Having a 40-odd character key isn't going to win me any favours with the users. – Josh Oct 11 '13 at 15:54
  • If you are using the id for navigation, why are you concerned with people guessing it? – Schleis Oct 11 '13 at 18:16
  • The brief was to have users create games which their friends can join using a pin, which is currently the primary key of the existing table. Now the brief has changed to make the pin less guessable by making it non-sequential. Nothing like working with moving goal posts. – Josh Oct 12 '13 at 10:55
1

Thanks for the suggestions, but no one actually answered my question as it stands.

My table of pins looks like: 

id |  pin   | used
 1   123456    1
 2   258464    0
 3   842364    0

And I use the query to get the next pin:

SELECT id, pin FROM game_pins WHERE used = '0' ORDER BY id LIMIT 1;

And then I update that row to SET used = '1'.

To prevent two people getting the same pin I use InnoDB transactions, which in CodeIgniter I've done by wrapping everything in:

$this->db->trans_start();

...

$this->db->trans_complete();
Josh
  • 3,445
  • 5
  • 37
  • 55
  • this answer is better than tometzky's answer if you require that pins for deleted games never get reused. If however this is not a requirement, then you are just wasting disk space. In generating this table you will have to fill it with random pins and to avoid duplicates you would have to potentially generate multiple random pins in order to find a unique one, in exactly the same way as tometzky. – user3338098 Jun 15 '15 at 15:58
  • you say tometzky's answer is to slow, but the run time is expected run time O(1), however your answer has _worst case_ time O(1), which means your algorithm has "real-time" guarantees, which is another positive aspect to your algorithm, however it is unlikely you need such performance for a game. – user3338098 Jun 15 '15 at 16:04
0

Ditch this additional table and just use rand() to generate random number between 100000 and 999999. Pick this random number until you find unused. It doesn't look like you need to do it very often, so it doesn't have to be very fast. But queries will be faster, as there's not need for additional join.

But remember that it's not any more secure than simple autoincrement - user can just try all 900k possibilities in several hours.

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • Unfortunately this will need to be done often, see http://stackoverflow.com/questions/19322234/mysql-using-random-number-as-primary-key?noredirect=1#comment28639889_19322292 for an explanation. Using `rand()` until a number is found that hasn't been used will be too slow. – Josh Oct 12 '13 at 10:56
  • That should not be a problem. I'd just use `rand()` until about about 50% percent of numbers will be used up and then I'd just add one "9" digit to the upper limit. This way a probability of drawing used number `X` times will be always less than `1/2^X`, like getting tails `X` times in a row while tossing a coin. In worst case you'd only try twice on average. – Tometzky Oct 12 '13 at 14:03