Locking user based on ip

[I-AM-OBODO]

I know something is wrong but dunno how to fix it. My intention is to lock user based on ip after 3 unsuccessful attempts. Its incrementing the login count but after 3 attempts, I just can't figure out how to lock the user and reset the value after some time.

 

I'd like a pointer towards the right/best thing to do should my code not be worthy.

 

Thanks and heres my code:

 

 <?php

$user_ip = $_SERVER['REMOTE_ADDR'];
$table_name = "loginattempts";

$query = "SELECT attempts FROM $table_name WHERE user_ip = '$user_ip'";
$result = mysql_query($query) or die("Invalid Login");

while($row = mysql_fetch_array($result)){
	$count = $row['attempts'];
}
if($count == 3){
	echo("Your login attempt is completed");
	
}else{
	$insert = "INSERT INTO $table_name WHERE user_ip = '$user_ip'";
	$result = mysql_query($insert);

	$update = "UPDATE $table_name SET attempts = attempts + 1 WHERE user_ip = '$user_ip'";
	$result = mysql_query($update);
}
$update = "UPDATE $table_name SET attempts = 0 WHERE lastlogin - NOW() = '60000'";
$result =  mysql_query($update);

?>

[ginerjm]

I would use a cookie (cookies must be enabled) and set a cookie named after the ip and the value of it would be my count.  Then I would first check the cookie and if value is = 3 I would put out the error message that says they exceeded their allowed attempts.  If not, then I would do my query to look for the user into and validate him.  If it failed, I would re-set my cookie.  If it doesn't fail (user logs in ok), then I would delete the cookie and go on about the business at hand.

 

One problem with your script is your insert query doesn't insert anything.

[terungwa]

Mr Chidi, you must note that that some users may end up sharing an IP address and this approach would end up blocking more than one user,

and you cannot manage DoS attacks by chaining throttling down to a single IP or user account.

I have seen discussed elsewhere that you should be tracking all failed login attempts across the site and associating them to a timestamp. you may wish to review the details here: http://stackoverflow.com/questions/2090910/how-can-i-throttle-user-login-attempts-in-php

Finally, mysql_* functions should not be used for new code. They are no longer maintained and the community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi.

 

[Psycho]

As explained above, there are many problems with trying to lock by IP addresses. Plus, anyone who knows what they are doing could simply use a proxy to get around the locks. But, if you are really intent on going forward there is a simple solution.

 

In the attempts table create a column to store a timestamp - have it auto-populated for new records so you don't have to add it on INSERT. Then create a NEW record on every attempt - or only failed attempts, depending on your needs. Then you can simply run a query based on number of attempts within the last X minutes. If the count is >= 3 then don't allow the new attempt.

[requinix]

The nice thing about Psycho's solution is that you have a record of logins, and you can refer to that both for administrative/auditing purposes, as well as present it to the user so they can monitor their own account.

[I-AM-OBODO]

how do I go about implementing it using pdo. guess will need a crash course on pdo

[ginerjm]

It's not so much to learn - that pdo.  It still boils down to writing the proper query statement and then just using a different set of function calls to execute that query and retrieve the results.

 

Using a prepared query:

$q = "select stuff from table where blah='$value1' and moreblah='$value2'";
$qresults = MySQL_query($q);
while($row = MySQL_fetch_assoc($qresults)
{
   handle results
}

becomes

$q = "select stuff from table where blah=:val1 and moreblah=:val2";
$qst = $pdo->prepare($q);
 
$parms = array('val1'=>$value1,'val2'=>$value2);
$qst->execute($parms)
 
while($row = $qst->fetch(PDO::FETCH_ASSOC))
{
    handle results
}

 See?  Not much different but lots more safety and security.

 

Note: you don't have to do anything to "prep" the vars being used in the query.  That is done by the call to 'prepare'.  Simply check your inputs for valid data (numbers where numbers are expected, etc.).  No need to addslashes or anything else.

Edited October 21, 2014 by ginerjm

[LeJack]

The thing about ginerjm's first post is that cookies can be manipulated since it's on the user's side. If it was on the server's side, it would be a different story.

 

Storing cookies is a bad idea.

 

Example:

 

1. User spams a form and gets locked out with cookies.

2. User right clicks -> View Page Info -> Security Tab -> View Cookies Button -> Deletes all cookies associated with the current domain.

3. Goes back and spams the form more.

4. Repeats all 3 steps. This will then be considered a brute force attack because the user is attempting to get into a credential page by abusing a broken security rule.

 

 

The only thing that cookies should exist is if you have a login system that logs a user on. Even then, you should be using sessions and not cookie. Cookies are not a good sign of security. The average Joe knows everything about cookies. It's the mainstream version of sessions. Not every Joe knows what sessions are and how they operate while the average Joe knows everything and all about cookies.

 

It's obvious where the exploits will come from.

 

My suggestion, don't use cookies or sessions. The best way on going about this would be log the user's agent, IP Address, timestamp, the requested page they were on, and amount of attempts in a table. Then while you lock the user, you should redirect them to a page where it lists ways of verifying who they are even if they run on the same IP Address.

 

I don't think this is a bad idea at all. Let's say you have a group of guests who are visiting you for a day. You find that your $500,000 bracelet was stolen from one of the guests. Are you going to do what everyone does and try to find the fastest and best way possible to get your bracelet back by calling the police?

 

No, what a smart person would do is lock all of the guests up in a room and then interrogate them one-by-one.

 

That's basically what we are doing here. You don't want to find the fastest and best way possible to lock a user by using sessions, cookies, what ever. You want to know who it is and what they are doing. Same with interrogating them one-by-one. You should interact with users who have been locked. This is so that they can verify/ prove that they aren't the culprit.

 

We are using the user's agent because then we can detect if the user is using which browser to access your website. If the person attempting an attack uses let's say Google Chrome, but the person who was verifying that it's not them are using let's say Netscape, then you know it's not them. We can also report this attack to the user's browser agent whether it'd be Mozilla Firefox, Google Chrome, Safari, Opera, .etc. We can request those browser agent to track who is doing this and block their access or we can do many other things.

 

We use their IP Address because this would give you a few seconds to detect how frequent the user is visiting your website. It doesn't matter if the user is using a proxy, it's still going to be blocked either way if the user attempts to fail again. We use timestamps to detect which time of the day they are doing this attempt. Then we use the page they're on so that we know exactly where the security vulnerabilities are so that we can fix these pages for future situations. Last but not least, we track how many failed attempts they have tried in order to gain access to your blocked page. This is so you know how to replicate the attempt again or if the user has stopped attempting completely.

 

If you aren't the one who did it, you wouldn't be attempting the same methods over and over again. You would probably contact the website's owner and let them know that this was a mistake. Only guilty people attempt new methods of trying to attack. If you lock their IP Address and they contact you and you unlock them and then it starts happening again, you already know who is doing the attacking.

[Jacques1]

Sorry, but the suggestions that have been made so far are rather naïve and technically flawed.

 

There are four problems:

• You're working around symptoms instead of fixing the actual problem (weak passwords).
• Anybody can lock out any user simply by entering a bunch of wrong passwords. This makes your website a perfect target for a denial-of-service attack.
• You're completely unprotected against an attacker trying a single password on multiple accounts (instead of multiple passwords on one account).
• Your counter mechanism (and Psycho's as well) can easily be circumvented with parallel requests: In your script, there's a time gap between checking the current counter and incrementing the value. Within that time frame, I can make as many attempts as I want, because the script will still “see” the old value.

First of all, it's questionable whether a log-in limit even makes sense. If the passwords of your users are so incredibly poor that they won't even survive a couple of guesses, then that's the problem. You may be able to protect your log-in form, but once an attacker has direct access to your database (through an SQL injection, for example), you're screwed. This is actually very likely given that you didn't know anything about proper database code up until now.

 

What you need are better passwords: Tell your users about password managers like KeePass, implement a password strength meter, maybe enforce a certain password policy. Help your users choose better passwords, then brute-force attacks won't be such a big problem.

 

If you absolutely must have a limit, this will be complicated. It's not just a simple database query and a bunch of checks.

 

First, incrementing the counter and getting the new value must be a single atomic operation so that there's no gap. One possibility is to use the LAST_INSERT_ID() function within an UPDATE query to update the value and capture it at the same time:

<?php

define('MAX_LOGIN_ATTEMPTS', 3);



$database = new PDO('mysql:host=localhost;dbname=YOUR_DB', 'YOUR_USER', 'YOUR_PASSWORD', array(
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
));

// increment the counter and capture the new value at the same time
$login_counter_stmt = $database->prepare('
	UPDATE
		users
	SET
		login_attempts = LAST_INSERT_ID(login_attempts + 1)
	WHERE
		name = :name
');
$login_counter_stmt->execute(array(
	'name' => $_POST['name'],
));

// does this user even exist?
if ($login_counter_stmt->rowCount())
{
	// get captured value
	$current_login_attempt = $database->query('SELECT LAST_INSERT_ID()')->fetchColumn();

	// check the value
	if ($current_login_attempt <= MAX_LOGIN_ATTEMPTS)
	{
		// check password etc.
	}
	else
	{
		echo 'Maximum number of log-in attempts exceeded.';
	}
}
else
{
	echo 'This user does not exist.';
}

Then you also need a global counter to prevent parallel attacks. If there's an unusually high number of failed log-in attempts, you need to take action. What exactly “unusually high” means depends on your system and needs to be decided by you.

 

Last but not least, you need to think about denial-of-service attacks. Automatically locking accounts is generally bad idea and can make your site completely unusable. Instead, you should implement a throttling mechanism. A simple solution is a good CAPTCHA like Google's reCAPTCHA. Of course you might also implement a time-based throttle, but this is easier said than done. You can't just do something like “three attempts in five minutes”, because then again an attacker can block the legitimate user.