[SOLVED] deterine time spread between records, take action

[bulgin]

Using Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2

 

 

I have an application that I'm building that examines apache logs (which are logged to a MySQL database).  There are certain strings that the MySQL database is on the alert for (specially crafted URLs).  If MySQL sees one of these specially crafted URLs, it is supposed to send out an email alert to an administrator.  That part I have working fine.  This is the problem, though.

 

A malicious user could determine what those specially-crafted URLs are supposed to look like (difficult but not impossible given the nature of how this system works) and then send a spam-load of them against my apache server thereby setting off a flurry of outbound emails.  Normally, these URLs appear very rarely and the alerts are generally limited to under 100 per day.  But I run a cron job that examines the logs every 2 minutes to see if a URL has appeared, and if so, send out an alert.

 

I believe what I need to do is have a MySQL query that sees the first occurrence of the URL, then sees if there is another one or several more just like it within a specified time frame, if not, send the alert, if so, only send the first alert and ignore the others.

 

I'm a little lost on now to do this and would appreciate some pointers.  Maybe something with counting?  Thanks.

[kittrellbj]

Hmmmm...  assuming you are using PHP to do this:

 

<?php
$query = "SELECT * FROM logtable WHERE url='$theurlyouwant'";
$result = mysql_query($query);
$count = mysql_num_rows($result); // Let's say, there are 500 in there.

while ($row = mysql_fetch_assoc($result)) {
$age = age($row['date']);  // You would use a function to establish age of record.  
                                                // Can explain further if needed.
if ($age <= 2) {  // On this line, you establish the age necessary to trigger
                              // it as true, in days, weeks, months, or however you
                               // have set it up through your age function.  For the
                              // example, let's assume "2 minutes".
$n++; // increment the count
$url = $row['url']; // sets URL as a string containing the URL in question.
}
// Message to send
$message = "There have been " . $n . " posts containing the URL : " . $url . " in the past 2 minutes.  This has been logged on MySQL, and it is my duty to inform you of this now.";

// your mail function

 

Then, you run the above query PHP file every two minutes (or however long between iterations), and allow the PHP script to send you an email about it.  You can also have the PHP script delete the records, or print them to a txt log file, or both if you wanted.

[bulgin]

Thanks!  That looks like a good place to start.  I will try it and see what happens.  The age function that you speak of... yes... I would like it if you could elaborate.

 

Thanks.

[kittrellbj]

Here is a link to an article with a method of doing it.  http://www.developertutorials.com/tutorials/php/calculating-difference-between-dates-php-051018/page1.html

 

Edit:  You will probably have to edit it to meet your specific needs (and the format of time you use in your database), but it works.

[bulgin]

Thank you!  That looks perfect!