Jump to content

[SOLVED] deterine time spread between records, take action


Recommended Posts

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.

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.

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.