I'd previously closed all comments on posts over 30 days old, across all 700 blogs on the installation, so I was dismayed to be getting comment spam still. How was it sneaking in? After looking through my logs for POST comments I pulled out a bunch of likely looking pages.<\/p>\n
grep wp-comments-post access.log<\/pre>\nOK so it seemed that although all the WP Posts were locked down, some Pages<\/em><\/strong> and some Media posts<\/strong><\/em> were still not locked down — the 30 day expiry thing must only apply to Posts. Grrr. The internet revealed no easy way to fix this so I looked in the database and crafted a query which would do this for me on one table …<\/p>\n
UPDATE `wp_1_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\"<\/pre>\n
\nThis closed all posts which were modified before October 31st last year. You can of course alter the date accordingly … \ud83d\ude42<\/p>\nOK, so now to get a list of relevant databases:<\/p>\n
SHOW TABLES LIKE \"wp\\_%\\_posts\"<\/pre>\nYou'll need those backslashes in there to escape the underscore character. This will give you a list of databases. Then use a bit of text editor search and replace (thank you jedit), to create an SQL script like this:<\/p>\n
UPDATE `wp_100_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_101_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_102_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_103_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_104_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_106_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_107_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";\r\nUPDATE `wp_10_posts` SET `comment_status` = \"closed\", `ping_status` = \"closed\" WHERE `post_modified` < \"2013-10-31 11:59:59\";<\/pre>\n… etc.<\/p>\n
Don't forget the ; at the end of the line. Then you can just run the script via the import function of PHPmyadmin, or pipe it into mysql with the command line.<\/p>\n
mysql -u user -pxxxxxx database_name < script.sql<\/pre>\nSweet deal. Locked down.<\/p>\n","protected":false},"excerpt":{"rendered":"
I'd previously closed all comments on posts over 30 days old, across all 700 blogs on the installation, so I was dismayed to be getting comment spam still. How was it sneaking in? After looking through my logs for POST comments I pulled out a bunch of likely looking pages. grep wp-comments-post access.log OK so … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-290","post","type-post","status-publish","format-standard","hentry","category-it"],"_links":{"self":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts\/290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/comments?post=290"}],"version-history":[{"count":0,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/posts\/290\/revisions"}],"wp:attachment":[{"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/media?parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/categories?post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/play.datalude.com\/blog\/wp-json\/wp\/v2\/tags?post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}