take that, damn spammers!
Mood: triumphant
Posted on 2006-12-03 22:33:00
Tags: programming sql
Words: 316

So I've noticed off and on for a while that my gallery has been getting some weird hits - mostly google searches for "hentay" (I'm guessing they meant hentai, which is most definitely a NSFW search term) and such. At first I laughed it off because they were obviously mistaken, but I couldn't figure out why it was happening. Apparently I have a knack for missing the blindingly obvious, so I was a bit shocked today when I looked on the front page of the gallery, again trying to figure out what was going on, and saw comment spam. Lots and lots of comment spam. (people posting links to their web sites to gain PageRank) Unfortunately, this meant I had to disable anonymous commenting, so if you want to comment just open an account and I'll approve it :-)

More unfortunately, there were still over 2000 comments (all of them spam) on the front page! I looked around and it turns out there's no way to bulk delete comments as of yet. And I sure wasn't going to sit there clicking more than 4000 times (one to delete, one to confirm). So I dug into the SQL and beat those bastards!

Anyway, in case anyone else runs into this problem, here's what I did: open up mysql and the first thing I found was that the root album had a g_id of 7. So I did these commands to first isolate the ID's of all the comments, and then delete them. There are probably better ways to do this but I'm a novice at this so I was aiming for safety...

CREATE TEMPORARY TABLE tmptable SELECT g2_Entity.g_id FROM g2_ChildEntity, g2_Entity WHERE g2_ChildEntity.g_id=g2_Entity.g_id AND g2_ChildEntity.g_parentId=7 AND g2_Entity.g_entityType='GalleryComment';

DELETE FROM g2_Comment, g2_Entity, g2_ChildEntity USING g2_Comment, g2_Entity, g2_ChildEntity, tmptable WHERE g2_Comment.g_id=tmptable.g_id AND g2_Entity.g_id=tmptable.g_id AND g2_ChildEntity.g_id=tmptable.g_id;

Hopefully this will be helpful to someone else, or at least a future version of myself :-)


Comment from ext_17502:


Sorry to see you are having spam problems, and also that this script wasn't available earlier. The Gallery team has now made a small script available that would have made this task much easier for you. You can check it out at http://codex.gallery2.org/index.php/Downloads:CommentBlaster

h0bbel - The Gallery Team

Comment from ext_17502:

Also, remember that you could add the Gallery 2 CAPTCHA for anonymous commenters, that way you probably wouldn't have to close unregistered comments.

Comment from gregstoll:

Hi, thanks for your comments! That CommentBlaster looks handy, although I had existing comments that I wanted to preserve. Luckily all the spam except for 3 were in one place...

Hey, great! I wanted to add a CAPTCHA but I forgot to look for it in the modules and stuff.

Thanks so much for your work on Gallery - I've had it running since I got my first digital camera and have loved it!

Comment from ext_17502:

Excellent, glad you found a way to fix your problems. Now, get the CAPTCHA going! :-)

This backup was done by LJBackup.