Registration Verification Link & Mysql

A

Anonymous

Guest
Very sorry for the long post,
but I need desperate help and sleep.
Ok here I go ,

Because I knew nothing when I started:
I have put together a simple login/registration system for my website
my first successful attempt was simply to register the user in the database
and if successful forwarded to the login page.
Since using this method i've noticed that some users are creating false usernames and accounts
which is becoming a catastrophe for me.

I decided i want to "integrate" a verification process where the user receives
an email with a link back to the website or preferably eg."verified.php"
for at least one line of defense against fake accounts.

I googled around(honestly im not a good coder or script writer)
grabbed a few useful snippets and began amending my reg system.

first I created 2 test files, "veremail.html" & "email.php"
then i modified my registration form action=register.php to action=veremail.html
so my form is now pointing to my test files.

I created a very basic test html page("veremail.html") stating "please enter email and submit".
the page has a form with 1 input field as 'email' and a submit button.
once submitted my form launches my mailer form ("form action=email.php").
I press submit and an echo msg displays "Success!" check your mail.

I open my inbox and in my spam folder is my email sent from noreply@mywebsite.com
inside are the instructions to click the verification link "http://mywebsite.com/register.php
the 2 test files are working perfectly. well so it seems.

This is where the trouble started
I assumed/guessed I could integrate my test files by simply changing the file order
example:
(original) registration form action=register.php which includes common.php(dbase), then to "protected area.php"
(amended) registration form action=veremail.php when submitted opens email.php and sends an email to the email address entered.
containing a link "http://membersonly@mywebsite.com/register.php"

In my email I click the link but 'no bananas' I get a blank page resulting registration.php i realise that my username, email and password has been forgotten
by the original registration form because my input is on my original reg form and I am NOT registered.
I instant panic because the website is LIVE.
So I've fudged this for now by (A)changing my original form action back the way it was.
so as soon as somebody registers the original method is executed and (B) in my config.php changed the header location for successful registration to "veremail.php"
and (C) changed the link in my mailer to simply http://my/website.com/home
to follow the amended path through their inbox back to my website.
I only did this to get the website back up and running without too much interference as im getting a lot of quality new registrations daily.
as i deal with bitcoin and this is a fairly new project for me I've been eagerly monitoring everything and stats so i can judge the performance of my content.
If the abusers hadn't of hit my site so hard i wouldn't have noticed except today when monitoring my transactions I notice the strange behaviour


my user is now unaware that they are already registered and the email exercise is futile.
all the user has to do is press back twice on their browser or enter my address and the user can login normally.
without even opening the email or clicking the link.
it wouldn't take long for somebody to figure this out
and i need it to work genuinely and ASAP as i'm concerned about losing new member confidence due to my inability
to code the script properly.
I really want to get this right for my members, I love them all for being involved but the abusers are gonna ruin it for everyone.

I read somewhere that i could add a row in my existing database eg. "verified_user"
that would add a unique token code to the end of my confirmation link
this is what i wanted but i found it very hard to implement the instructions into my current system(set of files)

Any help would be greatly appreciated
 
Here is some code, I hope it helps


veremail.php

Code: 
<div>
<center><font size="7" color="green"><u>MY WEBSITE!</u></font></center>
<br />
<br />
<center><font size="6" color="blue"><u>ACCOUNT ACTIVATION!</u></font></center>
<br />
<br />
<center><font size="5">
Please enter your email to receive a confirmation link to your inbox</font>
<br />
<br />
  <form action="email.php" method="post">
  Email: <input name="email" type="text" /><br />
  <br />
  <input type="submit" value="Submit" />
  </form>
</div>


my email.php

Code: 
<?php

require("common.php");

//if "email" variable is filled out, send email
  if (isset($_REQUEST['email']))  {
 
  //Email information
  $admin_email = "noreply@mywebsite.com";
  $email = $_REQUEST['email'];
  $subject ="Account Activation Link";
  $comment ="Please activate your account by clicking the link http://mywebsite.com/!!!SOLUTION GOES HERE!!!";
  
  //send email
  mail($email, "$subject", $comment, "From:" . $admin_email);
  
  //Email response
  echo "<br /><br /><center><h1><big><u>MY WEBSITE</u></big></h5></center><br /><br />
        <center><h1><u>SUCCESS!!!</u></h1></center><br /><br />
        <center><h3>check your inbox or spam folder to get your activation link.</h3></center>
        ";
  }
At the end I had a link back to the login but i took it out for for obvious reasons

My registration form is on a page of my site(for now)
registration.php

Code: 
<?php 

    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 
     
    // This if statement checks to determine whether the registration form has been submitted 
    // If it has, then the registration code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // Ensure that the user has entered a non-empty username 
        if(empty($_POST['username'])) 
        { 
            // Note that die() is generally a terrible way of handling user errors 
            // like this.  It is much better to display the error with the form 
            // and allow the user to correct their mistake.  However, that is an 
            // exercise for you to implement yourself. 
            die("Please<a href='http://mywebsite.com/Register'>go back</a>and enter a username."); 
        } 
         
        // Ensure that the user has entered a non-empty password 
        if(empty($_POST['password'])) 
        { 
            die("Please<a href='http://mywebsite.com/Register'>go back</a>and enter a password.");  
        } 
         
        // Make sure the user entered a valid E-Mail address 
        // filter_var is a useful PHP function for validating form input, see: 
        // http://us.php.net/manual/en/function.filter-var.php 
        // http://us.php.net/manual/en/filter.filters.php 
        if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) 
        { 
            die("Please<a href='http://mywebsite.com/Register'>go back</a>and enter a valid email address."); 
        } 
         
        // We will use this SQL query to see whether the username entered by the 
        // user is already in use.  A SELECT query is used to retrieve data from the database. 
        // :username is a special token, we will substitute a real value in its place when 
        // we execute the query. 
        $query = " 
            SELECT 
                1 
            FROM users 
            WHERE 
                username = :username 
        "; 
         
        // This contains the definitions for any special tokens that we place in 
        // our SQL query.  In this case, we are defining a value for the token 
        // :username.  It is possible to insert $_POST['username'] directly into 
        // your $query string; however doing so is very insecure and opens your 
        // code up to SQL injection exploits.  Using tokens prevents this. 
        // For more information on SQL injections, see Wikipedia:
        // http://en.wikipedia.org/wiki/SQL_Injection 
        $query_params = array( 
            ':username' => $_POST['username'] 
        ); 
         
        try 
        { 
            // These two statements run the query against your database table. 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        } 
         
        // The fetch() method returns an array representing the "next" row from 
        // the selected results, or false if there are no more rows to fetch. 
        $row = $stmt->fetch(); 
         
        // If a row was returned, then we know a matching username was found in 
        // the database already and we should not allow the user to continue. 
        if($row) 
        { 
            die("This username is already in use<a href='http://mywebsite.com/Register'>try again.</a>"); 
        } 
         
        // Now we perform the same type of check for the email address, in order 
        // to ensure that it is unique. 
        $query = " 
            SELECT 
                1 
            FROM users 
            WHERE 
                email = :email 
        "; 
         
        $query_params = array( 
            ':email' => $_POST['email'] 
        ); 
         
        try 
        { 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            die("Failed to run query: " . $ex->getMessage()); 
        } 
         
        $row = $stmt->fetch(); 
         
        if($row) 
        { 
            die("This email address is already registered<a href='http://mywebsite.com/Register'>try again.</a>"); 
        } 
         
        // An INSERT query is used to add new rows to a database table. 
        // Again, we are using special tokens (technically called parameters) to 
        // protect against SQL injection attacks. 
        $query = " 
            INSERT INTO users ( 
                username, 
                password, 
                salt, 
                email 
            ) VALUES ( 
                :username, 
                :password, 
                :salt, 
                :email 
            ) 
        "; 
         
        // A salt is randomly generated here to protect again brute force attacks 
        // and rainbow table attacks.  The following statement generates a hex 
        // representation of an 8 byte salt.  Representing this in hex provides 
        // no additional security, but makes it easier for humans to read. 
        // For more information: 
        // http://en.wikipedia.org/wiki/Salt_%28cryptography%29 
        // http://en.wikipedia.org/wiki/Brute-force_attack 
        // http://en.wikipedia.org/wiki/Rainbow_table 
        $salt = dechex(mt_rand(0, 2147483647)) . dechex(mt_rand(0, 2147483647)); 
         
        // This hashes the password with the salt so that it can be stored securely 
        // in your database.  The output of this next statement is a 64 byte hex 
        // string representing the 32 byte sha256 hash of the password.  The original 
        // password cannot be recovered from the hash.  For more information: 
        // http://en.wikipedia.org/wiki/Cryptographic_hash_function 
        $password = hash('sha256', $_POST['password'] . $salt); 
         
        // Next we hash the hash value 65536 more times.  The purpose of this is to 
        // protect against brute force attacks.  Now an attacker must compute the hash 65537 
        // times for each guess they make against a password, whereas if the password 
        // were hashed only once the attacker would have been able to make 65537 different  
        // guesses in the same amount of time instead of only one. 
        for($round = 0; $round < 65536; $round++) 
        { 
            $password = hash('sha256', $password . $salt); 
        } 
         
        // Here we prepare our tokens for insertion into the SQL query.  We do not 
        // store the original password; only the hashed version of it.  We do store 
        // the salt (in its plaintext form; this is not a security risk). 
        $query_params = array( 
            ':username' => $_POST['username'], 
            ':password' => $password, 
            ':salt' => $salt, 
            ':email' => $_POST['email'] 
        ); 
         
        try 
        { 
            // Execute the query to create the user 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        } 
         
        // This redirects the user back to the login page after they register
        header("Location: http://mywebsite.com/email.html"); 
         
        // Calling die or exit after performing a redirect using the header function 
        // is critical.  The rest of your PHP script will continue to execute and 
        // will be sent to the user if you do not die or exit. 
        die("Redirecting to http://mywebsite.com/Login"); 
    } 
     
?>

Im wondering if theres a way you could make this code check if the link has been pressed
hopefully by a human

login.php

Code: 
<?php 


    // First we execute our common code to connection to the database and start the session 
    require("common.php"); 
      

  


    // This variable will be used to re-display the user's username to them in the 
    // login form if they fail to enter the correct password.  It is initialized here 
    // to an empty value, which will be shown if the user has not submitted the form. 
    $submitted_username = ''; 
     
    // This if statement checks to determine whether the login form has been submitted 
    // If it has, then the login code is run, otherwise the form is displayed 
    if(!empty($_POST)) 
    { 
        // This query retreives the user's information from the database using 
        // their username. 
        $query = " 
            SELECT 
                id, 
                username, 
                password, 
                salt, 
                email 
            FROM users 
            WHERE 
                username = :username 
        "; 
         
        // The parameter values 
        $query_params = array( 
            ':username' => $_POST['username'] 
        ); 
         
        try 
        { 
            // Execute the query against the database 
            $stmt = $db->prepare($query); 
            $result = $stmt->execute($query_params); 
        } 
        catch(PDOException $ex) 
        { 
            // Note: On a production website, you should not output $ex->getMessage(). 
            // It may provide an attacker with helpful information about your code.  
            die("Failed to run query: " . $ex->getMessage()); 
        } 
         
        // This variable tells us whether the user has successfully logged in or not. 
        // We initialize it to false, assuming they have not. 
        // If we determine that they have entered the right details, then we switch it to true. 
        $login_ok = false; 
         
        // Retrieve the user data from the database.  If $row is false, then the username 
        // they entered is not registered. 
        $row = $stmt->fetch(); 
        if($row) 
        { 
            // Using the password submitted by the user and the salt stored in the database, 
            // we now check to see whether the passwords match by hashing the submitted password 
            // and comparing it to the hashed version already stored in the database. 
            $check_password = hash('sha256', $_POST['password'] . $row['salt']); 
            for($round = 0; $round < 65536; $round++) 
            { 
                $check_password = hash('sha256', $check_password . $row['salt']); 
            } 
             
            if($check_password === $row['password']) 
            { 
                // If they do, then we flip this to true 
                $login_ok = true; 
            } 
        } 
         
        // If the user logged in successfully, then we send them to the private members-only page 
        // Otherwise, we display a login failed message and show the login form again 
        if($login_ok)
       
        { 
            // Here I am preparing to store the $row array into the $_SESSION by 
            // removing the salt and password values from it.  Although $_SESSION is 
            // stored on the server-side, there is no reason to store sensitive values 
            // in it unless you have to.  Thus, it is best practice to remove these 
            // sensitive values first. 
            unset($row['salt']); 
            unset($row['password']); 
             
            // This stores the user's data into the session at the index 'user'. 
            // We will check this index on the private members-only page to determine whether 
            // or not the user is logged in.  We can also use it to retrieve 
            // the user's details. 
            $_SESSION['user'] = $row; 
            $_SESSION['logged_in'] = true;
    
         // Redirect the user to the private members-only page.
            
            header('Location: http://mywebsite.com/Home'); 
            die("Redirecting to: http://mywebsite.com/Login/"); 
        } 
        else 
        { 
            // Tell the user they failed 
            print("Login Failed.<a href='http://mywebsite.com/Login'</a><h3><u>Retry</u></h3>"); 
            
             
            // Show them their username again so all they have to do is enter a new 
            // password.  The use of htmlentities prevents XSS attacks.  You should 
            // always use htmlentities on user submitted values before displaying them 
            // to any users (including the user that submitted them).  For more information: 
            // http://en.wikipedia.org/wiki/XSS_attack 
            $submitted_username = htmlentities($_POST['username'], ENT_QUOTES, 'UTF-8'); 
        } 
    } 
     
?>






common.php

Code: 
<?php 

    // These variables define the connection information for your MySQL database 
    $username = "user"; 
    $password = "password"; 
    $host = "localhost"; 
    $dbname = "registeredusers"; 

    // UTF-8 is a character encoding scheme that allows you to conveniently store 
    // a wide varienty of special characters, like ¢ or €, in your database. 
    // By passing the following $options array to the database connection code we 
    // are telling the MySQL server that we want to communicate with it using UTF-8 
    // See Wikipedia for more information on UTF-8: 
    // http://en.wikipedia.org/wiki/UTF-8 
    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 
     
    // A try/catch statement is a common method of error handling in object oriented code. 
    // First, PHP executes the code within the try block.  If at any time it encounters an 
    // error while executing that code, it stops immediately and jumps down to the 
    // catch block.  For more detailed information on exceptions and try/catch blocks: 
    // http://us2.php.net/manual/en/language.exceptions.php 
    try 
    { 
        // This statement opens a connection to your database using the PDO library 
        // PDO is designed to provide a flexible interface between PHP and many 
        // different types of database servers.  For more information on PDO: 
        // http://us2.php.net/manual/en/class.pdo.php 
        $db = new PDO("mysql:host={$host};dbname={$dbname};charset=utf8", $username, $password, $options); 
    } 
    catch(PDOException $ex) 
    { 
        // If an error occurs while opening a connection to your database, it will 
        // be trapped here.  The script will output an error and stop executing. 
        // Note: On a production website, you should not output $ex->getMessage(). 
        // It may provide an attacker with helpful information about your code 
        // (like your database username and password). 
        die("Failed to connect to the database: " . $ex->getMessage()); 
    } 
     
    // This statement configures PDO to throw an exception when it encounters 
    // an error.  This allows us to use try/catch blocks to trap database errors. 
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     
    // This statement configures PDO to return database rows from your database using an associative 
    // array.  This means the array will have string indexes, where the string value 
    // represents the name of the column in your database. 
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 
     
    // This block of code is used to undo magic quotes.  Magic quotes are a terrible 
    // feature that was removed from PHP as of PHP 5.4.  However, older installations 
    // of PHP may still have magic quotes enabled and this code is necessary to 
    // prevent them from causing problems.  For more information on magic quotes: 
    // http://php.net/manual/en/security.magicquotes.php 
    if(function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc()) 
    { 
        function undo_magic_quotes_gpc(&$array) 
        { 
            foreach($array as &$value) 
            { 
                if(is_array($value)) 
                { 
                    undo_magic_quotes_gpc($value); 
                } 
                else 
                { 
                    $value = stripslashes($value); 
                } 
            } 
        } 
     
        undo_magic_quotes_gpc($_POST); 
        undo_magic_quotes_gpc($_GET); 
        undo_magic_quotes_gpc($_COOKIE); 
    } 
     
    // This tells the web browser that your content is encoded using UTF-8 
    // and that it should submit content back to you using UTF-8 
    header('Content-Type: text/html; charset=utf-8'); 
    session_start();
    
    // This initializes a session.  Sessions are used to store information about 
    // a visitor from one web page visit to the next.  Unlike a cookie, the information is 
    // stored on the server-side and cannot be modified by the visitor.  However, 
    // note that in most cases sessions do still use cookies and require the visitor 
    // to have cookies enabled.  For more information about sessions: 
    // http://us.php.net/manual/en/book.session.php 
    
    
    

    // Note that it is a good practice to NOT end your PHP files with a closing PHP tag. 
    // This prevents trailing newlines on the file from being included in your output, 
    // which can cause problems with redirecting users.
 
Wiping the user data base in phpmyadmin would help me identify or deter the abuser now that this code is in place and placing a simple msg displayed on the login page "database maintenance complete - Please re - register to activate your new account"
genuine users will accept the rub but i might lose a few registrations hopefully they will return, a small sacrifice for peace of mind
 
You must log in or register to reply here.
Back
Top