datatype - uid mysql

¿Hay forma de hacer coincidir IP con IP+CIDR directamente desde la consulta SELECT?

Question

Algo como

SELECT COUNT(*) AS c FROM BANS WHERE typeid=6 AND (SELECT ipaddr,cidr FROM BANS) MATCH AGAINST ''this_ip'';

Por lo tanto, primero no recupera todos los registros de la base de datos y luego los compara uno por uno.

Si c> 0 fueron emparejados.

Tabla BANS:

id int auto incr PK
typeid TINYINT (1=hostname, 4=ipv4, 6=ipv6)
ipaddr BINARY(128)
cidr INT
host VARCHAR(255)

DB: MySQL 5

Se conoce el tipo de IP y IPv (4 o 6) al realizar consultas.

IP es por ejemplo :: 1 en formato binario

IP BANNED es por ejemplo :: 1/64

Answer 1

Hmmm Podría construir una tabla de las máscaras cidr, unirla y luego comparar la ip ( & en MySQL) con la máscara con la ipaddress del bloque ban. ¿Haría eso lo que quieras?

Si no desea crear una tabla de máscara, puede calcular la máscara como -1 << (x-cidr) con x = 64 o 32 dependiendo.

Answer 2

Las direcciones IPv4, las direcciones de red y las máscaras de red son todos los números UINT32 y se presentan en forma legible por humanos como "quads de puntos". El código de la tabla de enrutamiento en el kernel realiza una comparación Y muy rápida a nivel de bits al verificar si una dirección está en un espacio de red determinado (red / máscara de red). El truco aquí es almacenar las direcciones IP de puntos cuádruples, las direcciones de red y las máscaras de red en sus tablas como UINT32, y luego realizar el mismo bit de 32 bits Y para su coincidencia. p.ej

SET @test_addr = inet_aton(''1.2.3.4'');
SET @network_one = inet_aton(''1.2.3.0'');
SET @network_two = inet_aton(''4.5.6.0'');
SET @network_netmask = inet_aton(''255.255.255.0'');

SELECT (@test_addr & @network_netmask) = @network_one AS IS_MATCHED;
+------------+
| IS_MATCHED |
+------------+
|          1 |
+------------+

SELECT (@test_addr & @network_netmask) = @network_two AS IS_NOT_MATCHED;
+----------------+
| IS_NOT_MATCHED |
+----------------+
|              0 |
+----------------+

Answer 3

MySQL: Convertir el rango de IP a CIDR

Pasé varias horas buscando una forma de usar MySQL para tomar un rango de direcciones IP y generar una dirección con formato CIDR que cubra el rango de IP adecuado para mantener una lista negra de IP.

Los detalles de mi entorno y mis requisitos Utilizo OpenWeb Analytics para registrar el tráfico en mi sitio web y varios de los que administro. Desarrollé un proceso para extraer distintas direcciones IP registradas por OWA y luego fusionar elementos de datos geográficos en los registros que luego se mantienen en una tabla personalizada. Con esta tabla de datos de IP a ubicación, se puede informar sobre aciertos de fuentes no deseadas, o fuentes irrelevantes para mi negocio local: China, Japón, Corea, Rusia, etc. Esto ha llevado a una lista creciente de PI direcciones muchos de los cuales caen dentro de la misma red. Para facilitar los requisitos de mantenimiento del archivo .htaccess de mi servidor, resulta beneficioso poder registrar las direcciones IP en lista negra en formato CIDR. Eso lleva a la necesidad de poder y producir el CIDR a partir de las direcciones IP ya registradas.

El enfoque de MySQL La mayoría de los hosts web proporcionan acceso a las bases de datos MySQL. Sin embargo, pocos, si es que hay alguno, permiten crear funciones de base de datos. Esto complicó un poco la codificación.

Ejemplo de SQL disponible en http://blog.watsoninfotech.com/2012/12/mysql-convert-ip-range-to-cidr.html

Answer 4

Para IPv4 , puedes usar:

SET @length = 4;

SELECT  INET_NTOA(ipaddr), INET_NTOA(searchaddr), INET_NTOA(mask)
FROM  (
  SELECT
        (1 << (@length * 8)) - 1 & ~((1 << (@length * 8 - cidr)) - 1) AS mask,
        CAST(CONV(SUBSTR(HEX(ipaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS ipaddr,
        CAST(CONV(SUBSTR(HEX(@myaddr), 1, @length * 2), 16, 10) AS DECIMAL(20)) AS searchaddr
  FROM  ip
) ipo
WHERE ipaddr & mask = searchaddr & mask

Answer 5

Recuerde que las direcciones IP no son una dirección de texto, sino una identificación numérica. Tengo una situación similar (estamos haciendo búsquedas geo-ip), y si almacena todas sus direcciones IP como enteros (por ejemplo, mi dirección IP es 192.115.22.33 por lo que se almacena como 3228767777), entonces puede buscar direcciones IP Fácilmente utilizando operadores de cambio a la derecha.

La desventaja de todos estos tipos de búsquedas es que no puede beneficiarse de los índices y tiene que hacer un análisis completo de la tabla cada vez que realiza una búsqueda. El esquema anterior puede mejorarse almacenando tanto la dirección IP de la red de la red CIDR (el comienzo del rango) como la dirección de transmisión (el final del rango), por lo que, por ejemplo, para almacenar 192.168.1.0/24 puede almacenar dos columnas:

network     broadcast
3232235776, 3232236031 

Y luego puedes hacerlo coincidir simplemente haces

SELECT count(*) FROM bans WHERE 3232235876 >= network AND 3232235876 <= broadcast

Esto le permitiría almacenar las redes CIDR en la base de datos y compararlas con las direcciones IP de manera rápida y eficiente aprovechando los índices numéricos rápidos.

Nota de la discusión a continuación :

MySQL 5.0 incluye una optimización de consultas a distancia llamada " intersección de fusión de índice " que permite acelerar tales consultas (y evitar exploraciones de tablas completas), siempre y cuando:

• Existe un índice de varias columnas que coincide exactamente con las columnas de la consulta, en orden. Entonces, para el ejemplo de consulta anterior, el índice debería ser (network, broadcast) .
• Todos los datos se pueden recuperar del índice. Esto es cierto para COUNT(*) , pero no lo es para SELECT * ... LIMIT 1 .

MySQL 5.6 incluye una optimización llamada MRR que también aceleraría la recuperación de la fila completa, pero está fuera del alcance de esta respuesta.