I had a run in with a blog spammer recently (see here), but there wasn't anyway in .TEXT to block comments by their content.
I didn't want to touch the web code, so I went ahead and hacked something together in the database.
Basically its a table of phrases that will be checked before an entry is made. If there are any hits, the comment won't be posted.
I haven't dug much into the .TEXT internals, so I don't know if this is 100% acccurate, but I tried to make it so it only affects public comments. The blog owner can still use all the blocked words they want.
If you want to use it, you'll need the table in this SQL script:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FILTER_WORD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [FILTER_WORD] (
[fw_ID] [int] IDENTITY (1, 1) NOT NULL ,
[fw_WORD] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_FILTER_WORD] PRIMARY KEY CLUSTERED
(
[fw_ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
The table's name is FILTER_WORD with 2 columns fw_ID (incrementing identity) and fw_WORD to hold the word/phrase/whatever you want to block. In my case these are domain names that always appear in the messages.
Now you need to modify one of the stored procedures: blog_InsertEntry
Here is the script in a text file, I don't want to paste the whole thing into this post: http://www.johnsample.com/misc/blog_InsertEntry.txt
Now just fill FILTER_WORD with phrases to block and you're set.
I hacked this thing together in a hurry, so I make no claims as to its suitability, or even that it may screw up and delete your entire DB. All I know is that it works for me, so be careful.
Issues to be aware of:
1. It is case sensitive
2. It will fail if the comment is more than 8000 (or maybe less) characters.
3. It doesn't tell the user why the comment was rejected, or even that the comment was rejected at all.
Now, if you want to test it, I put a test filter in my database: IAMATESTFILTER (Notice I can write it here, but you can't put it in the comments)
I hope this helps everyone!
UPDATE
I fixed the bug that was causing a delay in the posts and stopping email. Rerun the SQL file for the new version (just the stored procedure, no need to rebuild the table).
Filters are also case insensitive now.
UPDATE 2
The filters now check trackback entries.
IP, name, title, and author are also run against the filters.
To block IPs, just them into FILTER_WORD table like all the others.
I was wrong on where the trackbacks come in, this proc will need to be altered also:
http://www.johnsample.com/misc/blog_InsertPingTrackEntry.txt
UPDATE 3
Here's a beta version for Community Server that filters only anonymous posts. It hasn't been tested much (I don't use CS) but from what I can tell it works.
http://www.johnsample.com/misc/cs_weblog_Post_Create.txt
I've only tried it with v1.0.
The only thing that will be filtered are Anonymous posts, modify to your heart's content.
This is the only piece I added in, nothing else was touched:
----------------------------------------
---Filtering
If (@PostAuthor ='Anonymous')
begin
declare @temp_text varchar(8000)
set @temp_text = UPPER(CAST(@Body as varchar(7500))) + UPPER(isnull(@UserHostAddress,'')) + Upper(isnull(@TitleUrl,'')) + Upper(isnull(@PostAuthor,'')) + Upper(isnull(@Subject,'')) + UPPER(CAST(@PropertyValues as varchar(50)))
declare @word_buffer varchar(100)
declare filterCursor CURSOR LOCAL FAST_FORWARD for
select UPPER(fw_WORD) from FILTER_WORD
OPEN filterCursor
FETCH NEXT FROM filterCursor INTO @word_buffer
WHILE @@FETCH_STATUS = 0
BEGIN
IF (CHARINDEX(@word_buffer,@temp_text) > 0)
begin
CLOSE filterCursor
DEALLOCATE filterCursor
RAISERROR('Disallowed words or phrases detected. I you feel this message is in error, please contact blog owner.',11,1)
RETURN 1
end
FETCH NEXT FROM filterCursor INTO @word_buffer
END
CLOSE filterCursor
DEALLOCATE filterCursor
end
----------------------------------------