Good practice for creating unique identifiers for unique person identifiers

Question

I need to create a unique identifier for our users. I do not want to use the auto_incrementing identifier, because I do not want users to be able to guess how many users we have or what the growth rate is.

UUID is also not an option, because users will have to re-enter the identifier on the smartphone.

Therefore, I hope that I can reduce the "rough force" of the database in order to find unused identifiers. What would be a smart way to do this?

Thanks!

Answer 1

Create a list of random uniqe numbers (you can use the PHP range() and shuffle() functions) and save them in a database or even in a txt file. Make sure the list is long enough so that it lasts a while. Then, when you need a new identifier, just enter the first value from the list.

 $list = range(0,999999); //list now contains numbers from 0 to 999999 // if you ever need to add more ID to your list, start at 1000000. shuffle($list); //now it randomly ordered 

Answer 2

Ok, I'll try again. Your goals:

• obfuscation of the increment speed of your identifier - not sequential, not guessable, not calculated
• they are still “usable” for smartphone users, so long UUIDs or SHA1 hashes are beyond the scope.
• avoid the need for random guesses / blastforce applications in the database
• still have good database performance with your foreign keys.

In order to have good performance in your database, you must save the standard auto_incrementing integer for your PC. Note that InnoDB orders PK-based rows (see " Clustered InnoDB Index "), so if you use some kind of magic hash like a PC, you will get a lot of reordering in your clustered index, which will give poor write performance.

I suggest using an encryption algorithm (encryption, not hashing) to encrypt and decrypt the identifier you want to confuse. Also, for better obfuscation, you want to use the minimum length for the resulting string. The resulting string should still be usable, so you should use something like base64 to make it readable by the user.

Try this encryption example:

 function my_number_encrypt($data, $key, $base64_safe=true, $minlen=8) { $data = base_convert($data, 10, 36); $data = str_pad($data, $minlen, '0', STR_PAD_LEFT); $data = @mcrypt_encrypt(MCRYPT_BLOWFISH, $key, $data, MCRYPT_MODE_CBC); if ($base64_safe) $data = str_replace('=', '', base64_encode($data)); return $data; } function my_number_decrypt($data, $key, $base64_safe=true) { if ($base64_safe) $data = base64_decode($data.'=='); $data = @mcrypt_decrypt(MCRYPT_BLOWFISH, $key, $data, MCRYPT_MODE_CBC); $data = base_convert($data, 36, 10); return $data; } $key = "my super secret magic bytes"; $id = 12345678; // obtain_normal_key_from_mysql(); // give ID to user $enc = my_number_encrypt($i, $key); // get ID from user $dec = my_number_decrypt($enc, $key); // fetch from database using normal ID -> 12345678 // demo code for($i=10000; $i<10050; $i++) { $enc = my_number_encrypt($i, $key); $dec = my_number_decrypt($enc, $key); echo "$i -> $enc -> $dec", PHP_EOL; } 

Demo result:

 10000 -> 1RXK468NYes -> 10000 10001 -> QdEov5mjMPA -> 10001 10002 -> 2gsgzWJgD+8 -> 10002 10003 -> 2zwPwhqr9HI -> 10003 10004 -> Xq+kDh1UFuM -> 10004 10005 -> wfwv6TrW9xY -> 10005 10006 -> 1Lck1L0HJ/U -> 10006 10007 -> v+3YY2zfL1A -> 10007 10008 -> 5AmGlqD8byM -> 10008 10009 -> pZBIpPnKXHU -> 10009 10010 -> CAeWdKGkk8c -> 10010 10011 -> fYddnLOSK6U -> 10011 10012 -> na8Ry0erHv8 -> 10012 10013 -> zxNj+ZJVMBY -> 10013 10014 -> gWJWC9VulZc -> 10014 10015 -> 5pR9B79eM/E -> 10015 10016 -> MQtpBhpzHRA -> 10016 10017 -> dW+3nejBEIg -> 10017 10018 -> znB/feM6104 -> 10018 10019 -> RtdRwwRyEcs -> 10019 10020 -> 4cW/OWT140E -> 10020 10021 -> dIvK9VjOevg -> 10021 10022 -> QxLdfrucc/Y -> 10022 10023 -> M0KN3sX10Gs -> 10023 10024 -> 827yFJyDCG4 -> 10024 10025 -> JF/VRj92qL8 -> 10025 10026 -> IXTvn/SCzek -> 10026 10027 -> L4nFwvhgwX8 -> 10027 10028 -> z0lve9nhgDA -> 10028 10029 -> m/UBgZzfIXo -> 10029 10030 -> IfWcrLKTHXk -> 10030 10031 -> n/jPFwKR/9A -> 10031 10032 -> j1mm2kbeWl0 -> 10032 10033 -> cm7mOQMVa6k -> 10033 10034 -> jCUuweEyRME -> 10034 10035 -> LDaMcOWKxjg -> 10035 10036 -> Zcrd5XzhhIk -> 10036 10037 -> j0Yg/fCjyAA -> 10037 10038 -> /LmlvRHmmmg -> 10038 10039 -> t0juuzGSKs4 -> 10039 10040 -> 9CoRCVXaak4 -> 10040 10041 -> tFmImR4j0JM -> 10041 10042 -> nI3Thy51hLg -> 10042 10043 -> mTCJh0/h2mE -> 10043 10044 -> S196xdyb3Os -> 10044 10045 -> ItOyUp+J4Q4 -> 10045 10046 -> DL87SidiOLM -> 10046 10047 -> d+Nw3xBqV44 -> 10047 10048 -> 3YzVelaC4uI -> 10048 10049 -> fAUJVOl6PaU -> 10049 

Answer 3

You can create your record and update the field after a random uid over a function. the function will be checked for uniqueness:

 CREATE FUNCTION get_unique_uid_for_your_table() RETURNS VARCHAR(255) BEGIN DECLARE chars VARCHAR(48) DEFAULT '0123456789bcdfghjklmnopqrstvwxyz'; DECLARE len INTEGER DEFAULT 8; DECLARE new_uid VARCHAR(255) DEFAULT ''; DECLARE i INTEGER DEFAULT 0; WHILE LENGTH(new_uid) = 0 OR EXISTS(SELECT uid FROM your_table WHERE uid = new_uid) DO SET len = 8; SET new_uid = ''; WHILE (len > 0) DO SET len = len - 1; SET new_uid = CONCAT(new_uid, SUBSTR(chars, FLOOR(LENGTH(chars)) * RAND(), 1)); END WHILE; END WHILE; RETURN new_uid; END // 

For existing material, you can run this:

 UPDATE your_table SET uid = get_unique_uid_for_your_table(); 

and in the insert statement you can enter the following:

 INSERT INTO your_table ( #all_fields uid ) VALUES ( #all_values get_unique_uid_for_your_table() ); 

Answer 4

If you understand correctly, you are trying to allow users to select their unique identifier from a table where the user is no longer registered. If so, then I will have a User table with userID and userName columns. Then return the user IDs, where userName is still null.

 $query = "SELECT userID, userName FROM User WHERE userName=''"; $result=mysql_query($query); while ($row = mysql_fetch_assoc($result)) { echo $row['userID']; } 

If you want them to choose themselves, run a query that contains a where clause that will check if a particular user-entered number has a name. You can set the form using $ _POST ...

 <form method="POST" action="checkID.php"> <label for="userName">User Name:</label> <input name="userName" maxlength="40" id="userName" type="text" /> <label for="userID">User ID:(int only)</label> <input name="userID" maxlength="40" id="userID" type="text" /> <input type="submit"> </form> 

and then your checkID.php

 $userName = $_POST['userName']; $userID = $_POST ['userID']; 

and then your request

 $query = "SELECT userID, userName FROM User WHERE userName='' AND userID=$userID"; $result=mysql_query($query); if ($result) { echo "number is available"; $insert = "UPDATE User SET userName=$userName WHERE userID=$userID"; $inserted=mysql_query($insert); if ($inserted) { echo "you have been inserted as: "; echo $userID; echo $userName;; } } 

Well, I see this as the basis for you. Obviously, you will need to do some validation and error checking.

Hope this is what you need. Let me know if not, and come back with something else.