Generate unique identification tokens and store them

Question

For my application I have to generate unique tonkens. The user will be able to access a document concerning him via an url "https:xxxx.com/TOKEN".

The token will be composed of 6 characters in the alphabet "2346789abcdefghjkmnpqrtuvwxyz".

What is the best way to generate these tokens randomly? (I am working on a web application in .NET MVC with an SQL database).

I thought of an SQL stored procedure that generates a random number between 1 and 594823321, then convert it to my custom base 29.

But how do you generate an unused random number?

Thank you in advance for your help.

You can't generate them randomly if they are required to be unique. Imagine the case where you have N available numbers and have used N-1 of them. The last available number is then fixed and not random. — Matthew Watson, Apr 21 '21 at 09:42
This might provide you with some hints and ideas: https://stackoverflow.com/questions/1458468/youtube-like-guid. To ensure uniqueness then store in the DB as a primary key, or use a HashSet in C# — jason.kaisersmith, Apr 21 '21 at 09:42
Try following : Random rand = new Random(); string input = "2346789abcdefghjkmnpqrtuvwxyz"; string output = string.Join("", input.ToCharArray().OrderBy(x => rand.Next()).Take(6)); This produces a string with no duplicates. — jdweng, Apr 21 '21 at 09:44
@MatthewWatson Yes, I thought about this problem, but given the volume of use of the application we should not arrive at this situation for a very (very) long time. — Lams, Apr 21 '21 at 09:49
You could use SQL auto-increment to generate a linear progression of numbers, and then use some method of reordering the bits to generate a number that appears random but will never be repeated. (Probably would want to start the auto-incremented number at higher than zero though) — Matthew Watson, Apr 21 '21 at 09:49
If you want duplicates use this : string output = string.Join("", Enumerable.Range(0,6).Select(x => input[rand.Next(input.Length)])); — jdweng, Apr 21 '21 at 09:51
@MatthewWatson Oh, that's a good idea, I'm keeping it aside, thank you! — Lams, Apr 21 '21 at 09:59

score 0 · Answer 1 · answered Apr 21 '21 at 09:58

Most people use Guid.NewGuid().ToString(); Read more about it here: Microsoft Docs

A GUID is a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required. Such an identifier has a very low probability of being duplicated.

score 0 · Answer 2 · answered Apr 21 '21 at 11:03

Returning 6-character values that do not overlap is quite tricky. As Costa mentions, the canonical solution is to use UUIDs. There are pretty much guaranteed to be unique. But they are not 6 characters.

If they did not need to be "random", you could generate a number and convert it to the format you want:

select concat(substring(c.chars, (v.n / power(len(c.chars), 5)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n / power(len(c.chars), 4)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n / power(len(c.chars), 3)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n / power(len(c.chars), 2)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n / power(len(c.chars), 1)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n / power(len(c.chars), 0)) % len(c.chars) + 1, 1)
             )
from (values (0), (1), (100), (1000), (10000), (100000), (1000000), (10000000), (100000000)) v(n) cross join
     (values ('2346789abcdefghjkmnpqrtuvwxyz')) c(chars);

Here is a db<>fiddle. For the n value, you could use a sequence or if you never need more than one value per second, the number of seconds since some cutoff value.

Note that you could "tweak" the above to make it look more random. For instance, you can multiply v.n times some large random number:

select concat(substring(c.chars, (v.n * 1000117 / power(len(c.chars), 5)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n * 1000117 / power(len(c.chars), 4)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n * 1000117 / power(len(c.chars), 3)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n * 1000117 / power(len(c.chars), 2)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n * 1000117 / power(len(c.chars), 1)) % len(c.chars) + 1, 1),
              substring(c.chars, (v.n * 1000117 / power(len(c.chars), 0)) % len(c.chars) + 1, 1)
             )
from (values (cast(0 as bigint)), (1), (100), (1000), (10000), (100000), (1000000), (10000000), (100000000)) v(n) cross join
     (values ('2346789abcdefghjkmnpqrtuvwxyz')) c(chars);

This is probably good enough for what you want to do.

Absent that, brute force is an option. There are only 594,823,321 different values. This easily fits into a table. So you could generate them and then use the table to be sure you don't get duplicates.

Generate unique identification tokens and store them

2 Answers2