Anatoly Lubarsky Logo
programming, design, integration, games, music

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


Related Posts:

Saturday, March 26, 2005 2:29 AM

Comments

# Anti trackback spam patch

6/13/2005 3:22 PM by Anatoly Lubarsky: Weblog

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(04) December, 2008
(04) November, 2008
(05) October, 2008
(04) September, 2008
(05) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(04) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(04) October, 2007
(05) September, 2007
(12) August, 2007
(11) July, 2007
(14) June, 2007
(13) May, 2007
(13) April, 2007
(10) March, 2007
(11) February, 2007
(14) January, 2007
(14) December, 2006
(12) November, 2006
(08) October, 2006
(09) September, 2006
(06) August, 2006
(08) July, 2006
(10) June, 2006
(09) May, 2006
(22) April, 2006
(25) March, 2006
(12) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(16) October, 2005
(16) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(12) May, 2005
(12) April, 2005
(20) March, 2005
(11) February, 2005
(12) January, 2005
(18) December, 2004
(13) November, 2004
(12) October, 2004
(14) September, 2004
(09) August, 2004
(23) July, 2004
(19) June, 2004
(29) May, 2004
(19) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs