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

Note: We are moving the topics of this forum and it will be deleted at some point

Publish your own request for comments/change or patches for the next version of phpBB. Discuss the contributions and proposals of others. Upcoming releases are 3.2/Rhea and 3.3.
Oleg
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: [RFC|Accepted] IP banning to use 'longest prefix matchin

Post by Oleg » Fri May 04, 2012 6:50 pm

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

User avatar
bantu
3.0 Release Manager
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

Post by bantu » Fri May 04, 2012 9:37 pm

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. ;)

User avatar
AmigoJack
Registered User
Posts: 92
Joined: Wed May 04, 2011 7:47 pm
Location: グリーン ヒル ゾーン
Contact:

Re: [RFC|Accepted] IP banning to use 'longest prefix matchin

Post by AmigoJack » Sat May 05, 2012 11:27 pm

bantu wrote:UNHEX
...
varbinaries
-1 if not all DBMSes support UNHEX() or the VARBINARY type.

Oleg
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: [RFC|Accepted] IP banning to use 'longest prefix matchin

Post by Oleg » Wed May 09, 2012 4:37 am

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.

Post Reply