June 11th, 2006



The magic SQL1 line of the day: INSERT INTO phpbb_banlist (ban_userid) SELECT user_id FROM phpbb_users u LEFT JOIN phpbb_banlist b ON u.user_id = b.ban_userid WHERE u.username ~ '^[A-Z][a-z]+[0-9]{4}$' AND b.ban_userid ISNULL AND lower(user_website) LIKE '<a SQL text pattern>';

I just banned 312 DDR Freak spammer accounts within 15 minutes; it was a breeze.  XD

Spammer pattern courtesy of shinmizu.

P.S.  The “minimum account age to post” feature—a local phpBB2 mod that disallows a new account from posting for a certain period—seems to have a real deterring effect against spammers.  None of the 312 banned accounts could post anything ever.  XD

1 POSIX regular expression matching operators (~, ~*, !~ or !~*) are a PostgreSQL extension.