B# .NET Blog

Recently, I received quite some spam from the same IP addresses, so I decided to fight back by extending the spam trigger on the SQL Server database in order to block comments from a list of IP addresses. Pretty simple to do and effective. Here's a quick code snippet:

CREATE TRIGGER BlockBlackIPs ON dbo.blog_content INSTEAD OF INSERT AS

DECLARE @PostType int
SET @PostType = (SELECT [postType] FROM inserted)
 
 IF @PostType = 3 -- if post is a comment
 BEGIN
    DECLARE @BannedIPCount int
    DECLARE @SourceName nvarchar(200)
    SET @SourceName = (SELECT CAST([SourceName] AS nvarchar(200)) FROM inserted)
    SET @BannedIPCount = (SELECT COUNT(*) FROM BannedIPs WHERE IP=@SourceName)

    IF @BannedIPCount = 0
     INSERT INTO dbo.blog_content ([Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName])
     (SELECT [Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName] FROM inserted)
    ELSE
     UPDATE dbo.blog_content SET feedbackCount = feedbackcount-1 WHERE id = (SELECT parentID FROM inserted)
 END
ELSE
 BEGIN
   INSERT INTO dbo.blog_content ([Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName])
   (SELECT [Title], [DateAdded], [SourceUrl], [PostType], [Author], [Email], [SourceName], [BlogID], [Description], [DateUpdated], [TitleUrl], [Text], [ParentID], [FeedBackCount], [PostConfig], [EntryName] FROM inserted)
 END