I discourage storing CIDR notation - that'd be human readable, but comparing IPs would always end up in string operations for the DBMS.
Why not implementing a logic where we operate with 4 blocks of 32bit? Advantages are that it can be done with any DBMS plus the most simple indices can be used, resulting in great performance.
My idea requires the following:
- Every IPv4 will be converted to IPv6.
- IPv6 addresses must be fully qualified (eliminating shortcuts).
- To finally operate with an IPv6 address, their letters must be in lowercase and the colons need to be stripped, e.g.:
Code: Select all
$sIp6= strtolower( str_replace( ':', '', $sIp6 ) );
The database table for storing IP ranges would cut addresses into 4 blocks and store them separately as 32bit Integers:
Code: Select all
CREATE TABLE phpbb_ban_ip_table
( from1 INT( 10 ) UNSIGNED NOT NULL
, from2 INT( 10 ) UNSIGNED NOT NULL
, from3 INT( 10 ) UNSIGNED NOT NULL
, from4 INT( 10 ) UNSIGNED NOT NULL
, to1 INT( 10 ) UNSIGNED NOT NULL
, to2 INT( 10 ) UNSIGNED NOT NULL
, to3 INT( 10 ) UNSIGNED NOT NULL
, to4 INT( 10 ) UNSIGNED NOT NULL
, PRIMARY KEY( from1, from2, from3, from4, to1, to2, to3, to4 )
);
In PHP we always end up formulating an SQL query, so we need a function to cut the long IPv6 address into 4 blocks:
Code: Select all
function GetIp6Blocks( $sIp6 ) {
$aResult= array();
for( $iBitBlock= 24; $iBitBlock>= 0; $iBitBlock-= 8 ) // Cut IPv6 into 4 blocks of 32bit
$aResult[]= (string)base_convert( substr( $sIp6, $iBitBlock, 8 ), 16, 10 );
return $aResult;
}
Inserting a new IP range would be as easy as this:
Code: Select all
$sql= 'INSERT
INTO '. BAN_IP_TABLE. '( from1, from2, from3, from4, to1, to2, to3, to4 )
VALUES( '. implode( ', ', GetIp6Blocks( $sIp6From ) ). ', '. implode( ', ', GetIp6Blocks( $sIp6To ) ). ' )';
$db-> sql_query( $sql );
Checking an IP if it is within a range would also be very easy:
Code: Select all
$aTarget= GetIp6Blocks( $sIp6ToCheck );
$sql= 'SELECT *
FROM '. BAN_IP_TABLE. '
WHERE '. $aTarget[0]. ' BETWEEN from1 AND to1
AND '. $aTarget[1]. ' BETWEEN from2 AND to2
AND '. $aTarget[2]. ' BETWEEN from3 AND to3
AND '. $aTarget[3]. ' BETWEEN from4 AND to4';
$hResult= $db-> sql_query( $sql );
$bFound= $db-> sql_fetchrow( $hResult );
$db-> sql_freeresult( $hResult );
if( $bFound ) { ... }
That's it already. CIDR support can be achieved indirectly:
- As for user input, we simple convert a CIDR notation into an IP range (starting IP and ending IP).
- As for user display (output) we could check each IP range we have and extract the common prefix of both IPs, then check if the remaining parts can be written as a subnet - and only then, we're able to display it in CIDR notation.
Advantage of this is that the user who manages the ban list can decide himself if he wants a mixed display with IP ranges and possible CIDR notations, or IP ranges only.
Any Volunteers? 
