[SOLVED]Problem building a time-dependent MySQL INSERT query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

[SOLVED]Problem building a time-dependent MySQL INSERT query

Post by steedvlx »

Hi,

I'm writing an authentication script that bans the IP for thirty minutes if the user makes more than 3 unsuccessful login attempts. The query is to jam the ip address, timestamp, and the timestamp plus 30 minutes into a MySQL table.

For starters, my server uses PHP 4.3.1, MySQL 4.1, and Apache 1.3

I have the following code written:

Code: Select all

else { //the attempt_count >= 3...
			$admin_auth = "EXCEEDED"; //more than 3 login attempts have failed in the same session
			$auth_message = "You have exceeded the maximum login attempts for this session. To prevent hacking, further attempts from your IP address have been disabled";
			$ip_address = decode_ip($user_ip); //function from from phpBB
			$expires =             //supposed to be NOW + 30minutes????
			mysql_select_db($database_conn_member_site, $conn_member_site); //connection is already open
			$query_ip_ban_insert = sprintf("INSERT INTO tbl_ip_ban VALUES ('$ip_address','NULL','$expires')";
			$result_ip_ban_insert = mysql_query($query_ip_ban_insert, $conn_member_site) or die("snake-eyes...query crapped out! " . mysql_error());
			session_unregister(attempt_count);  //get rid of the attempt counter so it won't be a factor when the ban expires.
}
I already read draco_03's post on how to build the query, but I am still at a loss for how to add 30 minutes onto the timestamp so I can jam it in the table with the rest of it. (Time and Date functions really confuse me. Plus there's not really a good reference in English around here.) :wink:

Any help cleaning this up would be greatly appreciated.

Thanks,
Last edited by steedvlx on Tue Apr 12, 2005 12:14 pm, edited 1 time in total.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

I wouldn't do it by IP address.

If this for "unsuccessful logins" why not have a columns in the user database for a ban time for "that username".

Take you could use (to get the time in 30 mins):

Code: Select all

$time_in_30 = strtotime("+ 30 minutes"); //Time in 30 mins
then just check when they log in that the value of $time_in_30 is LESS THAN the value of date() :wink:
Bennettman
Forum Contributor
Posts: 130
Joined: Sat Jun 15, 2002 3:58 pm

Post by Bennettman »

Thing is, that method would allow an unauthorized person to lock a legitimate member out by deliberately trying to log into the legit member's username.

However, as d11wtg hit on, you don't need two timestamps in the DB. You can either put in the ban time and have the script check it's more than 30 minutes past it, or put in the time that it's unlocked again. Use the strtotime method he posted to get the correct timestamp.

Also, don't forget to delete the entry once the ban's been lifted!
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Bennettman wrote:Thing is, that method would allow an unauthorized person to lock a legitimate member out by deliberately trying to log into the legit member's username.
That's the whole point - security. We want to lock the account in the interest of security if somebody is trying to gain access.

it's easy to change an IP and keep going at it. Especially if you have a dynamic IP. All you have to do is drop your internet connection and reconnect - chances are you'll have a new IP :-)
Bennettman
Forum Contributor
Posts: 130
Joined: Sat Jun 15, 2002 3:58 pm

Post by Bennettman »

Point well taken. I suppose it really depends on the usage of the area protected by the login. If it's a forum, for example, it wouldn't really be suitable as the scope for abuse is high (what with usernames being freely available), whereas a username lock would be suitable for say an administration page.

If anything, a brute force access attempt would be made much slower by an IP block, because of the need to switch IP at every three attempts.
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

Thanks for the replies.

It should be noted that this is a secondary authorization procedure. The primary auth is done by another logon script in the main site. This auth procedure requires that they be logged on at that level before they can even see this auth script.

My thinking is that if someone gets this far and cannot get their admin password correct in 4 attempts, then something is smelly.

An IP lockdown is the only other way I have of protecting the member's data.

The web site will be Japanese, and over 50% of the people here have highBW DSL of at least 8Mb. The rest hover around the old 1Mb DSL access. The point is, even if they totally disconnect their router, the IP when they reconnect will be the same since everything here is linked to your phone number. (Adds a totally new aspect to P2P usage as well.)

So, given all of this, IP lockdowns can be extremely effective even if just for 30 minutes or an hour.

Regards,
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

Bennettman wrote:
However, as d11wtg hit on, you don't need two timestamps in the DB. You can either put in the ban time and have the script check it's more than 30 minutes past it, or put in the time that it's unlocked again. Use the strtotime method he posted to get the correct timestamp.

Also, don't forget to delete the entry once the ban's been lifted!
The dual entry system is debateable, I agree. Basically, I just decided just to store both in the DB instead of performing the calculations every time the system is accessed.

Both are needed by the IP profiling script which has to run on every page access...For example, if an account is routinely accessed from a given IP, then the ban may just be for 30 minutes and only for that IP. If the IP is totally unrecognized by the profiling system OR failed attempts to access the same user account from different IP addresses occur, then the user account will be locked down until the administrator physically unlocks it and resets ALL passwords associated with that user.

All of this is coding which is proving to be beyond my current limits. :(

Regards,
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

OK, I thought I had it all licked. The script executes and returns the proper page (no white error screen). But, the data is not at all what I expected, and I cannot see why.

Code: Select all

else { //the attempt_count >= 3...
			$hostname_conn_temp_reg = "localhost";
			$database_conn_temp_reg = "database"; //obviously not real
			$username_conn_temp_reg = "username"; //obviously not real
			$password_conn_temp_reg = "password"; //this too 
			$conn_insert = mysql_connect($hostname_conn_temp_reg, $username_conn_temp_reg, $password_conn_temp_reg) or trigger_error(mysql_error(),E_USER_ERROR); 
			$admin_auth = "EXCEEDED"; //more than 3 login attempts have failed in the same session
			$auth_message = "You have exceeded the maximum login attempts for this session. To prevent hacking, further attempts from your IP address have been disabled";
			$ip_address = decode_ip($user_ip); //function from from phpBB
			$expires = strtotime("+ 30 minutes"); //Time in 30 mins
			mysql_select_db($database_conn_member_site, $conn_insert); //connection is already open
			$query_ip_ban_insert = "INSERT INTO tbl_ip_ban VALUES ('$ip_address','NULL','$expires') ";
			$result_ip_ban_insert = mysql_query($query_ip_ban_insert, $conn_insert) or die("INSERT record failed. No record added " . mysql_error());
			session_unregister(attempt_count);  //get rid of the attempt counter so it won't be a factor when the ban expires.
			mysql_close($conn_insert);
}
The timestamps in the DB are all zeros. But, the IP is correct. Any ideas as to what I did wrong this time?

Thanks,
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

i didnt take a very long look but why are u inserting NULL into ur db when i believe you should be inserting the current time?
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

shiznatix wrote:i didnt take a very long look but why are you inserting NULL into your db when i believe you should be inserting the current time?
The table row is set up to use NOW as the default when a null value is introduced (supposedly, but it still didn't work). :cry:

I did have it expressly defined as strtotime("now")in another version, but still kept getting zeros. I also tried echo()ing the values to the browser with the same results.

I just didn't think it was necessary to update by posting both sets of code for this particular problem since that was the only change I made.

Regards,
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

Sorry to bump. But, I just cannot figure out why I keep getting nothing but zeros from the script. Here is what it looks like now. (only slightly different from before)

Code: Select all

if ($admin_auth != "TRUE" && $attempt_count > 3) {
			$hostname_conn_temp_reg = "localhost";
			$database_conn_temp_reg = "database_name";
			$username_conn_temp_reg = "user_name";
			$password_conn_temp_reg = "password";
			$conn_insert = mysql_connect($hostname_conn_temp_reg, $username_conn_temp_reg, $password_conn_temp_reg) or trigger_error(mysql_error(),E_USER_ERROR); 
			$admin_auth = "EXCEEDED"; //more than 3 login attempts have failed in the same session
			$auth_message = "You have exceeded the maximum login attempts for this session. To prevent hacking, further attempts from your IP address have been disabled";
			$ip_address = decode_ip($user_ip); //function from phpBB
			$ban_time = strtotime("now");
			$expires = strtotime("+30 minutes"); //Time in 30 mins
			mysql_select_db($database_conn_member_site, $conn_insert); //connection is already open
			$query_ip_ban_insert = "INSERT INTO tbl_ip_ban VALUES ('$ip_address','$ban_time','$expires') ";
			$result_ip_ban_insert = mysql_query($query_ip_ban_insert, $conn_insert) or die("INSERT record failed. No data added" . mysql_error());
			session_unregister(attempt_count);  //get rid of the attempt counter so it won't be a factor when the ban expires. rely on IP ban to block.
			mysql_close($conn_insert);
Can anyone clue me in to how I can get valid timestamps out of $ban_time and $expires? I get a valid IP address in the record though. So, I know the query itself is working.

Thanks,
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

What column type are you inserting the timestamps into?
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

TIMESTAMP is the column type shown in phpMyAdmin
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

It uses different timestamps (I beleieve) ...

Just use a varchar columns instead (or use the date() function to format the timestamp into the correct formt).
MySQL manual wrote:Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000)

....a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format
User avatar
steedvlx
Forum Contributor
Posts: 122
Joined: Wed Jun 11, 2003 10:54 pm
Location: Osaka, Japan

Post by steedvlx »

d11wtq wrote:It uses different timestamps (I beleieve) ...

Just use a varchar columns instead (or use the date() function to format the timestamp into the correct formt).
OK, thanks. I have done that, but with more strange results. It's probably because the way date and time are handled is so confusing to me.

In the DB, I am now getting "1113325356". I see from my book here that this is a linux timestamp. And, the expiration time is "1113327156" (the difference is indeed 1800 seconds)So, I guess all is well.


I was expecting 20050411020600. I guess that I have to dive into those cryptic date() and time() functions to get the expected results?

Thanks for the help. I never would have thought it to be a MySQL issue.
Post Reply