Searching for a text string within the results of a MySQL query

[Mr Fwibbles]

Hi,

 

I've set up a system where, if someone tries to access an admin area with an incorrect password, the user's IP is inserted into a database table, then a field called "banned" is set to "true" if their IP exists in the table 5 or more times:

 

//Connect to the database
mysql_connect($conf['db_host'], $conf['db_user'], $conf['db_pass']) or die( mysql_error() );
@mysql_select_db($conf['site_database']) or die( mysql_error() );

//Bad password message
echo 'Invalid password. Your IP address ( ';
if ( isset($_SERVER["REMOTE_ADDR"]) )    {echo '' . $_SERVER["REMOTE_ADDR"] . ' ';}
else if ( isset($_SERVER["HTTP_X_FORWARDED_FOR"]) )    {echo '' . $_SERVER["HTTP_X_FORWARDED_FOR"] . ' ';}
else if ( isset($_SERVER["HTTP_CLIENT_IP"]) )    {echo '' . $_SERVER["HTTP_CLIENT_IP"] . ' ';}
echo ') has been logged. Repeat offenders will be blocked from accessing the website and reported to their ISP.';
$type1 = $_SERVER["REMOTE_ADDR"];
$type2 = $_SERVER["HTTP_X_FORWARDED_FOR"];
$type3 = $_SERVER["HTTP_CLIENT_IP"];
$date = date("j/n/Y");

//Show the IP on screen
for ($i = 1; $i <= 3; $i++) {
  if (stripos(${type.$i}, '.')) {
    $ip = ${type.$i};
    break;
  }
}

//Update the database table with this user's IP
$blockNewQ = "INSERT INTO `blocked_ips` SET `date` = (STR_TO_DATE('$date', '%d/%m/%Y')), `ip_address` = '$ip";
$blockNewResult = mysql_query($blockNewQ) or die( mysql_error() );

//Set this IP to banned if it is now in the database 5 or more times
if ($ip) {
  $band = ( mysql_num_rows( mysql_query("SELECT `ip_address` FROM blocked_ips WHERE `ip_address`='$ip' GROUP BY `ip_address` HAVING count(*) >= 5") ) > 0 ) ? 'true' : 'false';
  mysql_query("UPDATE blocked_ips SET banned = '$band' WHERE ip_address = '$ip'");
}

//For testing
echo $ip;
echo $band;

//Regardless of the connection type, the password was wrong and we're finished logging info, so close MySQL and kill the script.
mysql_close();

 

That seems to be working fine, but I'm having trouble with running the check on another page to see if the current user's IP has been set to "banned". Here's what I have so far:

 

//Connect to the database or die
mysql_connect($conf['db_host'], $conf['db_user'], $conf['db_pass']) or die( mysql_error() );
@mysql_select_db($conf['site_database']) or die( mysql_error() );

//Get the user's IP
$type1 = $_SERVER["REMOTE_ADDR"];
$type2 = $_SERVER["HTTP_X_FORWARDED_FOR"];
$type3 = $_SERVER["HTTP_CLIENT_IP"];

//Show the IP on screen for testing
for ($i = 1; $i <= 3; $i++) {
  if (stripos(${type.$i}, '.')) {
    $ip = ${type.$i};
    break;
  }
}
echo "<b>$ip</b>";

//Is this user's IP blocked?

$ipblockquery = "SELECT `ip_address` FROM `blocked_ips` WHERE `banned` LIKE 'true'";
$ipblockresult = mysql_query($ipblockquery) or die( mysql_error() );

while ($row = mysql_fetch_array ($ipblockresult)) {
echo "$row[0]";
}

 

...but I can't figure out how to search for the user's IP within the query results (in order to redirect them or show an error message instead of loading the rest of the page)! My guess is that arrays are involved, but I can't tell what the right function to use is or even where to include it. I'm new to php and this code is pretty much cobbled together from various examples I've seen around the web. Please help!

[Buddski]

$sql = "SELECT `ip_address` FROM `blocked_ips` WHERE `banned` = 'true' AND ip_address='".$ip."'";
$sql_query = mysql_query($sql) or trigger_error(mysql_error());
if (mysql_num_rows($sql_query) > 0) {
// This IP address was found to be banned in the database //
} else {
// Its all good..
}

 

Might help you a little.

[Mr Fwibbles]

It worked. Such a fast reply, too! Thank you, that's amazing!