Only Log In Activated Accounts

doubledee · January 8, 2012

I am having a problem with my User Log-In...

When a User creates an account on my website, an e-mail is sent that looks like this...

Congratulations!

Your account has been created, and a confirmation e-mail sent to: "[email protected]"

Please click on the link in that e-mail to activate your account.

Then when they click on the link, it takes them to my 'activate.php" page which updates the User's record by removing the Activation Code.

The "Activation" seems to work fine. However, the problem that I just realized is that I am doing nothing to prevent someone from Registering, NOT Activating his/her account, but still being able to Log In?!

I guess what I need to do when a User logs in is check to be sure that the "activation_code" column is NULL, right?

Here is a snippet of my Log In script...

if (empty($errors)){
	// Valid form data.

	// ************************
	// Find Member Record.		*
	// ************************

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

	// Build query.
	$q = 'SELECT id, first_name
			FROM member
			WHERE email=? AND pass=?';

	// Prepare statement.
	$stmt = mysqli_prepare($dbc, $q);

	// Bind variables to query.
	mysqli_stmt_bind_param($stmt, 'ss', $email, $pass);

	// Execute query.
	mysqli_stmt_execute($stmt);

	// Store results.
	mysqli_stmt_store_result($stmt);

	// Check # of Records Returned.
	if (mysqli_stmt_num_rows($stmt)==1){
		// Member was Found.

		// Bind result-set to variables.
		mysqli_stmt_bind_result($stmt, $memberID, $memberFirstName);

		// Fetch record.
		mysqli_stmt_fetch($stmt);

		// Set Session variables.
		$_SESSION['memberID'] = $memberID;
		$_SESSION['memberFirstName'] = $memberFirstName;
		$_SESSION['loggedIn'] = TRUE;

What would be the best way to fix this?

Thanks,

Debbie

trq · January 8, 2012

$q = 'SELECT id, first_name
			FROM member
			WHERE email=? AND pass=? AND activation_code IS NULL'

doubledee · January 8, 2012

$q = 'SELECT id, first_name
			FROM member
			WHERE email=? AND pass=? AND activation_code IS NULL'

Thanks.

How fancy should I get with this?

Should I do a separate query just to check if the User's account has been activated?

Would it be insecure to tell a User who is attempting to log in, "Your account must be activated before you can sign in." ??

Debbie

PaulRyan · January 8, 2012

Well it could be simplified into 1 query, for example:

<?PHP

  $query = "SELECT `activation_code` FROM `members` WHERE `email` = '{$email}' AND `pass` = '{$password}'";

  $doQuery = mysqli_query($connection, $query) or die(mysqli_error($connection));

  if(mysqli_num_rows($doQuery)) {
    $user = mysqli_fetch_assoc($doQuery);

    //### Depending on how you determine what is active, these could be switched around
    if($user['activation_code'] == '') {
      //### Record matched and account is active
    } else {
      //### Record matched, but account is not active
    }

  } else {
    //### No record matched
  }
?>

Obviously this is overly simplified, but that is how I myself would do it.

Seems to work as expected, and makes it easier to define the login error for the end user.

Regards, PaulRyan.

doubledee · January 8, 2012

Well it could be simplified into 1 query, for example:

<?PHP

  $query = "SELECT `activation_code` FROM `members` WHERE `email` = '{$email}' AND `pass` = '{$password}'";

  $doQuery = mysqli_query($connection, $query) or die(mysqli_error($connection));

  if(mysqli_num_rows($doQuery)) {
    $user = mysqli_fetch_assoc($doQuery);

    //### Depending on how you determine what is active, these could be switched around
    if($user['activation_code'] == '') {
      //### Record matched and account is active
    } else {
      //### Record matched, but account is not active
    }

  } else {
    //### No record matched
  }
?>

Obviously this is overly simplified, but that is how I myself would do it.

Seems to work as expected, and makes it easier to define the login error for the end user.

Regards, PaulRyan.

Will your approach work with Prepared Statements?

Debbie

PaulRyan · January 8, 2012

I've not really used prepared statements before, but I'm guessing it would work, just by looking at your current code I can logically think of a way to do it.

Change this:

  $q = 'SELECT id, first_name
        FROM member
        WHERE email=? AND pass=?';

To this:

  $q = 'SELECT id, first_name, activation_code
        FROM member
        WHERE email=? AND pass=?';

Then change this:

mysqli_stmt_bind_result($stmt, $memberID, $memberFirstName);

To this:

mysqli_stmt_bind_result($stmt, $memberID, $memberFirstName, $activated);

and lastly change this:

// Set Session variables.
$_SESSION['memberID'] = $memberID;
$_SESSION['memberFirstName'] = $memberFirstName;
$_SESSION['loggedIn'] = TRUE;

To this:

  if($activate != '') {
    // Set Session variables.
    $_SESSION['memberID'] = $memberID;
    $_SESSION['memberFirstName'] = $memberFirstName;
    $_SESSION['loggedIn'] = TRUE;
  } else {
    //### Record found, but account is not activated.
  }

Of course that is untested but I should imagine it will work

Regards, PaulRyan.

doubledee · January 8, 2012

PaulRyan,

Thanks for the code.

In another situation that would work just fine, but the way my script is structured, it isn't exactly working.

Dare I post my entire log-in script here? :shy:

Thanks,

Debbie

PaulRyan · January 8, 2012

It's up to you, if I am unable to help, someone else maybe able to, I'll give it a go

Regards, PaulRyan.

doubledee · January 8, 2012

It's up to you, if I am unable to help, someone else maybe able to, I'll give it a go

Regards, PaulRyan.

Okay, we can try.

Before posting my code, let me say that I am supposed to have this done and uploaded later tonight. (While I always strive to write better code, now wouldn't be the time to completely re-do what I have...)

Here is my PHP and the beginning of my HTML...

<?php //Build Date: 2011-12-26

// Initialize a session.
session_start();

// Access Constants.
require_once('../config/config.inc.php');


// *************************************************************
// HANDLE FORM.																								 *
// *************************************************************
if ($_SERVER['REQUEST_METHOD']=='POST'){
	// Form was Submitted (Post).

	// Initialize Errors Array.
	$errors = array();

	// Trim all form data.
	$trimmed = array_map('trim', $_POST);


	// ************************
	// Validate Form Data.		*
	// ************************

	// Check Email.
	if (empty($trimmed['email'])){
		$errors['email'] = 'Please enter your E-mail address.';
	}else{
		$email = $trimmed['email'];
	}

	// Check Password.
	if (empty($trimmed['pass'])){
		$errors['pass'] = 'Please enter your Password.';
	}else{
		$pass = $trimmed['pass'];
	}


//*********************************************

//*********************************************
	// **************************
	// Attempt to Log-In User.	*
	// **************************
	if (empty($errors)){
		// Valid form data.

		// ************************
		// Find Member Record.		*
		// ************************

		// Connect to the database.
		require_once(WEB_ROOT . 'private/mysqli_connect.php');

		// Build query.
		$q = 'SELECT id, first_name, activation_code
					FROM member
					WHERE email=? AND pass=?';

		// Prepare statement.
		$stmt = mysqli_prepare($dbc, $q);

		// Bind variables to query.
		mysqli_stmt_bind_param($stmt, 'ss', $email, $pass);

		// Execute query.
		mysqli_stmt_execute($stmt);

		// Store results.
		mysqli_stmt_store_result($stmt);

		// Check # of Records Returned.
		if (mysqli_stmt_num_rows($stmt)==1){
			// Member was Found.

			// Bind result-set to variables.
			mysqli_stmt_bind_result($stmt, $memberID, $memberFirstName, $activationCode);

			// Fetch record.
			mysqli_stmt_fetch($stmt);

			if ($activationCode == ''){
				// Account Activated.
				// Set Session variables.
				$_SESSION['memberID'] = $memberID;
				$_SESSION['memberFirstName'] = $memberFirstName;
				$_SESSION['loggedIn'] = TRUE;
			}else{
				// Account Not Activated.
				$errors['pass'] = 'Please activate your Account before logging in.';

				// I NEED MY CODE TO DROP DOWN TO THE FORM AT THIS POINT TO DISPLAY THE ERROR MESSAGE.
				// EXIT() WON'T WORK HERE.
				// I WAS GOING TO DO A SECOND QUERY ABOVE THIS BLOCK TO CHECK FOR THE ACTIVATION CODE
				// MAYBE MY CODE IS TOO COMPLICATED?
			}

			// Close prepared statement.
			mysqli_stmt_close($stmt);

			// Close the connection.
			mysqli_close($dbc);


			// ******************
			// Redirect User.		*
			// ******************

			// Add-a-Comment Redirect.
			if (isset($_GET['addComment']) && ($_GET['addComment']==TRUE)){
				header("Location: " . BASE_URL . "articles/add_comment.php");

				// End script.
				exit();
			}

			// Normal Redirect.
			if (isset($_SESSION['returnToPage'])){
				header("Location: " . BASE_URL . $_SESSION['returnToPage']);
			}else{
				// Take user to Home Page.
				header("Location: " . BASE_URL . "index.php");
			}

		}else{
			// Member not Found.
			$_SESSION['loggedIn'] = FALSE;

			$errors['pass'] = 'The E-mail and Password do not match those on file.';
		}// End of FIND MEMBER RECORD
	}else{
		// Invalid form data.
		// Drop through to display Form.
	}//End of ATTEMPT TO LOG-IN USER

}else{
	// Form was not Submitted (Get).
	// Drop through to display Form.
}//End of HANDLE FORM
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<!-- ################## DEBBIE ##################### -->

Thanks,

Debbie

El Chupacodra · January 8, 2012

I might use the same in my current log in script if you don't mind.

Looks great.

Thanks for sharing Paul.

PaulRyan · January 8, 2012

Not a problem El Chupacodra, as I see it, 2 birds with 1 stone

Debbie, I see your issue, you could possibly add a check before the remaining code to check for the activation error, it exists don't run the code and just skip it?

If you could send me the file, because copy and pasting it adding lots of lines between each line, I could have a better look and a little play around.

Regards, PaulRyan.

doubledee · January 9, 2012

Not a problem El Chupacodra, as I see it, 2 birds with 1 stone

Debbie, I see your issue, you could possibly add a check before the remaining code to check for the activation error, it exists don't run the code and just skip it?

If you could send me the file, because copy and pasting it adding lots of lines between each line, I could have a better look and a little play around.

Regards, PaulRyan.

Here is what I ended up doing. Maybe not the prettiest, but it seems to be working...

<?php //Build Date: 2012-01-08

// Initialize Session.
session_start();

// Initialize Variables.
$_SESSION['loggedIn'] = FALSE;

// Access Constants.
require_once('../config/config.inc.php');


// *************************************************************
// HANDLE FORM.											*
// *************************************************************
if ($_SERVER['REQUEST_METHOD']=='POST'){
	// Form was Submitted (Post).

	// Initialize Errors Array.
	$errors = array();

	// Trim all form data.
	$trimmed = array_map('trim', $_POST);


	// ************************
	// Validate Form Data.	*
	// ************************

	// Check Email.
	// Check Password.


	// ********************************
	// Check for Account Activation.	*
	// ********************************

	// Connect to the database.
	require_once(WEB_ROOT . 'private/mysqli_connect.php');

	// Build query.
	$q1 = 'SELECT activation_code
				FROM member
				WHERE email=? AND pass=?';

	// Prepare statement.
	$stmt1 = mysqli_prepare($dbc, $q1);

	// Bind variables to query.
	mysqli_stmt_bind_param($stmt1, 'ss', $email, $pass);

	// Execute query.
	mysqli_stmt_execute($stmt1);

	// Store results.
	mysqli_stmt_store_result($stmt1);

	// Check # of Records Returned.
	if (mysqli_stmt_num_rows($stmt1)==1){
		// Bind result-set to variables.
		mysqli_stmt_bind_result($stmt1, $activationCode);

		// Fetch record.
		mysqli_stmt_fetch($stmt1);

		if (is_null($activationCode)){
			// Account Activated.
			// Continue logging in User.
		}else{
			// Account Not Activated.
			$errors['pass'] = 'Please activate your Account before logging in.';
		}

		// Close prepared statement.
		mysqli_stmt_close($stmt1);
	}


	// **************************
	// Attempt to Log-In User.	*
	// **************************
	if (empty($errors)){
		// Valid form data.

		// ************************
		// Find Member Record.		*
		// ************************

		// Build query.
		$q = 'SELECT id, first_name, activation_code
					FROM member
					WHERE email=? AND pass=?';

Debbie

Sign In

Only Log In Activated Accounts

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information