March 8, 2012

Convert auto increment ID to 9 digit random serial number

Question by dotmlj

I have a MySQL database with users. Every row has an unique auto increment ID (1,2,3…). Now I need to convert this to a unique and random looking serial number that I can convert back to the ID – all using PHP.

User ID’s go from 1 to 99999999999 (INT(11)).

All serial numbers should have a minimum of 9 digits and never start with a 0.
Users should not easily be able to figure out how to guess a working serial number.

Thank’s 🙂

Answer by Ishtar

You could do some simple ‘encryption’. Take a (‘secret’) prime p 27407 and base 17 (for example). Compute the multiplicative inverse inv of base mod p-1, 12897. wolframalpha can do that for you.

Id to serial number

serial = id^base mod p
serial = 42^17 % 27407 = 24978

Serial to id

id = serial^inv mod p
id = 24978^12897 % 27407 = 42

This can be calculated quickly by exponentiation bysquaring. Only ids between 0 and 27407 can be used, (if not enough take a bigger prime number) and all have a unique invertible serial number.

To increase obscurity you can XOR the result with some value.

It’s not real cryptography, just stupid security through obscurity, but will take most humans quite some effort to crack.

Answer by Starx

I will not recommend doing what you are trying to do.

You see, autoincrement are generally done to represent to constantly avoid redundant data and still maintain readability.

So, instead update your database structure to store hash as well. The structure might be somethign like id, hash, name and so on.

In the hash, you can use any logic

$hash = sha1("secretanswer".$userid);
$trimmedhash = substr($has,0,9); //get a 9 digit random hash

Hashes are one way encryption and it is so for a reason also. Anyways, in order to verify the hash, you can do the same algorithm again

   $userid = "getthissomehow";
   $hash = sha1("secretanswer".$userid);
   $trimmedhash = substr($has,0,9);

   $prevhash = "asfaf23rfs"; //get previously stored hash somehow
   if($trimmedhash == $prevhash) { 
     //valid
   }
...

Please fill the form - I will response as fast as I can!