The Tridecadal Korean (astralblue) wrote,
The Tridecadal Korean


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.


  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.