Spam Control - quick fix

I've noticed a lot of spam posted to the forums lately, so I thought I'd share this 'quick fix' (hack ;) that I've used to remove/prevent spammy Contact Us messages (when I don't want to use a CAPTCHA).  The basic idea is that a SQL trigger prevents the contact us email from being inserted into the email queue.  It does require that you have direct access to our SQL server and a tool like SSMS...


CREATE TABLE [dbo].[SpamPhrase](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  Phrase [nvarchar](50) NOT NULL,  
PRIMARY KEY CLUSTERED
(
  [Id] ASC
))

GO

--do these as needed - e.g.
insert into [SpamPhrase](Phrase) values ('generic cialis')


CREATE TRIGGER QueuedEmailSpamBlocker
ON [dbo].QueuedEmail
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON

    if exists ( select 1
                  from inserted i
                  join SpamPhrase s on i.Body like '%'+s.Phrase+'%'
                 where i.Subject like '%Contact us%'
              )
        rollback transaction
END

GO

(Note, that this may throw an Exception that put up an error message in the browser, but it's just a bot doing the spam, so they won't mind ;)

I think something needs to be done.  It could be me but it seems like it is getting worse.

At the current time of writing of the 12 listed posts on the first page of 'General Support', 9 of them are spam.

Thanks a lot for suggestion! Actually it's already implemented. We have a list of such words. But spammers always find new "spammy words"

Yes, the spammers are a pain in the a**...

Why don't you enable forum recaptcha and end it once and for all ?

Andrei

I suggest looking for "https" in the subject.   Most of them have that.

Andrei, have you looked at https://www.stopforumspam.com as a way of trying to prevent spam?

I imagine you might have come across it but I mention it here just incase.  It's a free service and seems quite popular after looking at other websites.

They have an api that you can use.  To quote their website:

A typical successful response would look like this:

<response success="true">
    <type>ip</type>
    <appears>yes</appears>
    <lastseen>2007-09-18 05:48:53</lastseen>
    <frequency>2</frequency>
</response>

Examples:

http://api.stopforumspam.org/api?ip=91.186.18.61
http://api.stopforumspam.org/[email protected]
http://api.stopforumspam.org/api?username=MariFoogwoogy

You could potentially fill in some quick api requests using some of the spam email/ip/username information you have in your forum database to see if any get reported as spam.  This would allow you to see if this could be a possible solution (if you have time to implement).

davidandrewpowell wrote:
Thanks a lot, David! We'll definitely check this service

How about limiting the number of new topics a user can post in a hour / day?

(and you still did not block when 'https:' is in the subject ;)

New York wrote:
Actually spammers usually post 1-2 topics per account. So it won't help

Why not enable reCAPTCHA?