Best way to allow users to reset a password.

[tangoforce]

IF you have multiple servers, the prefix would be a good idea if they all access the same database yes.

As for the second option, why would you need it if your database column is set to be unique? - You won't because it won't accept duplicate values (you'd need to use mysql_affected_rows() to see if it was accepted).

You can use the 2nd parameter if you want but if its only for password resets it's not going to be used as much as a normal login form. Once the user has reset you just wipe the unique key out of their record so you're not really going to need a super long unique string.

[LearningCoder]

Ah I see so once the user resets their password, I insert a new unique id into the reset column in case they want to reset again or wipe it completely?

Regards,

LC.

[tangoforce]

No! You ONLY put it in there when they click the forgot password link. It would be pointless to fill up the column with unique ids just in case. Leave the default for the column as null and only put a value there when the user clicks your lost / forgotten password link.

[LearningCoder]

I'm just currently working on it. When I create this new table field, do I set the default value to NULL to give the column a NULL value? Sounds like a bit of a stupid question but I need to be sure before going ahead and working on it.

I see there is a checkbox and also a drop down list in the 'Default' row.

Kind regards,

LC.

[tangoforce]

There should be a null checkbox - just check that.

[LearningCoder]

It's saying I have an error with my sql syntax.

I am trying to create a column called reset, which is a varchar and also making sure the NULL checkbox is ticked.

Do you know what I am doing wrong? There is also a drop-down list called Index and one value is UNIQUE so I tried choosing that one also but it didn't work.

What am I doing wrong?

Regards,

LC.

[tangoforce]

Seeing as you've not posted the sql error message...

Alternative route is to put the user id AND a key in the link. That way the key from uniqid() doesn't have to be unique as long as it is correct. Then you don't need to worry about it being a unique column.

[LearningCoder]

Here is my sql error:

Error

SQL query:

ALTER TABLE `members` ADD `reset` VARCHAR NULL

MySQL said: Documentation
#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 'NULL' at line 1 

So just so I am clear, I can pass the user id and a unique key (which is generated from uniqid()) through the link in the email.

What do you mean by as long as it is correct?

Do I still need to create the table field with your alternative route?

Regards,

LC.

[tangoforce]

As long as the key in the link that the user clicks is the same as the one in the database.

Also for varchar, you have to supply a length for the field. If you have 32 characters, (eg md5 hash) you tell it 32. If you have 8 then you tell it 8. If you tell it nothing, then it will fail (as it did with you).

The alternative is probably better for you. Again the link that the user clicks must contain the same key that you have in the reset column - that will avoid you having to setup the column as unique.

[LearningCoder]

Ok so now I fill the 'reset' column with a value returned from uniqid() when the user registers, so when they need to reset, I can select the 'reset' data and send it inside the link?

Regards,

LC.

[tangoforce]

No!

As I've told you before, just generate the uniqid() when the user CLICKS THE FORGOT PASSWORD LINK.

Then store it in the table and send the email with the link.

When the user clicks it, check the user id in the link, select their record and check that the key from the link matches what is in the reset column. If it is, it's the legitimate user. If not then output an error.

The reason I don't want you to generate a uniqid() when the users register is because if someone hacks your site and gets into your db and grabs your reset column they can screw over every single user of your site. Therefore you only put it there when its needed.

[LearningCoder]

Ah ok. I noticed the uniqid() function returns a string 13 characters long so when I created my table I set the Length/Value column to 13. It has been created now. Thanks for the help. So, I register the user as normal, inserting nothing into the reset column...

If they need to reset their password, they enter their username and this is when I create a key with uniqid and insert it into their column.....I then send them an email which contains a link, which contains the users id and their unique key.

After the email is sent and they open it. They click the link which then takes them to a page where they enter their new password. The id and unique id is passed through to that page where it is retrieved and stored into a variable. After they submit their new password, I query the database selecting a match looking to see if the unique key matches the one which was retrieved from the email link?

I hope I am somewhat near the concept....

Regards,

LC.

[tangoforce]

Originally Posted by LearningCoder

Ah ok. I noticed the uniqid() function returns a string 13 characters long so when I created my table I set the Length/Value column to 13. It has been created now. Thanks for the help. So, I register the user as normal, inserting nothing into the reset column...

If they need to reset their password, they enter their username and this is when I create a key with uniqid and insert it into their column.....I then send them an email which contains a link, which contains the users id and their unique key.

After the email is sent and they open it. They click the link which then takes them to a page where they enter their new password. The id and unique id is passed through to that page where it is retrieved and stored into a variable. After they submit their new password, I query the database selecting a match looking to see if the unique key matches the one which was retrieved from the email link?

I hope I am somewhat near the concept....

SPOT ON!

[LearningCoder]

Oh thank god. Ok Ima get to work on this now and see what I can come up with. It seems relatively straight forward.

Thanks for the help so far. I'll keep the thread updated.

Kind regards,

LC.

[LearningCoder]

Here is my do_reset_password.php (form action file):

PHP Code:

<?php

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

   $errors = array();
   $_POST['username'] = trim($_POST['username']);
   
   if(empty($_POST['username'])){
      $errors[] = "You must fill in the username field.";
   }
   
     if(empty($errors)){
        
        $con = new mysqli("localhost","root","","demo_central");
        $stmt = $con->prepare("SELECT email FROM members WHERE username=?");
        
        $username = mysqli_real_escape_string($con,$_POST['username']);
        
        $stmt->bind_param("s",$username);
        $stmt->bind_result($email);
        $stmt->execute();
        $stmt->store_result();
        
        $row = $stmt->num_rows;
        
        if($row == 1){//if $row == 1, we matched a correct username from the database....so we create a unique id to insert into users row.
           
           $unique_id = uniqid();
           
           $stmt = $con->prepare("UPDATE members SET reset='{$unique_id}'");
           
           
           
           
        }
        else{
           print("That username does not exist.<br />");
           print("<a href='reset_password.php'>Please try again.</a>");
           exit(0);
        }
        
     }
     else{
        $error_string = implode("<br />",$errors);
        print($error_string);
        print("<br />");
        print("<a href='reset_password.php'>Please try again.</a>");
        exit(0);
     }
}

?>

In regards to the inserting of the unique id, I'm not sure if it requires an UPDATE or an INSERT statement? My guess would be an UPDATE as we are updating the record from NULL to the key value. I am having issues with it as it is not updating the record.

Do you know what I need to change?

Kind regards,

LC.