Greg (gregstoll) wrote,
Greg
gregstoll

  • Mood:

take that, damn spammers!

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 :-)
Tags: programming, sql
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 4 comments