<?php
/**
 * blocklist_update.php
 * Pflegt eine lokale IP-Sperrliste (PHP-Datei), gespeist aus Honeypot-Treffern
 * und/oder manueller Markierung ("IP sperren") in phpList.
 * Die Sperrliste wird von config.php bei jedem Seitenaufruf ausgelesen
 * (siehe Wiki-Kapitel 8.4 - Lese-Check).
 *
 * Voraussetzungen:
 * - phpList-Attribut "Honeypot" (Text)
 * - phpList-Attribut "IP sperren" (Checkbox, manuelle Markierung)
 * - phpList-Attribut "IP bereits gesperrt" (Checkbox, Duplikatschutz)
 *
 * Aufruf: per Cron, z. B. alle 15 Minuten
 */
 
$dbHost = '127.0.0.1';
$dbName = 'DEINE_DB';
$dbUser = 'DEIN_DB_USER';
$dbPass = 'DEIN_DB_PASSWORT';
 
$pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
 
$attrHoneypotId = X;
$attrIpBlockId  = Y;
$attrBlockedId  = Z;
 
$blocklistFile = '/pfad/zu/private/ip_blocklist.php';
 
$sql = "SELECT DISTINCT u.id
        FROM phplist_user_user u
        LEFT JOIN phplist_user_user_attribute uaip ON uaip.userid = u.id AND uaip.attributeid = :ipBlockId AND uaip.value = 'on'
        LEFT JOIN phplist_user_user_attribute uah ON uah.userid = u.id AND uah.attributeid = :honeypotId AND uah.value != ''
        LEFT JOIN phplist_user_user_attribute uab ON uab.userid = u.id AND uab.attributeid = :blockedId
        WHERE (uaip.value = 'on' OR uah.value IS NOT NULL)
          AND (uab.value IS NULL OR uab.value = '')";
 
$stmt = $pdo->prepare($sql);
$stmt->execute(['ipBlockId' => $attrIpBlockId, 'honeypotId' => $attrHoneypotId, 'blockedId' => $attrBlockedId]);
$candidates = $stmt->fetchAll(PDO::FETCH_COLUMN);
 
function getSubscriberIp($pdo, $userid) {
    $stmt = $pdo->prepare("SELECT ip FROM phplist_user_user_history 
                            WHERE userid = :uid AND ip IS NOT NULL AND ip != ''
                            ORDER BY date ASC LIMIT 1");
    $stmt->execute(['uid' => $userid]);
    return $stmt->fetchColumn();
}
 
$currentBlocklist = file_exists($blocklistFile) ? include $blocklistFile : [];
 
$newlyBlocked = [];
foreach ($candidates as $userid) {
    $ip = getSubscriberIp($pdo, $userid);
    if ($ip && !in_array($ip, $currentBlocklist, true)) {
        $currentBlocklist[] = $ip;
        $newlyBlocked[] = $ip;
    }
    $upd = $pdo->prepare("INSERT INTO phplist_user_user_attribute (userid, attributeid, value) 
                           VALUES (:uid, :aid, 'on')
                           ON DUPLICATE KEY UPDATE value = 'on'");
    $upd->execute(['uid' => $userid, 'aid' => $attrBlockedId]);
}
 
if (!empty($newlyBlocked)) {
    $currentBlocklist = array_values(array_unique($currentBlocklist));
    $content = "<?php\nreturn " . var_export($currentBlocklist, true) . ";\n";
    file_put_contents($blocklistFile, $content, LOCK_EX);
    echo "Neu gesperrt: " . implode(', ', $newlyBlocked) . "\n";
} else {
    echo "Keine neuen IPs zu sperren.\n";
}