3

I'm working on a php newsletter script. I've got a MySQL table of subscribers where each user has a UUID assigned and wondered if using it in the unsubscribe link would be secure enough.

I build it by replacing the href attribute together with the query and the UUID that matches the user's email:

// Get UUID based on submitted email

$link = "href='https://example.com/unsubscribe.php?id=$uuid'>unsubscribe</a>)";

$html_message = file_get_contents('welcome_template.html');
$html_message = str_replace('href="">unsubscribe</a>', $link, $html_message);

// Headers and stuff

mail($email, $respond_subject, $html_message, $headers, '-fhello@example.com');

By secure enough I mean if it is considered a bad practice and if it's "easy" to predict the UUID after some queries.

This answer to a similar question states that

A simpler method is a random string of a specific length (e.g. 30 chars) stored in a table with a unique constraint on that field.

Is a UUIDa valid random string?

ajmnz
  • 742
  • 7
  • 19
  • 1
    What type of UUID are you using? If using only the default `UUID()` function from MySQL, then no it is not secure. You should include a second verification to match against the `UUID`, such as email, hash or random number, otherwise a number generator could easily brute force the UUID's, by simply changing the first 9-14 characters. – Will B. Mar 31 '20 at 22:57
  • If it is a [*UUID version 4* (aka Random UUID)](https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_(random)), then it's generally taken as "unguessable". If it's a UUID based off a *sequence* generator (hence the comment about MySQL's version) then not so much .. generating a *random* sequence of 30 characters manually is little different than using a *random* UUID generator, and it comes down to quality of the random used. Both can also be stored in the database. – user2864740 Mar 31 '20 at 22:58
  • @fyrye I'm using the default `UUID()` function from MySQL, yes. So your suggestion is to add `?email=$email`, for example, to the URL and compare them in the unsubscribe script? – ajmnz Mar 31 '20 at 23:01
  • I would _not_ include the raw email in URLs. That leaks more information. Use an appropriately _random_ token source (which is _not_ the default `UUID()` in MySQL, as previously mentioned) - the source does not matter given a sufficiently large output space (_a_ UUID is fine) and randomization (`UUID()` in MySQL is not). An alternative might be to transmit the _hash_ of the email in the URL and use a secondary check on that (using an HMAC would be the extension of this). Hash, not raw email. – user2864740 Mar 31 '20 at 23:01
  • How about combining a random unique string like you suggest and see if it matches with the UUID? – ajmnz Mar 31 '20 at 23:03
  • Correct. As it is for an unsubscribe email being distributed, the email or `base_64` of the email will prevent someone else from manipulating the link to unsubscribe others. Otherwise you would typically use an additional email address form field for the user to submit and verify their unsubscribe preferences. – Will B. Mar 31 '20 at 23:04
  • @fyrye What would be your recommendations for the unique random string? In terms of approach. I'm fairly new to mysql and php – ajmnz Mar 31 '20 at 23:08
  • Generally speaking bad practice questions are off-topic, but in this instance, the aspect of "Secure" is meaning not easily manipulated. The answer would be No, other solutions to make it less manipulable would be primarily opinionated and ultimately based on your personal preference. I would rely on a hashing methods like MD5 with the user's email and a random salt. The other commenter is suggesting that you are distributing spam as opposed to things like order or shipping notifications. – Will B. Mar 31 '20 at 23:12
  • Alright, will give it a go. Thanks – ajmnz Mar 31 '20 at 23:14
  • @user2864740 since when are newsletters spam? – ajmnz Mar 31 '20 at 23:17
  • @ajmnz Geeze.. no sense of humor. (Anyway, almost every "newletter" I get _is_ spam, hiding behind some pretense of usefulness: if email is sent to me that is not in regard to _a specific business transaction_, I mark it as spam and delete it immediately. The only exception would be emails that I explicit opt-into, which are well, "none" at this point.) – user2864740 Apr 01 '20 at 01:27
  • @user2864740 If I have a table of subscribers means that at some pointed they opted in to receive news about a specific subject. And if they consider it spammy, that's why I'm asking for advice to create a one-click unsub link :) – ajmnz Apr 01 '20 at 11:02
  • Just to let you know, I finally decided to encrypt the email using [this](https://stackoverflow.com/a/46872528/10831896) php functions and later decrypt it and check if that's the actual email. Thanks for the suggestions. – ajmnz Apr 01 '20 at 16:25
  • 1
    Encryption is a bit much IMO, as you don't really need to decode the link value, just ensure something else matches against the uuid in the link, since the corresponding user is associated with the UUID and changing either value invalidates the link. As I suggested with MD5, the link can be validated using `SELECT users.email FROM users WHERE id = @uuid AND MD5(CONCAT(users.email, @salt)) = @hash;` and generated by using `SELECT id, MD5(CONCAT(users.email, @salt)) AS hash FROM users WHERE users.email = @email;` – Will B. Apr 01 '20 at 18:16

1 Answers1

2

Predictability depends on which version of UUID you're using:

  1. Easily predictable if they have samples generated on the same machine at around the same time.
  2. Easily predictable if they have samples generated on the same machine at around the same time.
  3. Very easily predictable if you publish your namespace UUID
  4. Completely unpredictable
  5. Very easily predictable if you publish your namespace UUID

MySQL's UUID() function produces V1 UUIDs, but there are other generators to get the other versions if desired.

However, in your specific use case, there is the additional issue of discoverability. If I want to unsubscribe another user from your newsletter, then the first thing I'm going to do is look around in other parts of your UI to see if you expose the target's UUID, email address or whatever other key you choose, and then predictability doesn't matter.

This is why many such systems don't bother securing this surface and instead send a confirmation email to the unsubscribed user, with a link to either "click to confirm" or "click to undo" depending on how likely you think attacks or mistakes will be vs legitimate requests. And that link should include a random, unique identifier generated for that specific unsubscribe request so the attacker has no hope of guessing it.

StephenS
  • 1,813
  • 13
  • 19
  • 2
    Thanks for taking the time to explain this. Ended up using a UUIDv4 and an email hash. – ajmnz Apr 18 '20 at 14:23