[RFC|Accepted] IP banning to use 'longest prefix matching'
Re: [RFC|Accepted] IP banning to use 'longest prefix matchin
8 integers being 8 database columns? Try writing some queries with such a schema.
- bantu
- 3.0 Release Manager
- Posts: 557
- Joined: Thu Sep 07, 2006 11:22 am
- Location: Karlsruhe, Germany
- Contact:
Re: [RFC|Accepted] IP banning to use 'longest prefix matchin
I found this in my notes:

Code: Select all
SELECT ban_status
FROM phpbb_ip_banning_test
WHERE UNHEX('00000000000000000000000000000001') BETWEEN ip_ban_range_start AND ip_ban_range_end
ORDER BY (ip_ban_range_end - ip_ban_range_start)
LIMIT 1
- 00000000000000000000000000000001 is the IP address of the client
ip_ban_range_start
andip_ban_range_end
are varbinariesban_status
is a boolean that says whether the entry is a ban or a whitelist- This example is for IPv6 only. IPv4 is handled by a similar table with 32 bit integers instead of varbinaries.
ip_ban_range_end - ip_ban_range_start
can be stored in a column if calculation is too expensive (probably makes sense for IPv6, but is not required for IPv4)- BETWEEN is inclusive in this case
- AmigoJack
- Registered User
- Posts: 110
- Joined: Wed May 04, 2011 7:47 pm
- Location: グリーン ヒル ゾーン
- Contact:
Re: [RFC|Accepted] IP banning to use 'longest prefix matchin
-1 if not all DBMSes supportbantu wrote:UNHEX
...
varbinaries
UNHEX()
or the VARBINARY type.Re: [RFC|Accepted] IP banning to use 'longest prefix matchin
From my experiments my impression was that unhex('00000000000000000000000000000001') produced a string of length 1, which means comparisons would not work correctly (z would compare greater than 1a for example).
Edit: although perhaps this could be dealt with by lpadding appropriately.
Edit: although perhaps this could be dealt with by lpadding appropriately.