Posts on December 3, 2006
take that, damn spammers!
Posted on 2006-12-03 22:33:00
Tags: programming sql
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 :-)
Posted on 2006-12-03 22:49:00
...except for not actually getting a Wii. I got up at 6:45 this morning and went to my Target - they only had 3! Went to a different Target and they had 27 but I was 55 or so in line. I was pretty disappointed at first, but the electronics guy at Sears has been looking for one too and he was very Zen about it, which helped. Overall the people I've dealt with trying to get one have been very friendly. And there are always more on the way, and I have my list of phone numbers and when to call, etc., so I'm hoping soon will be my time.
David's sister was in town this weekend, so yesterday we saw the National Gallery of Art and walked around a ton to see the Washington Monument, Lincoln Memorial, World War II Memorial, Vietnam Memorial, and almost saw the White House Christmas tree (well, we saw it but it hasn't been officially lit yet). Took a lot of pictures, which will go up at some point. Today we took it easier - after church we went to Annapolis to eat and look around. Found a nice little pottery store as well as a bookstore/coffee shop before we had to drop her off at the airport. When we got home I took a much-needed nap and am back to feeling pretty decent.
This backup was done by LJBackup.