Page 1 of 1

deterine time spread between records, take action

Posted: Mon Mar 23, 2009 7:34 pm
by bulgin
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.

Re: deterine time spread between records, take action

Posted: Tue Mar 24, 2009 3:50 am
by php_east
there are two phenomenas you mentioned. one is on exists, the other is on high fequency occurrence. i would also suggest a low frequency occurence trigger. basically you have a simpe alarming system.

[ exists ] level 1
[ low frequency ] level 2
[ high frequency ] level 3
[ switch off alarm, alarm buffering period ] level 4

you would also need to store the state of the alarm, so that you can have an algorithm to check for the current state and the results of last 2 mins analysis, and then decide on its action
( wether to raise, lower or reset the alarm ).

are you planning on making your solution available in the market ?
i would like a simple low priced solution to this kind of thing. don't have time to make one.
you idea seems very good and effective against this problem.

Re: deterine time spread between records, take action

Posted: Tue Mar 24, 2009 4:08 am
by VladSun
You can find the average time interval between hits and use it to approve/cancel the email alert.
I think you'll find the solution in this page http://iptables-tutorial.frozentux.net/ ... LIMITMATCH useful. You only need to implement it in SQL ;)

I use it to prevent log flood in my firewalls.