Anti Referral Spam Patch
CAPTCHA control helps me to fight comment spam. It is very effective. Nevertheless, I get lots of referrer spam each day during these months. Even though the most aggressive IPs are banned on the hosters level, I still get each day about 100 referrer spam records in the database. Usually I delete them and have already created a T-SQL script, that does the work. There are not so many anti-referrer spam solutions for .Text based engines. Almost all of them use instead of trigger that work around the original .Text T-SQL procedure to patch the table. I think, that instead of trigger is not a good solution because of performance overhead and trigger maintenance. Instead, my solution uses 2 simple patches to stored procedures.
Step 1 -- Create a banned keywords table and fill it with keywords (like poker). Something like that:
CREATE TABLE blog_BannedURLs
(
theWord VARCHAR (255) NOT NULL,
CONSTRAINT [PK_blog_BannedURLs] PRIMARY KEY CLUSTERED
(
theWord
) ON [PRIMARY]
) ON [PRIMARY]
GO
Step 2 -- Patch blog_GetUrlID procedure
ALTER PROC dbo.blog_GetUrlID
(
@Url NVARCHAR(255)
,@UrlID INT OUT
)
AS
BEGIN
SET NOCOUNT ON
SET @UrlID = -1
IF ((@Url IS NOT NULL) AND (LEN(@Url) > 0))
BEGIN
SELECT @UrlID = UrlID
FROM blog_Urls WITH (NOLOCK)
WHERE Url = @Url
IF (@@ROWCOUNT = 0)
BEGIN
DECLARE @BannedWordsCount INT
SELECT @BannedWordsCount = COUNT(theWord)
FROM blog_bannedURLs WITH (NOLOCK)
WHERE CHARINDEX(theWord, @URL, 0) > 0
IF (@BannedWordsCount) > 0
BEGIN
SELECT @UrlID = -1
END
ELSE
BEGIN
INSERT INTO blog_Urls
VALUES(@Url)
SELECT @UrlID = @@IDENTITY
END
END
END
SET NOCOUNT OFF
END
Step 3 -- add (@UrlID > -1) to the IF in InsertReferral
ALTER PROC dbo.blog_InsertReferral ... ... IF ((@UrlID IS NOT NULL) AND (@UrlID > -1))
It's ready. Enjoy :)
Updated 2005-04-02
Saturday, March 26, 2005 2:29 AM
