bulgin Posted March 24, 2009 Share Posted March 24, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/150796-solved-deterine-time-spread-between-records-take-action/ Share on other sites More sharing options...
kittrellbj Posted March 24, 2009 Share Posted March 24, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/150796-solved-deterine-time-spread-between-records-take-action/#findComment-792529 Share on other sites More sharing options...
bulgin Posted March 25, 2009 Author Share Posted March 25, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/150796-solved-deterine-time-spread-between-records-take-action/#findComment-793243 Share on other sites More sharing options...
kittrellbj Posted March 26, 2009 Share Posted March 26, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/150796-solved-deterine-time-spread-between-records-take-action/#findComment-794306 Share on other sites More sharing options...
bulgin Posted March 26, 2009 Author Share Posted March 26, 2009 Thank you! That looks perfect! Quote Link to comment https://forums.phpfreaks.com/topic/150796-solved-deterine-time-spread-between-records-take-action/#findComment-794456 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.