OK folks, I came up with something helpful and I thought I would pass it on. Recently I had a spam bot (or maybe a real person, who knows) join the forums. Went right through all of the security controls and verifications. Then, said user went about posting almost 500 porn posts all over my site’s forums. Posting topics, posts, pics and embedded videos, it was really bad and I was really mad. First off I banned the user, no problem. I figured I would do that so I could keep relevant info like IP and email addresses rather than just deleting him. Now I had the arduous task of removing all those damn posts and topics.
I do know that you can delete a user and tell SMF to also delete all of those users topics posts as well, but that hasn’t worked in the past so I did it on my own. Here is what I did.
I dug around in the database at the MySQL prompt and determined what his user ID was, and what to key on to remove all of his posts and topics. Here are a few simple queries that worked wonders for me and saved a lot of time. I would think they would do the same for you, but remember that anything can happen so BACKUP YOUR DATABASE FIRST!
*Note: All of these queries use smf_ as the prefix to the tables, that is the default and of course will have to be changed if you used something different!
OK, so first we need to figure out the ID number of the offending user. I simply did a query on all users, listing the user ID and name and then looked through until I found the one I was looking for, like this:
select ID_MEMBER, memberName from smf_members;
If you want, you can specify the username to just get that record, might depend on how many users you have. Sometimes I just like to do things manually to be thorough. It would look like this (naturally put the real username in there and keep the single quotes):
select ID_MEMBER, memberName from smf_members where memberName = 'spambotusername';
Once you have the user ID, you can then remove all of the posts and topics. For this example, I will use the ID of 10 just to make it simple. It will look like this (no single quotes here because it’s a number and not text):
delete from smf_messages where ID_MEMBER = 10;
Now you can delete all of the topics:
delete from smf_topics where ID_MEMBER_STARTED = 10;
And there you have it, all of the offending messages from that user are gone. Of course, if you have many spammers you’ll have to repeat this process for each one. I then recommend going to forum maintenance in the admin panel and running the various repair tools to clean things up in the database. I hope this helps, and like I said before, backup your database and files before you start monkeying with anything. I can’t be held responsible if something gets foobar’d.
Hey, why don’t you discuss this with other users over in the forums!