Monday, July 4, 2011

Working with IP's in MySQL and MariaDB

For MySQL it's a best practice to store IP addresses in a INT column rather than in a VARCHAR. This allows for more compact storage. This best practice is only for MySQL as PostgreSQL does have data types like inet and cidr.

MySQL is equiped with the INET_ATON() and INET_NTOA() functions since version 3.23.15. In MySQL 5.6.3 the INET6_ATON() and INET6_NTOA() functions were addes to finaly add IPv6 address support. To make it easier to work with IPv4 and IPv6 addresses the IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV4() and IS_IPV6() functions were added. The difference between the new and old functions is that the old functions use INT UNSIGNED to store IP addresses and the new function uses VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses.

Here are some examples about how you could do nifty things with IP addresses in MySQL.

Store hosts and networks in MySQL
CREATE TABLE `hosts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hostname` varchar(255) DEFAULT NULL,
`domainname` varchar(255) DEFAULT NULL,
`ip` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
)

CREATE TABLE `networks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`network` int(10) unsigned NOT NULL,
`mask` int(10) unsigned NOT NULL,
`name` varchar(255) DEFAULT '',
PRIMARY KEY (`id`),
KEY `network` (`network`),
KEY `mask` (`mask`)
)

Insert and retrieve entries
mysql> INSERT INTO `hosts`(`hostname`,`domainname`,`ip`) VALUES('test6','example.com',INET_ATON('192.168.1.2'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----+----------+-------------+------------+
| id | hostname | domainname  | ip         |
+----+----------+-------------+------------+
|  6 | test6    | example.com | 3232235778 |
+----+----------+-------------+------------+
1 row in set (0.00 sec)

mysql> SELECT hostname,domainname,INET_NTOA(ip) ip FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----------+-------------+-------------+
| hostname | domainname  | ip          |
+----------+-------------+-------------+
| test6    | example.com | 192.168.1.2 |
+----------+-------------+-------------+
1 row in set (0.00 sec)

Convert a subnetmask to CIDR notation.
mysql> SELECT INET_ATON('255.255.255.0'),BIT_COUNT(INET_ATON('255.255.255.0'));
+----------------------------+---------------------------------------+
| INET_ATON('255.255.255.0') | BIT_COUNT(INET_ATON('255.255.255.0')) |
+----------------------------+---------------------------------------+
|                 4294967040 |                                    24 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION mask_to_cidr (mask CHAR(15)) RETURNS INT(2) DETERMINISTIC RETURN BIT_COUNT(INET_ATON(mask));
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mask='255.255.252.0';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mask,mask_to_cidr(@mask);
+---------------+---------------------+
| @mask         | mask_to_cidr(@mask) |
+---------------+---------------------+
| 255.255.252.0 |                  22 |
+---------------+---------------------+
1 row in set (0.00 sec)

And convert a CIDR mask back to dot-decimal notation

mysql> CREATE FUNCTION cidr_to_mask (cidr INT(2)) RETURNS CHAR(15) DETERMINISTIC RETURN INET_NTOA(CONV(CONCAT(REPEAT(1,cidr),REPEAT(0,32-cidr)),2,10));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT cidr_to_mask(22);
+------------------+
| cidr_to_mask(22) |
+------------------+
| 255.255.252.0    |
+------------------+
1 row in set (0.00 sec)

Use the & operator to find the network address using an IP and mask.
mysql> SELECT INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3'));
+------------------------------------------------------------------+
| INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3')) |
+------------------------------------------------------------------+
| 192.168.2.0                                                      |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @netmask='255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @ipaddr = '192.168.2.6';Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
1 row in set (0.00 sec)

This can be used to join a table with host ip's with a table of networks. (Remember the netmasks table in Solaris and/or NIS?)
mysql> SELECT CONCAT(h.hostname,'.',h.domainname) fqdn, INET_NTOA(h.ip) ip, INET_NTOA(n.mask & h.ip) network, INET_NTOA(n.mask) mask FROM hosts h INNER JOIN networks n ON (n.mask & h.ip) = n.network;
+-------------------+-------------+-------------+---------------+
| fqdn              | ip          | network     | mask          |
+-------------------+-------------+-------------+---------------+
| test1.example.com | 192.168.0.1 | 192.168.0.0 | 255.255.255.0 |
| test2.example.com | 192.168.0.2 | 192.168.0.0 | 255.255.255.0 |
| test3.example.com | 192.168.0.3 | 192.168.0.0 | 255.255.255.0 |
| test4.example.com | 10.0.0.1    | 10.0.0.0    | 255.0.0.0     |
| test5.example.com | 10.0.0.2    | 10.0.0.0    | 255.0.0.0     |
+-------------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

With the ~ operator the mask can be inverted and then be used to find the broadcast address using the XOR operator |.
mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network, CONV(CONV(SUBSTRING(CONV(~INET_ATON('255.255.255.0'),10,2),-32),2,10) | INET_ATON(@ipaddr),10,2) broadcast\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
broadcast: 11000000101010000000001011111111
1 row in set (0.00 sec)

You could use a view to make it easier for users to select the rows they wanted without using functions. But that will result in a full scan if you search using an IP. MySQL does not optimize this by first converting the search value to an INT but converts all rows to a dot-decimal notation and then compares it. MariaDB has a nifty feature called persistent virtual columns which allow indexing. For the example below this means that instead of scanning all four rows it just uses the index to find the row we need.

mysql> CREATE TABLE `iptest`(`ip_n` int(11) UNSIGNED NOT NULL, `ip_a` CHAR(15) AS (INET_NTOA(`ip_n`)) PERSISTENT, PRIMARY KEY(`ip_n`), KEY `ip_a` (`ip_a`));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `iptest`(`ip_n`) VALUES (INET_ATON('192.168.1.1')),(INET_ATON('192.168.1.2')),(INET_ATON('192.168.1.3')),(INET_ATON('192.168.1.4'));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `iptest`;
+------------+-------------+
| ip_n       | ip_a        |
+------------+-------------+
| 3232235777 | 192.168.1.1 |
| 3232235778 | 192.168.1.2 |
| 3232235779 | 192.168.1.3 |
| 3232235780 | 192.168.1.4 |
+------------+-------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | iptest | ref  | ip_a          | ip_a | 16      | const |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW v_iptest AS SELECT ip_n,INET_NTOA(ip_n) ip_a FROM `iptest`;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM `v_iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | iptest | index | NULL          | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

25 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Really nice!

    I have one question... For example when I have a table with lots of networks (and each having differrent netmask etc.)... How would a query look like when I want to find all networks containing a given IP ordered by netmask?

    Example:
    Networks:
    10.0.0.0 / 8
    10.0.2.0 / 24
    10.10.0.0 / 16

    And now I want to find the networks matching my IPs.
    when searching for 10.10.5.100 I want to get 10.10.0.0 / 16
    when searching for 10.0.2.100 I want to get 10.0.2.0 / 24
    when searching for 10.0.5.100 I want to get 10.0.0.0 / 8

    How would that work?

    Kind regards
    Martin

    ReplyDelete
  3. The every day payback rate won't be higher than 10% of day by day net deals, the day by day rate depends on the month to month charge cards deals volume and the measure of loan required. The payback time allotment is organized for a 6-9 months term, however it's not settled, and there won't be any punishments on the off chance that it takes longer. Cash Advances corona

    ReplyDelete
  4. Ceaselessly steady and an exceptional sense of duty regarding the universe of bloggers.
    192 .168.0.1

    ReplyDelete
  5. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. https://192-168-i-i.com

    ReplyDelete
  6. A lot of people having an incorrect image about the cash advance loans or sometimes refer it as bad credit payday loans. Plus d'information

    ReplyDelete
  7. I wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight. bezoek website

    ReplyDelete
  8. Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing.  deze website

    ReplyDelete
  9. The data you have posted is extremely valuable. The locales you have alluded was great. A debt of gratitude is in order for sharing... privacyinthenetwork

    ReplyDelete
  10. Grandstream PBX System- Grandstream Distributor Cameroon VDS the Grandstream distributor in  Cameroon continues to bring innovative Grandstream Products to the IP communications / Telephony market with compelling values and features. Grandstream Networks is headquartered in Brookline, Massachusetts with offices in Yealink Office Phones

    ReplyDelete
  11. I think this is an informative post and it is very beneficial and knowledgeable. Therefore, I would like to thank you for the endeavors that you have made in writing this article. All the content is absolutely well-researched. Thanks... https://192-168-i-i.com

    ReplyDelete
  12. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also t shirt met tekst

    ReplyDelete
  13. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work. shisha

    ReplyDelete
  14. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. estilo de vida

    ReplyDelete
  15. Other computerized gadgets that have the ability to get to web do likewise utilize Internet Protocol delivers to speak with different gadgets just as PCs. It permits the area of billions of advanced administrations that are associated with the web to be pinpointed and separated from different gadgets for example in the event that somebody needs your postage information to send you a letter, a far off PC needs your Internet Protocol address to speak with your PC.linksys router login

    ReplyDelete
  16. For instance, when the business accuses the laborer after a light obligation work isn't working out. This circumstance can bring about end of time misfortune remuneration benefits. Email Extractor Software

    ReplyDelete
  17. If you are unable to Setup Linksys Wireless Router? Don't worry: our experts helps you find the solution with quick & simple ways. Get in touch with us. Dial our toll-free helpline numbers at USA/CA: +1-888-480-0288 and UK/London: +44-800-041-8324.

    ReplyDelete
  18. If you are looking for a solution on how to add Disney Plus on Roku, Don’t worry: this article helps you find the best solution for adding channels on Roku.

    ReplyDelete
  19. Are you looking for a solution on how to resolve Linksys WiFi Extender Not Working? Don’t worry visit our website or get in touch with our experienced experts.

    ReplyDelete
  20. You can access many blocked websites from your office or school using proxy websites. site web

    ReplyDelete
  21. Pretty nice post. I simply stumbled upon your weblog and wanted to mention that I’ve truly loved browsing your blog posts. After all I will be subscribing on your feed and I’m hoping you write once more soon! White House Market Link

    ReplyDelete
  22. Thanks for the blog loaded with such a large amount of information. Stopping by your blog helped me to induce what i used to be yearning for. white house market

    ReplyDelete
  23. this product is good and thanks for a post for this kind off post .After you set up your NETGEAR WiFi range extender, you can log in to the extender to view and change the settings. If you have not installed and connected your WiFi range extender to your existing WiFi network, visit NETGEAR Support and download your model’s quick start guide before continuing. Verify that your model is tagged under the “This article applies to:” section.mywifiext setup

    ReplyDelete
  24. The Wi-Fi booster security is quite guaranteed. It is usually not different from the same security level that typical Wi-Fi routers provide, such as WPA2, WPA and WEP among others.mywifiext setup

    ReplyDelete
  25. They will be answerable for help of the database they planned on the off chance that one isn't. Other than this distinction the ranges of abilities are basically the same, https://onohosting.com/

    ReplyDelete

Note: Only a member of this blog may post a comment.