User:RobinHood70/Abuse Filter Spam Procedure

The UESPWiki – Your source for The Elder Scrolls since 1995
Jump to: navigation, search

This will document all procedures and relevant queries used to block spammers based on the Abuse Filter logs. Text in blue needs to be changed before running the queries.

Prolific IP Spammers[edit]

Find prolific IP spammers since last check:

SELECT afl_ip, afl_user_text, COUNT(*) Cnt
FROM abuse_filter_log
WHERE afl_timestamp >= 20220800000000
GROUP BY afl_ip, afl_user_text
HAVING Cnt >= 50 #May need to be adjusted depending on frequency of check
ORDER BY afl_ip;

Scan the list for patterns of IPs that may need range-blocked. Check against existing blocks and/or re-run the query, going back further, to determine if a wider block range is appropriate. If desired, enter IP into APNIC, RIPE, ARIN, or other address registry to determine CIDR range for ISP.

Find prolific IP spammer ranges:

SELECT LEFT(afl_ip, LOCATE('.', afl_ip, LOCATE('.', afl_ip) + 1) - 1) ip_prefix, COUNT(*) Cnt
FROM abuse_filter_log
WHERE afl_timestamp >= 20220800000000
GROUP BY ip_prefix
HAVING Cnt >= 50
ORDER BY ip_prefix;

Compare this with the previous query and, using the query below, check any that appear on this list that didn't appear on the previous one. This will pick up spammers whose edits are spread across a range.

SELECT DISTINCT afl_ip
FROM abuse_filter_log
WHERE afl_ip LIKE '192.168.%' AND afl_timestamp >= 20220800000000
ORDER BY afl_ip;

Check proposed range blocks from the above queries against the following query to ensure there are no legitimate edits coming from the range. If there are, narrow the range or use specific IP blocks. Otherwise, block the range (turn all checkboxes off for range blocks).

SELECT *
FROM revision
WHERE rev_user_text LIKE '192.168.%' /* 'User_Name%' */;

This query can be used find spam edits that may have been overlooked as well as legitimate edits by both users and IPs. If there are legitimate edits from an IP in a range, the range will have to be adjusted, using an individual block if needed. Block all remaining IP ranges normally.

Prolific Named Spammers[edit]

Check for prolific named spammers since last check:

SELECT afl_user_text, COUNT(*) Cnt
FROM abuse_filter_log
WHERE afl_ip != afl_user_text AND afl_timestamp >= 20220800000000
GROUP BY afl_user_text
HAVING Cnt >= 10 #May need to be adjusted depending on frequency of check
ORDER BY afl_user_text;

Double-check edits where a user has triggered the filter while attempting legitimate edits. Go to user contributions then, if no good edits there, click the Abuse Log link in the top bar from there. Check if there are common links between their edits and others' or if they've been highly prolific about adding a specific link (e.g., 20+ abuse edits); if so, add to MediaWiki:Spam-blacklist.

Block remaining users normally.

Wider IP Patterns[edit]

Occasionally check for patterns of single blocks that may need a range block:

SELECT 
    CAST(LEFT(ipb_address, LOCATE('.', ipb_address, LOCATE('.', ipb_address) + 1) - 1) AS char (7)) Address16,
    COUNT(*) Cnt
FROM ipblocks
WHERE ipb_user = 0 AND ipb_range_start = ipb_range_end AND ipb_expiry != 'infinity'
GROUP BY Address16
HAVING Cnt >= 3
ORDER BY Cnt DESC;