Page 1 of 2
[SOLVED]Problem building a time-dependent MySQL INSERT query
Posted: Mon Apr 11, 2005 11:45 am
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.)
Any help cleaning this up would be greatly appreciated.
Thanks,
Posted: Mon Apr 11, 2005 11:56 am
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()

Posted: Mon Apr 11, 2005 12:05 pm
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!
Posted: Mon Apr 11, 2005 1:26 pm
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

Posted: Mon Apr 11, 2005 1:42 pm
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.
Posted: Tue Apr 12, 2005 12:04 am
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,
Posted: Tue Apr 12, 2005 12:21 am
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,
Posted: Tue Apr 12, 2005 3:20 am
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,
Posted: Tue Apr 12, 2005 3:50 am
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?
Posted: Tue Apr 12, 2005 5:58 am
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).
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,
Posted: Tue Apr 12, 2005 10:09 am
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,
Posted: Tue Apr 12, 2005 10:36 am
by Chris Corbyn
What column type are you inserting the timestamps into?
Posted: Tue Apr 12, 2005 11:09 am
by steedvlx
TIMESTAMP is the column type shown in phpMyAdmin
Posted: Tue Apr 12, 2005 11:20 am
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
Posted: Tue Apr 12, 2005 12:05 pm
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.