basic question regarding email activation

hpesoj_m

this is my script for inserting a data to mysql at the same time sending an activation link to the new registered user

insert.php

<?
$database="database_registration";

$email =$_POST['emailForm'];
$password =$_POST['passwordForm'];
$fname =$_POST['fnameForm'];
$lname =$_POST['lnameForm'];
$address =$_POST['addressForm'];
$cnumber =$_POST['cnumberForm'];
$timeColumn = time();
$statColumn = 0;
$url = 'http://www.domain.com/test/activate.php?reg='.md5($password).'&code='.base64_encode($timeColumn);
$db_connection = mysql_connect("localhost","domain","password");
$result = mysql_query($sql, $db_connection);
mysql_select_db($database) or die(mysql_error());
$query="INSERT INTO regusers VALUES ('','$email','$password','$fname','$lname','$address','$cnumber','$timeColumn','$statColumn')";
mysql_query($query);
mysql_close();

$From ="Company";
$ToEmail = "$email";
$ToName = "$fname $lname";
$ToSubject = "Company Registration";
$Message = "Please activate your registration : \n $url \n Company Registration Activate";

mail($ToName." <".$ToEmail.">",$ToSubject, $Message, "From: ".$From." <".$Email.">");

?>

with that, this is how my activation email looks like :

Please activate your registration :
http://www.domain.com/test/activate.php?reg=5f4dcc3b5aa765d61d8327deb882cf99&code=MTExOTg2MTU0OA==
Company Registration Activate

...ive been wondering how did i get that last 2 "==" after my link. means to say that my link stops at the last "OA" but the 2 equal signs stays just right after "OA". is there a way that i could take that off if thats not part of the link

for the second question

activate.php

<?
$database="database_registration";
$db_connection = mysql_connect("localhost","domain","password");
mysql_select_db($database) or die(mysql_error()); 
mysql_query("UPDATE regusers SET status = 1 WHERE (passworddb = " .md5($_GET['reg']) . ") AND (timedb = " .base64_decode($_GET['code']). ")", $db_connection) or die(mysql_error());
mysql_close();

?>

is there something wrong with this line???

mysql_query("UPDATE regusers SET status = 1 WHERE (passworddb = " .md5($_GET['reg']) . ") AND (timedb = " .base64_decode($_GET['code']). ")", $db_connection) or die(mysql_error());

...coz ive been getting this kind of error

Unknown column '696d29e0940a4957748fe3fc9efd22a3' in 'where clause'

thanks in advance!

Weedpacket

Because you didn't put quotes around your MD5 string (passworddb = '" .md5($_GET['reg']) . "') , the database reads the query as asking about the contents of a column named "696d29e0940a4957748fe3fc9efd22a3" and it can't find one.

hpesoj_m

Originally posted by Weedpacket
Because you didn't put quotes around your MD5 string (passworddb = '" .md5($_GET['reg']) . "') , the database reads the query as asking about the contents of a column named "696d29e0940a4957748fe3fc9efd22a3" and it can't find one.

...should i also add a quote on the

" .base64_decode($_GET['code'])

...tried changing it already, but to no avail, the status of the user didnt update....same script except for the addition of the quote. the error is no longer there, but no updates has been made in the mysql

hpesoj_m

hi guys...can i anyone help me with my last problem...been having a hard time making the update of the status of the new register user...this is my script for activate.php

<?
$database="database_registration";
$db_connection = mysql_connect("localhost","username","password");
mysql_select_db($database) or die(mysql_error()); 
mysql_query("UPDATE regusers SET status = 1 WHERE (passworddb = '" .md5($_GET['reg']) . "') AND (timedb = '" .base64_decode($_GET['code']). "')", $db_connection) or die(mysql_error());
mysql_close();

?>

...that script will be use for the activation that the register user would receive right after he/she register...sample of the link is below

http://www.rated17.com/test/activate.php?reg=5f4dcc3b5aa765d61d8327deb882cf99&code=MTExOTg2NjQ0OA

thanks in advance!

Weedpacket

Originally posted by hpesoj_m
...ive been wondering how did i get that last 2 "==" after my link. means to say that my link stops at the last "OA" but the 2 equal signs stays just right after "OA". is there a way that i could take that off if thats not part of the link

It should be part of the link, the == is the end of the base-64 encoding of '1119861548'. The fact that it wasn't included in the link is because "MTExOTg2MTU0OA==" is invalid in a URL - because of the '=' signs. You'll want to use urlencode(base64_encode($timeColumn)). 'Course, decoding should work without them, but '+' and '/' are also valid base64 characters, and they will stuff up URLs if they appear.

Two questions about your database schema:
1) Do you have some way of identifying users other than their choice of password (I'm thinking of the remote but possible case that two users might choose the same password)?
2) What is the type of your timedb field? If it's a timestamp, you should be aware that MySQL's timestamps are a different format to the Unix timestamps used elsewhere (such as in time()).

Oh, and an observation:

"... WHERE (passworddb = '" .md5($_GET['reg']) . "')

Isn't $_GET['reg'] already MD5'd? I think it is, which means you're searching not for md5($password), but md5(md5($password)) - which is not likely to be there.

hpesoj_m

actually the timestamp part is completely ok...what ive been confusing about is having the password encrypted with md5 coz the script cant seem to find them in the database.

for example, a new register user has a password of "minepassword" and stores that "minepassword" to the column passwordClm in the mysql database. then send the authentication email in this form

$url = 'http://www.domain.com/test/activate.php?reg='.md5($password).'&code='.urlencode(base64_encode($timestamp));

...when the newly registered user received the email and click that link, it leads them to the activate.php page wc has a script :

mysql_query("UPDATE regusers SET status = 1 WHERE (passwordClm = '".($_GET['reg'])."') AND (timedb = '".base64_decode($_GET['code'])."')", $db_connection) or die(mysql_error());

the decoding of timestamp (base64_encode($timestamp)) ==> base64_decode($timestamp))) manage to find its match, but the password cant. thats why im wondering if theres a decoding script for md5, same as base64_decode for base64_encode...

thanks in advance!

Weedpacket

Originally posted by hpesoj_m
thats why im wondering if theres a decoding script for md5

No, there isn't.

hpesoj_m

...so is there something wrong with my script??? how can my md5 password cant seem to find its match in the mysql database...