CODEWITHSUNDEEP
phphtmlmysql
limixmedia
limixmedia

Reputation: 59

PHP & MySQL email verify not working

I have a problem. I made registration with email verification on my website. It was working two days ago, but yesterday it stopped working. I tryied to change to code, but nothing happened. The problem is that the code didn't find any matches. Here is code:

    ob_start();
 require_once 'include/connect.php';
 $pripojenie=mysql_query("SELECT * FROM users");
 $row=mysql_fetch_array($pripojenie);
 if(isset($_GET['tokenCode'])){
    $token = $_GET['tokenCode'];
    $query = "UPDATE users SET userStatus='Y' WHERE tokenCode='$token'";
    if($dbcon->query($query)){
            //odoslať email
        $to=$_GET['userEmail'];
        $subject='Účet aktivovaný';
        $headers = "MIME-Version: 1.0" . "\r\n";
        $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
        $headers .= 'From: <[email protected]>' . "\r\n";
        $text="<!DOCTYPE html>
        <html>
        <head>
        </head>
        <body>
        <p>Ahoj!<br><br>Ďakujem za aktiváciu účtu na webovej stránke <a href='http://limix.eu'>LiMix.eu</a>.<br><br>S pozdravom<br>Maximilián Csank
        </body>
        </html>";
        mail($to, $subject, $text, $headers);
        header('Location: index.php?error=4');
        exit();

 }
 //ak je už aktívny, presmerovať na chybu
 } else if ($row['userStatus']=='Y') {
    header('Location: index.php?error=7');
    exit();
} else {
//ak je zlý token, presmerovať na chybu
header('Location: index.php?error=5');
exit();
 }

What's wrong with this code? Thanks.

EDIT I've got another code, which is mine, but it wasn't working so I commented it.

$mail=$row['userEmail'];
  $toke=$row['tokenCode'];
  $token=isset($_GET['$toke']);
  $email=isset($_GET['$mail']);
  $query="UPDATE users SET active='1' WHERE tokenCode='$token', 
  userEmail='$email'";
  if ($dbcon>query($query)){
    header('Location: index.php?error=4');
  }

Upvotes: 0

Views: 294

Answers (1)

Professor Abronsius
Professor Abronsius

Reputation: 33823

An example of how you should be able to validate the user email address by sending an email with a hyperlink that contains the token - you do not need to include the email address in any links sent.

Assuming that the email you generate contains a hyperlink like this:

<a href='https://www.example.com/verify.php?token=$token'>Click here to verify registration</a>

and using simple functions to generate/test the token - like this:

function createtoken( $email, $key ){
    return hash_hmac( 'ripemd160', sha1( $email ), $key );
}
function verifyemail( $email, $token, $key ){
    return createtoken( $email, $key )===$token;
}


/* define a secret used to hash the email address prior to sending email */
define( 'SECRET_KEY', sha1('This is ever so secret and never changes') );

Then to process the user activation

/* verify.php */
if( $_SERVER['REQUEST_METHOD']=='GET' && !empty( $_GET['token'] ) ){
    try{
        /* Get the token from the URI */
        $token=filter_input( INPUT_GET, 'token', FILTER_SANITIZE_STRING );


        $dbhost =   'localhost';
        $dbuser =   'xxx'; 
        $dbpwd  =   'xxx'; 
        $dbname =   'xxx';
        $db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


        /* assume $db is a mysqli connection */
        $sql='select distinct `email` from `users` where `token`=? and `userstatus`=?';
        $stmt=$db->prepare( $sql );

        if( $stmt ){

            $status=0;
            $stmt->bind_param( 'si', $token, $status );
            $result=$stmt->execute();

            if( $result ){

                /* Store the result & bind column to variable BEFORE getting `num_rows` */
                $stmt->store_result();
                $stmt->bind_result( $email );

                /* Fetch number of rows from db as integer ~ there should be only 1 at most */
                $rows=$stmt->num_rows;

                /* Fetch the result into the variable & tidy up */
                $stmt->fetch();
                $stmt->free_result();                   
                $stmt->close();


                if( $rows==1 ){

                    /* Token was found - validate and update db */
                    if( verifyemail( $email, $token, SECRET_KEY ) ){

                        $sql='update `users` set `userstatus`=? where `token`=?';

                        $stmt=$db->prepare( $sql );
                        if( $stmt ){
                            /*
                                in my test table `users`, the column `userstatus` is set as `tinyint(1)`
                                so 1=yes/true and 0=no/false rather than string yes/no
                            */
                            $yes=1;
                            $stmt->bind_param( 'ss', $yes, $token );
                            $result = $stmt->execute();

                            if( $result ){
                                $rows = $db->affected_rows;
                                if( $rows===1 ){
                                    $status=@mail( $email, 'success', 'email validated' );

                                    exit( header( 'Location: /login?validated='.$status ) );
                                }
                            } else {
                                throw new Exception('unable to update record',5);
                            }
                        }
                    } else {
                        throw new Exception('unable to verify email',4);
                    }
                } else {
                    /* Token cannot be found */
                    throw new Exception('Invalid token',3);
                }
            } else {
                throw new Exception('query failed',2);
            }
        } else {
            throw new Exception('unable to prepare sql statement',1);
        }
    }catch( Exception $e ){
        exit( header( 'Location: /index.php?error='.$e->getCode().'&message='.$e->getMessage() ) );
    }
}

--

It is the value of $token that needs to be both recorded in the database ( for same record as user's email address ) and used in the hyperlink which is sent in the HTML email.

define( 'SECRET_KEY', sha1('This is ever so secret and never changes') );

$token = createtoken( '[email protected]', SECRET_KEY ); /* yields: c6bc1ba4a8193cd965f1175197b5170c4c385040 */

A basic example users table:

mysql>describe users;
+------------+---------------------+------+-----+------------------+----------------+
| Field      | Type                | Null | Key | Default          | Extra          |
+------------+---------------------+------+-----+------------------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL             | auto_increment |
| username   | varchar(64)         | NO   | MUL | NULL             |                |
| email      | varchar(64)         | NO   | MUL | [email protected] |                |
| token      | varchar(64)         | NO   | MUL | default_token    |                |
| userstatus | tinyint(1) unsigned | NO   |     | 0                |                |
+------------+---------------------+------+-----+------------------+----------------+

/* add the user */
mysql>insert into `users` (`username`,`email`,`token`) values ('fred.bloggs','[email protected]','c6bc1ba4a8193cd965f1175197b5170c4c385040');

/*

    It would be at this point ( adding user to db ) that you generate the email to the user with a confirmation link that they must click. 
    The status is set to zero / no so they should not be able to login until that is updated.

    The above is the equivalent of the user clicking "submit" after completing the form for example

*/

mysql> select * from users;
+----+-------------+-----------------------+------------------------------------------+------------+
| id | username    | email                 | token                                    | userstatus |
+----+-------------+-----------------------+------------------------------------------+------------+
|  1 | fred.bloggs | [email protected] | c6bc1ba4a8193cd965f1175197b5170c4c385040 |          0 |
+----+-------------+-----------------------+------------------------------------------+------------+

The user clicks the link in his/her email and is taken to the verify.php page ( for example ) and the querystring https://www.example.com/verify.php?token=c6bc1ba4a8193cd965f1175197b5170c4c385040 contains the token - so at this point the verification starts with the code posted previously.

You tend not to see such a simplified url when it comes to validation - but in essence this is what is happening. The actual url that is provided for the user to click would probably be much more complicated as some might consider the above mechanism easy to break ( should they get to see the email to begin with )

I hope that helps a little in getting your registration confirmation working ~ as you didn't share the portions of code that generate the token, record to db or send the email to the user it can only be offered as guidance rather than the actual answer but certain portions of it ( namely the prepared statements approach ) should be adopted/adapted to your code to prevent naughty people hacking your db ;-)

Upvotes: 1

Related Questions