Problems with email activation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Problems with email activation

Post by Blaade »

HI!

My Sign up script:

Code: Select all

<?php
session_start();
$mysql_server = 'localhost';
$mysql_username = 'username';
$mysql_password = 'password;
$mysql_database = 'database';
$mysql_table = 'USERS';
$success_page = './thank_you_page.html';
$error_message = "";
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'signupform')
{
   $newusername = $_POST['username'];
   $newemail = $_POST['email'];
   $newpassword = $_POST['password'];
   $confirmpassword = $_POST['confirmpassword'];
   $website = $_SERVER['HTTP_HOST'];
   $script = $_SERVER['SCRIPT_NAME'];
   $timestamp = time();
   $code = md5($website.$timestamp.rand(100000, 999999));
   if ($newpassword != $confirmpassword)
   {
      $error_message = 'Password and Confirm Password are not the same!';
   }
   else
   if (!ereg("^[A-Za-z][a-z_.]{3,25}[a-z0-9]$", $newusername))
   {
      $error_message = 'Username is not valid, please check and try again!';
   }
   else
   if (!ereg("^[a-z0-9]{5,50}$", $newpassword))
   {
      $error_message = 'Password is not valid, please check and try again!';
   }
      else
   if (!ereg("^[A-Za-z0-9\.|-|_]*[@]{1}[A-Za-z0-9\.|-|_]*[.]{1}[a-z]{2,5}$", $newemail))
   {
      $error_message = 'Email is not a valid email address. Please check and try again.';
   }
     if (empty($error_message))
   {
      $db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
      if (!$db)
      {
         die('Failed to connect to database server!<br>'.mysql_error());
      }
      mysql_select_db($mysql_database, $db) or die('Failed to select database<br>'.mysql_error());
      $sql = "SELECT username FROM ".$mysql_table." WHERE username = '".$newusername."'";
      $result = mysql_query($sql, $db);
      if ($data = mysql_fetch_array($result))
      {
         $error_message = 'Username already used. Please select another username.';
      }
   }
   if (empty($error_message))
   {
      $crypt_pass = md5($newpassword);
      $newusername = mysql_real_escape_string($newusername);
      $newemail = mysql_real_escape_string($newemail);
      $newfullname = mysql_real_escape_string($newfullname);
      $sql = "INSERT `".$mysql_table."` (`username`, `password`, `email`, `active`, `code`) VALUES ('$newusername', '$crypt_pass', '$newemail', 1, '$code')";
      $result = mysql_query($sql, $db);
      mysql_close($db);
      $subject = 'Email confirmation';
      $message = 'Hi! Thanks for creating an account on our site. Click the link below to confirm your email address:';
      $message .= "\r\nUsername: ";
      $message .= $newusername;
      $message .= "\r\nPassword: ";
      $message .= $newpassword;
      $message .= "\r\n";
      $message .= "\r\nhttp://".$website.$script."?user=".$newusername."&code=$code";
      $message .= "\r\n\r\nThis is an automated message - please do not reply";
      $header  = "From: webmaster@myhoo22.com"."\r\n";
      $header .= "Reply-To: webmaster@myhoo22.com"."\r\n";
      $header .= "MIME-Version: 1.0"."\r\n";
      $header .= "Content-Type: text/plain; charset=utf-8"."\r\n";
      $header .= "Content-Transfer-Encoding: 8bit"."\r\n";
      $header .= "X-Mailer: PHP v".phpversion();
      mail($newemail, $subject, $message, $header);
      header('Location: '.$success_page);
      exit;
   }
}
else
if (isset($_GET['code']) && isset($_GET['user']))
{
   $db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
   if (!$db)
   {
      die('Failed to connect to database server!<br>'.mysql_error());
   }
   mysql_select_db($mysql_database, $db) or die('Failed to select database<br>'.mysql_error());
   $sql = "SELECT * FROM ".$mysql_table." WHERE username = '".$_GET['user']."' AND code = '".$_GET['code']."'";
   $data = mysql_query($sql);
   if(!$data) {
	  die("There was an error in the following sql statement :<hr>$sql");
	}  
        mysql_close($db);
   header("refresh:5;url=log_in.php");
echo 'Your user account was succesfully activated. You\'ll be redirected in about 5 secs. If not, click <a href="log_in.php">here</a>.';
exit;
}
?>
My Login script:

Code: Select all

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'loginform')
{
   $success_page = './index.php';
   $error_page = './error_page.html';
   $mysql_server = 'localhost';
   $mysql_username = 'myhoo22c_blade';
   $mysql_password = 'opinci23';
   $mysql_database = 'myhoo22c_bladesdb';
   $mysql_table = 'USERS';
   $crypt_pass = md5($_POST['password']);
   $found = false;
   
   $db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
   if (!$db)
   {
      die('Failed to connect to database server!<br>'.mysql_error());
   }
   mysql_select_db($mysql_database, $db) or die('Failed to select database<br>'.mysql_error());
   $sql = "SELECT password, active FROM ".$mysql_table." WHERE username = '".mysql_real_escape_string($_POST['username'])."'";
   $result = mysql_query($sql, $db);
   if ($data = mysql_fetch_array($result))
   {
      if ($crypt_pass == $data['password'] && $data['active'] != 0)
      {
         $found = true;
               }
   }
   mysql_close($db);
   if($found == false)
   {
      header('Location: '.$error_page);
      exit;
   }
   else
   {
      session_start();
      $_SESSION['username'] = $_POST['username'];
      $rememberme = isset($_POST['rememberme']) ? true : false;
      if ($rememberme)
      {
         setcookie('username', $_POST['username'], time() + 3600*24*30);
         setcookie('password', $_POST['password'], time() + 3600*24*30);
      }
      header('Location: '.$success_page);
      exit;
   }
}
$username = isset($_COOKIE['username']) ? $_COOKIE['username'] : '';
$password = isset($_COOKIE['password']) ? $_COOKIE['password'] : '';
?>
Now i set in mysql table the column "active" and set it to ENUM ('0','1') default 0. I receive email activation link, i click it but the active status in the database doesn't turn to 1, it stays 0 (default) and i can't login. What is wrong in my scripts, do i need to add something or i shouldn't use ENUM? I don't do complex coding, i use a web builder program and for the complex stuff that need scripts i take them from sites. Hope you can resolve my problem. Thanks!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

ENUM is fine. The problem is you're trying to insert the value 1, when you need to insert the value '1'

Other notes:
You're missing a closing quote from $mysql_password, though that's probably just from obfuscating the password for here.
ereg() is deprecated. You should use preg_match().
Don't use md5 to hash passwords. It's better than nothing, but not much.
Don't use SELECT *
Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Re: Problems with email activation

Post by Blaade »

Thanks for the reply. I tried value '1' but then the active status becomes 1 after the user submits the sign up form and can login without clicking the validation link. Tried values '0,' '$0', $0, '$1' and so on for 2 days. The quote from $mysql_password is my bad, erased it by mistake when i've put "password". I will try preg_match(), saw some examples on the internet. As for the don't use SELECT*...i don't know what to use instead. Unfortunately i don't know coding. The php script u see there is made with a drag and drop web builder program which has the option "Convert to form" if i want to add, remove or modify stuff. I managed to modify a lot of things as i wanted with scripts i found on the internet, can't complain but for 2 days i face 2 problems which seems i can't solve: this one and to save data form on another page within website, another post added on this forum which i think u answered as well.
On other forum someone said to use and UPDATE query so the active value would change from 0 to 1 when the activation link is clicked but having a hard time with that too...Tried

Code: Select all

mysql_query("UPDATE users SET active=1 WHERE email='$newemail'"); 
,

Code: Select all

$query = "UPDATE users SET `active`=1 WHERE `email`='$newemail'";
with quotes, without, but nothing. Just making a fool out of myself.
Thanks a lot for the reply and advices, if u have some more feel free to write them, i really appreciate it.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

Blaade wrote:I tried value '1' but then the active status becomes 1 after the user submits the sign up form and can login without clicking the validation link.
I assumed that was the desired behaviour since I saw 1 in the INSERT query. Besides, the bit that checks $_GET['user'] and $_GET['code'] doesn't update the database, so it doesn't really do anything. You may want to consider reworking that bit.
Blaade wrote:As for the don't use SELECT*...i don't know what to use instead.
Specify which column(s) you want. It saves you from transferring data needlessly and will make it easier to detect bugs if your table schema changes and your code isn't changed accordingly.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

I've made a couple of minor modifications that should make the registration page behave as intended. This is untested, so I can't guarantee there are no mistakes.

Code: Select all

<?php

session_start();
$mysql_server = 'localhost';
$mysql_username = 'username';
$mysql_password = 'password';
$mysql_database = 'database';
$mysql_table = 'USERS';
$success_page = './thank_you_page.html';
$error_message = "";
if ($_SERVER['REQUEST_METHOD'] == 'POST' && $_POST['form_name'] == 'signupform')
{
    $newusername = $_POST['username'];
    $newemail = $_POST['email'];
    $newpassword = $_POST['password'];
    $confirmpassword = $_POST['confirmpassword'];
    $website = $_SERVER['HTTP_HOST'];
    $script = $_SERVER['SCRIPT_NAME'];
    $timestamp = time();
    $code = md5($website . $timestamp . rand(100000, 999999));
    if ($newpassword != $confirmpassword)
    {
        $error_message = 'Password and Confirm Password are not the same!';
    }
    else
    if (!ereg("^[A-Za-z][a-z_.]{3,25}[a-z0-9]$", $newusername))
    {
        $error_message = 'Username is not valid, please check and try again!';
    }
    else
    if (!ereg("^[a-z0-9]{5,50}$", $newpassword))
    {
        $error_message = 'Password is not valid, please check and try again!';
    }
    else
    if (!ereg("^[A-Za-z0-9\.|-|_]*[@]{1}[A-Za-z0-9\.|-|_]*[.]{1}[a-z]{2,5}$", $newemail))
    {
        $error_message = 'Email is not a valid email address. Please check and try again.';
    }
    if (empty($error_message))
    {
        $db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
        if (!$db)
        {
            die('Failed to connect to database server!<br>' . mysql_error());
        }
        mysql_select_db($mysql_database, $db) or die('Failed to select database<br>' . mysql_error());
        $sql = "SELECT username FROM " . $mysql_table . " WHERE username = '" . $newusername . "'";
        $result = mysql_query($sql, $db);
        if ($data = mysql_fetch_array($result))
        {
            $error_message = 'Username already used. Please select another username.';
        }
    }
    if (empty($error_message))
    {
        $crypt_pass = md5($newpassword);
        $newusername = mysql_real_escape_string($newusername);
        $newemail = mysql_real_escape_string($newemail);
        $newfullname = mysql_real_escape_string($newfullname);
        $sql = "INSERT `" . $mysql_table . "` (`username`, `password`, `email`, `active`, `code`) VALUES ('$newusername', '$crypt_pass', '$newemail', '0', '$code')";
        $result = mysql_query($sql, $db);
        mysql_close($db);
        $subject = 'Email confirmation';
        $message = 'Hi!Thanks for creating an account on our site. Click the link below to confirm your email address:';
        $message .= "\r\nUsername: ";
        $message .= $newusername;
        $message .= "\r\nPassword: ";
        $message .= $newpassword;
        $message .= "\r\n";
        $message .= "\r\nhttp://" . $website . $script . "?user=" . $newusername . "&code=$code";
        $message .= "\r\n\r\nThis is an automated message - please do not reply";
        $header = "From: webmaster@myhoo22.com" . "\r\n";
        $header .= "Reply-To: webmaster@myhoo22.com" . "\r\n";
        $header .= "MIME-Version: 1.0" . "\r\n";
        $header .= "Content-Type: text/plain; charset=utf-8" . "\r\n";
        $header .= "Content-Transfer-Encoding: 8bit" . "\r\n";
        $header .= "X-Mailer: PHP v" . phpversion();
        mail($newemail, $subject, $message, $header);
        header('Location: ' . $success_page);
        exit;
    }
}
else
if (isset($_GET['code']) && isset($_GET['user']))
{
    $db = mysql_connect($mysql_server, $mysql_username, $mysql_password);
    if (!$db)
    {
        die('Failed to connect to database server!<br>' . mysql_error());
    }
    mysql_select_db($mysql_database, $db) or die('Failed to select database<br>' . mysql_error());
    // I'm assuming the column id exists. If not, use your primary key in place of id
    $sql = "SELECT id FROM " . $mysql_table . " WHERE username = '" . $_GET['user'] . "' AND code = '" . $_GET['code'] . "'";
    $id = mysql_fetch_row(mysql_query($sql));
    if (!$id)
    {
        die("There was an error in the following sql statement :<hr>$sql");
    }
    // User has been found, so we'll activate the account
    $query = "UPDATE {$mysql_table} SET active = '1' WHERE id = {$id}";
    mysql_query($query);
    header("refresh:5;url=log_in.php");
    echo 'Your user account was succesfully activated. You\'ll be redirected in about 5 secs. If not, click <a href="log_in.php">here</a>.';
    exit;
}
?>
Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Re: Problems with email activation

Post by Blaade »

I want the value to be 0 in the "active" column, in the database, until the user clicks the validation link and after he clicks to become 1. Even if in the INSERT the value is 1, it shows 0 in the database. The "$_GET['user'] and $_GET['code']" i don't know what they do. I just dragged the "Sign up form" and introduced in Properties the font, colors, background, mysql db and a whole bunch of other stuff and the program makes the script according to what i select in Properties and it has and option called "Convert to form" where i can see the script and add,remove,change what i want as long it's correct. So, what u see there is the script made by the program not by me...
Now i'm killing myself to introduce some UPDATE query but nothing seems to function... :banghead:
Hope i'll get to the bottom of this until i have kids:)Thanks again for your time, i appreciate it.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

Did you try the code I posted above? It's untested, as I mentioned, but it should work.
Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Re: Problems with email activation

Post by Blaade »

Sure nice to see someone helping out...
After i click the validation link this appears : There was an error in the following sql statement :SELECT id FROM users WHERE username = 'Blade' AND code = '2781ef92cfaf2e7350c99d2ac6865a8d'
I made "id" column and made it primary key.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

Use mysql_error() to see what the error actually says, or run the query manually.
Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Re: Problems with email activation

Post by Blaade »

let me see if i know where to do that:)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

phpMyAdmin is probably easiest, unless you have shell access.
Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Re: Problems with email activation

Post by Blaade »

i am in phpmyadmin but...wrote in SQL and gives me syntax error....put it in Query and it puts me too select columns..
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

Are there no details provided along with that syntax error?
Blaade
Forum Commoner
Posts: 29
Joined: Tue Feb 28, 2012 8:44 am

Re: Problems with email activation

Post by Blaade »

only...#1064 - 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 'mysql_error()' at line 1
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problems with email activation

Post by Celauran »

mysql_error() is PHP code. I meant copy the actual query into phpMyAdmin.

Code: Select all

SELECT id FROM users WHERE username = 'Blade' AND code = '2781ef92cfaf2e7350c99d2ac6865a8d'
Post Reply