[SOLVED] new user activation email - theory and implementation

[Lodius2000]

can someone tell me what php does when the user clicks the activation email link? is there a pending users table and when the verification email link is clicked the pending user goes into the user table?

 

is there a active/inactive field per user? if this is true then how do you check on each page if the user is active, or do you just do the check once, if so where

 

whats going on, can you just tell me the theory and general implementation

 

thanks

[ratcateme]

basically the way i do it is i have in my users table 2 fields for email activation

one field called active which is a 1 or 0. 0 is not active 1 is active and they are allowd to login (i also use this for temporary account blocking) and another fuield with a md5 hash in it.

i send the user a email with a link looking like

checkemail.php?user=therusername&check=thehash

and so  checkemail looks something like this:

$user = mysql_real_escape_string($_GET['user']);
$hash = mysql_real_escape_string($_GET['check']);
mysql_query("UPDATE `users` SET `active` = 1 WHERE `username` = '{$user}' AND `emailCheck` = '{$hash}'");
if(mysql_affected_rows() == 0){
    echo "Invalid email check";
}else{
    header("Location: login.php");
}

 

Scott.

[MadTechie]

To follow on from  Scott..

as for checking on each page you don't need to make calls to the database to see if they are active, just save it to a session on login (as you would with the UserID or UserName)

[Lodius2000]

Scott

ok that makes sense,

 

Mad techie, dunno why i didnt think of session specifically, sometimes ima moran

 

Thanks

 

solved

[ratcateme]

jut thought that is not a very good script really because if a block an account and they still have there email check link they can recheck there email and it will unblock there account

$user = mysql_real_escape_string($_GET['user']);
$hash = mysql_real_escape_string($_GET['check']);
if($hash != ''){
    mysql_query("UPDATE `users` SET `active` = 1 AND `emailCheck` = '' WHERE `username` = '{$user}' AND `emailCheck` = '{$hash}'");
    if(mysql_affected_rows() == 0){
        echo "Invalid email check";
    }else{
        header("Location: login.php");
    }
}else{
    echo "Invalid check";
}

would be better

 

Scott.

 

 

[Lodius2000]

Scott if you deactivate the account, the `active` is set to zero, so if they were to run this script the `active is set back to one, so affected rows will always be more than 0, right

 

or am i missing something

 

-unsolved

[ratcateme]

if you deactivate a account then active would be set to 0.

but if with my first script if they re clicked the link then active would be set to 1.

but i resolved this problem with the second script by clearing emailCheck then checking for blank test hashes.

so the script will fail on a second try

 

Scott.

[MadTechie]

and they can't just have a hash of nothing due to the

if($hash != '')

to unsolve a post click solved again ???

 

Happy 1000 post ratcateme 

[ratcateme]

Happy 1000 post ratcateme 

Thanks lol never noticed

[Lodius2000]

gotcha... thanks

 

-resolved

[haku]

My activation theory (I've simplified it a little for this post):

 

I have two tables. The first is the users information, and contains their username, password, and an active row (that can be 0 or 1, as others in this thread mentioned). The second table has two columns - user ID and activation code. Actually there is deadline too, but lets ignore that for this post.

 

1) When the user registers

- their data is entered into the users table

- A 20 character alphanumeric activation code is created, hashed, and entered into the second table with the users id

- the un-hashed version of the activation code is appended to the URL, and the URL is emailed to the user

2) When the user clicks the activation link

- the user is asked for their username

- upon submission of the username, the activation code is hashed, and the database is searched for a combination of that hash and username

- if the combination is found, that row is deleted from the activation codes table, and the 'active' is set to 1 in the user table.

 

Why I hash the activation code: The activation code is basically like a 1-time password. As such, it's more secure to store it hashed, than to just leave it raw.

Why I require the user to enter their username on the activation page: If there is no requirement for this, someone could theoretically keep trying random codes in order to activate an un-activated account. By requiring the username, you ensure that even if someone stumbles across an active access_code, they still need to input the user's name to use it.

[Lodius2000]

so whats wrong with this script then --- it is almost verbatim what ratcatme posted, just uses my db abstraction... it always says "Invalid activation code. Your account status cannot be activated..."  and I double checked that active is set to 0. I even grabbed the hash from the db and it is exactly the same so I dunno whats wrong.

 

<?php

session_start();


require ('../PEAR/PEAR/DB.php');
require ('db_login.php');
$db->setErrorHandling(PEAR_ERROR_DIE);

$user = mysql_real_escape_string($_GET['user']);
$hash = mysql_real_escape_string($_GET['check']);
if($hash != ''){
    $q = $db->query("UPDATE users SET active = 1 AND active_hash = '' WHERE username = ? AND active_hash = ?", array($user, $hash));
    if($db->affectedRows($q) == 0){

	print '<p>Invalid activation code. Your account status cannot be activated. Please email the <a href="mailto:' . $admin_email . '">site admin</a> and describe your problem</p>.';

    }else{
	$_SESSION['message'] = $user . " has been activated, please login.";
        header("Location: login.php");
    }
}else{

print 'There was an error in the activation of your account. Please email the <a href="mailto:' . $admin_email . '">site admin</a> and describe your problem</p>.';

}

?>

 

thanks

[uniflare]

im just jumpin in here, echo the query, put the query in phpmyadmin, if it works in phpmyadmin and you cant get it to work properly try using a simple mysql_query rather than ur abslayer.

 

Other than that, make sure there is no whitespace, hidden characters etc.

[Philip]

$q = $db->query("UPDATE `users` SET `active` = '1' AND `active_hash` = '' WHERE username = ? AND active_hash = ?", array($user, $hash))

 

What DB errors are you getting, if any? I'm pretty sure you can't update the column thats part of the WHERE clause, but I could be wrong. A quick check on PHPMyAdmin throws some whacky errors.

[Lodius2000]

Guys, thanks for the input...

 

//query retooled for raw sql input, (no DBA)

 

UPDATE `users` SET `active` =1 AND `active_hash` = '' WHERE `username` = 'my-username' AND `active_hash` = 'hash-goes-here'

 

returns 0 affected rows, I am sure all of the variables are in the right place, so what is wrong with the query?

[MadTechie]

echo the query

and then grab the SQL and try the where in a select

ie

SELECT * FROM users WHERE `username` = 'my-username' AND `active_hash` = 'hash-goes-here' 

[Lodius2000]

<?php
echo "UPDATE `users` SET `active` = 1 AND `active_hash` = '' WHERE `username` = $user AND `active_hash` = $hash";

//query where clause from above
$d = $db->query("SELECT * FROM users WHERE `username` = $user AND `active_hash` = $hash");

//print results
print_r($d);
?>

 

returns DB Error: no such field

[Lodius2000]

SOLVED...

 

I just broke the query into 2 update queries, one sets the active to 1 and the other empties the active_hash.... still dunno why the other query didnt work but I am starting to thing it is as KingPhilip suggested: 'I'm pretty sure you can't update the column thats part of the WHERE clause'