71

Right now I'm generating a 25 character string stored in the database that can only have a 1 time use and expires 30 minutes after user registration.

http://example.com/security/activate/ZheGgUNUFAbui4QJ48Ubs9Epd

I do a quick database lookup and the logic is as follows:

If the account is not activated AND if the email was not verified AND the validation code is still valid, activate the account, mark the email address as verified and mark the validation code as used.

Every 72 hours for example it would flush expired and used validation codes. This is in order to tell the user that the activation link clicked has expired for example if he look at his email the day after and try the link.

Should I include the user UUID in the url? Do I need to include something else?

I thought about making sure the IP address on the registration form match the IP address of the request when the activation link is pressed, but for me I mainly read my emails on my cellphone for this kind of stuff so it would be a pain for UX.

HypeWolf
  • 731
  • 1
  • 6
  • 7
  • 60
    Don't bother keeping/checking ip address. There are so many reasons ip addresses can change in a short time - just moving from one coffee shop to the one next door will do that. – Neil Nov 05 '18 at 07:58
  • 20
    In addition to the other answers, always provide a way to indicate that the registration with the given e-mail address was a mistake / should not exist. Verification e-mails where the only possible action is to confirm aren't very useful . *From personal experience: I have a fairly common name (in Hungary), and I constantly receive mails requesting account confirmation for other people who mistakenly provided my address instead of theirs, and most of them cannot be rejected. The best part is when they keep "reminding" me every week to confirm my account that I never made.* – molnarm Nov 05 '18 at 10:20
  • 20
    30 minutes is a bit harsh - I'd probably manage to get from here to my email in that amount of time, but it's the same distance back again to the Web, and I'm pretty sure I couldn't legally manage the round trip in less than 50 minutes. – Toby Speight Nov 05 '18 at 11:03
  • 4
    @MártonMolnár It's better to teach users that the proper way to handle the receipt of an unexpected confirmation email is to delete (or archive) the email. Clicking on links in emails you did not expect to receive is a risk you shouldn't take. Automatically re-sending the confirmation email is a bad practice and I would flag those as spam. – kasperd Nov 05 '18 at 13:14
  • 25
    30 minutes? Why? I don't see how you are accomplishing anything. I expect to have *at least* 24hours to activate an account... – Giacomo Alzetta Nov 05 '18 at 15:05
  • 1
    I would also add a function where before the URL is added to the website where it is checked in the database to make sure you don't use the same string twice – DarkStar Nov 05 '18 at 10:05
  • 15
    Indeed, 30 minutes is far too short. Your mail might not even be delivered to the user's mailbox in such a short time. 24 hours is normal, and almost all people will have gotten the email by then. – Michael Hampton Nov 05 '18 at 20:41
  • 1
    As an aside, for code generation this is one of those cases where it's usually simpler to just generate a random UUID and call it done. – chrylis -cautiouslyoptimistic- Nov 07 '18 at 22:49
  • 1
    this isn't a security consideration, but please make sure there's a way to resend the email if it got lost along the way. also, if logins are based on usernames rather than email, please let users log in and change their email even if the account isn't yet verified — sometimes you make a typo :) – Eevee Nov 08 '18 at 07:19

6 Answers6

111

How are you generating the 25 character string which you include in the URL? Is it completely random, or is it based off the current time, or the users email? It should be random and not guessable.

You should make sure the verification page actually renders (not just that a GET request occurred). Browsers such as chrome (and antivirus programs) often load URLs without the user explicitly clicking them as either a pre-fetch or to scan for security reasons.

That could result in a scenario where a malicious actor (Eve) wants to make an account using someone else's email (Alice). Eve signs up, and Alice received an email. Alice opens the email because she is curious about an account she didn't request. Her browser (or antivirus) requests the URL in the background, inadvertently activating the account.

I would use JavaScript on the page to verify the page actually rendered, and also include a link in the email where users can report that they did NOT create this account.

Daisetsu
  • 5,110
  • 1
  • 14
  • 24
  • 1
    Awesome, thank you very much for your insight. The string is indeed generated from crypto.randomBytes function of NodeJS and the application already call a javascript function but not for the reason you stated. This is a thorough answer that highlight things I didn't think of. – HypeWolf Nov 04 '18 at 23:52
  • 9
    I would also add to this - if you have a change email function, make sure you clear any existing tokens when the user changes their email address. This prevents a user from registering with a valid email, changing their email to an invalid account and then clicking your registration link. – Dave Satch Nov 05 '18 at 06:38
  • 33
    `I would use JavaScript on the page to verify the page actually rendered` worth noting that the JS *can be blocked* from the user. If it is, then maybe they clock on the link *thinking* that verified the account but when they try to log in, say, next week they find it's been deleted. – VLAZ Nov 05 '18 at 07:09
  • 4
    @vlaz that's true, but users who are blocking JavaScript by default are often aware this breaks potentially critical functionality. This could be mitigated by making sure that the "confirmation" that the account is activated is made visible by JavaScript. Any user with JS disabled would see a message indicating the problem and solution. – Daisetsu Nov 05 '18 at 07:14
  • 82
    Sometimes the page has a large button "Activate account". This makes it clear that account is not yet active, solves the JS off issue, and allows having a "cancel request" next to it, all at once. – Calimo Nov 05 '18 at 07:25
  • 1
    Does returning a redirect (302) to the real activation controller solves antivirus-prefetch problem? I mean... do them follow all redirects until they land on a page? – usr-local-ΕΨΗΕΛΩΝ Nov 05 '18 at 08:15
  • 4
    Can you explain why the benefits of "*a link in the email where users can report that they did NOT create this account*" outweigh the benefits of training users **never** to click links in e-mails which they weren't expecting to receive? – Peter Taylor Nov 05 '18 at 12:04
  • 48
    If you want a guarantee that the e-mail address is not activated on accident, use a POST request. It's good practice to never let GET requests change the server state. See also [The Spider of Doom](https://thedailywtf.com/articles/The_Spider_of_Doom). – Fax Nov 05 '18 at 13:40
  • 1
    @usr-local-ΕΨΗΕΛΩΝ If AV link scanners could be stopped by a redirect, then phishing/malware links could easily employ the same tactic. – user71659 Nov 05 '18 at 17:29
  • 5
    That sounds great. My conclusion is that account activations shouldn't be done via HTTP GET. Actually, that violates the HTTP verb semantics now that I think – usr-local-ΕΨΗΕΛΩΝ Nov 05 '18 at 18:07
  • @usr-local-ΕΨΗΕΛΩΝ [I agree HTTP GET is a bad choice for operational commands](https://security.stackexchange.com/questions/135513/what-could-an-img-src-xss-do/135548#135548) – VLAZ Nov 06 '18 at 06:17
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackexchange.com/rooms/85421/discussion-on-answer-by-daisetsu-what-should-a-verification-email-consist-of). – schroeder Nov 06 '18 at 22:23
23

Adding to the Daisetsu's answer ( as I cannot write comments yet ).

It would not be bad to create a form and ask user to create password. this way, user would need to enter new password and after submitting it, account would be activated. It would solve the background antivirus check.

As for URL, it should be enough as long, as it's random string. There is very small chance that you will have 2 keys of same content at once.

Checking the IP would not be good as some ISPs may rotate IP addresses so you would block someone who wants to register but cannot.

Maros
  • 331
  • 1
  • 3
  • I've actually used this method on the latest website I've developed. To add to this, you can create a database with all the "random strings" (aka: tokens) that you've generated. This way, you can guarantee that there's no re-use of the key. MySQL returns the error 1062, if you use the token as a primary key, making it super easy to verify if the key exists. – Ismael Miguel Nov 07 '18 at 17:57
  • @DavyM Thank you. At first I was trying to create an addition but got into it and created helpful answer. Seems like that. – Maros Nov 07 '18 at 18:21
  • @IsmaelMiguel Yeah. You can do this too if you want to be sure that the key won't be used in the future. Also you can add some timestamp to it so the token can be re-used for after some time ( removing old tokens on monthly basis ). – Maros Nov 07 '18 at 18:24
  • 1
    While that may seem to be a good idea, there's still the issue that someone has the token and can re-use it. Imagine that an user is trying to find the email with the token, by using the subject. The user remembers it from another email. And finds a random password reset email with a token. The user clicks it. Since you deleted the tokens and re-created new ones, it *may* happen, if you are unlucky, that that token was actually for someone else. And they change the password for that someone else. Now they have access with some other account. – Ismael Miguel Nov 07 '18 at 18:59
  • 1
    @IsmaelMiguel You are right. I did not think about this. – Maros Nov 07 '18 at 19:31
19

Your email should include an activation code, not a link. You should not send any link used for account management in an email. If you (the legitimate party) email links (especially with long strings of random characters) to users, then you make it a little more difficult to distinguish legitimate emails from phishing attempts.

The activation code should consist of characters which can easily be copied and pasted (no spaces, '+', '-', etc.) and which are easy to manually enter as well. Letters and numbers work. The security of the activation code depends it being unpredictable. You derive the value using the system's secure random number generator, just as you should be doing with session IDs for cookies.

Activation codes should be stored in a database table, each row with a profile ID, the activation code*, and the expiration time. You don't want to put the profile ID or expiration time in a cookie, URL, or the activation code.** A malicious user could tamper with the ID to take over another account. You need to verify those things server-side because your security policy can't be blindly trusting client data.

The activation code should be submitted via a POST request. Automatically redirect users to the account activation URL (which can just be https://example.com/security/activate) when they're done with the rest of the registration. Make it easy to access the account activation page again in case they accidentally close it.

You do not want a GET request to perform any action. A link might get visited without user interaction (due to browser prefetching or email security services). Furthermore putting secrets in a URL could leak sensitive data. (Through referral headers or browser addons.)

Don't worry about the IP, it still may change. (Same for user agent.)

I don't know if a cancellation feature is desirable. I don't even use the unsubcribe links in unsolicited emails because it may be a way to determine whether or not there is a human associated with an email address. If the activation code is long enough to resist brute force or if you limit the number of activation code attempts then I don't see the harm in just letting the activation code expire eventually.

On the other hand it could be nice to reduce the number of unwanted account activation emails. You can limit the number of emails you send to an address, to be polite. Say a maximum of 3 per day and a maximum of 5 per month. (I don't know if that's too high or too low.) As a non-user I would just make emails from your website auto-deleted or auto-marked-read.

There are a lot of variables to consider for unwanted activation emails. Most probably have more to do with user experience than security. You could go as far as providing a "cancel my activation and never send emails to this address because I'm never going to register" feature, but that has obvious problems. (You still need to verify the person's email too.)

* It doesn't hurt to hash the activation code. It's also not critical to hash, as with passwords, because each code has a one-time use, contains no private information, is randomly generated by the server, and expires.

** You could actually prevent tampering using a cryptographic MAC, but I would strongly discourage attempting that for most developers. Cryptography is hard to get right.

Future Security
  • 1,701
  • 6
  • 13
  • Wow <3 Thanks! Would you use a CD-key like token? `2ZRIF-07SWU-ZQVA7-SVSIN-OCFDC` This kind of code, big in the middle of the email would make it easy to understand the purpose of it with the activation link below. This string could be generated with crypto.randomBytes() but my concern is the short alphabet, but then again the string if 25 characters long so brute-forcing it should take a while with temp ban if a brute-force is detected. Any great article about cryptographic MAC? :D – HypeWolf Nov 05 '18 at 20:16
  • 2
    @HypeWolf 36 isn't that short; 36^25 is big enough for me. – wizzwizz4 Nov 05 '18 at 20:33
  • @wizzwizz4 True. Maybe I'm just paranoid. – HypeWolf Nov 05 '18 at 20:35
  • 3
    @HypeWolf Paranoia and good security aren't necessarily related; you're using a GET request to trigger an action (when it should instead take you to a page with a button that sends a POST request to trigger the action). – wizzwizz4 Nov 05 '18 at 20:39
  • You say : "Activation codes should be stored in a database table". Could it make sense to go one step further and hash it ? As it's a random string you might go for a fixed salt (otherwise you'll need to get it from the client side). – Thierry Nov 06 '18 at 07:47
  • I disagree with this answer. We are talking about a one-time code here. All the various sniffing, proxy logfile reading, etc. threats are useless against it. For a MitM attack it makes no difference. I don't see a security gain by making the process more complicated and disallowing a simple GET. – Tom Nov 06 '18 at 14:18
  • 1
    @Tom Other answers and comments have pointed out that a GET might be triggered by mistake, e.g. by a security scanner. I also agree that, in general, we should discourage users from clicking links in e-mails, although until Paypal make their genuine announcements look less like phishing attempts, this is probably a losing battle. – IMSoP Nov 06 '18 at 15:13
  • @Thierry Absolutely. Or perhaps H(fixed-salt, email-address, activation-code). It's probably a low risk to skip hashing of the code, however. (I believe that because it has a one-time use, contains no private information, is generated by the server, and expires.) Passwords of course should *definitely* be hashed. – Future Security Nov 06 '18 at 18:36
  • 1
    @HypeWolf The alphabet size isn't important. Only the number of possible codes. If you add a counter to your profile/verification-code and invalidate it after too many wrong answers then you can use a much, much shorter code. I would even drop vowels to avoid randomly spelling real words. Treat O and 0 as identical, I, L, and 1 as well. Remove spaces/separators. etc. The number of possible distinct codes is the only factor that affects security. Any other decisions are user experience questions. – Future Security Nov 06 '18 at 22:35
  • 1
    @Tom You correct, none of those things make a difference. Putting the code in a GET parameter vs putting it in a POST parameter matters though. A GET may be a false positive if you don't check for the same session cookie. However someone might be registering on their laptop but read email on their phone. Or the cookie can be cleared if they close the browser after registering but before verifying. Besides that the only security difference is whether or not you prime users for phishing. (We're not talking about password reset systems. That's very different.) – Future Security Nov 06 '18 at 22:51
  • @IMSoP it might be, but then whatever triggered it is made sloppy. It should use HEAD or OPTIONS depending on what it wants to check. There are things to consider and the main is that GET should be idempotent but is not in this use case. – Tom Nov 06 '18 at 23:15
  • 1
    @HypeWolf I wouldn't use a security code like that with `-` hyphens in it, because when I try to double-click on it, only one part of it is selected, rather than the whole number. – Charlie Harding Nov 07 '18 at 13:19
  • @CharlieHarding Indeed, good catch! – HypeWolf Nov 07 '18 at 21:11
  • Do you recommend verification codes (instead of verification links) even for the most important of account-management mails like email confirmation, password reset, email change, etc.? – lonix Jul 23 '19 at 10:37
  • @lonix Yes. For either method you generate a random token. A verification link puts that token in the URL. A verification code is a token that the user enters in an HTML form. The only risk specific to verification codes that I can think of comes about from shortening the token for the sake of user convenience. If you use the same verification code length as the length you would have used for URL tokens, then the verification code method is at least as safe. – Future Security Jul 23 '19 at 18:12
  • @FutureSecurity True, but in reality the code will be a very short token (let's say 6-9 digits or so) whereas the link's token will be a monstrous hex string. So under those circumstances, surely you'd be against codes (and in favor of links)? – lonix Jul 24 '19 at 07:31
  • @lonix It doesn't need to be that short to be usable. And the code doesn't need to be too long to be secure. Short keys and short passwords are bad, but the verification token isn't either of those. A short password can be bruteforced by a slow online attack. But unlike passwords, tokens can be invalidated if a client enters the wrong code too many times. Unlike keys, there is no way for a hacker to determine if they got the right code other than by submitting it to the server. – Future Security Jul 25 '19 at 03:02
  • @lonix You could base-32 encode a random 60 bit number, giving you 12 characters. You can limit that token to 10 incorrect answers. You would need to submit millions of guesses per second if you wanted to reset someone else's password within a thousand years. That's without any rate limiting or monitoring. Depending on how valuable the thing you're trying to protect is, you can add or remove characters to your satisfaction. – Future Security Jul 25 '19 at 03:17
  • @lonix Technically it's possible to do offline cracking of a hashed token if someone gains read access to a database while the authentication server is live. (It doesn't hurt to hash the tokens even if they're short.) To partially mitigate that problem the token should expire after some time. Absent hacks, leaks, and insider attacks there's no offline cracking a hacker could take advantage of unless the server does something odd like send the hash of the token to clients, however. – Future Security Jul 25 '19 at 03:26
  • @FutureSecurity Thanks for your insights! You've convinced me to use codes instead of links. I'll give the user 3 chances to get the code right, within 10 minutes, then invalidate it. – lonix Jul 25 '19 at 07:44
8

As with anything where you are using security practices, the exposure and threat assessment are something which you will have to evaluate for your unique environment and use case. The existing answers provide plenty of touch points for that evaluation, as well as pointers for things to avoid - such as GET triggered actions. Instead, I'm going to focus more on the "user" part of the UX. I will touch on many of the comments scattered about on the page as well.

Exactly what you are "verifying" with the email-triggered action is a factor to consider, as well. If all you are doing is confirming that it is a valid email address, then almost any action will be sufficient. Verifying the email, the intention to create the account, and that the user receiving the email is the user who initiated the account creation requires a bit more effort.

One "effort" you should not have to take, however, is the sending of reminder emails. Assuming the user intentionally created the account, used a valid email address, and wishes to use the account for whatever purpose you are offering accounts, they will be looking for the email, and will undertake the activation process as soon as they are able to. The link, code, or whatever, might expire before they use it if they get sidetracked on other matters while waiting for the email. Allowing them to resend the verification email in such cases is fine. A reminder email, however, is much more likely to be a spam trigger than a benefit to the user.

As a user I appreciate having options available to activate/verify my account. The most common option set I've encountered is where the email provides a link which I can either click or cut & paste, and a confirmation code which I can enter into a form field on a page within my account settings area on the website. Seldom is the confirmation code anything but an integer, 5 to 9 digits long.

The verification emails which give me, as a user, the most confidence are those which have a crypto-looking hash in the URL (/verify?l=lgGS2SBjMTU4NjkwNjYxMjI5MDBhZDk2YjEyMzMzYjNhZmQxOb), which takes me to a page unique to me where I then have to enter the password I used to create the account. The use of the hash confirms that the link was received in an email, not guessed or brute-forced, thus verifying that the email is valid. The serving of that unique page, prior to any other actions, allows the server to mark the email it was sent to as "valid" without confirming the account creation. The entering of the password confirms that there is an actor on the other end, as opposed to some anti-virus, malware detection, or pre-fetch operation. The validity of the password confirms, to a reasonable degree of certainty, that the receiver of the email and the creator of the account are the same person.

The proposed time-limit of 30 minutes does seem a bit severe, while a 24-hour period might be too large if your threat assessment suggests that your exposure in 24-hours is unacceptable. I believe that 2 hours should be sufficient to almost any user's environment. Especially so if the ability to resend verification is available. Even using a mobile device with a 14 kb/s connection to a remote POP account should be able to complete the exchange inside 120 minutes.

The use of JS to somehow verify human actions can be problematic. JS can be turned off, and is much more often than many web developers would like to know. Secondly, ad blockers can block JS on a per-site, or per-source, basis even when JS is enabled in the browser. I block many JS sources, including Google's analytics scripts, on a global basis, and whitelist some for sites, such as Stack Exchange, where I'm willing to support them with the use of my data.

Including a link in the email, or on the confirmation page, to "cancel" the account is a waste. In the email it is actually counter productive in that is suggests that clicking on a link about an account you don't want is a good idea. Instead, the email should include verbiage to indicated that doing nothing will cause the account to not be confirmed, and maybe deleted. A link to cancel on the confirmation page is even worse, as it is rewarding bad behavior. I frequently get emails, as part of an old Google snafu, directed to another Gmail account, and I presume the opposite is true for the other account as well. [In the old days Google didn't merge dotted and undotted user names, so my dotted user name and someone else's undotted version are different accounts, yet Google will slip up once in a while and I get their email anyway :( ]

How tightly you couple the "verified" email address with the activation link is a function of your use case and threat assessment. I am mildly annoyed when I have to re-validate my account after changing the associated email address. How accepting I am of the process is proportional to my "value" of the account. For my bank account, PayPal, etc., I'm 100% accepting. On PcPartsPicker, however, I'd be around 15% accepting of such a process.

As to the IP and/or user agent, ignore them. As a case in point, I often will view sites, and choose to create an account, using my mobile device. I will not, however open emails on it. If I create an account, and learn that I need to verify or confirm it somehow, and email is the offered method, I'll wait until I get home to open the email on my desktop, where I can inspect it. The IP and user agent will thus be very different. My password, however, will remain the same, and that ought to be sufficient to verify me as the original account creator.

Just remember that emails are about as secure as the Jumbo-tron in Times Square, and proceed accordingly.

  • 1
    "Seldom is the confirmation code anything but an integer, 5 to 9 digits long." this bears emphasis. With a one time code that expires in 30 minutes, 25 characters is complete overkill. Even 6 alphanumeric characters requires 600k POSTs/sec to cover 50% in 30 minutes. Throttle the verification service to delay a hundredth of a second and an adversary can scan at most 0.008% of the keyspace, they have a 1 in 12,000 chance assuming they consume your entire verification service for a full 30 minutes... – TemporalWolf Nov 06 '18 at 20:26
  • @TemporalWolf did you include the fact that the adversary can scan in parallel? – Paŭlo Ebermann Nov 07 '18 at 00:31
  • @PaŭloEbermann If you throttle your verification service then that puts an upper limit on the number of keys they can scan. It's all about balancing risk. For a bank? Probably not. For a small business? Probably. Where I'd start considering bumping it is when the number of new accounts during any sliding window exceeds 100 or so. That still provides <1% chance of someone guessing anyone's code and a 100% chance of detection of the attempt (assuming some monitoring). 6 characters is ~32 bits of entropy. 10 is ~52. 25 is ~130. You control the brute force speed via your verification service. – TemporalWolf Nov 07 '18 at 19:16
2

A verification email needs few things, both for being reasonably safe and user-friendly:

  1. It must be clear to the user what's going on. Being explained why this email is being received (e.g. "...someone registered the account BLAH on our site, this is to confirm...") rules out most dumb errors and thwarts fishing mails reasonably well. You hopefully know whether or not you've just registered an account at some service (unless you're completely dement). So, receiving an email which explains this comes as no surprise. On the other hand side, such an email knowing you didn't register anywhere should (well, hopefully) be suspicious enough. If it isn't, and the user clicks on any random link sent via e-mail, there's nothing you could do anyway, not knowing what mails people whom you don't know may or may not receive.
  2. A random (non-sequential!) token which is reasonably long (long enough to guarantee it is unguessable and doesn't collide) passed back to the server. That token is also stored in the database for comparison. It doesn't matter how long exactly it is and what particular format it has. It doesn't need to be human-readable. Anything upwards of 20 or so characters (assuming base64 encoding) should work, but I'd use 40 characters to be sure because it doesn't really cost you anything. A 240-bit pseudorandom string is pretty much guaranteed to be unique and unguessable. For convenience, I would actually make it a link, and the link can indeed be a GET request. Yes, users aren't supposed to click on links, but they'll do it anyway (you're not going to educate them!). On the other hand, user experience is much better compared to having to copy-paste some obscure string.
  3. A HTML form with a "click to confirm" sort of button which re-POSTs the data, this guarantees that speculative loading of URLs doesn't let stuff happen (unintentionally or even maliciously) that the user owning the mail address doesn't know about. The form should display a reasonable amount of information so the user knows what's going on, and as a last chance to trigger "WTF?" in case the user didn't register that account.
    If you're worried about robots, the form can include an "I'm not a robot" if you wish so (personally, I deem that superfluous, but your mileage may vary).
  4. A reasonable expiry date (or time). What's reasonable? Nobody can give a definitive good-for-all answer. I'd say anything from 4 hours to two days is probably good. Usually, when you register for something, you get your confirmation mail within 5-10 seconds, and you sit in front of the computer waiting for it. Might be longer if you're using typical paranoia-mode corporate mail which spends minutes on scanning every external e-mail. Though, you might get a phone call or have to go somewhere in the middle of it, so having the token valid for a couple of hours or a day certainly is no mistake. Also, mail can be delayed (rare, but it happens).
    On the other hand, there's no point in wasting disk space by keeping around confirmation tokens (and blocking account names!) for weeks, months, or years. Not only does this consume resources to no avail, but someone who doesn't confirm within a day or two probably never will, anyway. Or, they could just re-register another time. Which, really, doesn't cost anything.
Damon
  • 5,001
  • 1
  • 19
  • 26
1

From your question, I am taking the following assumptions and if any of them are invalid, so is my answer:

  1. the user registers on your webpage and sets username/email and password during registration
  2. the e-mail verification has the purpose of ensuring that the user has registered with a proper e-mail address.
  3. activation of the account is the only thing that the e-mail link does. It does not allow a password reset or other such functions. It basically does "UPDATE user SET activated=true WHERE token=%1"

In this case, from a security perspective, your approach is absolutely fine. 25 characters gives you enough assurance against random collisions and brute-force attempts, the one-time use protects against sniffing and similar attacks and anyways the only thing someone could accomplish is to activate an account.

You might want to include the user ID if you have a lot of users for performance reasons (UPDATE: not really, see comments below) Finding the user by a string token will result in a table scan, while finding him by ID and then just fetching and comparing the string is an index lookup. However, this does expose the user ID, which you may or may not want to do.

Tom
  • 10,124
  • 18
  • 51
  • 1
    "Finding the user by a string token will result in a table scan" - not if you put an index on the token column. A text index may be marginally slower than a numeric one, but there is no reason at all to do a table scan. – IMSoP Nov 06 '18 at 15:15
  • Indexes are not for free. Would you put an index on the table that will be used **once** in the lifetime of each row ? (also, it will be null for most rows. Hm, it actually might have some good performance because of that) – Tom Nov 06 '18 at 23:12
  • 2
    You can just have a separate table (with index) for the activation tokens instead of putting this into the user table. – Paŭlo Ebermann Nov 07 '18 at 00:33
  • 1
    Use a separate table for activation tokens: `token`, `uid`, `expiry`. Upon HTTP request using a token run `DELETE FROM TokenTable WHERE expiry < SYSDATE(); SELECT uid FROM TokenTable WHERE token="$received_token";` Then use the uid to load the proper page data, etc. Absent a massive user base an index would be more resource intensive than necessary. 200 records, always fresh, shouldn't be that bad to scan if needed. –  Nov 07 '18 at 01:52
  • Agreed, @GypsySpellweaver that would be a good solution. – Tom Nov 07 '18 at 06:34