Assistance needed converting flatfile code to use MySQL

schwim

Hi there guys,

I think I've got most of it converted, but would like help on the following portion.

Currently, the code writes the bot data to a flatfile, and the following function checks for a match of remote_addr:

See code below

1) Will this work ok? Does anyone see anything that happened in the flatfile version of the code that I neglected to take care of?

2) Is it efficient? Dealing with things like this, the numbers can add up quick, and I don't want it to get bogged down.

Your thoughts are appreciated.

thanks,
json

schwim

Hi there guys,

I decided to put away the crack pipe, then I fixed a lot of the problems with the code, for instance, it seems that the db method is working opposite to the flatfile. The flatfile did certain things if it didn't match an existing record, and I had written the mysql version to do things if it did match, so I reversed a lot of what needed to be done.

Here's what I have now. Comments concerning function and efficiency is appreciated:

include("../config.php");  //Depending on your directory structure, you may need to change this//

/*Setting all of the server variables*/
$REMOTE_ADDR = $_SERVER['REMOTE_ADDR'];
$REQUEST_URI = $_SERVER['REQUEST_URI'];
$REQUEST_METHOD = $_SERVER['REQUEST_METHOD'];
$HTTP_REFERER = $_SERVER['HTTP_REFERER'];
$HTTP_USER_AGENT = $_SERVER['HTTP_USER_AGENT'];
$SERVER_PROTOCOL = $_SERVER['SERVER_PROTOCOL'];

echo("<html>
<head><title>Tag, you're it!</title></head>
<body>
<p>There is nothing here to see. So what are you doing here ?</p>
<p><a href='http://www.domain.com'>Go home.</a></p>");

$habitual = 0;
$query = "SELECT remote_addr, hits FROM ".$prefix."botpot";

$result = mysql_query ($query) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query);

$result_rows = mysql_num_rows($result);



/*If no record, then it must be new!*/

if($result_rows!=0){

$result = mysql_query ($query);
while ($row = mysql_fetch_assoc ($result)) {
    if($REMOTE_ADDR == $row['remote_addr']{
        $habitual++;
        $hits = $row['hits']+1;
    }
}
}

if ($habitual == 0) { /* we just see a new bad bot not yet listed ! */
    /* send a mail to hostmaster */
    $tmestamp = time();
    $datum = date("Y-m-d (D) H:i:s",$tmestamp);
    $from = "badbot-watch@domain.com";
    $to = "you@domain.com";
    $subject = "Alert: bad robot";
    $msg = "A bad robot hit $REQUEST_URI $datum \n\n";
    $msg .= "address is $REMOTE_ADDR, agent is $HTTP_USER_AGENT\n";
    mail($to, $subject, $msg, "From: $from");

/* append bad bot address data to blacklist database: */

$sql = "INSERT INTO ".$prefix."botpot (id, hits, remote_addr, date, request_method, request_uri, server_protocol, http_referer, http_user_agent) VALUES ('NULL', '1', '$REMOTE_ADDR', '$tmestamp', '$REQUEST_METHOD', '$REQUEST_URI', '$SERVER_PROTOCOL', '$HTTP_REFERER', '$HTTP_USER_AGENT')";

$result = mysql_query($sql) or die('MySQL error: ' . mysql_error() . '<hr/>' . $sql);


}else{

mysql_query("UPDATE ".$prefix."botpot SET hits = $hits WHERE remote_addr = '$REMOTE_ADDR'") or die("Cannot query the database.<br><br>" . mysql_error());


}

echo("</body>
</html>");

This code is supposed to do the following:

1) Check to see if we've seen this IP before
2) If we haven't, then we enter the information into the db and email the contact.
3) If we have, we simply increase the hits field by 1.

thanks,
json

schwim

EDIT: It's functioning, and we're back to the previous thread's questions.

thanks,
json

halojoy

it is functioning
yes, you have done a good coding

one thought only,
when there are many records in this table, we may be able to speed up a little
whenever we check something has to do with activity
It is good we search from last activity timestamp and backwards in time.

When looking for a username, it is more probably we quickly find a person
if searching from last registered downto first registered ( id=1,2,3 )
Or even better last seen, last visit.

new rows are added as last row (highest id)
would be good if the search is peformed from last id and downto 1
or even better, latest date of visit to timestamps far back in time.
you can do break; (see my code) when the while loop has found IP
it is not very meaningful to go on looping

This is PHP manual for [man]break[/man]

$habitual = 0;
$query = "SELECT remote_addr, hits FROM ".$prefix."botpot";

$result = mysql_query ($query) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query);

$result_rows = mysql_num_rows($result);



/*If no record, then it must be new!*/

if($result_rows!=0){

$result = mysql_query ($query);
while ($row = mysql_fetch_assoc ($result)) {
    if($REMOTE_ADDR == $row['remote_addr']{
        $habitual++;
        $hits = $row['hits']+1;
        break;
    }
}
}

schwim

hi there Halo,

thanks very much for the suggestion concerning break;, It should make a huge difference as the tables grow in size.

Concerning setting the order of retrieval(latest to oldest): Can I set an order on my query to do this? Is that what you meant?

$query = "SELECT remote_addr, hits FROM ".$prefix."botpot";

If so, how exactly would I create that?

I really appreciate your help,
json

halojoy

I dont know hardly anything
when comes to Structured Query Language (sql )

But I think either of this is the case.
1. when CREATE a table in MySQL, you can specify how records will be ordered
By which field table will be ordered.
( maybe something with Primary or KEY or like that )

you may be able to specify in SELECT ( the query you show, yes )
if attack the table ASC or DESC, from first or last end of data

Result can also be sorted, SORT BY in SELECT
but of course this will take some time, so maybe is not so much to gain.

Regards