PHP/MariaDB - Confirm Email Email

I was wondering if you could take a look at how I am verifying emails and let me know if I'm doing this correctly.

The website is example.com.

When you create an account I create a cryptographic hash and store it in the database. Then I email the url example.com/confirm_email.php?hex=asdf340483fdsd0fgsdfg.

I retrieve the account from the database. I need to fix a SQL error, but I will change it to update the hex to null and the boolean value for email confirmed to true. Then the user can log in.

Is this correct?
burnedfacelessAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
How did you come up with the hash?  Does the url/hash go stale after a certain amount of time?
0
Loganathan NatarajanLAMP DeveloperCommented:
I retrieve the account from the database. I need to fix a SQL error, but I will change it to update the hex to null and the boolean value for email confirmed to true. Then the user can log in.


Yes, You can do this way and make sure you validate the hash once it is confirmed.  You need display message as "already confirmed the email"... Also hash to be unique and follow standard way of generating it.
0
gr8gonzoConsultantCommented:
I second Scott's question about how you generate the hash, and also whether or not you clean up old codes after a while.  The generation process alone will make a significant difference in how secure this will be.

Also, what happens when someone confirms the email? Are they logged into that account automatically, or do you just confirm the address and then prompt them to login? (The latter is the more secure approach)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Julian HansenCommented:
The general principle is correct.

The rules for an activation email are as follows
1. Key must be unique
2. Key must not be guessable
3. Key can only be used once

There are various ways to achieve this - your method appears to follow these rules.

If there is a signup period a timestamp in the database can be used to check the "freshness" of the key.

As far as generating the key is concerned I personally use the UUID() (MySQL) function to create these values.

When the account is activated by the click on the link, you wipe the key and update the status of the account to active.

You can chose whether or not to perform a login automatically at his point but the process up to this point is the same irrespective of whether you sign the user on or take them to a login.
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
burnedfacelessAuthor Commented:
This is how I am generating the hash. Please let me know if I should use the MySQL UUID function

            $confirm_hex = bin2hex(random_bytes(16));

Open in new window

0
Julian HansenCommented:
It doesn't matter what you use as long as it satisfies the rule of unique and unguessable.

You are not securing data with the hash - it is just a unique ID you are using to link an email address to an account for activation.
0
nociSoftware EngineerCommented:
be sure you use a good secure hash algorithm, preseed it with a sufficiently large secret text.
also factor in the time & email address...  Then you only need to keep time of entry & email address.
Upon receipt of same address & fetch the timestamp, and recompute the hash. If the provided is the same
then accept the registration and setup the account.
0
Julian HansenCommented:
@Noci
be sure you use a good secure hash algorithm, preseed it with a sufficiently large secret text.

Why? This is an activation email - the hash does not contain nor protect any data - it is simply a unique unguessable value used to link an email to an account?
0
nociSoftware EngineerCommented:
you want to establish a relation between a mail address & an account. And you want to trust that relation into the future.
And you don't want someone else to be able to send fake mails on your behalf....
Problem is e-mail is NOT protected like https... even if transit is TLS, it can be changed while in an MTA's custody.

A unique value you store, Like a base64 encoded string from a random number generator might by usable as well.
0
Julian HansenCommented:
And you don't want someone else to be able to send fake mails on your behalf....
Explain how that is going to work?

The application here is
Please click this <a href="http://somedomain.com/activate/99999-aaaaa-fffffff-00000">link</a> to confirm your email

Open in new window


I don't see how a cryptographically secure hash makes any difference or where a non-cryptographically secure key enables someone to send emails on your behalf?

All we are doing is creating a non-guessable URL that is linked to an email address to confirm that the email address entered is owned by the person who entered the data.

We are confusing and over complicating the issue here by getting into cryptographically secure hashes. For salting passwords yes - but email activation - does nothing extra over any randomly, unique value.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.