PHPJunkyard support forum

Code: Select all

https://myhesk.domain.com/inc/mail/import_Geo-Block-List.php?suppressOutput=true&key=XXXXXXXXXXX&allowedCountries=CH,DE,AT,FR,IT&excludedIPRanges=1.1.1.1-2.2.2.2,8.8.8.8

Code: Select all

<?php
/*
!!!!!-------- IMPORTANT ---------!!!!!
This script potentially imports a large number of entries. Displaying the entries under "Banned IPs" may therefore cause a browser crash!

This script downloads a CSV file from a URL containing IP address ranges and associated country codes.
It clears a database table and fills it with the IP address ranges that are not included in the list of allowed countries
or are within the excluded IP ranges.

Before importing, it checks if the "id" column in the "hesk_banned_ips" table is of type BIGINT(6) with AUTO_INCREMENT.
If not, the column is adjusted accordingly.
This is necessary because the original HESK table only allows SMALLINT(5), which cannot accommodate more than 65,535 rows.

The CSV file must be structured as follows:
Column 1: Start IP Address: The starting address of the IP range (e.g., 1.1.1.1).
Column 2: End IP Address: The ending address of the IP range (e.g., 1.1.1.10).
Column 3: Country Code: The 2-letter country code (e.g., CH, DE, AT).

Example of a line: 1.1.1.1,1.1.1.10,CH

Possible parameters:
- allowedCountries: A comma-separated list of 2-letter country codes that should not be inserted into the database.
At least the country code of your own country must be specified, otherwise everything will be blocked!
Example: ?allowedCountries=CH,DE,AT

- excludedIPRanges: A comma-separated list of IP ranges or individual IP addresses that should not be inserted into the database.
Example: ?excludedIPRanges=1.1.1.1-1.1.1.10,2.2.2.2

- suppressOutput: If this parameter is set to 'true', no screen outputs will be displayed.
Example: ?suppressOutput=true
*/

define('IN_SCRIPT', 1);
define('HESK_PATH', dirname(dirname(dirname(__FILE__))) . '/');
define('NO_HTTP_HEADER', 1);

// Parameter
$url = "https://github.com/sapics/ip-location-db/raw/refs/heads/main/geo-whois-asn-country/geo-whois-asn-country-ipv4.csv";
$allowedCountries = isset($_GET['allowedCountries']) ? array_map('trim', explode(',', $_GET['allowedCountries'])) : ['CH'];
$excludedIPRanges = isset($_GET['excludedIPRanges']) ? array_map('trim', explode(',', $_GET['excludedIPRanges'])) : [];
$suppressOutput = isset($_GET['suppressOutput']) && $_GET['suppressOutput'] == 'true';

require(HESK_PATH . 'hesk_settings.inc.php');
require(HESK_PATH . 'inc/common.inc.php');

// To execute the script, an API key must be provided using the parameter `?key=XXXXXXXXX`.
hesk_authorizeNonCLI();

function ipToCIDR($ipAddress) {
    $octets = explode('.', $ipAddress);
    if (count($octets) != 4) {
        return -1;
    }
    return $octets[0] * 256 * 256 * 256 + $octets[1] * 256 * 256 + $octets[2] * 256 + $octets[3];
}

function isIPInExcludedRange($ip, $excludedIPRanges) {
    $ipLong = ipToCIDR($ip);

    foreach ($excludedIPRanges as $range) {
        if (strpos($range, '-') !== false) {
            list($startIP, $endIP) = explode('-', $range);
            $start = ipToCIDR(trim($startIP));
            $end = ipToCIDR(trim($endIP));

            if ($ipLong >= $start && $ipLong <= $end) {
                return true;
            }
        } else {
            $excludedIP = ipToCIDR(trim($range));
            if ($ipLong == $excludedIP) {
                return true;
            }
        }
    }
    return false;
}

function getDBConnection($db_name, $db_user, $db_pass) {
    try {
        $db = new PDO('mysql:host=localhost;dbname=' . $db_name, $db_user, $db_pass);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $db;
    } catch (PDOException $e) {
        die("Datenbankverbindungsfehler: " . $e->getMessage());
    }
}

function checkAndFixIdColumn($db) {
    try {
        $result = $db->query("SHOW COLUMNS FROM hesk_banned_ips LIKE 'id'");
        $column = $result->fetch(PDO::FETCH_ASSOC);

        if ($column && (strtoupper($column['Type']) != 'BIGINT(6)' || strtoupper($column['Extra']) != 'AUTO_INCREMENT')) {
            $db->exec("ALTER TABLE hesk_banned_ips MODIFY COLUMN id BIGINT(6) NOT NULL AUTO_INCREMENT");
            if (!$suppressOutput) {
                echo "Die Spalte 'id' wurde auf BIGINT(6) AUTO_INCREMENT geändert.<br />";
            }
        }
    } catch (PDOException $e) {
        echo "Fehler beim Überprüfen oder Ändern der Spalte 'id': " . $e->getMessage() . "<br />";
    }
}

function truncDB($db) {
    try {
        $trunc = $db->prepare("TRUNCATE TABLE hesk_banned_ips");
        $trunc->execute();
        if (!$suppressOutput) {
            echo "Tabelle wurde erfolgreich geleert.<br />";
        }
    } catch (PDOException $e) {
        echo "Fehler beim Leeren der Tabelle: " . $e->getMessage() . "<br />";
    }
}

function insertIPRange($db, $startIP, $endIP, $country, $suppressOutput) {
    $ipFromCIDR = ipToCIDR($startIP);
    $ipToCIDR = ipToCIDR($endIP);
    $ipDisplay = $startIP . ' - ' . $endIP;

    try {
        $stmt = $db->prepare("INSERT INTO hesk_banned_ips (ip_from, ip_to, ip_display, banned_by, dt) VALUES (?, ?, ?, 3, NOW())");
        $stmt->execute([$ipFromCIDR, $ipToCIDR, $ipDisplay]);
        if (!$suppressOutput) {
            echo $ipDisplay . ' | ' . $country . '<br />';
        }
    } catch (PDOException $e) {
        echo "Fehler beim Einfügen in die Datenbank: " . $e->getMessage() . "<br />";
    }
}

function downloadAndProcessCSVFromUrl($url, $db, $allowedCountries, $excludedIPRanges, $suppressOutput) {
    try {
        $file = fopen($url, 'r');
        if ($file === FALSE) {
            throw new Exception("Fehler beim Öffnen der URL.");
        }

        while (($line = fgetcsv($file)) !== FALSE) {
            if (count($line) < 3) continue;

            $startIP = trim($line[0]);
            $endIP = trim($line[1]);
            $country = trim($line[2]);

            if (!in_array($country, $allowedCountries)) {
                $startIPCIDR = ipToCIDR($startIP);
                $endIPCIDR = ipToCIDR($endIP);

                $excludedIPsInRange = [];
                foreach ($excludedIPRanges as $range) {
                    if (strpos($range, '-') === false) {
                        $excludedIP = ipToCIDR(trim($range));
                        if ($excludedIP >= $startIPCIDR && $excludedIP <= $endIPCIDR) {
                            $excludedIPsInRange[] = $excludedIP;
                        }
                    }
                }

                sort($excludedIPsInRange);

                $currentStartIP = $startIP;
                foreach ($excludedIPsInRange as $excludedIP) {
                    $excludedIPStr = long2ip($excludedIP);
                    if ($currentStartIP != $excludedIPStr) {
                        insertIPRange($db, $currentStartIP, long2ip($excludedIP - 1), $country, $suppressOutput);
                    }
                    $currentStartIP = long2ip($excludedIP + 1);
                }

                if ($currentStartIP != $endIP) {
                    insertIPRange($db, $currentStartIP, $endIP, $country, $suppressOutput);
                }
            } else {
                if (!$suppressOutput) {
                    echo '<strong>' . $startIP . ' - ' . $endIP . ' | ' . $country . ' (erlaubtes Land, nicht eingefügt)</strong><br />';
                }
            }
        }
        fclose($file);
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

try {
    $db = getDBConnection($hesk_settings['db_name'], $hesk_settings['db_user'], $hesk_settings['db_pass']);
    checkAndFixIdColumn($db);
    truncDB($db);
    downloadAndProcessCSVFromUrl($url, $db, $allowedCountries, $excludedIPRanges, $suppressOutput);
} catch (Exception $e) {
    echo "Ein Fehler ist aufgetreten: " . $e->getMessage();
}
?>