Generating Unique Values for MySQL Unique Fields

Question

When a user signs up to my website, for example, I create a unique string of length 40 and insert it into the database. We then send an email to the newly signed up user containing this unique string in the form of an URL, and this all works fine. But how do you create and insert this signup entity ensuring that the field with the unique property is actually unique?

My current approach is as follows:

$key = '';
$repeat = true;
while ($repeat) {
    $key = generate_hash(40);
    $is_unique = (count($model->get_by_key($key)) === 0) ? true : false;
    $repeat = ($is_unique) ? false : true;
}
// At this point I know the key is unique and can insert it...

I am not happy with this code, as I try to avoid while loops in general. Is there a better way to achieve what I am doing here?

The bottom line is that the MySQL field containing these keys has the unique property and I need to know that no unique-violations will occur when inserting these keys.

Have a look at [`UUID()`](http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid) — M Khalid Junaid, Feb 03 '15 at 09:37
Does this string have to be (globally) unique? Or do you want the nonce (very) hard to be guessed? Since you mentioned [new user,signup,email] my guess is: the latter. — VolkerK, Feb 03 '15 at 09:42
These keys should be unique. The signed up user clicks on the link containing this key in his email and the key is used to determine which user account to unlock. This is just one example of where unique keys are used and there has to be a better way of generating them — Donal.Lynch.Msc, Feb 03 '15 at 09:48
In the example you've described the nonce doesn't have to be globally unique - just hard to be guessed. `please confirm your email account by following the link: http://.....?userid=&nonce=`, just like debit cards and atm machines: There isn't only one card with a specific pin, but having a specific card (id) its hard (enough) to guess the corresponding pin. — VolkerK, Feb 03 '15 at 09:52
Ok, so the keys dont need to be unique here. And I should ensure that the presented nonce & userid match in the db before unlocking the user account. This approach is definitely better - thanks! — Donal.Lynch.Msc, Feb 03 '15 at 10:05
correct. random/hard-to-guess + unique: hard problem; hard-to-guess+something-unique-but-not-secret: eas...ier. — VolkerK, Feb 03 '15 at 10:07

iivannov · Accepted Answer · 2015-02-03T11:08:25.710

Ok, basically you have two approaches.

Let the DB handle this and use UUID() to create a unique identifier that you can use in the emails.

According to your MySQL version you have some options:

A. With MySQL 4.1 and above you can use the function directly with INSERT INTO:
```
INSERT INTO table_name 
    (unique_id, field1, field2) 
VALUES 
    (UUID(), "smth", "smth")
```
B. With MySQL 5.0 you can create a TRIGGER and have the unique field automatically filled upon insertion:
```
CREATE TRIGGER 
unique_id
BEFORE INSERT ON 
table_name 
FOR EACH ROW
SET NEW.unique_id = UUID()
```
You can check programatically if the the generated id is really unique. The same you are doing now but you can refactor the code a little bit like so:
```
$key = generate_hash(40);
while ( count($model->get_by_key($key)) ) {
    $key = generate_hash(40);
}

//here you have an unique id in $key 
```

score 0 · Answer 2 · answered Feb 03 '15 at 09:51

0

There are built in unique id functions in almost every language for that.In php,

echo uniqid();

answered Feb 03 '15 at 09:51

Sahil Jain

177
2
11

score 0 · Answer 3 · answered Feb 03 '15 at 10:01

0

You can generate 256^40 different 40-characters strings. Sun will explode before you generate 2 identical strings.

answered Feb 03 '15 at 10:01

Bioukh

1,888
1
16
27

score 0 · Answer 4 · answered Feb 03 '15 at 10:20

You can use md5(time()) as your unique value instead. md5 of time wouldn't be same for each user submitting.

$time = time();
$md5 = md5($time);

As long as md5() just containing 32 string length, you can add an unique word after the md5().

$unique = $md5."uniq1234";

Now you have 40 unique string length. At last you can add $unique as your unique value in database

score -1 · Answer 5 · answered Feb 03 '15 at 09:51

You can use this function to generate uniqueID ,

function keygen($length=10)
{
    $key = '';
    list($usec, $sec) = explode(' ', microtime());
    mt_srand((float) $sec + ((float) $usec * 100000));

    $inputs = array_merge(range('z','a'),range(0,9),range('A','Z'));

    for($i=0; $i<$length; $i++)
    {
        $key .= $inputs{mt_rand(0,61)};
    }
    return $key;
}

echo keygen(10);

Generating Unique Values for MySQL Unique Fields

5 Answers5