A good practice for creating human-typable, non-sequential unique id's

Question

I need to create a unique id for our users. I don't want to use an auto_incrementing id, because I don't want users to be able to guess how many users we have, or what the growth rate is.

A UUID is not really an option either, because users will have to re-type the id on a smartphone.

So I'm hoping I can reduce 'brute-forcing' the database as much as possible to find unused ids. What would be a smart way to go about this?

Thank you!

Answer 1

OK, i'll give it another try. Your goals are:

• obfuscate the increment rate of your identifier - not sequential, not guessable, not calculatable
• have that still "usable" for smartphone users, thus long UUIDs or SHA1 hashes are out of scope
• avoid necessity of random guesses / bruteforce apporaches to the database
• still have good database performance with your Foreign Keys

To still have good performance in your database you should keep a standard auto_incrementing integer for your PK. Note that InnoDB orders the rows based on the PK (see "InnoDB clustered Index"), so if you use some kind of magic hash as PK, you will end up with a lot of reordering in your clustered index, giving bad write performance.

I suggest to use an encryption algorithm (encryption, not hashing) to encrypt and decrypt the ID you want to obfuscate. Also, for best obfuscation, you want to use a minimum length for the resulting string. The resulting string should be still usable, so you must use something like base64 to present it user readable.

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 2

If I understand you correctly, you are trying to let users choose their own unique ID from a table where there is not a user registered already. If this is the case then I would have my User table with userID and userName columns. Then return the userIDs 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 their own then run a query that has a where clause which will check whether a specific, user entered, number has a name. You can set the form up by 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 query

$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 being the basics for you. Obviously you will need to run some validation and error checks.

Hope this is what you're after. Let me know if not and will come back with something else.

Answer 3

you can create your record and update the field after with a random uid over a function. the function will be check 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 the existing stuff, you can run this:

UPDATE your_table SET uid = get_unique_uid_for_your_table();

and in the insert statement you can type this:

INSERT INTO your_table
(
    #all_fields

    uid
)
VALUES
(
    #all_values

    get_unique_uid_for_your_table()
);

Answer 4

Create a list of random uniqe numbers (you could use PHP's range() and shuffle() functions), and have it stored in database or even in a txt file. Make sure the list is long enough so that it lasts for some time. Then whenever you need a new ID, just pop 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's to your list, start at 1000000.
shuffle($list); //now it's randomly ordered