[RFC|Accepted] IP banning to use 'longest prefix matching'

[Oleg]

8 integers being 8 database columns? Try writing some queries with such a schema.

[bantu]

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 and ip_ban_range_end are varbinaries
• ban_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

I don't know whether this actually works.

[AmigoJack]

UNHEX
...
varbinaries

-1 if not all DBMSes support UNHEX() or the VARBINARY type.

[Oleg]

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.