help creating anti-abuse query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help creating anti-abuse query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-03-29 : 18:18:14

I have a website that is currently being abused by bots. The site is a community site and what is happening is that certain accounts are loggin into the site and sending 10,000's of internal mail spams.

So far this is my SPROC, can anyone make suggestions on how to modify it so that we can throttle the frequency of messages sent? I am thinking of returning a specific block parameter when the frequency of IM's is too great. Perhaps insert it into and alternate table that stores messages that have not been approved to be sent ..

Anybody have any experience in this area? Any recommended courses of action?

As always, your help is much appreciated!

Thanks,
mike123

CREATE PROCEDURE [insert_InstantMessage]
(
@MessageToID [int],
@MessageFromID [int],
@Message [varchar](500),
@Mobile [tinyint],
@Date [smalldatetime],
@blocked [tinyint] OUTPUT
)
AS SET NOCOUNT ON
SELECT userID from BlockList WHERE userID = @messageToID and BlockUserID = @MessageFromID
IF @@Rowcount = 0
begin
INSERT INTO [InstantMessage]
(
[MessageToID],
[MessageFromID],
[Message],
[mobile],
[Date],
[Checked]
)
VALUES
(
@MessageToID,
@MessageFromID,
@Message,
@mobile,
@Date,
'0'
)
SELECT @blocked = 0
end
ELSE
begin
SELECT @blocked = 1
end

GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 19:42:39

You could add code to limit the number of posts for a user to a reasonable amount, maybe 200 in the last 24 hour period, and 20 in the last hour.

You could run some analysis of non-bot posts to see what reasonable post limits are.

CODO ERGO SUM

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-03-29 : 20:34:17

hi Michael,

Thanks for the suggestion, seems like a good idea. I'm having difficulty with the t-sql. I think a good check would be to check for X amount of IM's in the past 5 minutes. I need a construct that if they are over X IM's in the past 5 minutes it inserts into an alternate table

How could I accomplish this efficiently as its called alot?

Thanks,
Mike

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-29 : 20:58:14

Another thing you might try is to limit accounts that have just registered to a small number of posts for the first day or week, maybe 20 per day. If you are capturing IP addresses, limit the number of accounts they can register in one day to a small number.

Also, when someone goes over their limit for posts in an hour or day, you could ask them to answer a text question. This would make it a lot more work to program the bots to attack your site.

If you need help with queries, you should post your table DDL, including indexes and constraints, and stored procedure code. Please post it in the CODE tags to make it easier to read.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources