Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Convert IP addresses to 32 bit integers and make a column for the address and the mask. You can use <code>INET_NTOA()</code> and <code>INET_ATON()</code> functions. Your 10.0.%.1 range can be expressed as network 10.0.0.1 with a mask of 255.255.0.255. To see if a given IP address matches this network, you apply the mask to the IP address, and compare it to the network address. If they match, the address is in the network. For example, if you want to test 10.0.4.1, you apply the mask to it using bitwise AND:</p> <pre><code>10.0.4.1 &amp; 255.255.0.255 = 10.0.0.1 </code></pre> <p>This matches your network address, so this IP is in the network.</p> <p>You can store this network in your table like this (ip and mask are integers):</p> <pre><code>CREATE TABLE networks (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, ip INT, mask INT); INSERT INTO networks (ip, mask) VALUES (INET_ATON('10.0.0.1'), INET_ATON('255.255.0.255')); </code></pre> <p>You can find if a given IP (10.0.23.1) matches like this:</p> <pre><code>SELECT * FROM networks WHERE ip &amp; mask = INET_ATON('10.0.23.1') &amp; mask </code></pre> <p>This will select the IPs as integers though, if you want them human readable:</p> <pre><code>SELECT id, INET_NTOA(ip) AS ipstring, INET_NTOA(mask) AS maskstring FROM networks WHERE ip &amp; mask = INET_ATON('10.0.23.1') &amp; mask </code></pre> <p>This allows you to test if a single given IP address matches a network. If you want to test a range of IPs, you'd have to apply the mask to each one in the range and test each one as a above. You can do it in a single query, it'll just have to list every IP address you're testing.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow do I store (and find) a string with a wildcard in MySQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGary G
    1. USGary G
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COInteresting take, is it possible to query for a range of IP's like this? say range from `10.0.12.1` to `10.0.123.2` if you use `BETWEEN int1 AND int2`?
      singulars
      1. PO
      1. USElias Van Ootegem
    2. CONo, you can't do that. You'd have to apply the mask to each IP address in the range and then compare that to the network address. This is because the network mask can have holes in it, and even if both ends of your range are in the network, some addresses in the middle of the range might not be. For example, if my mask was 255.255.2.255 for a 10.0.0.1 network, IPs 10.0.0.1, 10.0.4.1 and 10.0.5.1 would be in the network, but 10.0.2.1 and 10.0.3.1 would not.
      singulars
      1. PO
      1. USGary G
    3. COWouldn't `WHERE ip & mask BETWEEN INET_ATOM('10.0.20.1') & mask AND INET_ATOM('10.0.50.1') & mask` work then? I see no reason why it wouldn't, Worst case you'd need a join of sorts, but unless I'm missing something, it would work, right?
      singulars
      1. PO
      1. USElias Van Ootegem
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload