Avatar of ucsdmbdm
ucsdmbdm asked on

Php MySQL email verification link

Hi Guys,
I would like to know if any of you can explain the step by step process for creating and sending new registration activation links. In other words, when a new user sign up on your site, you don't let them login until they click on the activation link they receive in their email.  I have the complete process in my mind but I haven't done it in PHP and want to see how you guys will approach it. For example, one question I have in mind is that if you use  one table with and extra field for activation or 2 tables one containing temp users and the other containing the confirmed users) and so on. I am not sure what is the standard secure way of doing it that everyone esle is using. I also haven't done this in PHP.

PHPWeb Applications

Avatar of undefined
Last Comment
Vel Eous

8/22/2022 - Mon

create a temporary table where you will store all the information that the user provides during account setup. When the user submits the data, generate a random key. You have to make sure no other record  exists on the temporary table with that key. If so, you have to keep regenerating that key until you get a unique key. Once you have a unique key, insert all the data provided by the user AND the random key into your db and send the person some link like

when the user clicks on it, activation.php will read the key parameter from the querystring and find the corresponding matching record. If such record is found move the data to the "real" table(s) and delete the record from the temporary table.

What our company does is we have a single user table, with an Active field of type Boolean.  

1. So when the user first signs up, the default of the Active field is 0.  
2. The system then sends out an activation email to the person, which contains a link to your activation script and the userid passed as a parameter.  
3. The script will then activate the user by setting the user's active field to 1.
4. Lastly, the script will then automatically log the user in, and take them to the index page.

You might consider using encryption on the userid field.  MD5 is a good choice I think.  i.e. Store your userIDs in the database with MD5 encryption.  And use that as your userID parameter when sending the email.  This way, you will ensure that no one will be able to just edit the activation link by typing in a different number and activate other users.

Hope this helps.
Vel Eous

My spin on the matter:

In your user table have an "active" field and "joined" field along with the usual info.  When the user signs up, the "active" field gets a default value of 0 and the "joined" field gets the current unix timestamp.

An email is generated which contains a link back to a PHP script on your site that contains a base64 encoded version of the timestamp and a user id (or something of your choice) (http://mysite.com/activate?u=1010&t=fbf832bgibfb7368r020).  On this page you grab user id and the encoded timestamp then decode it back to a readable timestamp then compare it against what was stored in the DB for the given user id.

If a match is made the "active" field is updated to 1 and the user may login etc etc.

Obviously you don't have to use timestamps, a random number could be generated and hashed but generally a timestamp is unique enough for this sort of thing and considering its not "sensitive" data possibly ott.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

I agree with the single table approach. In the user management system that I am currently building I have a field called "verified". Another reason for a single table is to allow users to change their email address. There is a column in the table specifically to hold a new email address. This new email address must be verified before it will become active.

I would also suggest using something more than just the users id to create a verification code. In my case I use several of the columns from the users new data including such things as the users password, the date and time that they submitted the account creation form and/or other items and I encrypt these. Though I won't say what fields I use, you should get the idea. When they submit the verification you decrypt this information and use it to activate the correct account.

Thanks everyone for the answering.
I am still a little confused on the key I generate. Should I create a random key add it in my table and send it to the users for verification in combination with username or userid? Is it better to completely generate the key based on the username (i.e. xor it with sth). I did't understand how I can use md5 for creation of the key. I thought md5 is a one way mapping encryption and I can't get to the username from the key. Did you mean to store the encrypted md5 key in a column and do a string comparison when they click on the link ?


yes, it's a one way encryption.  However, you can do it like this...

eg. your userID is 1, but what you store in your database is md5(1).  

Then, in your activation link, you include md5(1).  

In your activation script, you just need match the userid, which is md5(1) with what's in the database.

This is like using GUIDs for userIDs in .NET world.  So instead of storing numbers in the database for userids, you use md5 strings.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

>>Should I create a random key add it in my table and send it to the users for verification in combination with username or userid?
Not " in combination with username or userid". Simply send the random key. If the user clicks on http://yoursite.com/activation.php?key=adfafopoewrsdwrwrtwet
then activation.php will "see" the key that you originally sent to the user and will be able to match against the one you have stored on your table.

In regards to using one or two  tables, neither method is right nor wrong. Both will work. I suggested two tables because I have had lots of users that create accounts but never actually activated them. So I would periodically (and safely) just "delete all the records earlier than 3 days".

>> Is it better to completely generate the key based on the username (i.e. xor it with sth).
No. use md5

>> I did't understand how I can use md5 for creation of the key
a. $key = md5($userid);
b. save the $key to the table
c. email http://yoursite.com/activation.php?key=$key

if the key you sent is 'abc', then you will already have abc stored on the table. So when you get it back look for a record that contains 'abc' in the 'activationkey' field. You should have at most ONE record.

>>Did you mean to store the encrypted md5 key in a column and do a string comparison when they click on the link ?
Vel Eous

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vel Eous

Typo in my previous comment, missed out the table name in the above UPDATE statement.

UPDATE table_name SET ...

Thanks for taking the time to explain this to me.
In a more advanced activation link, I see some sites require you to activate your account within a time- frame. Is there a reason for that ? Do they simply store the time-stamp and have some external code or crontab delete the rows containing the old data ?
I've seen that mostly used for the bank systems. I will try your solutions and will get back to you guys once it's implemented.

Thanks again,
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vel Eous

One good reason for doing that is if someone does sign up with a bogus email address, rather than that user sitting there taking up database space and a possible username, the unrequired record is wiped from your database.

Thanks everyone for getting back to me
Tchuki, you mentioned the base64 combined with username encryption whereas other suggestions were pointing out using md5 as a one way hashing solution so they can't get the username out of it. Let's say the email is sent out to a hacker (hypothetically) which solution is more secure and can prevent him from possible exploits based on the link he has in his email ? This topic is really interesting to me now and I would love to hear your thoughts on that.

Thanks everyone,
Vel Eous

Hashing is a good idea but you don't really want to send a password hash via the URL.  You could hash the username or another user unique identifier but you would need to store the hash in the table as well which considering it will only be used once is a bit redundant.

Using base64_encode() / base64_decode means you just encode and decode a value that is stored in the DB already so when it is sent to the URL it isn't obvious what its value is.

I regularly use an encoded unix timestamp as my authentication method which is highly likely to be unique to every user.

Passing variables via the URL is usually fairly safe as long as you take the require precautions of cleaning anything you collect from the URL.

You could prevent an attack on your scripts / database by salting any data you pass through the URL although this isn't ideal for all problems. However, for something like this, would counter act brute for attacks and raindbow attacks.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.