Gallery2 Comment Spam

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’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:-

SELECT g_id INTO TEMPORARY tmptable FROM g2_comment WHERE
g_host='' or
g_host='' or
g_comment like '%http:%' or
g_comment like '%url=%';

DELETE FROM g2_entity WHERE g_id IN (SELECT g_id FROM tmptable);
DELETE FROM g2_childentity WHERE g_id IN (SELECT g_id FROM tmptable);
DELETE FROM g2_comment WHERE g_id IN (SELECT g_id FROM tmptable);
DROP TABLE tmptable;

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.

This entry was posted in Gallery2. Bookmark the permalink.

Leave a Reply