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!

This post could help you further i think. http://stackoverflow.com/questions/307486/short-unique-id-in-php — Paolo_Mulder, Feb 27 '12 at 13:01
'human-typability' will grow if you use the patter ([consontant][vocal])* and only small letters. — user1027167, Feb 27 '12 at 13:45
why don't you let the user choose it's own username, which is your ID, what your are looking for? Your user might have also other apps on smartphone, you would bother him to remember another id/password. A UUID could be used for REST requests, because you wouldn't want to use the primary ID for any communication to user client or partners. — vik, Feb 27 '12 at 14:07

Kaii · Answer 1 · 2014-05-14T17:23:32.027

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

I like this solution, but the simplicity of a seed table appeals to me. I don't actually have a need for decryption, because I'll just store the value in a field. — Evert, Feb 27 '12 at 14:32
@Evert i just wanted to demonstrate a scalable approach. the simplicity of the seed table appeals to me as well. but i don't like the need to "re-seed" it when the upper limit is reached. the encryption solution is 100% complete obfuscation and also hides the range of ID's. — Kaii, Feb 27 '12 at 14:38
Yea, but for what it's worth.. I did upvote you :) It's a very clever solution. — Evert, Feb 27 '12 at 14:43
FWIW: Maybe it would be better to use Base32 instead of Base64. This eliminates handling problems by humans: No case-sensitivity, no ambigious characters (0 vs O, 1 vs l), no special characters, ... But it will make the string much longer. — Kaii, Sep 11 '16 at 17:07

score 7 · Accepted Answer · answered Feb 27 '12 at 13:01

7

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

answered Feb 27 '12 at 13:01

Mchl

61,444
9
118
120

2

An extension to this would be to map the generated ID using the S/KEY dictionary to make it really easy to type (32 bit would mean 3 short words) faqs.org/rfcs/rfc1760.html has the dictionary. – Joachim Isaksson Feb 27 '12 at 13:04
cool and simple solution, but the table will not be loked. whats happen on two or more insert-requests at the same time? – silly Feb 27 '12 at 13:09
If you worry about that, transactions are there for you. – Mchl Feb 27 '12 at 13:26
transactions in combination with contraints. – user1027167 Feb 27 '12 at 13:29
1

This turns difficult if you have nearly a billion ID's. For instance, the file will weight GB's. – Jacob Sánchez May 09 '20 at 01:48

silly · Answer 3 · 2012-02-27T13:06:28.583

4

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()
);

edited Feb 27 '12 at 13:06

answered Feb 27 '12 at 12:58

silly

7,789
2
24
37

Very complete answer. 'Deterministic' is incorrect in this case though :) – Evert Feb 27 '12 at 13:04
thats a bruteforce approach as well .. implemended in SQL not PHP, though. – Kaii Feb 27 '12 at 13:05
But it also seems this method will actually brute-force a solution until it finds a number. This wouldn't scale really well; so I think I would prefer a seed-table as suggested by @Mchl. – Evert Feb 27 '12 at 13:05
if the column uid has an index, then the database doesn't make a 'brute-force', but a binary search and scales much much better. if the column uid has an 'unique constraint' then you only have to fetch the error and try with the next random number – user1027167 Feb 27 '12 at 13:41
also the possibilty that the outer while-loop runs a second time is very very low – user1027167 Feb 27 '12 at 13:50

Philip Bevan · Answer 4 · 2012-02-28T09:01:27.417

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.

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

4 Answers4

Linked