deterine time spread between records, take action
Posted: Mon Mar 23, 2009 7:34 pm
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.
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.