2

I found this method to store IP addresses in MySQL database as integer using INET_ATON: https://stackoverflow.com/a/5133610/4491952

Since IPv4 addresses are 4 byte long, you could use an INT (UNSIGNED) that has exactly 4 bytes:

`ipv4` INT UNSIGNED

And INET_ATON and INET_NTOA to convert them:

INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1"));
SELECT INET_NTOA(`ipv4`) FROM `table`;

For IPv6 addresses you could use a BINARY instead:

`ipv6` BINARY(16)

And use PHP’s inet_pton and inet_ntop for conversion:

'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")'
'SELECT `ipv6` FROM `table`'
$ipv6 = inet_pton($row['ipv6']);

But how can I do a wildcard search, for example 192.168.%, using INET_ATON and PHP's ip2long function?

Community
  • 1
  • 1
Trondro Mulligan
  • 485
  • 3
  • 19
  • 5
    You don't, wildcards only make sense with text. You'd search for a range with good old `BETWEEN`. – Álvaro González Mar 20 '17 at 16:23
  • @ÁlvaroGonzález Is it a good method if I store IP addesses like 192.168.1.1 as 192168001001 (int 12) instead of including the dots and using varchar(15)? Then I can do wildcard search as well. – Trondro Mulligan Mar 20 '17 at 16:28
  • 1
    That way you get the efficiency the string comparisons and the readability of binary streams. Give me a minute, I think I'll compose an answer. – Álvaro González Mar 20 '17 at 16:30
  • 1
    It's worth noting that Postgres has [an IP column type](https://www.postgresql.org/docs/current/static/datatype-net-types.html) that handles this a lot better. In MySQL it depends on how you're fetching these values, and if you need to store IPv4 and IPv6 in the same column or not. The raw "numerical" form is good for masking, the text form is better for substring-type comparisons. Using `BINARY` for IPv6 is really annoying in practice, you end up with junk characters you can't read when doing manual queries. Plain-text often wins out in terms of practicality here. – tadman Mar 20 '17 at 16:33
  • 1
    **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use manual escaping and string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). Accidentally unescaped data is a serious risk. – tadman Mar 20 '17 at 16:33

2 Answers2

5

One neat trick MySQL offers is bit shifting. You can use it to see if an ip is contained within an address block written in cidr notation. You can use this method treating your addresses as X.X.X.X/16 cidr block.

set @cidr_block:='10.20.30.40/16';

select inet_ntoa(inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1))<<(32-substring_index(@cidr_block,'/',-1))) as first_ip,
                 inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1))<<(32-substring_index(@cidr_block,'/',-1))  as first_ip_num,
        inet_ntoa((((inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1)))+1)<<(32-substring_index(@cidr_block,'/',-1)))-1) as last_ip,
                 (((inet_aton(substring_index(@cidr_block,'/',1))>>(32-substring_index(@cidr_block,'/',-1)))+1)<<(32-substring_index(@cidr_block,'/',-1)))-1  as last_ip_num
;
+-----------+--------------+---------------+-------------+
| first_ip  | first_ip_num | last_ip       | last_ip_num |
+-----------+--------------+---------------+-------------+
| 10.20.0.0 |    169082880 | 10.20.255.255 |   169148415 |
+-----------+--------------+---------------+-------------+
1 row in set (0.00 sec)

Shortcut to seeing if an ip is in the address block - simply sift both cidr address and ip to see if they are the same. Of course, this will be a table scan if applied to stored values.

select inet_aton('127.0.0.1')>>16 = inet_aton('127.0.10.20')>>16 as `1 = true`;
+----------+
| 1 = true |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

select inet_aton('127.0.0.1')>>16 = inet_aton('127.10.10.20')>>16 as `0 =  false`;
 +-----------+
 | 0 = false |
 +-----------+
 |         0 |
 +-----------+
 1 row in set (0.00 sec)
RMathis
  • 588
  • 2
  • 7
4

Wildcard search operates on strings and, since it can't normally benefit from indexes, it tends to be extremely slow.

If you store IP addresses in a normalised representation aimed at machines (vs the human-readable dot-notation) you can treat them as if they were numbers, use many standard operators and make good use of indexes. An example:

SELECT *
FROM foo
WHERE dot_notation LIKE '192.168.%';

... can be rewritten as:

SELECT *
FROM foo
WHERE as_integer BETWEEN INET_ATON('192.168.0.0') AND INET_ATON('192.168.255.255');

Even these INET_ATON() instances are for mere readability, you could just enter the resulting integer. If you use PHP it's trivial because you can outsource it to PHP:

$sql = 'SELECT *
    FROM foo
    WHERE as_integer BETWEEN ? AND ?';
$params = [
   // Not sure whether you still need the sprintf('%u') trick in 64-bit PHP
   ip2long('192.168.0.0'), ip2long('192.168.255.255')
];

I cannot test it right now but I understand this should work with IPv6 as well.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360