Need help cleaning up mass spam on SMF forum (mysql commands needed?)

10 replies
I need to go through my forum and delete all messages which contain spam words, like c*alis, v*gra etc....

How can i do this?

SELECT * FROM `smf_messages` WHERE `body` LIKE %cialis%;

Something like that?
#cleaning #commands #forum #mass #mysql #needed #smf #spam
  • Profile picture of the author Tim Brownlaw
    I feel your pain GeorgR.

    I've got an old SMF DB to play with from a forum I took down ages back as it got swamped by our misguided friends!

    I was going to clean it up but never got back to it...

    I just ran your little Select on it -
    SELECT * FROM `smf_messages` WHERE `body` LIKE '%cialis%'
    and it came back with 61 hits...

    But that's not helping you...
    I've had a quick peek over at the SMF forum and there are a few suggestions there but nothing like - click and kill... after doing a review of course.

    I'd also want it to remove the offending member(s) as well.

    So the Select will show the entries, it won't remove them.
    I'm a little strapped for time at the moment to come up with an actual solution and test it... But I will be working on one later... Again that doesn't help you out right now.

    Are we talking 100's, 1000's?

    We really need to collect all the Spammers, put them on an island with a PC each on an intranet and let them Spam each other to oblivion!

    I'm currently filtering out some spammers links they put into posts on my older WP blogs...

    Hopefully someone else has some good ideas or has had to do this in the past!

    Cheers
    Tim
    {{ DiscussionBoard.errors[3392571].message }}
    • Profile picture of the author jminkler
      Originally Posted by Tim Brownlaw View Post

      We really need to collect all the Spammers, put them on an island with a PC each on an intranet and let them Spam each other to oblivion!
      I blame the American companies who pay for PPC
      {{ DiscussionBoard.errors[3404024].message }}
  • Profile picture of the author KirkMcD
    DELETE* FROM `smf_messages` WHERE `body` LIKE %cialis%;
    {{ DiscussionBoard.errors[3392626].message }}
    • Profile picture of the author Tim Brownlaw
      Originally Posted by KirkMcD View Post

      DELETE* FROM `smf_messages` WHERE `body` LIKE %cialis%;
      Don't forget the single quotes around '%cialis%'
      So it should be DELETE* FROM `smf_messages` WHERE `body` LIKE '%cialis%';

      We also need to Delete the members that created the posts and the member count as well.

      It's a little more involved but it will at least get rid of those particular ones. Then there are probably a few more matches need to get rid of the others.

      Cheers
      Tim
      {{ DiscussionBoard.errors[3392651].message }}
  • Profile picture of the author KirkMcD
    i don't have smf, so a little pseudo code

    delete from members_table where member_id in (SELECT DISTINCT member_id FROM `smf_messages` WHERE `body` LIKE '%cialis%');

    then delete the posts.
    {{ DiscussionBoard.errors[3394305].message }}
    • Profile picture of the author Tim Brownlaw
      GeorgR, if you've just got posts that have a few keywords which is enough to detect all of your Spamming Posts, then I've got a fix.
      I'm still testing it at the moment.

      My Sample "Spammed to high heaven" SMF Database has got about every combination under the sun in it, making detecting the Spam comments a bit of a challange, to say the least.
      I'm only hitting about 8% of the messages with a rather long keyword list.

      It's got over 5000 spammers registered and over 1600 topics.

      So I think we need to add phase B to this.
      Better to bring up a list of all the usernames with some details - re their websites which they love to put in their profiles - select them all by default, then go through and untick the real members.

      Then hit the "Snuff the Spamming Mongrels" button. I think I'll actually call it that too!

      So Phase A - detect keywords and snuff the spammers out of existence is working.

      If that is enough for you, I'll toss you the script with destructions.
      Do you know how to perform a backup of your forums DB- just in case?

      Cheers
      Tim
      {{ DiscussionBoard.errors[3395383].message }}
      • Profile picture of the author Tim Brownlaw
        I've Found a quick Fix for this.

        Now this only works if your Spam messages contain the word "cialis" AND it WONT Remove the offending members.
        You need to grab the ID_MEMBER from the message and use it to kill the member, which my little script does.

        You can run the query under your Cpanel->phpmyadmin->SQL
        - Make sure you select the correct DB for this to work.

        DELETE * FROM `smf_messages` WHERE `body` LIKE '%cialis%';
        Of course you can elaborate on the above if you have more keywords like...
        DELETE * FROM smf_messages
        WHERE
        body LIKE '%cialis%' OR
        body LIKE '%othernastythings%';

        Then log into your SMF Admin and Click on Forum Maintenance under Maintenance on the bottom left of the menus.

        In the top section that appears under Forum Maintenance - General Maintenance Click on the following...
        1. Recount all Forum totals and statistics - this cleans up the counts after you've clobbered the bad messages.
        2. Find and repair any errors - this fixes up the broken bits
        3. Empty out unimportant logs and probably good to run
        4. Find and repair any errors again just to make sure.

        Remember this will only clear the messages and not remove the Members.

        But until I get my little script ready for public use, that should get you up and running.

        Cheers
        Tim
        {{ DiscussionBoard.errors[3397931].message }}
  • Profile picture of the author jminkler
    You may also want to look into mysql triggers

    especially 'ON INSERT'

    To just delete comments like this before they are inserted
    {{ DiscussionBoard.errors[3399750].message }}
    • Profile picture of the author Tim Brownlaw
      Originally Posted by jminkler View Post

      You may also want to look into mysql triggers

      especially 'ON INSERT'

      To just delete comments like this before they are inserted
      Thanks Jarret,
      Yeah I'm looking at how to tighten this thing up.
      I did it some time back - after I found it had more holes than a sieve.
      Allegedly the "new" version is a bit tighter but we've heard that before!

      I'm pouring over ways to make it harder for the mongrels.
      Cheers
      Tim
      {{ DiscussionBoard.errors[3403152].message }}
      • Profile picture of the author jminkler
        Originally Posted by Tim Brownlaw View Post

        Thanks Jarret,
        Yeah I'm looking at how to tighten this thing up.
        I did it some time back - after I found it had more holes than a sieve.
        Allegedly the "new" version is a bit tighter but we've heard that before!

        I'm pouring over ways to make it harder for the mongrels.
        Cheers
        Tim
        And people wonder why you need secure sites .. pff

        You may also want to stop them at the source, record their IP, and add it to the htaccess file ..

        another trick I like to do, put in a hidden field called email or something common, and if they try to submit data in that param .. thats when you know its spam, record ban block etc on that IP then.
        {{ DiscussionBoard.errors[3403988].message }}

Trending Topics