Making a subscriber's account inactive so they can't login.

I have a website with a database. When a user joins their account is active and they are able to login to their account. The code is set up so that their account will expire in on month. I then want their account to be inactive so they will not be able to login.

I would like to know if I include that somewhere in this section of code.

if ($uid > 0) {
							
							// Update the users table:
							$q = "UPDATE users SET active=1, date_expires = IF(date_expires < NOW(), ADDDATE(date_expires, INTERVAL 1 MONTH), ADDDATE(NOW(), INTERVAL 1 MONTH)), date_modified=NOW() WHERE id=$uid";
							$r = mysqli_query ($connect, $q);
							if (mysqli_affected_rows($connect) != 1) {
								trigger_error('The user\'s expiration date could not be updated!');
							}

Open in new window

Wanda MarstonCreative DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
just change:
SET active=1

to
SET active=0

if ($uid > 0) {
							
							// Update the users table:
							$q = "UPDATE users SET active=0, date_expires = IF(date_expires < NOW(), ADDDATE(date_expires, INTERVAL 1 MONTH), ADDDATE(NOW(), INTERVAL 1 MONTH)), date_modified=NOW() WHERE id=$uid";
							$r = mysqli_query ($connect, $q);
							if (mysqli_affected_rows($connect) != 1) {
								trigger_error('The user\'s expiration date could not be updated!');
							}
}

Open in new window


The expiration date ill still be one month in the future, but since active=0, it is very likely that the login page will not allow access to the site once it sees active=0.
0
Wanda MarstonCreative DirectorAuthor Commented:
Thanks for your response.

I want the user to be able to login for one month until the account expires so the account has to start off by being active and then the active column should change to 0.

I have previously tried what you are suggesting and don't think that will work.
0
hieloCommented:
OK, I understand.  But the code you posted seems to be the "activation" query.  What you want is something that needs to do immediately after the login query.  I don't know what your login query looks like now, but if you were to execute the following as your login query:
SELECT `id`,`active`, (DATEDIFF(Now(),`date_expires`)  > 0) as `expired` FROM `users` WHERE `username`=? AND `password`=? LIMIT 1;

If the query returns a result, then the username and password matched.  Then you can check the value of $row['expired'] (which will have either 0 or 1) an if it is expired, then do your update query:

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno())
{
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

$sql = sprintf("'SELECT `id`,`active`, (DATEDIFF(Now(),`date_expires`)  > 0) as `expired` FROM `users` WHERE `username`='%s' AND `password`='%s'", mysqli_real_escape_string($_POST['username']), mysqli_real_escape_string($_POST['password']) );

$result = mysqli_query($link, $sql);

if( $result )
{
	if( 1==mysqli_num_rows($result) )
	{
		$row = mysqli_fetch_assoc($result);

		if( $row['expired'] && $row['active'] )
		{
			$sql = sprintf('UPDATE `users` SET `active`=0 WHERE `id`=%d LIMIT 1', $row['id']);
			mysqli_query($link, $sql);
		}
	}
	
	mysqli_free_result($result);
}
mysqli_close($link);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Wanda MarstonCreative DirectorAuthor Commented:
Yes, the login query now checks to see that the account is active and so it will let anyone in with a 1 in the active column.

How do I get the 1 to change to a 0 when the account expires?
0
hieloCommented:
My last post checks if the account has expired and if so it updates active to zero.
0
Ray PaseurCommented:
Theory and practice of client authentication is here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html

Theory and practice of client registration and confirmation is here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3939-Registration-and-Email-Confirmation-in-PHP.html

I would recommend adding an expiration date column to the table of users.  The default value might be today's date, meaning that if they have not confirmed their registration, they're not able to use the site (this is not a technical issue - just a UX issue).  At the time of registration confirmation, use PHP date('c', strtotime('TODAY + 1 MONTH')) to get the future expiration date.  Update the table to show they confirmed the registration and at the same time, update the table to add the future expiration date.  Then go back to the login script and add a check for the date.  Not only will you test for the username and password, but you'll also test to see that the current value of date('c') is less than or equal to the expiration date column in the user's row of the database.

Date and time handling is here (note the ambiguity of the term "month"):
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
Wanda MarstonCreative DirectorAuthor Commented:
This currently is my login script:
$q = "SELECT id, username, type FROM users WHERE (email='$e' AND pass='"  .  get_password_hash($p) .  "') AND active = 1 ";
	//$q = "SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM users WHERE (email='$e' AND pass='"  .  get_password_hash($p) .  "') AND active = 1 ";
	

Open in new window


There is also currently a date_expires column and an active column that will have a 1 in it when the registration.

Just trying to do this in the most simple direct way in case the coding changes later, for whatever reason.

hielo - I haven't checked your code yet but will do so at a later time today - thanks
0
hieloCommented:
The simplest way to prevent users to login is to also check for expired accounts during the login query -- meaning you don't have to set active=0.

$q = "SELECT id, username, type FROM users WHERE (email='$e' AND pass='"  .  get_password_hash($p) .  "') AND (active = 1) AND (DATEDIFF(Now(),`date_expires`)>0) ";

But if you would rather set active to zero, then refer to my code above.
0
Wanda MarstonCreative DirectorAuthor Commented:
Okay I will try that.
0
Wanda MarstonCreative DirectorAuthor Commented:
Thank you everyone for your quicky replies. I probably didn't explain the situation as well as I could have done and will provide more information the next time I ask a question of Experts Exchange.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.