Jump to content
Larry Ullman's Book Forums
Jacques

Chapter 12 Resetting Password More Securely

Recommended Posts

Hi Larry,

I changed the database query from the original in your reset_password.php script to the below query in order to get the values to assign to the user's sessions when the URL signs the user in. I get the following error (Column 'date_expires' in where clause is ambiguous) because the users table also has a 'date_expires' column. What alias should I use on 'date_expires'? I tried (a.date_expires>NOW() FROM access_tokens AS a) but it obviously didn't work and couldn't find anything useful on the net. Any suggestions would be much appreciated.

$q = "SELECT a.user_id, u.email, LEFT(u.first_name,1) AS icon, CONCAT(u.first_name, ' ', u.last_name) AS name, l.lang, t.timezone FROM access_tokens AS a INNER JOIN users AS u ON u.id=u.id INNER JOIN languages AS l ON l.id=l.id INNER JOIN timezones AS t ON t.id=t.id WHERE token=? AND date_expires>NOW()";


 

Share this post


Link to post
Share on other sites

So you need to clarify which date_expires you're referring to by prefacing "date_expires" with the table name or alias. As you're probably referring to the access_tokens table, which has been aliased to "a", you'd change the conditional to "AND a.date_expires > NOW()". 

What you tried was close but a query only has one "FROM" clause and you added a second one as part of the "WHERE" conditionals. 

Share this post


Link to post
Share on other sites

Hi Larry,

Thank you very much for your response.

I updated the query and it doesn't give the "ambiguous" error anymore, but it now gives the following user reset error: "Either the provided token does not match that on file or your time has expired. Please resubmit the "Forgot your password?" form." The script does insert a new token and the correct date/time into the "access_tokens" table.  I also checked the query again and couldn't find any errors. My script is included below. Your thoughts would be much appreciated. Thank you.

<?php

// Require the configuration before any PHP code as the configuration controls error reporting:
require('includes/config.inc.php');
// The config file also starts the session.

// Redirect invalid user:
if (isset($_SESSION['user_id'])) {
	$url = 'index.php'; // Define the URL.
	header("Location: $url");
	exit(); // Quit the script.
}

// Require the database connection:
require(MYSQL);

// Include the page title:
$page_title = $words['reset_page_title_1'];

// Include the HTML header file:
include('templates/header.html');

// For storing reset error only:
$reset_error = '';

// For storing password errors:
$pass_errors = array();

if (isset($_GET['t']) && (strlen($_GET['t']) === 64) ) { // First access
	$token = $_GET['t'];

	// Fetch the user ID:
	$q = "SELECT a.user_id, u.email, LEFT(u.first_name,1) AS icon, CONCAT(u.first_name, ' ', u.last_name) AS name, l.lang, t.timezone FROM access_tokens AS a INNER JOIN users AS u ON u.id=u.id INNER JOIN languages AS l ON l.id=l.id INNER JOIN timezones AS t ON t.id=t.id WHERE token=? AND a.date_expires>NOW()";
	$stmt = mysqli_prepare($dbc, $q);
	mysqli_stmt_bind_param($stmt, 's', $token);
	mysqli_stmt_execute($stmt);
	mysqli_stmt_store_result($stmt);
	if (mysqli_stmt_num_rows($stmt) === 1) {
		mysqli_stmt_bind_result($stmt, $user_id, $email, $icon, $name, $lang_id, $timezone_id);
		mysqli_stmt_fetch($stmt);

		// Create a new session ID:
		session_regenerate_id(true);
		$_SESSION['user_id'] = $user_id;

		// Store the data in a session:
		//$_SESSION['user_id'] = $user_id;
		$_SESSION['email'] = $email;
		$_SESSION['icon'] = $icon;
		$_SESSION['name'] = $name;
		$_SESSION['lid'] = $lang_id;
		$_SESSION['timezone'] = $timezone_id;

		// Clear the token:
		$q = 'DELETE FROM access_tokens WHERE token=?';
		$stmt = mysqli_prepare($dbc, $q);
		mysqli_stmt_bind_param($stmt, 's', $token);
		mysqli_stmt_execute($stmt);

	} else {
		$reset_error = '<div class="reset my-5">
			<div class="reset-header text-center">
				<i class="fas fa-lock fa-4x"></i>
				<h2 class="display-5 my-2 font-weight-normal">' . $words['reset_message_1'] . '</h2>
				<p class="my-3 font-weight-normal text-center">' . $words['reset_message_2'] . '</p>
			</div>
		</div>';
	}
	mysqli_stmt_close($stmt);

} else { // No token!
	$reset_error = '<div class="reset my-5">
		<div class="reset-header text-center">
			<i class="fas fa-lock fa-4x"></i>
			<h2 class="display-5 my-2 font-weight-normal">' . $words['reset_error_1'] . '</h2>
			<p class="my-3 font-weight-normal text-center">' . $words['reset_error_2'] . '</p>
		</div>
	</div>';
}

// If it's a POST request, handle the form submission:
if (($_SERVER['REQUEST_METHOD'] === 'POST') && isset($_SESSION['user_id'])) {

	// Okay to change password:
	$reset_error = '';
			
	// Check for a password and match against the confirmed password:
	if (preg_match('/^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\$%\^&\*])(?=.{12,})^/', $_POST['pass1']) ) {
		if ($_POST['pass1'] == $_POST['pass2']) {
			$p = $_POST['pass1'];
		} else {
			$pass_errors['pass2'] = $words['reset_validation_1'];
		}
	} else {
		$pass_errors['pass1'] = $words['reset_validation_2'];
	}
	
	if (empty($pass_errors)) { // If everything's OK.

		// Define the query:
		$q = 'UPDATE users SET pass=? WHERE id=? LIMIT 1';
		$stmt = mysqli_prepare($dbc, $q);
		mysqli_stmt_bind_param($stmt, 'si', $pass, $_SESSION['user_id']);
		$pass = password_hash($p, PASSWORD_BCRYPT);
		mysqli_stmt_execute($stmt);

		if (mysqli_stmt_affected_rows($stmt) === 1) {

			// Send a confirmation email:
			$email = ($_SESSION['email']);
			$body = $words['reset_email_1'];
			$body = wordwrap ($body,70);
			mail($email, $words['reset_email_2'], $body, 'FROM: ' . SEND_EMAIL);

			// Let the user know the password has been changed:
			echo '<div class="reset my-5">
				<div class="reset-header text-center">
					<i class="fas fa-lock fa-4x"></i>
					<h2 class="display-5 my-2 font-weight-normal">' . $words['reset_message_3'] . '</h2>
					<p class="my-3 font-weight-normal text-center">' . $words['reset_message_4'] . '</p>
				</div>
			</div>';
			include('templates/footer.html'); // Include the HTML footer file.
			exit();

		} else { // If it did not run OK.

			trigger_error('<div class="reset my-5">
				<div class="reset-header text-center">
					<i class="fas fa-lock fa-4x"></i>
					<h2 class="display-5 my-2 font-weight-normal">' . $words['reset_error_3'] . '</h2>
					<p class="my-3 font-weight-normal text-center">' . $words['reset_error_4'] . '</p>
				</div>
			</div>'); 

		}
		mysqli_stmt_close($stmt);

	} // End of empty($pass_errors) IF.
	
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST') {
	$reset_error = '<div class="reset my-5">
		<div class="reset-header text-center">
			<i class="fas fa-lock fa-4x"></i>
			<h2 class="display-5 my-2 font-weight-normal">' . $words['reset_error_5'] . '</h2>
			<p class="my-3 font-weight-normal text-center">' . $words['reset_error_6'] . '</p>
		</div>
	</div>';
} // End of the form submission conditional.

// If it's safe to change the password, show the form:
if (empty($reset_error)) {

	// Requires the form functions script, which defines create_form_input():
	require_once('includes/form_functions.inc.php');

	echo '<form class="reset my-5" action="reset.php" method="post" accept-charset="utf-8">
		<div class="reset-header text-center">
			<i class="fas fa-lock fa-4x"></i>
			<h2 class="display-5 my-2 font-weight-normal">' . $words['reset_form_1'] . '</h2>
			<p class="my-3 font-weight-normal text-center">' . $words['reset_form_2'] . '</p>
		</div>';
		create_form_input('pass1', 'password', '', $pass_errors, array('placeholder'=>$words['reset_form_3']));
		echo '<small class="form-text text-muted">' . $words['reset_form_4'] . '</small>';
		create_form_input('pass2', 'password', '', $pass_errors, array('placeholder'=>$words['reset_form_5'])); 
		echo '<input type="submit" name="submit_button" value="' . $words['reset_form_6'] . '" id="submit_button" class="btn btn-lg btn-block btn-custom" />
	</form>';

} else {
	echo '<div class="reset my-5">
		<div class="reset-header text-center">
			<i class="fas fa-lock fa-4x"></i>
			<h2 class="display-5 my-2 font-weight-normal">' . $reset_error . '</h2>
			<p class="my-3 font-weight-normal text-center">' . $reset_error . '</p>
		</div>
	</div>';
}

// Include the HTML footer file.
include('templates/footer.html');
?>

 

Share this post


Link to post
Share on other sites

So I believe the page gives that result if that "SELECT a.user_id, u.email, LEFT(u.first_name,1) AS icon, ..." query doesn't return exactly one row. There wasn't anything obviously amiss there upon first inspection. I would run the query manually using the mysql client to see why it's not returning a row. (You'll need to insert the token value into the query, of course.) Try removing various conditionals to see which is the problem. 

Share this post


Link to post
Share on other sites

Hi Larry,

Thank you for your guidance. The query from the code above calls all the users, languages and time zones from their respective tables so no wonder the script didn't execute!

The correct query is: $q = "SELECT a.user_id, u.type, u.email, LEFT(u.first_name,1) AS icon, CONCAT(u.first_name, ' ', u.last_name) AS name, u.lang_id, u.timezone_id FROM access_tokens AS a INNER JOIN users AS u ON u.id=u.id WHERE a.token=? AND a.user_id=u.id AND a.date_expires>NOW()"; The script now executes and stores the correct sessions.

Just one last question if I may: Should I generate a session id for an admin user within the reset.php script or let the admin user first reset his or her password via the link and then sign out and sign in again through the signin.php page which will generate the admin session? My main concern is security.

Share this post


Link to post
Share on other sites

That's an excellent question! Depending upon what it means to be an "admin" user, I'd be inclined to not allow admin users to reset their password at all. If a password is forgotten, the admin should personally contact the site--who presumably knows the admin--who would manually help with the reset. Such an arrangement, while inconvenient, would prevent a hack attempt. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...