{"id":1498,"date":"2010-09-06T17:18:01","date_gmt":"2010-09-06T15:18:01","guid":{"rendered":"http:\/\/planetozh.com\/blog\/?p=1498"},"modified":"2010-09-10T12:58:16","modified_gmt":"2010-09-10T10:58:16","slug":"spam-magnet-blog-posts","status":"publish","type":"post","link":"https:\/\/planetozh.com\/blog\/2010\/09\/spam-magnet-blog-posts\/","title":{"rendered":"Spam Magnet Blog Posts"},"content":{"rendered":"

Lately, every time I manually flagged a comment as spam, I started to have the impression that they all were posted on the same post, namely an old announcement for my plugin "Absolute Comments".<\/p>\n

An SQL query later, my impression was confirmed: this blog post *is* a spam magnet:<\/p>\n

\"\"<\/p>\n

I suspect that some particular keywords in page titles act like pheromones to spammers. These keywords probably include "comments", "guestbook", "feedback" and anything that will make them think there's a form they can spam.<\/p>\n

The SQL query I used to list blog posts sorted by the number of spam they get is:<\/p>\n

\n\n\t\t
\n\n\t\t
\n\t\t\t\t\t\t< View plain text<\/span> ><\/a>\n\t\t\t\t\t<\/div>\n\n\t\t
sql<\/div>\n\n\t\t\n\t\t
\n\n\t<\/div>\n\t\n\t
\n\t\t
  1. SELECT<\/span> COUNT<\/span>(<\/span>wp_posts.<\/span>ID)<\/span> as<\/span> spam_count,<\/span> wp_posts.<\/span>ID,<\/span> wp_posts.<\/span>post_title<\/div><\/li>\n
  2. FROM<\/span> wp_posts,<\/span> wp_comments<\/div><\/li>\n
  3. WHERE<\/span> wp_comments.<\/span>comment_approved =<\/span> 'spam'<\/span> AND<\/span> wp_comments.<\/span>comment_post_ID=<\/span>wp_posts.<\/span>ID<\/div><\/li>\n
  4. GROUP<\/span> BY<\/span> wp_posts.<\/span>ID<\/div><\/li>\n
  5. ORDER<\/span> BY<\/span> spam_count DESC<\/span><\/div><\/li>\n<\/ol>\t<\/div>\n\n<\/div>\n\n

    If you want to play with it, I made a quick standalone script that will run the query and output a pretty interactive pie chart. Download spam-magnet.txt<\/a>, rename as .php<\/tt> and put it in your blog root (where wp-load.php<\/tt> is). Live demo: spam-magnets.php<\/a>.<\/p>\n

    Edit<\/strong>: per request, I've made this a plugin too<\/a>.<\/p>\n

    If you run it and notice a pattern on your blogs too, share your analysis! I've always enjoyed useless fun stats :)<\/p>\n","protected":false},"excerpt":{"rendered":"

    Lately, every time I manually flagged a comment as spam, I started to have the impression that they all were posted on the same post, namely an old announcement for my plugin "Absolute Comments". An SQL query later, my impression was confirmed: this blog post *is* a spam magnet: I suspect that some particular keywords […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[2,324,27,74,245],"class_list":["post-1498","post","type-post","status-publish","format-standard","hentry","category-published","tag-code","tag-comments","tag-spam","tag-stats","tag-wordpress"],"_links":{"self":[{"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/posts\/1498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/comments?post=1498"}],"version-history":[{"count":0,"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/posts\/1498\/revisions"}],"wp:attachment":[{"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/media?parent=1498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/categories?post=1498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/planetozh.com\/blog\/wp-json\/wp\/v2\/tags?post=1498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}