Überprüfen Sie, ob sich die IP im Subnetz befindet

Ich habe eine Tabelle A mit IP-Adressen (ipNumeric) als vorzeichenlose Ints und eine Tabelle B mit Subnetzen (subnetNumeric):

<code>INET_NTOA(ipNumeric) = 192.168.0.1
INET_NTOA(subnetNumeric) = 192.168.0.0
</code>

Ich möchte überprüfen, ob diese IP Mitglied eines Subnetzes ist.

Die Subnetze sind Klasse A, B und C.

Ist dies in MySQL in angemessener Zeit im laufenden Betrieb möglich oder sollten die Subnetzbereiche vorberechnet werden?

Antworten auf die Frage(4)

um das Testen zu unterstützen.

<code>CREATE TABLE a (ipNumeric INT UNSIGNED);
INSERT INTO a (ipNumeric) VALUES(INET_ATON("172.16.40.101"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("192.168.1.12"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("10.1.5.51"));
INSERT INTO a (ipNumeric) VALUES(INET_ATON("10.7.1.78"));      
CREATE TABLE b (subnetNumeric INT UNSIGNED);
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("192.168.0.0"));
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("10.1.0.0"));
INSERT INTO b (subnetNumeric) VALUES (INET_ATON("172.16.0.0"));
</code>

Führen Sie nun eine Auswahlsuche nach Übereinstimmungen zwischen den Tabellen basierend auf Adresse & Maske = Subnetz durch. Hier nehmen wir Subnetze der Klasse B (255.255.0.0) an. Bitverschiebung ist nicht erforderlich, da die Funktion INET_ATON () verwendet werden kann.

<code>SELECT INET_NTOA(a.ipNumeric),INET_NTOA(b.subnetNumeric) FROM a,b 
       WHERE (a.ipNumeric & INET_ATON("255.255.0.0")) = b.subnetNumeric;

+------------------------+----------------------------+
| INET_NTOA(a.ipNumeric) | INET_NTOA(b.subnetNumeric) |
+------------------------+----------------------------+
| 192.168.1.12           | 192.168.0.0                |
| 10.1.5.51              | 10.1.0.0                   |
| 172.16.40.101          | 172.16.0.0                 |
+------------------------+----------------------------+
</code>
Lösung für das Problem

es ist machbar. Die Idee ist, dass wir die Subnetzmaske berechnen, indem wir die höchstwertigen Bits auf 1 setzen, so viele, wie von der Subnetzklasse vorgegeben. Für eine Klasse C wäre das

<code>SELECT -1 << 8;
</code>

Dann UND die Subnetzmaske mit der IP-Adresse, die Sie haben; Befindet sich die IP innerhalb des Subnetzes, sollte das Ergebnis der Subnetzadresse entsprechen - Standardnetzwerkmaterial. Am Ende haben wir also:

<code>SELECT (-1 << 8) & INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");
</code>

Aktualisieren: Ja, die Netzwerkklasse muss bekannt seinoder die Subnetzmaske (die äquivalente Information ist). Überlegen Sie, wie Sie mit dem Fall umgehen können, in dem sich das Subnetz befindetX.Y.0.0 wenn Sie diese Informationen nicht hatten. Ist dasX.Y.0.0/16 oderX.Y.0.0/8 wo das dritte Oktettpassiert einfach 0 sein? Keine Möglichkeit zu wissen.

Wenn Sie die Subnetzmaske kennen, kann die Abfrage als geschrieben werden

<code>SELECT (-1 << (33 - INSTR(BIN(INET_ATON("255.255.255.0")), "0"))) &
       INET_ATON("192.168.0.1") = INET_ATON("192.168.0.0");
</code>
 matiasf04. Apr. 2012, 00:09
Danke, aber ist dies abhängig von der Kenntnis der Subnetzklasse?
 TSG18. Okt. 2016, 04:11
Gute Antwort - aber was ist mit IPv6? Ist das überhaupt möglich?
 Jon04. Apr. 2012, 00:27
@ Matiasf: Es ist. Schauen Sie sich das Update an.

Dies ist die von uns verwendete MySQL-Funktion.

<code>DELIMITER $$
DROP FUNCTION IF EXISTS `ip_in_subnet_mask`$$
CREATE DEFINER=`root`@`%` FUNCTION `ip_in_subnet_mask`(ip VARCHAR(20), subnet VARCHAR(20), netmask VARCHAR(20)) RETURNS TINYINT(1)
    DETERMINISTIC
BEGIN
    RETURN (INET_ATON(ip) & INET_ATON(netmask)) = INET_ATON(subnet);
END$$
DELIMITER ;
</code>

z.B. Suchen Sie alle Zeilen, in denen t.ip im Bereich 192.168.0.x liegt, wobei 0 <= x <= 255

<code> SELECT *
 FROM t
 WHERE
    ip_in_subnet_mask(t.ip, "192.168.0.0", "255.255.255.0")
</code>

wenn die Adressen als Zeichenfolgen gespeichert werden. Wenn Sie diesen Teil Ihres Algorithmus verwenden möchten, können Sie hier meine Lösung verwenden.

Insbesondere für IPv6 ist dies ein wenig knifflig. In IPv6 können optional komprimierte Segmente vorhanden sein, z. B. 1 :: 1, was 1: 0: 0: 0: 0: 0: 0: 1 entspricht. Dies ist der Hauptgrund für die Schwierigkeit.

Die IPAddress-Java-Bibliothek Erzeugt mysql SQL, um nach Adressen in einem bestimmten Subnetz zu suchen. Der Link beschreibt dieses Problem ausführlicher. Haftungsausschluss: Ich bin der Projektmanager.

Der grundlegende Algorithmus besteht darin, den Netzwerkabschnitt der Subnetzadresse zu nehmen, dann jede Variante dieses Abschnitts zu nehmen (z. B. sind die beiden obigen Zeichenfolgen Varianten der vollständigen Adresse 1 :: 1), dann die Anzahl der Segmenttrennzeichen zu zählen und dann zu tun Ein mysql-Teilstring für die übereinstimmende Adresse, aber auch die Gesamtzahl der Trennzeichen in der übereinstimmenden Adresse.

Hier ist Beispielcode:

<code>public static void main(String[] args) throws IPAddressStringException {
    System.out.println(getSearchSQL("columnX", "1.2.3.4/16"));
    System.out.println(getSearchSQL("columnX", "1:2:3:4:5:6:7:8/64"));
    System.out.println(getSearchSQL("columnX", "1::8/64"));
}

static String getSearchSQL(String expression, String ipAddressStr) throws IPAddressStringException {
    IPAddressString ipAddressString = new IPAddressString(ipAddressStr);
    IPAddress ipAddress = ipAddressString.toAddress();
    IPAddressSection networkPrefix = ipAddress.getNetworkSection(ipAddress.getNetworkPrefixLength(), false);
    StringBuilder sql = new StringBuilder("Select rows from table where ");
    networkPrefix.getStartsWithSQLClause(sql, expression);
    return sql.toString();
}
</code>

Ausgang:

<code>Select rows from table where (substring_index(columnX,'.',2) = '1.2')
Select rows from table where (substring_index(columnX,':',4) = '1:2:3:4')
Select rows from table where ((substring_index(columnX,':',4) = '1:0:0:0') OR ((substring_index(columnX,':',2) = '1:') AND (LENGTH (columnX) - LENGTH(REPLACE(columnX, ':', '')) <= 5)))
</code>

Ihre Antwort auf die Frage