Sql count IP during a specific time frame

Databases
1

Is it possible to have a sql statement with a date field and an IP field where you can count the number of the SAME IP’s within the last 12 hours (could be any time so designated)?

Then you have an if/then statement that says that those IP’s that exceed a certain number during that time frame you can response.write a message that their access is denied.

I am trying to prevent certain IP’s that abuse or bots a webpage.

Your thoughts are appreciated.

2

If each visit is stored as one row with its IP and time in a table then this query should get you all IP’s that visited more than 100 times in the last 12 hours:

SELECT COUNT(ip) AS visits, ip
FROM access_log
WHERE log_time>NOW() - INTERVAL 12 HOUR
GROUP BY ip
HAVING visits>100

This query is almost exactly like the one I use myself when I do anti-bot systems for web sites. I think 12 hours is way too long a period for detecting abusing bots, you need to react much more quickly to be effective. Often a bot will hammer your site heavily for half an hour, 1 or 2 hours and then leave. Sometimes there are intense bots that abuse your site for a couple of minutes and that’s it. Most often I check for the number of visits within the last 10 minutes and run the check every 5 minutes and block new abusers at those 5-minute intervals.

3

Thanks for your reply. I was working on this after my post and came up with this:

SELECT IP, count(*) as toomuch from table_name where datetime > dateadd(d,-5, GETDATE()) group by IP having count(*) > 800

Is that about the same as yours? And then don’t you have to stop them from entering?

I came up with this, but it’s not totally working to stopping the entry into the database:

if objRS.eof = false then
Response.Write "access denied"
Response.End()
end if

Your thoughts are appreciated.

4

Judging from the function names I suppose you are using MS SQL while my query was for MySQL but other than that they are pretty much the same.

I don’t know the logic of your code, I suppose it’s just the end part of something more so I can’t comment (and to be honest I don’t recognize what language this is…).

Stopping IP’s from entering is another subject but I believe this should be done as early as possible. The nature of malicious bots is that they are generating so many page requests that they can consume many resources both on the web server and the database. If you connect to the database for each bot request, even on the ban list, to check if the IP is on the ban list then this may already be too much strain on the server.

The most effective method will depend on the type of web server used. When I use Apache then I make use of the .htaccess file. When my cron job detects there is an IP to be banned it adds a rule to .htaccess to ban the IP - it acts like a kind of firewall. Then when the bot comes in its request is terminated very early with a 403 response - even before the actual web server language is started (for example PHP) and of course there’s no connection to the database, either. You need to check what kind of rules you can use on your web server and take advantage of them if possible.

I remove the IP’s from the ban list after 24 hours - most often the bots give up before that period and I can safely remove the ban. Another bot will certainly come from a different IP.

1 Like
5

Yes, I am using ms sql with asp code. Thanks for your thoughts, I will look into .htaccess file.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.