How do I implement a password Reset Link

Question

I currently have a system where if a user has forgotten their password, they can reset it by clicking on a forgot password link. They will be taken to a page where they enter in their username/email and then an email will be sent to the user, I wanted to know how can I implement a password reset link in the email so once the user clicks on the link he/she is taken to a page which will allow them to reset their password.

This is the code in my controller

public ActionResult ForgotPassword()
        {
           //verify user id

            string UserId = Request.Params ["txtUserName"];
            string msg = "";
            if (UserId == null) 
            {
                msg = "You Have Entered An Invalid UserId - Try Again";
                ViewData["ForgotPassword"] = msg;
                return View("ForgotPassword");
            }

            SqlConnection lsql = null;
            lsql = DBFactory.GetInstance().getMyConnection();

            String sqlstring = "SELECT * from dbo.[USERS] where USERID = '" + UserId.ToString() + "'";
            SqlCommand myCommand = new SqlCommand(sqlstring, lsql);
            lsql.Open();
            Boolean validUser;         
            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {

                validUser = false;
                while (myReader.Read())
                {
                    validUser = true;

                }
                myReader.Close();
            }
            myCommand.Dispose();  


            if (!validUser) 
                  {
                msg = "You Have Entered An Invalid UserId - Try Again";
                ViewData["ForgotPassword"] = msg;
                lsql.Close();
                return View("ForgotPassword");
            }

            //run store procedure


            using (lsql)
            {
                SqlCommand cmd = new SqlCommand("Stock_Check_Test.dbo.RESET_PASSWORD", lsql);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramUsername = new SqlParameter("@var1", UserId);

                cmd.Parameters.Add(paramUsername);


                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (Convert.ToInt32(rdr["RC"]) == 99)
                    {
                        msg = "Unable to update password at this time";
                        ViewData["ForgotPassword"] = msg;
                        lsql.Close();
                        return View("ForgotPassword");  

                    }
                }
            }


            msg = "new password sent";
            ViewData["ForgotPassword"] = msg;
            lsql.Close();
            return View("ForgotPassword");
        }

This is my current stored procedure which sends the user an email

ALTER PROCEDURE [dbo].[A_SEND_MAIL]
    @var1 varchar (200), -- userid
    @var2 varchar (200) -- email address
AS
BEGIN
declare @bodytext varchar(200);
set @bodytext = 'Password Reset for user: ' +@var1 + ' @' + cast (getDate() as varchar) + ' ' ;
EXEC msdb.dbo.sp_send_dbmail 
@profile_name='Test',
@recipients=@var2,
@subject='Password Reset',
@body=@bodytext
END 

GO

Answer 1

Create a table that has a structure like

create table ResetTickets(
    username varchar(200),
    tokenHash varbinary(16),
    expirationDate datetime,
    tokenUsed bit)

Then in your code when the user clicks the reset password button you will generate a random token then put a entry in that table with the hashed value of that token and a expiration date of something like DATEADD(day, 1, GETDATE()) and appends that token value on the url you email to the user for the password reset page.

www.example.com/passwordReset?username=Karan&token=ZB71yObR

On the password reset page you take the username and token passed in, hash the token again then compare that with the ResetTickets table, and if the expiration date has not passed yet and the token has not been used yet then take the user to a page that lets them enter a new password.

Things to be careful about:

1. Make sure to expire the token, don't let a email from two years ago reset the password.
2. Make sure to mark the token as used, don't let other users of the computer use the browser's history to reset other users passwords.
3. Make sure you generate the random token safely. Don't use Rand and use it to generate the token, two users who reset at the same time would get the same token (I could reset my password and your password at the same time then use my token to reset your account). Instead make a static RNGCryptoServiceProvider and use the GetBytes method from that, the class is thread safe so you don't need to worry about two threads using the same instance.
4. Be sure to parameterize your queries. In your current code if I typed in the userid '; delete dbo.[USERS] -- it would delete all the users in your database. See the linked SO post for more info on how to fix it.
5. Be sure you hash the token, your passwordReset page only accepts the unhashed version, and you never store the unhashed version anywhere (including email logs of outgoing messages to users). This prevents an attacker who has read access to the database from making a token for some other user, reading the value that was sent in the email, then sending the same value himself (and perhaps getting access to an administrator user who can do more stuff than just read values).