...

View Full Version : Email account activation help



PRodgers4284
03-02-2008, 01:39 PM
I have a registration form on my website that sends an email to the users email address once they have completed the registration form. I have the email sending fine, but im having difficulty getting the activation link to work, the activation link sets a field in the database to 1 which indicates an active account. I am trying to use the user password and timestamp to identifiy them in the database but i not sure if im doing this correctly. Can anyone help?

Email Script is:


require_once('class.phpgmailer.php');
$mail = new PHPGMailer();
$mail->IsSMTP(); // send via SMTP
$mail->Host = 'ssl://smtp.gmail.com'; // SMTP servers
$mail->FromName = '********.com';
$mail->AddAddress($email);
$mail->Subject = 'Registration';
$mail->Body = "Your account has been successfully created with the following details:\n\nUsername: $username\nPassword: $password\nEmail: $email\nForename: $forename\nSurname: $surname\nLocation: $location\n\nPlease click on the link to activate your account.\n";
$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?hash='.md5($password).'&stamp='.base64_encode($stamp)'>Activate Account</a>";
$mail->Send();
}
}


The activate.php code


<?php
UPDATE users
SET active = 1
WHERE (password = "'.md5($_GET['hash']).'") AND (timestamp = '.base64_decode($_GET['stamp'].')
?>

abduraooft
03-02-2008, 02:33 PM
<?php
UPDATE users
SET active = 1
WHERE (password = "'.md5($_GET['hash']).'") AND (timestamp = '.base64_decode($_GET['stamp'].')
?>
Where is your mysql_query() call?

mysql_query("UPDATE users
SET active = 1
WHERE password = '".md5($_GET['hash'])."' AND timestamp = '".base64_decode($_GET['stamp']."'" ) or die(mysql_error());

PRodgers4284
03-02-2008, 02:38 PM
Where is your mysql_query() call?

mysql_query("UPDATE users
SET active = 1
WHERE password = '".md5($_GET['hash'])."' AND timestamp = '".base64_decode($_GET['stamp']."'" ) or die(mysql_error());

Hi abduraooft, im using the following query:


<?php
mysql_query("UPDATE `users` SET `active` = '1' WHERE `password` = ".md5($_GET['hash'])." AND `timestamp` = ".base64_decode($_GET['stamp']."");
?>

Im getting the following error though:

Parse error: syntax error, unexpected ';'

abduraooft
03-02-2008, 03:19 PM
The error is not related to your query (I believe), there might be some mismatches in the double/single quotes somewhere else. PHP parser should have pointed out the line number where the error resides.

_Aerospace_Eng_
03-02-2008, 03:47 PM
You really need to start using error checking on your queries. I suspect your query might be failing. Try this.

<?php
$pass = md5($_GET['hash']);
$stamp = base64_decode($_GET['stamp']);
$sql = "UPDATE `users` SET `active` = '1' WHERE `password` = '$pass' AND `timestamp` = $stamp";
$result = mysql_query($sql) or die(mysql_error());
?>
What data type is your active column? Is it an int or a string? I'm guessing its likely an int.

PRodgers4284
03-02-2008, 03:57 PM
You really need to start using error checking on your queries. I suspect your query might be failing. Try this.

<?php
$pass = md5($_GET['hash']);
$stamp = base64_decode($_GET['stamp']);
$sql = "UPDATE `users` SET `active` = '1' WHERE `password` = '$pass' AND `timestamp` = $stamp";
$result = mysql_query($sql) or die(mysql_error());
?>
What data type is your active column? Is it an int or a string? I'm guessing its likely an int.

Aerospace thanks for the reply, the active field is set as a varchar(1) in the database. I tried the query you provided but im getting the following error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

My code is now:


<?php
include("database.php");
$pass = md5($_GET['hash']);
$stamp = base64_decode($_GET['stamp']);
$sql = "UPDATE `users` SET `active` = '1' WHERE `password` = '$pass' AND `timestamp` = $stamp";
$result = mysql_query($sql) or die(mysql_error());
?>

_Aerospace_Eng_
03-02-2008, 04:01 PM
Change this line

$result = mysql_query($sql) or die(mysql_error());
to this

$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
Copy and paste what you get here.

PRodgers4284
03-02-2008, 04:04 PM
Change this line

$result = mysql_query($sql) or die(mysql_error());
to this

$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
Copy and paste what you get here.

Aerospace I get the following error after making the changes:

The error was: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
The query was: UPDATE `users` SET `active` = '1' WHERE `password` = 'fb469d7ef430b0baf0cab6c436e70375' AND `timestamp` =

abduraooft
03-02-2008, 04:09 PM
echo 'pass: '.$pass = md5($_GET['hash']);
echo '<br/>stamp: '.$stamp = base64_decode($_GET['stamp']);
Check whether this values are actually reaching there..

_Aerospace_Eng_
03-02-2008, 04:16 PM
Looks like the timestamp isn't set anywhere. I see the problem. You aren't concatenating your functions properly. Change this

$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?hash='.md5($password).'&stamp='.base64_encode($stamp)'>Activate Account</a>";
to this

$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?hash=".md5($password)."&amp;stamp=".base64_encode($stamp)."'>Activate Account</a>";

PRodgers4284
03-02-2008, 04:28 PM
Looks like the timestamp isn't set anywhere. I see the problem. You aren't concatenating your functions properly. Change this

$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?hash='.md5($password).'&stamp='.base64_encode($stamp)'>Activate Account</a>";
to this

$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?hash=".md5($password)."&amp;stamp=".base64_encode($stamp)."'>Activate Account</a>";


Aerospace ive made the changes but im stilling getting the error

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Have you any ideas what could be the problem?

PRodgers4284
03-02-2008, 04:40 PM
It seems to be getting the password but not the timestamp.

PRodgers4284
03-02-2008, 05:29 PM
I still cant get this to work, my email code is:


require_once('class.phpgmailer.php');
$mail = new PHPGMailer();
$mail->IsSMTP(); // send via SMTP
$mail->Host = 'ssl://smtp.gmail.com'; // SMTP servers
$mail->FromName = '*******.com';
$mail->AddAddress($email);
$mail->Subject = '******* Registration';
$mail->Body = "Your account has been successfully created with the following details:\n\nUsername: $username\nPassword: $password\nEmail: $email\nForename: $forename\nSurname: $surname\nLocation: $location\n\nPlease click on the link to activate your account.\n";
$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?hash=".md5($password)."&amp;stamp=".base64_encode($stamp)."'>Activate Account</a>";
$mail->Send();


My activate.php file is:


<?php
include("database.php");
$pass = md5($_GET['hash']);
$stamp = base64_decode($_GET['stamp']);
$sql = "UPDATE `users` SET `active` = '1' WHERE `password` = '$pass' AND `timestamp` = $stamp";
$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
?>


Im getting the following error:

The error was: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
The query was: UPDATE `users` SET `active` = '1' WHERE `password` = '35f504164d5a963d6a820e71614a4009' AND `timestamp` =

I cant see where the problem is.

_Aerospace_Eng_
03-02-2008, 06:56 PM
Where are $password and $stamp coming from? I don't seem them declared in your code. md5 always returns something even if its just a blank string. You can use this generator on your password to check and see if it matches the current md5 value.

http://www.adamek.biz/md5-generator.php

PRodgers4284
03-02-2008, 07:10 PM
Where are $password and $stamp coming from? I don't seem them declared in your code. md5 always returns something even if its just a blank string. You can use this generator on your password to check and see if it matches the current md5 value.

http://www.adamek.biz/md5-generator.php

Aerospace, could i check the username and password instead of the password and timestamp.

My full code for the register.php is:


<?php
$error_stat = 0;
$username_message = '';
$password_message = '';
$forename_message = '';
$surname_message = '';
$email_message = '';
$mobile_message = '';
$dob_message = '';
$location_message = '';
$checkbox_message = '';


if (isset($_POST['submit'])) {

$username = $_POST['username'];
$password1 = $_POST['password'];
$password2 = $_POST['password2'];
$md5password = md5($_POST['password']);
$forename = $_POST['forename'];
$surname = $_POST['surname'];
$email = $_POST['email'];
$mobile = $_POST['mobile'];
$dob = $_POST['dob'];
$location = $_POST['location'];
$ip = $_SERVER['REMOTE_ADDR'];


//Error checking




//Username check)
if (empty($username)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a username
$username_message = '*Please enter a username*';
}
if(usernameTaken($username,$conn))
{
$error_stat = 1;
$username_message = '*User name is taken, choose another one*';
}

$username = $_POST['username'];
$username = trim($username);

if (strlen($username) > 12){
$error_stat = 1;
$username_message = '*The username must be 12 characters or less*';
}

$username = $_POST['username'];
$username = trim($username);

if (strlen($username) < 4){
$error_stat = 1;
$username_message = '*Username must be at least 4 characters*';
}

else if ( preg_match( '/\W/', $username)){
$error_stat = 1;
$username_message = '*Invalid username, letters only, no spaces*';

}

//Password check)
if($password1 != $password2)
{
$error_stat = 1;
$password_message = '*Passwords don\'t match*';
}

if (empty($password1)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a username
$password_message = '*Please enter a password*';
}

if(!$password1 || !$password2)
{
$error_stat = 1;
$password_message = '*Please enter both passwords*';
}

$password = $_POST['password'];
$password = trim($password);

if (strlen($password) < 4){
$error_stat = 1;
$password_message = '*Password must be at least 4 characters*';
}

else if ( preg_match( '/\W/', $password)){
$error_stat = 1;
$password_message = '*Invalid password, letters only, no spaces*';

}




//Forename check)
if (empty($forename)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a username
$forename_message = '*Please enter your forename*';
}

else if (ctype_digit($forename)) {
$error_stat = 1;
$forename_message .= '*Invalid forename*';
}

else if ( preg_match( '/\W/', $forename)){
$error_stat = 1;
$forename_message = '*Invalid forename, letters only, no spaces*';

}


$forename = $_POST['forename'];
$forename = trim($forename);

if (strlen($forename) > 12){
$error_stat = 1;
$forename_message = '*The forename must be 12 characters or less*';
}




//Surname check)
if (empty($surname)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a username
$surname_message = '*Please enter your surname*';
}

else if (ctype_digit($surname)) {
$error_stat = 1;
$surname_message .= '*Invalid surname*';
}

else if ( preg_match( '/\W/', $surname)){
$error_stat = 1;
$surname_message = '*Invalid surname, letters only, no spaces*';

}



$surname = $_POST['surname'];
$surname = trim($surname);

if (strlen($surname) > 12){
$error_stat = 1;
$surname_message = '*The surname must be 12 characters or less*';
}





//Email check)
if (empty($email)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter an email address
$email_message = '*Please enter your email address*';
}

//Check format of email address entered
else if(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$", $email)){
$error_stat = 1;
//Set the message to tell the user to enter a valid email address
$email_message = '*Invalid Email Address*';
}

if(emailTaken($email,$conn))
{
$error_stat = 1;
$email_message = '*Email is taken please choose another one*';
}

$email = $_POST['email'];
$email = trim($email);

if (strlen($email) > 30){
$error_stat = 1;
$email_message = '*The email address must be 30 characters or less*';
}




//Mobile number check)

if (empty($mobile)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a dob
$mobile_message = '*Please enter your mobile number*';
}

else if (!ctype_digit($mobile)) {
$error_stat = 1;
$mobile_message .= '*The mobile phone number must be only numbers*';
}

if(mobileTaken($mobile,$conn))
{
$error_stat = 1;
$mobile_message = '*Mobile already in use, choose another one*';
}



$mobile = $_POST['mobile'];
$mobile = trim($mobile);

if (strlen($mobile) > 11){
$error_stat = 1;
$mobile_message = '*Invalid mobile number*';
}

$mobile = $_POST['mobile'];
$mobile = trim($mobile);

if (strlen($mobile) < 11){
$error_stat = 1;
$mobile_message = '*Invalid mobile number, must be 11 numbers*';
}


//DOB check)

if (empty($dob)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a dob
$dob_message = '*Please enter your date of birth*';
}

//Check the format and explode into $parts
elseif (!ereg("^([0-9]{2})/([0-9]{2})/([0-9]{4})$",
$dob, $parts)){
$error_stat = 1;

//Set the message to tell the user the date is invalid
$dob_message = '*Invalid dob, must be DD/MM/YYYY format*';
}

elseif (!checkdate($parts[2],$parts[1],$parts[3]))
{
$error_stat = 1;

//Set the message to tell the date is invalid for the month entered
$dob_message = '*Invalid dob, month must be between 1-12*';
}

elseif (intval($parts[3]) < 1948 ||
intval($parts[3]) > intval(date("Y")))
{

$error_stat = 1;

//Set the message to tell the user the date is invalid for the year entered
$dob_message = '*Invalid dob, year must 1948 onwards*';
}

//Terms and condition check)
if(!isset($_POST['checkthis'])){
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a dob
$checkbox_message = '*You did not accept terms and conditions*';
}

if ($location == 'Please Select'){
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;
$location_message = '*Please select a location*';
}



//Then, only run the query if there were no errors (if $error_stat still equals 0)
if ($error_stat == 0) {
mysql_query("INSERT INTO users (username, password, forename, surname, email, mobile, dob, location, ipaddress) VALUES ('$username', '$md5password', '$forename', '$surname', '$email', '$mobile', '$dob', '$location', '$ip')");
mysql_query("INSERT INTO cv (username) VALUES ('$username')");
echo "<h3>Registration Successful!</h3>";
echo "<p>Thankyou, <b>$username</b>,registration was successful</p>";
echo "<p>login.</p>";
echo "<a href=\"index.php\">Login</a>";

//Then, only run the query if there were no errors (if $error_stat still equals 0)
require_once('class.phpgmailer.php');
$mail = new PHPGMailer();
$mail->IsSMTP(); // send via SMTP
$mail->Host = 'ssl://smtp.gmail.com'; // SMTP servers
$mail->FromName = '*******.com';
$mail->AddAddress($email);
$mail->Subject = '*******Registration';
$mail->Body = "Your account has been successfully created with the following details:\n\nUsername: $username\nPassword: $password\nEmail: $email\nForename: $forename\nSurname: $surname\nLocation: $location\n\nPlease click on the link to activate your account.\n";
$mail->Body = "<a href='http://localhost/Jobs4U/activate.php?username=$username'>Activate Account</a>";
$mail->Send();
}
}

//Then, for the form, only show it if 1) the form hasn't been submitted yet OR 2) there is an error
if (!isset($_POST['submit']) || $error_stat == 1) {


?>



activate.php


<?php
include("database.php");
$pass = md5($_GET['hash']);
$stamp = base64_decode($_GET['stamp']);
$sql = "UPDATE `users` SET `active` = '1' WHERE `password` = '$pass' AND `timestamp` = $stamp";
$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
?>

PRodgers4284
03-02-2008, 08:09 PM
I have changed the email script to check the username and password, not sure if its correct or not:


require_once('class.phpgmailer.php');
$mail = new PHPGMailer();
$mail->IsSMTP(); // send via SMTP
$mail->Host = 'ssl://smtp.gmail.com'; // SMTP servers
$mail->FromName = '*******.com';
$mail->AddAddress($email);
$mail->Subject = '******Registration';
$mail->Body = 'http://localhost/Jobs4U/activate.php?username=$username&password=$password
$mail->Send();


activate.php file is changed to:


<?php
include("database.php");
$sql = "UPDATE `users` SET `active` = '1' WHERE `username` = '$username' AND `password` = $password";
$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
?>

_Aerospace_Eng_
03-02-2008, 09:10 PM
Bad idea, now their password will be visible in the url. And no its not correct.

$mail->Body = "http://localhost/Jobs4U/activate.php?username=$username";
You could just use username but you need something unique. What I do is generate a random string and store it into the database. Then when I send them the welcome email I put their random string in the activate link. Then when they go to activate I check the database for the string. On your activate page you never tell the script what $username and $password are.

<?php
include("database.php");
$username = '';
if(isset($_GET['username']))
{
$username = mysql_real_escape_string($_GET['username']);
}
$sql = "UPDATE `users` SET `active` = '1' WHERE `username` = '$username'";
$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
?>
Here is my current php verify email script if it helps.

<?php
include('dbconnect.php');
include('functions.php');
$success = '';
$rid = '';
unset($_SESSION['msg']);
if($rid == '')
{
$rid = escape_data($_GET['rid']);
$sql = "SELECT * FROM userinfo WHERE verified = 0 AND encstr = '$rid'";
$result = mysql_query($sql) or die("The mysql error was: " . mysql_error() . "<br>The sql was: " . $sql);
if(mysql_num_rows($result) == 0)
{
$_SESSION['msg'] = "Your email has not been verified. Please check to make sure that you have used the link as specified in your email";
}
if(mysql_num_rows($result) == 1)
{
$sql = "UPDATE userinfo SET verified=1 WHERE encstr = '$rid'";
doQuery($sql);
if(isset($_SESSION['page']))
{
$_SESSION['msg'] = '<p>Your email address has been verified. You will be redirected shortly to the page you were viewing before you registered. If you are not redirected click <a href="'.$_SESSION['page'].'">here</a></p>';
header("Refresh: 5;url=".$_SESSION['page']);
}
else
{
$_SESSION['msg'] = '<p>Your email address has been verified. You will be redirected shortly to the login page. If you are not redirected click <a href="login.php">here</a></p>';
header("Refresh: 5;url=http://".$_SERVER['HTTP_HOST']."/login.php");
}

}
if(mysql_num_rows($result) > 1)
{
$_SESSION['msg'] = 'There was a problem verifying your email address. Please contact me by clicking <a href="contact.php">here</a>';
}
}
mysql_close($db);
?>

PRodgers4284
03-02-2008, 09:38 PM
Bad idea, now their password will be visible in the url. And no its not correct.

$mail->Body = "http://localhost/Jobs4U/activate.php?username=$username";
You could just use username but you need something unique. What I do is generate a random string and store it into the database. Then when I send them the welcome email I put their random string in the activate link. Then when they go to activate I check the database for the string. On your activate page you never tell the script what $username and $password are.

<?php
include("database.php");
$username = '';
if(isset($_GET['username']))
{
$username = mysql_real_escape_string($_GET['username']);
}
$sql = "UPDATE `users` SET `active` = '1' WHERE `username` = '$username'";
$result = mysql_query($sql) or die('The error was: ' . mysql_error() . '<br>The query was: ' . $sql);
?>
Here is my current php verify email script if it helps.

<?php
include('dbconnect.php');
include('functions.php');
$success = '';
$rid = '';
unset($_SESSION['msg']);
if($rid == '')
{
$rid = escape_data($_GET['rid']);
$sql = "SELECT * FROM userinfo WHERE verified = 0 AND encstr = '$rid'";
$result = mysql_query($sql) or die("The mysql error was: " . mysql_error() . "<br>The sql was: " . $sql);
if(mysql_num_rows($result) == 0)
{
$_SESSION['msg'] = "Your email has not been verified. Please check to make sure that you have used the link as specified in your email";
}
if(mysql_num_rows($result) == 1)
{
$sql = "UPDATE userinfo SET verified=1 WHERE encstr = '$rid'";
doQuery($sql);
if(isset($_SESSION['page']))
{
$_SESSION['msg'] = '<p>Your email address has been verified. You will be redirected shortly to the page you were viewing before you registered. If you are not redirected click <a href="'.$_SESSION['page'].'">here</a></p>';
header("Refresh: 5;url=".$_SESSION['page']);
}
else
{
$_SESSION['msg'] = '<p>Your email address has been verified. You will be redirected shortly to the login page. If you are not redirected click <a href="login.php">here</a></p>';
header("Refresh: 5;url=http://".$_SERVER['HTTP_HOST']."/login.php");
}

}
if(mysql_num_rows($result) > 1)
{
$_SESSION['msg'] = 'There was a problem verifying your email address. Please contact me by clicking <a href="contact.php">here</a>';
}
}
mysql_close($db);
?>


Hi Aerospace, I have the username as unique, i have a check within the registration page that prevents users from entering a username that already exists in the database, so each username will be unique to each user. I was intending to use md5 for the password so that it is not visible. Would it be ok to use the username and password for the activation?

_Aerospace_Eng_
03-02-2008, 09:55 PM
If you want but you need to get your script working first. So now what do you have? Remember you still need to tell the activate script where the variables are coming from.

PRodgers4284
03-03-2008, 02:17 PM
If you want but you need to get your script working first. So now what do you have? Remember you still need to tell the activate script where the variables are coming from.


Aerospace i got i working with the username, the activation.php file updates the table field "active" once the user selects the url in the email. Thanks again for your help, appreciate it :)

rafiki
03-03-2008, 05:35 PM
why are you md5($_GET['pass']) when its already been done before it was emailled?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum