{"id":235,"date":"2008-09-25T10:31:22","date_gmt":"2008-09-25T10:31:22","guid":{"rendered":"http:\/\/blog.stmellion.org\/?p=235"},"modified":"2008-09-25T10:31:22","modified_gmt":"2008-09-25T10:31:22","slug":"gallery2-comment-spam","status":"publish","type":"post","link":"https:\/\/blog.stmellion.org\/?p=235","title":{"rendered":"Gallery2 Comment Spam"},"content":{"rendered":"<p>I store all my photos in <a href=\"http:\/\/gallery.menalto.com\/\">Gallery2<\/a> with a <a href=\"http:\/\/www.postgresql.org\/\">PostgreSQL<\/a> database back-end.  It works well in most respects but fails miserably when it comes to managing Comments.  Whilst it&#8217;s possible to delete them, it can only be done one at a time; a painfully slow process when there can be tens of thousands of spam comments.  This little PostgreSQL script can take the pain out of it:-<\/p>\n<pre>\r\nSELECT g_id INTO TEMPORARY tmptable FROM g2_comment WHERE\r\ng_host='189.59.236.131' or\r\ng_host='159.148.82.3' or\r\ng_comment like '%http:%' or\r\ng_comment like '%url=%';\r\n\r\nDELETE FROM g2_entity WHERE g_id IN (SELECT g_id FROM tmptable);\r\nDELETE FROM g2_childentity WHERE g_id IN (SELECT g_id FROM tmptable);\r\nDELETE FROM g2_comment WHERE g_id IN (SELECT g_id FROM tmptable);\r\nDROP TABLE tmptable;\r\n<\/pre>\n<p>This should only be treated as an example but it provides a good foundation to work from.  Bulk comments often come from common IP Addresses so the g_host entries can be used to handle that without too much pain.  The g_comment fields match comments that contain http or url entries.  As none of my good comments contain links, these are my ultimate Spam comment killers.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I store all my photos in Gallery2 with a PostgreSQL database back-end. It works well in most respects but fails miserably when it comes to managing Comments. Whilst it&#8217;s possible to delete them, it can only be done one at a time; a painfully slow process when there can be tens of thousands of spam&hellip; <a class=\"more-link\" href=\"https:\/\/blog.stmellion.org\/?p=235\">Continue reading <span class=\"screen-reader-text\">Gallery2 Comment Spam<\/span><\/a><\/p>\n","protected":false},"author":24,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32],"tags":[],"class_list":["post-235","post","type-post","status-publish","format-standard","hentry","category-gallery2","entry"],"_links":{"self":[{"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=\/wp\/v2\/posts\/235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=\/wp\/v2\/users\/24"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=235"}],"version-history":[{"count":1,"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=\/wp\/v2\/posts\/235\/revisions"}],"predecessor-version":[{"id":236,"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=\/wp\/v2\/posts\/235\/revisions\/236"}],"wp:attachment":[{"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.stmellion.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}