MySQL / MariaDB generate secure sequence

Question

What is the best way to generate a secure sequence of characters on MySQL / MariaDB?

I want to use such a sequence for a user verification process where the user will get an E-Mail that contains a link like this: www.myawesomewebsite.com/verify/<sequence> Obviously, this sequence should not be guessable / predictable, which is also the reason why I do not use the builtin function UUID() (see here).

Possible duplicate of [How to generate a UUIDv4 in MySQL?](https://stackoverflow.com/questions/32965743/how-to-generate-a-uuidv4-in-mysql) — Nico Haase, May 15 '19 at 15:29
Not a duplicate, the answer says that the presented method is not cryptographically secure. — Lehks, May 15 '19 at 16:20
For what you are doing it is more than secure enough. You are only using it for a verification process not to store state secrets. — Dave, May 15 '19 at 17:01

score 0 · Answer 1 · answered May 15 '19 at 22:28

In the case you are using them (putting the verification sequence in a URL), each verification sequence has to be absolutely unique (there has to be no chance that one verification sequence accidentally verifies a different user), unless the user must reenter their data at the URL given to them, in which case the verification string need not be unique. To generate a unique identifier, you should combine—

a unique part, which in this case can be the record number of the user in question, with
a random part, which is a random number generated with a cryptographic random number generator (e.g., random_int in PHP 7 and greater).

See also this question.

Also, since the verification string will appear in a URL which has to be transmitted to the server via HTTP, you ought to use HTTPS to transmit that sequence.

score 0 · Answer 2 · answered May 26 '19 at 05:07

How about

MD5(CONCAT(user_id, 'a secret salt'))

If you have 9 trillion users, there is one chance of 9 trillion of two users having the same MD5. But it is 32 hex digits.

The builtin UUID is "type-1", which implies that it has a time component. Notie how much of two uuids is the same:

mysql> SELECT UUID(), SLEEP(1), UUID()\G
*************************** 1. row ***************************
  UUID(): b1a32a13-7f73-11e9-baa3-e4a7a03bbe24
SLEEP(1): 0
  UUID(): b23bc508-7f73-11e9-baa3-e4a7a03bbe24

Even so, 7 out of 32 characters are different after only 1 second.

Here's a millisecond delay:

mysql> SELECT UUID(), SLEEP(0.001), UUID()\G
*************************** 1. row ***************************
      UUID(): 074ca3bc-7f74-11e9-baa3-e4a7a03bbe24
SLEEP(0.001): 0
      UUID(): 074ccdc5-7f74-11e9-baa3-e4a7a03bbe24

Running UUID on different machines will yield a bunch of other digits being different.

MySQL / MariaDB generate secure sequence

2 Answers2