• Sie befinden sich hier: Startseite > SQL-Backstube > Zeitliche IP Sperre

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

10.07.2010: zeitliche IP-Sperre realisieren

Problemstellung

Immer wieder gibt es die Anforderung eine bestimmte IP-Adresse eine zeitlang zu sperren. Beispielsweise bei Tippspielen oder ähnlichem. Wie läßt sich so etwas einfach realisieren? Das folgende Beispiel zeigt eine mögliche Lösung. Da hierbei spezielle MySQL SQL-Befehle genutzt werden, ist die Abfrage nicht unmittelbar auf andere Datenbanken portierbar. Aber prinzipiell stellen auch andere Datenbanken ähnliche Zeitberechnungen zur Verfügung.

Getestet mit folgenden Datenbanken:

* MySQL 5.1.30

Datenbank-Tabelle

Die Datenbank-Tabelle hierfür sieht wie folgt aus. Damit die Abfrage später schnell genug ist, benötigen wir auch einen passenden Index.

Listing 1:

1. /**
2. * Log-Tabelle mit den IP-Adressen
3. */
4. CREATE TABLE ip_sperre (
5. ip VARCHAR(60) NOT NULL,
6. zeit TIMESTAMP NOT NULL
7. );
9. CREATE INDEX sx_ip_sperre_01 ON ip_sperre ( ip, zeit);

Konzept

Kommt ein Benutzer (mit z. B. der IP 255.255.255.254 ) auf die Seite, wird zuerst seine IP geprüft. Gibt es zu dieser IP bereits einen Eintrag, der jünger als 1 Stunde ist, liefert die Abfrage ein Ergebnis. Existiert kein Eintrag innerhalb der letzten Stunde, liefert die Abfrage als Ergebnis die Ziffer 0.

Listing 2:

1. SELECT COUNT(*) AS sperre
2. FROM ip_sperre
3. WHERE ip = '255.255.255.254'
4. GROUP BY ip
5. HAVING MAX(zeit) >= SUBTIME(current_timestamp,'01:00:00.000000');

Ist keine Sperre erforderlich, wird ein Datensatz mit dieser IP (z. B. 255.255.255.254 ) und einem aktuellen Zeitstempel eingefügt.

Listing 3:

1. INSERT INTO ip_sperre
2. ( ip, zeit ) VALUES
3. ( '255.255.255.254', CURRENT_TIMESTAMP);

Testdaten

Für das nachfolgende Beispiel legen wir uns ein paar Testdaten an. Hierbei handelt es sich um drei verschiedene IP-Adressen mit jeweils eigenem Zeitstempel.

Listing 4:

1. /**
2. * Einfügen von drei Testdatensätzen
3. */
4. INSERT INTO ip_sperre
5. ( ip, zeit ) VALUES
6. ( '127.0.0.1', '2010-07-09 11:00:00.000000'),
7. ( '127.0.0.2', '2010-07-09 12:30:00.000000'),
8. ( '127.0.0.3', '2010-07-09 13:00:00.000000');

Und so läßt sich prüfen, welche IPs zum aktuellen Zeitpunkt (13:00 Uhr am 05.07.2010) gesperrt sind, also der neueste Eintrag vor weniger als einer Stunde erfolgt ist.

Listing 5:

1. /**
2. * Alle gesperrten IPs laut Zeitstempel minus 1 Stunde suchen
3. */
4. SELECT ip AS sperre
5. FROM ip_sperre
6. GROUP BY ip
7. HAVING MAX(zeit) >= SUBTIME('2010-07-09 13:00:00.000000','01:00:00.000000');
9. +-----------+
10. | sperre |
11. +-----------+
12. | 127.0.0.2 |
13. | 127.0.0.3 |
14. +-----------+
15. 2 rows in set (0.08 sec)

Mit Hilfe eines GROUP BY über die Spalte IP und HAVING MAX(zeit) wird der neueste Eintrag zu jeder IP gefunden. Ist dieser neueste Zeiteintrag (siehe HAVING MAX(zeit)) dann aktueller als der berechnete Zeitwert von SUBSTIME(), dann läuft die Sperrzeit noch.

Der Funktion SUBTIME werden zwei Parameter übergeben. Zuerst die aktuelle Testzeit (05.07.2010 13:00 Uhr), sowie als zweiter Parameter die Zeitmenge (1 Stunde), die abgezogen werden soll.

SQL - Abfrage

So wird ermittelt, ob eine IP gesperrt ist.

Listing 6:

1. /**
2. * Ermittelt ob eine IP noch gesperrt ist
3. * sperre => 0 (Nein) , => 1 (Ja)
4. */
5. SELECT COUNT(*) AS sperre
6. FROM ip_sperre
7. WHERE ip = '127.0.0.1'
8. GROUP BY ip
9. HAVING MAX(zeit) >= SUBTIME('2010-07-09 13:00:00.000000','01:00:00.000000');
11. Empty set (0.01 sec)
13. mysql>

Als Ergebnis liefert MySQL Empty set. Die IP 127.0.0.1 ist demnach nicht mehr in der Zeitsperre. Dieser Benutzer darf sich wieder anmelden.

Hinweis: Da es verschiedenste Möglichkeiten gibt die IP zu manipulieren, ist eine alleinige Prüfung auf die IP nicht ausreichend, um die Manipulation einer Abstimmung zu verhindern.

https://twiedmann.de