IP Range nach Besucher IP durchsuchen

[exoterm]

Hallo.
ich habe ein kleines Problem und suche einen Denkanstoss.
Also in meiner MySQL Datenbank Tabelle habe ich IP Ranges in folgender Form gespeichert:

Spalte1(startip) | Spalte2(endip)
2.20.132.0 | 2.20.132.255
194.25.145.0 | 194.25.145.63
93.88.225.0 | 93.88.239.255
usw.

Es sind über 150000 Adressbereiche gespeichert.

Nun meine Frage was würdet ihr mir empfehlen wie ich diese Adressbereiche am schnellsten nach der Besucher IP durchsuchen kann bzw. am schnellsten durchsuchen kann aus welchem Adressbereich der Besucher kommt?

Für konstruktive Antworten schonmal Danke in Voraus.

[erc]

Jedes Oktett mit 0 auf drei Ziffern auffüllen und dann kannst du mit

besucher_ip BETWEEN startip AND endip 

suchen. Die besucher_ip muss natürlich auch aufgefüllt werden. Das Prinzip würde auch bei IPv6 funktionieren, nur ist das auffüllen da etwas komplizierter.

Alternativ kannst du dir auch die INET_ATON/INET_ATOA funktionen von Mysql anschauen. Nachteil ist die funktionieren nicht mit IPv6 und sind für ein Menschen nicht lesbar.

[G.Schuster]

Ab 5.6 gibt's INET6_ATON() / INET6_NTOA().

[Gast]

//OT:
über 150 000 adressbereiche sind keine pivaten ips, auch ändert sich da ja alle nase lang was.
-> whois.
nebenbei: es ist sinnvoll ips in der gleichen reihenfolge zu speichern:

2.20.132.0 | 2.20.132.255
93.88.225.0 | 93.88.239.255

mal runter und mal hochzählen?

-> fortgeschrittenen? -> anounce .

[akretschmer]

Nun meine Frage was würdet ihr mir empfehlen wie ich diese Adressbereiche am schnellsten nach der Besucher IP durchsuchen kann bzw. am schnellsten durchsuchen kann aus welchem Adressbereich der Besucher kommt?

Dazu verwendet man passende Datentypen und passende Indexe:

test=*# select * from ip_ranges ;
       ip
----------------
 192.168.0.0/24
 10.0.0.0/8
(2 rows)

Time: 0,189 ms
test=*# \d ip_ranges
 Table "public.ip_ranges"
 Column | Type | Modifiers
--------+------+-----------
 ip     | cidr |
Indexes:
    "idx_ip" gist (ip inet_ops)

test=*# select * from ip_ranges where ip >> '10.1.2.3';
     ip
------------
 10.0.0.0/8
(1 row)

Time: 0,268 ms
test=*# explain select * from ip_ranges where ip >> '10.1.2.3';
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using idx_ip on ip_ranges  (cost=0.13..8.14 rows=1 width=32)
   Index Cond: ((ip)::inet >> '10.1.2.3'::inet)
(2 rows)

[CSharp]

Du kannst auch mit ip2long in PHP die werte als Integer in die Datenbank schreiben und lesen.

• Hält die Indizes und Spalten klein
• Ist schnell zu durchsuchen

[GoodbyeKitty]

Nimm am Besten das richtige Werkzeug für die Aufgabe... Und nicht unbedingt PHP/MySQL.

Bau Dir ein Zonefile (WAS ein NS-Server antwortet, ist ja erstmal beliebig - kann also auch eine Range sein) und pack das in RBLDNSD rein (http://www.corpit.ru/mjt/rbldnsd.html).
Aus der Problemstellung leite ich einfach mal ab, dass Du das ja (hoffentlich!) sowieso nicht in einem normalen Webhosting-Paket ausprobieren willst, oder? Folglich sollte das Ausführen vom RBLDNS ja möglich sein. Und dann mit einem beliebigen DNS-Client (host, dig, [irgendeine PHP-Klasse, wird es sicherlich geben]) ranfahren.

Falls Du das wirklich mit der Datenbank lösen willst:
- Google: "cidr to range"
- INT-Felder, bloß keine CHAR oder noch schlimmer VARCHAR
- Index auf (ip_start, ip_end)
- Wenn Du die ofiziellen Daten der RIR's (RIPE, ARIN etc.) verwendest: viel Spaß damit, da sind Fehler drin (u.a. Überlappungen)

Viel Erfolg

[GoodbyeKitty]

- Wenn Du die ofiziellen Daten der RIR's (RIPE, ARIN etc.) verwendest: viel Spaß damit, da sind Fehler drin (u.a. Überlappungen)

Vergessen: neben den Überlappungen (= Fehler) sind die Daten da natürlich auch noch hierarchisch drin abgelegt, d.h. eine Range kann > 1 Besitzer haben (Sub-Delegationen). Musst dann nach der Größe sortieren; am Einfachsten speicherst Du Dir den CIDR-Suffix (der Teil nach dem "/") mit zur Range dazu und nimmst dann den größten Suffix (= kleinste Range) als Treffer.

[Gast]

Nimm am Besten das richtige Werkzeug für die Aufgabe... Und nicht unbedingt PHP/MySQL.

Bau Dir ein Zonefile (WAS ein NS-Server antwortet, ist ja erstmal beliebig - kann also auch eine Range sein) und pack das in RBLDNSD rein (http://www.corpit.ru/mjt/rbldnsd.html).

wie willste ips ranges zuordnen, und das halbwegs automatisiert?
von was für überlappungen spricht du?

[GoodbyeKitty]

wie willste ips ranges zuordnen, und das halbwegs automatisiert?
von was für überlappungen spricht du?

Ranges sind Adressbereiche, die stehen so in den Zonefiles der RIR's drin. Halbwegs automatisiert: So funktioniert das Internet
Das ist Reverse-DNS, man muss ja aber nicht zwangsläufig mit einem Hostnamen antworten - genauso gut kann man sich ein Zonefile bauen, in dem z.B. die enthaltende Range drinsteht, die dann per Request zurückgegeben wird.

Überlappungen: in den Files der RIR's - zumindest in denen der RIPE waren beim letzten Mal, als ich die in der Hand hatte - Ranges drin, die z.B. über das Ende ihrer übergeordneten Ranges (="Eltern") hinausliefen.

[hausl]

Du kannst auch mit ip2long in PHP die werte als Integer in die Datenbank schreiben und lesen.
...

http://php-de.github.io/jumpto/ip/#der-falsche-weg

LG

[Gast]

Das ist Reverse-DNS, man muss ja aber nicht zwangsläufig mit einem Hostnamen antworten - genauso gut kann man sich ein Zonefile bauen, in dem z.B. die enthaltende Range drinsteht, die dann per Request zurückgegeben wird.

dein vorschlag läuft daraus hinaus diese zone files selbst zu schreiben?
ich ging davon aus, ews gäbe ein command bei dem ich die zone es belibigen dns im netz abfragen könnte.

von hand; ich wünsche dir recht viel glück, bei 150 000 adressbereichen.

Überlappungen: in den Files der RIR's - zumindest in denen der RIPE waren beim letzten Mal, als ich die in der Hand hatte - Ranges drin, die z.B. über das Ende ihrer übergeordneten Ranges (="Eltern") hinausliefen.

mag sein. mein dns server lässt sowas nicht zu.

[GoodbyeKitty]

dein vorschlag läuft daraus hinaus diese zone files selbst zu schreiben?

Format ist trivial, das ist also kein Problem

ich ging davon aus, ews gäbe ein command bei dem ich die zone es belibigen dns im netz abfragen könnte.

nein, das geht nicht. Wäre auch fatal, wenn das ginge: dann könnte man bequem z.B. alle Domains abfragen, die bei Hoster 2&3 gehosted werden
Desweiteren liegen ja auf den einzelnen NS-Servern auch immer nur ganz kleine Ausschnitte: nämlich für das Netz, das sie verwalten (also die Range). Anfragen, die nicht beantwortet werden können (weil sie nicht das verwaltete Netz betreffen), werden an einen übergeordneten NS weitergereicht, der "kennt" dann den zuständigen Nameserver.

von hand; ich wünsche dir recht viel glück, bei 150 000 adressbereichen.

Warum denn von Hand? Bei den RIRs liegen die Zonefiles zum Download!
Z.B.: ftp://ftp.ripe.net/ripe/dbase/split/

Zonefiles fliegen "da draußen" übrigens auch so eine ganze Menge rum: Blacklisting, IP2Country etc. wird auch so gemacht.

mag sein. mein dns server lässt sowas nicht zu.

Deswegen habe ich ja auch darauf hingewiesen, dass da Fehler drin sind. Weil man die Files eben nicht einfach ungesehen verwenden kann (sind afaik sowieso keine fertigen Zonefiles)

[CSharp]

http://php-de.github.io/jumpto/ip/#der-falsche-weg

LG

Cool!

Danke fürs korrigieren! Aber das man sie nicht als String in die DB reinschreiben soll ist glaube ich trotzdem korrekt.

[Gast]

Nun meine Frage was würdet ihr mir empfehlen wie ich diese Adressbereiche am schnellsten nach der Besucher IP durchsuchen kann bzw. am schnellsten durchsuchen kann aus welchem Adressbereich der Besucher kommt?

solange die fragestellung so vage ist bleibe ich bei whois.

die netzmaske hat erst akretschmer ins spiel gebracht, und auch nur in hinblick auf das desing der db, was ich für die zweite lösung halte.