Skrevet av Emne: CIDR and subnet masks in MySQL  (Lest 11439 ganger)

Utlogget Floyd-ATC

  • Livstidsdiktator
  • Administrator
  • Guru
  • *****
  • Innlegg: 542
  • Karma: +12/-0
    • MSN Messenger - floyd@atc.no
    • Vis profil
    • floyd.atc.no
    • E-post
CIDR and subnet masks in MySQL
« på: 09. April 2013, 23:00 pm »
  • [applaud]0
  • [smite]0
  • Have you ever needed to do IP address calculations in MySQL and gave up because you couldn't figure out how? Here's a few tricks to help you on the way.

    Start off by creating a table showing the most common subnet masks.:
    Kode: [Velg]
    CREATE TABLE netmasks (
      cidr int not null unique primary key,
      netmask varchar(15) not null,
      size BIGINT
    );

    INSERT INTO netmasks (cidr) VALUES
    ( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9),
    (10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
    (20), (21), (22), (23), (24), (25), (26), (27), (28), (29),
    (30), (31), (32);

    UPDATE netmasks SET netmask = inet_ntoa(pow(2,32)-pow(2, 32-cidr)), size = POW(2, 32-cidr);

    Now define two stored functions to let you convert between CIDR and ordinary (quad decimal octet) subnet masks:
    Kode: [Velg]
    DELIMITER //

    CREATE FUNCTION cidr_of_mask(mask VARCHAR(15))
    RETURNS INT
    BEGIN
      DECLARE i INT;

      SELECT cidr INTO i
      FROM netmasks
      WHERE netmask = mask;

      RETURN i;
    END //

    CREATE FUNCTION mask_of_cidr(i INT)
    RETURNS VARCHAR(15)
    BEGIN
      DECLARE mask VARCHAR(15);

      SELECT netmask INTO mask
      FROM netmasks
      WHERE cidr = i;

      RETURN mask;
    END //

    DELIMITER ;

    Finally, you'll probably want a function to check if a given IP address is within a certain subnet:
    Kode: [Velg]
    DELIMITER //

    CREATE FUNCTION ip_in_subnet(ip VARCHAR(15), net VARCHAR(15), mask VARCHAR(15))
    RETURNS BOOLEAN
    DETERMINISTIC
    BEGIN
      RETURN INET_ATON(ip) & INET_ATON(mask) = INET_ATON(net) & INET_ATON(mask);
    END //

    DELIMITER ;

    Good! You can now convert subnet masks as easy as saying
    Kode: [Velg]
    mysql> select cidr_of_mask("255.255.255.240");             
    +---------------------------------+
    | cidr_of_mask("255.255.255.240") |
    +---------------------------------+
    |                              28 |
    +---------------------------------+
    1 row in set (0.00 sec)

    mysql> select mask_of_cidr(21); 
    +------------------+
    | mask_of_cidr(21) |
    +------------------+
    | 255.255.248.0    |
    +------------------+
    1 row in set (0.00 sec)
    Note that non-contiguous subnet masks and invalid CIDR values will produce NULL as expected.

    What about checking IP addresses then?
    Kode: [Velg]
    mysql> select ip_in_subnet("10.0.0.1", "10.0.0.0", "255.255.255.0");
    +-------------------------------------------------------+
    | ip_in_subnet("10.0.0.1", "10.0.0.0", "255.255.255.0") |
    +-------------------------------------------------------+
    |                                                     1 |
    +-------------------------------------------------------+
    1 row in set (0.15 sec)

    mysql> select ip_in_subnet("192.168.1.1", "10.0.0.0", "255.255.255.0");       
    +----------------------------------------------------------+
    | ip_in_subnet("192.168.1.1", "10.0.0.0", "255.255.255.0") |
    +----------------------------------------------------------+
    |                                                        0 |
    +----------------------------------------------------------+
    1 row in set (0.01 sec)

    And because we calculate subnet masks the proper way, it works properly with non-contiguous subnet masks as well:
    Kode: [Velg]
    mysql> select ip_in_subnet("10.150.37.5", "10.0.32.0", "255.0.248.0");   
    +---------------------------------------------------------+
    | ip_in_subnet("10.150.37.5", "10.0.32.0", "255.0.248.0") |
    +---------------------------------------------------------+
    |                                                       1 |
    +---------------------------------------------------------+
    1 row in set (0.01 sec)

    Have fun :-)


    -Floyd.

    --
    Det finnes 10 typer mennesker;
    de som forstår binærtall, de som ikke gjør det, og de som forstår Grey code.

    Utlogget DJdur

    • Minecraft-Operators
    • Wannabe
    • ***
    • Innlegg: 94
    • Karma: +0/-0
    • #swag
      • MSN Messenger - oozziizz@hotmail.com
      • Vis profil
      • My twitter!
      • E-post
    Sv: CIDR and subnet masks in MySQL
    « Svar #1 på: 11. April 2013, 19:20 pm »
  • [applaud]0
  • [smite]0
  • Bare lurer; er det egentlig noen engelske som leser forumet? :p



    Utlogget Floyd-ATC

    • Livstidsdiktator
    • Administrator
    • Guru
    • *****
    • Innlegg: 542
    • Karma: +12/-0
      • MSN Messenger - floyd@atc.no
      • Vis profil
      • floyd.atc.no
      • E-post
    Sv: CIDR and subnet masks in MySQL
    « Svar #2 på: 11. April 2013, 20:24 pm »
  • [applaud]0
  • [smite]0
  • Jeg poster disse tingene når jeg har jobbet lenge med dem uten å finne noe på google. Så kommer de og indekserer sånn at nestemann som lurer på det samme kan finne løsningen :-)


    -Floyd.

    --
    Det finnes 10 typer mennesker;
    de som forstår binærtall, de som ikke gjør det, og de som forstår Grey code.

    Morder269

    • Gjest
    Sv: CIDR and subnet masks in MySQL
    « Svar #3 på: 19. April 2013, 19:32 pm »
  • [applaud]0
  • [smite]0
  • + det hjelper med pagerank om noen større sider gir deg noen linker :)



    Utlogget Cybersyn

    • n00b
    • *
    • Innlegg: 2
    • Karma: +1/-0
      • Vis profil
      • localhost
    Sv: CIDR and subnet masks in MySQL
    « Svar #4 på: 23. Februar 2014, 21:40 pm »
  • [applaud]0
  • [smite]0
  • Tråden blir vel ikke indekset hvis forumet den ligger i ikke er synlig for gjester? ???



    Utlogget Emilpoika

    • Minecraft-Operators
    • Guru
    • ***
    • Innlegg: 1.278
    • Karma: +12/-0
    • #Emilpoika
      • Vis profil
      • Emilpoikas Facebookside
    Sv: CIDR and subnet masks in MySQL
    « Svar #5 på: 23. Februar 2014, 21:43 pm »
  • [applaud]0
  • [smite]0
  • Tråden blir vel ikke indekset hvis forumet den ligger i ikke er synlig for gjester? ???

    Et veldig godt poeng! :P


    "So much time on ATC alone, and yet I've never been to me."
    "Half a bee, philosophically, must ipso facto half not be. But half the bee has got to be, vis-à-vis its entity - d'you see? But can a bee be said to be or not to be an entire bee when half the bee is not a bee, due to some ancient injury?