Page MenuHomePhorge
Files F118362285

D3674.1775778917.diff
No OneTemporary
Actions

Authored By
Unknown
Size
20 KB
Referenced Files
None
Subscribers
None

D3674.1775778917.diff
View Options

diff --git a/src/app/Console/Commands/Data/Import/IP4NetsCommand.php b/src/app/Console/Commands/Data/Import/IP4NetsCommand.php
--- a/src/app/Console/Commands/Data/Import/IP4NetsCommand.php
+++ b/src/app/Console/Commands/Data/Import/IP4NetsCommand.php
@@ -19,7 +19,7 @@
*
* @var string
*/
- protected $description = 'Update IP4 Networks';
+ protected $description = 'Import IP4 Networks';
/**
* Execute the console command.
@@ -57,7 +57,7 @@
continue;
}
- $bar = $this->createProgressBar($numLines, "Importing IPv4 Networks from {$file}");
+ $bar = $this->createProgressBar($numLines, "Importing IPv4 Networks from {$rir}-{$today}");
$fp = fopen($file, 'r');
@@ -84,7 +84,7 @@
continue;
}
- if ($items[1] == "*") {
+ if ($items[1] == "*" || $items[1] == "" || $items[1] == "ZZ") {
continue;
}
@@ -96,19 +96,16 @@
$items[5] = "19700102";
}
- if ($items[1] == "" || $items[1] == "ZZ") {
- continue;
- }
-
$bar->advance();
$mask = 32 - log($items[4], 2);
+ $broadcast = long2ip((ip2long($items[3]) + 2 ** (32 - $mask)) - 1);
$net = \App\IP4Net::where(
[
- 'net_number' => $items[3],
+ 'net_number' => inet_pton($items[3]),
'net_mask' => $mask,
- 'net_broadcast' => long2ip((ip2long($items[3]) + 2 ** (32 - $mask)) - 1)
+ 'net_broadcast' => inet_pton($broadcast),
]
)->first();
@@ -130,9 +127,9 @@
$nets[] = [
'rir_name' => $rir,
- 'net_number' => $items[3],
+ 'net_number' => inet_pton($items[3]),
'net_mask' => $mask,
- 'net_broadcast' => long2ip((ip2long($items[3]) + 2 ** (32 - $mask)) - 1),
+ 'net_broadcast' => inet_pton($broadcast),
'country' => $items[1],
'serial' => $serial,
'created_at' => Carbon::parse($items[5], 'UTC'),
diff --git a/src/app/Console/Commands/Data/Import/IP6NetsCommand.php b/src/app/Console/Commands/Data/Import/IP6NetsCommand.php
--- a/src/app/Console/Commands/Data/Import/IP6NetsCommand.php
+++ b/src/app/Console/Commands/Data/Import/IP6NetsCommand.php
@@ -57,7 +57,7 @@
continue;
}
- $bar = $this->createProgressBar($numLines, "Importing IPv6 Networks from {$file}");
+ $bar = $this->createProgressBar($numLines, "Importing IPv6 Networks from {$rir}-{$today}");
$fp = fopen($file, 'r');
@@ -84,7 +84,7 @@
continue;
}
- if ($items[1] == "*") {
+ if ($items[1] == "*" || $items[1] == "" || $items[1] == "ZZ") {
continue;
}
@@ -96,19 +96,15 @@
$items[5] = "19700102";
}
- if ($items[1] == "" || $items[1] == "ZZ") {
- continue;
- }
-
$bar->advance();
$broadcast = \App\Utils::ip6Broadcast($items[3], (int)$items[4]);
$net = \App\IP6Net::where(
[
- 'net_number' => $items[3],
+ 'net_number' => inet_pton($items[3]),
'net_mask' => (int)$items[4],
- 'net_broadcast' => $broadcast
+ 'net_broadcast' => inet_pton($broadcast),
]
)->first();
@@ -130,9 +126,9 @@
$nets[] = [
'rir_name' => $rir,
- 'net_number' => $items[3],
+ 'net_number' => inet_pton($items[3]),
'net_mask' => (int)$items[4],
- 'net_broadcast' => $broadcast,
+ 'net_broadcast' => inet_pton($broadcast),
'country' => $items[1],
'serial' => $serial,
'created_at' => Carbon::parse($items[5], 'UTC'),
diff --git a/src/app/IP4Net.php b/src/app/IP4Net.php
--- a/src/app/IP4Net.php
+++ b/src/app/IP4Net.php
@@ -2,10 +2,23 @@
namespace App;
+use Illuminate\Database\Eloquent\Casts\Attribute;
use Illuminate\Database\Eloquent\Model;
+/**
+ * The eloquent definition of an IP network.
+ *
+ * @property string $country Country code
+ * @property int $id Network identifier
+ * @property string $net_broadcast Network broadcast address
+ * @property string $net_number Network address
+ * @property int $net_mask Network mask
+ * @property string $rir_name Network region label
+ * @property int $serial Serial number
+ */
class IP4Net extends Model
{
+ /** @var string Database table name */
protected $table = "ip4nets";
/** @var array<int, string> The attributes that are mass assignable */
@@ -23,16 +36,49 @@
/**
* Get IP network by IP address
*
- * @param string $ip IPv4 address
+ * @param string $ip IP address
*
- * @return ?\App\IP4Net IPv4 network record, Null if not found
+ * @return ?self IP network record, Null if not found
*/
public static function getNet($ip)
{
- $where = 'INET_ATON(net_number) <= INET_ATON(?) and INET_ATON(net_broadcast) >= INET_ATON(?)';
+ $ip = inet_pton($ip);
- return self::whereRaw($where, [$ip, $ip])
- ->orderByRaw('INET_ATON(net_number), net_mask DESC')
+ if (!$ip) {
+ return null;
+ }
+
+ return static::where('net_number', '<=', $ip)
+ ->where('net_broadcast', '>=', $ip)
+ ->orderByRaw('net_number, net_mask DESC')
->first();
}
+
+ /**
+ * net_number accessor. Internally we store IP addresses
+ * in a numeric form, outside they are human-readable.
+ *
+ * @return \Illuminate\Database\Eloquent\Casts\Attribute
+ */
+ protected function netNumber(): Attribute
+ {
+ return Attribute::make(
+ get: fn ($ip) => inet_ntop($ip),
+ set: fn ($ip) => inet_pton($ip),
+ );
+ }
+
+ /**
+ * net_broadcast accessor. Internally we store IP addresses
+ * in a numeric form, outside they are human-readable.
+ *
+ * @return \Illuminate\Database\Eloquent\Casts\Attribute
+ */
+ protected function netBroadcast(): Attribute
+ {
+ return Attribute::make(
+ get: fn ($ip) => inet_ntop($ip),
+ set: fn ($ip) => inet_pton($ip),
+ );
+ }
}
diff --git a/src/app/IP6Net.php b/src/app/IP6Net.php
--- a/src/app/IP6Net.php
+++ b/src/app/IP6Net.php
@@ -2,37 +2,8 @@
namespace App;
-use Illuminate\Database\Eloquent\Model;
-
-class IP6Net extends Model
+class IP6Net extends IP4Net
{
+ /** @var string Database table name */
protected $table = "ip6nets";
-
- /** @var array<int, string> The attributes that are mass assignable */
- protected $fillable = [
- 'rir_name',
- 'net_number',
- 'net_mask',
- 'net_broadcast',
- 'country',
- 'serial',
- 'created_at',
- 'updated_at'
- ];
-
- /**
- * Get IP network by IP address
- *
- * @param string $ip IPv6 address
- *
- * @return ?\App\IP6Net IPv6 network record, Null if not found
- */
- public static function getNet($ip)
- {
- $where = 'INET6_ATON(net_number) <= INET6_ATON(?) and INET6_ATON(net_broadcast) >= INET6_ATON(?)';
-
- return IP6Net::whereRaw($where, [$ip, $ip])
- ->orderByRaw('INET6_ATON(net_number), net_mask DESC')
- ->first();
- }
}
diff --git a/src/app/Providers/AppServiceProvider.php b/src/app/Providers/AppServiceProvider.php
--- a/src/app/Providers/AppServiceProvider.php
+++ b/src/app/Providers/AppServiceProvider.php
@@ -23,17 +23,22 @@
/**
* Serialize a bindings array to a string.
- *
- * @return string
*/
- private static function serializeSQLBindings(array $array): string
+ private static function serializeSQLBindings(array $array, string $sql): string
{
- $serialized = array_map(function ($entry) {
+ $ipv = preg_match('/ip([46])nets/', $sql, $m) ? $m[1] : null;
+
+ $serialized = array_map(function ($entry) use ($ipv) {
if ($entry instanceof \DateTime) {
return $entry->format('Y-m-d h:i:s');
+ } elseif ($ipv && is_string($entry) && strlen($entry) == ($ipv == 6 ? 16 : 4)) {
+ // binary IP address? use HEX representation
+ return '0x' . bin2hex($entry);
}
+
return $entry;
}, $array);
+
return implode(', ', $serialized);
}
@@ -77,7 +82,7 @@
sprintf(
'[SQL] %s [%s]: %.4f sec.',
$query->sql,
- self::serializeSQLBindings($query->bindings),
+ self::serializeSQLBindings($query->bindings, $query->sql),
$query->time / 1000
)
);
diff --git a/src/database/migrations/2022_07_07_100000_ip_nets_optimization.php b/src/database/migrations/2022_07_07_100000_ip_nets_optimization.php
new file mode 100644
--- /dev/null
+++ b/src/database/migrations/2022_07_07_100000_ip_nets_optimization.php
@@ -0,0 +1,97 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+ /**
+ * Run the migrations.
+ */
+ public function up(): void
+ {
+ Schema::dropIfExists('ip4nets');
+ Schema::dropIfExists('ip6nets');
+
+ Schema::create(
+ 'ip4nets',
+ function (Blueprint $table) {
+ $table->bigIncrements('id');
+ $table->string('rir_name', 8);
+ $table->bigInteger('net_number');
+ $table->bigInteger('net_broadcast');
+ $table->tinyInteger('net_mask')->unsigned();
+ $table->string('country', 2)->nullable();
+ $table->bigInteger('serial')->unsigned();
+ $table->timestamps();
+
+ $table->index(['net_number', 'net_broadcast', 'net_mask']);
+ }
+ );
+
+ Schema::create(
+ 'ip6nets',
+ function (Blueprint $table) {
+ $table->bigIncrements('id');
+ $table->string('rir_name', 8);
+ $table->bigInteger('net_number');
+ $table->bigInteger('net_broadcast');
+ $table->tinyInteger('net_mask')->unsigned();
+ $table->string('country', 2)->nullable();
+ $table->bigInteger('serial')->unsigned();
+ $table->timestamps();
+
+ $table->index(['net_number', 'net_broadcast', 'net_mask']);
+ }
+ );
+
+ // VARBINARY is MySQL specific and Laravel does not support it natively
+ DB::statement("alter table ip4nets change net_number net_number varbinary(4) not null");
+ DB::statement("alter table ip4nets change net_broadcast net_broadcast varbinary(4) not null");
+ DB::statement("alter table ip6nets change net_number net_number varbinary(16) not null");
+ DB::statement("alter table ip6nets change net_broadcast net_broadcast varbinary(16) not null");
+ }
+
+ /**
+ * Reverse the migrations.
+ */
+ public function down(): void
+ {
+ Schema::dropIfExists('ip4nets');
+ Schema::dropIfExists('ip6nets');
+
+ Schema::create(
+ 'ip4nets',
+ function (Blueprint $table) {
+ $table->bigIncrements('id');
+ $table->string('rir_name', 8);
+ $table->string('net_number', 15)->index();
+ $table->tinyInteger('net_mask')->unsigned();
+ $table->string('net_broadcast', 15)->index();
+ $table->string('country', 2)->nullable();
+ $table->bigInteger('serial')->unsigned();
+ $table->timestamps();
+
+ $table->index(['net_number', 'net_mask', 'net_broadcast']);
+ }
+ );
+
+ Schema::create(
+ 'ip6nets',
+ function (Blueprint $table) {
+ $table->bigIncrements('id');
+ $table->string('rir_name', 8);
+ $table->string('net_number', 39)->index();
+ $table->tinyInteger('net_mask')->unsigned();
+ $table->string('net_broadcast', 39)->index();
+ $table->string('country', 2)->nullable();
+ $table->bigInteger('serial')->unsigned();
+ $table->timestamps();
+
+ $table->index(['net_number', 'net_mask', 'net_broadcast']);
+ }
+ );
+ }
+};
diff --git a/src/tests/Feature/Controller/AuthTest.php b/src/tests/Feature/Controller/AuthTest.php
--- a/src/tests/Feature/Controller/AuthTest.php
+++ b/src/tests/Feature/Controller/AuthTest.php
@@ -42,7 +42,7 @@
$this->expectedExpiry = \config('auth.token_expiry_minutes') * 60;
- \App\IP4Net::where('net_number', '127.0.0.0')->delete();
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
$user = $this->getTestUser('john@kolab.org');
$user->setSetting('limit_geo', null);
@@ -56,7 +56,7 @@
$this->deleteTestUser('UsersControllerTest1@userscontroller.com');
$this->deleteTestDomain('userscontroller.com');
- \App\IP4Net::where('net_number', '127.0.0.0')->delete();
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
$user = $this->getTestUser('john@kolab.org');
$user->setSetting('limit_geo', null);
diff --git a/src/tests/Feature/Controller/NGINXTest.php b/src/tests/Feature/Controller/NGINXTest.php
--- a/src/tests/Feature/Controller/NGINXTest.php
+++ b/src/tests/Feature/Controller/NGINXTest.php
@@ -20,7 +20,7 @@
'limit_geo' => null,
'guam_enabled' => false,
]);
- \App\IP4Net::where('net_number', '127.0.0.0')->delete();
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
$this->useServicesUrl();
}
@@ -37,7 +37,7 @@
'limit_geo' => null,
'guam_enabled' => false,
]);
- \App\IP4Net::where('net_number', '127.0.0.0')->delete();
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
parent::tearDown();
}
diff --git a/src/tests/Feature/Controller/PasswordResetTest.php b/src/tests/Feature/Controller/PasswordResetTest.php
--- a/src/tests/Feature/Controller/PasswordResetTest.php
+++ b/src/tests/Feature/Controller/PasswordResetTest.php
@@ -18,7 +18,7 @@
$this->deleteTestUser('passwordresettest@' . \config('app.domain'));
- \App\IP4Net::where('net_number', '127.0.0.0')->delete();
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
}
/**
@@ -28,7 +28,7 @@
{
$this->deleteTestUser('passwordresettest@' . \config('app.domain'));
- \App\IP4Net::where('net_number', '127.0.0.0')->delete();
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
parent::tearDown();
}
diff --git a/src/tests/Unit/UtilsTest.php b/src/tests/Unit/UtilsTest.php
--- a/src/tests/Unit/UtilsTest.php
+++ b/src/tests/Unit/UtilsTest.php
@@ -8,13 +8,54 @@
class UtilsTest extends TestCase
{
/**
+ * Test for Utils::countryForIP()
+ */
+ public function testCountryForIP(): void
+ {
+ // Create some network records, the tables might be empty
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
+ \App\IP6Net::where('net_number', inet_pton('2001:db8::ff00:42:0'))->delete();
+
+ $this->assertSame('', Utils::countryForIP('127.0.0.1', ''));
+ $this->assertSame('CH', Utils::countryForIP('127.0.0.1'));
+ $this->assertSame('', Utils::countryForIP('2001:db8::ff00:42:1', ''));
+ $this->assertSame('CH', Utils::countryForIP('2001:db8::ff00:42:1'));
+
+ \App\IP4Net::create([
+ 'net_number' => '127.0.0.0',
+ 'net_broadcast' => '127.255.255.255',
+ 'net_mask' => 8,
+ 'country' => 'US',
+ 'rir_name' => 'test',
+ 'serial' => 1,
+ ]);
+
+ \App\IP6Net::create([
+ 'net_number' => '2001:db8::ff00:42:0',
+ 'net_broadcast' => \App\Utils::ip6Broadcast('2001:db8::ff00:42:0', 8),
+ 'net_mask' => 8,
+ 'country' => 'PL',
+ 'rir_name' => 'test',
+ 'serial' => 1,
+ ]);
+
+ $this->assertSame('US', Utils::countryForIP('127.0.0.1', ''));
+ $this->assertSame('US', Utils::countryForIP('127.0.0.1'));
+ $this->assertSame('PL', Utils::countryForIP('2001:db8::ff00:42:1', ''));
+ $this->assertSame('PL', Utils::countryForIP('2001:db8::ff00:42:1'));
+
+ \App\IP4Net::where('net_number', inet_pton('127.0.0.0'))->delete();
+ \App\IP6Net::where('net_number', inet_pton('2001:db8::ff00:42:0'))->delete();
+ }
+
+ /**
* Test for Utils::emailToLower()
*/
public function testEmailToLower(): void
{
- $this->assertSame('test@test.tld', \App\Utils::emailToLower('test@Test.Tld'));
- $this->assertSame('test@test.tld', \App\Utils::emailToLower('Test@Test.Tld'));
- $this->assertSame('shared+shared/Test@test.tld', \App\Utils::emailToLower('shared+shared/Test@Test.Tld'));
+ $this->assertSame('test@test.tld', Utils::emailToLower('test@Test.Tld'));
+ $this->assertSame('test@test.tld', Utils::emailToLower('Test@Test.Tld'));
+ $this->assertSame('shared+shared/Test@test.tld', Utils::emailToLower('shared+shared/Test@Test.Tld'));
}
/**
@@ -22,17 +63,17 @@
*/
public function testNormalizeAddress(): void
{
- $this->assertSame('', \App\Utils::normalizeAddress(''));
- $this->assertSame('', \App\Utils::normalizeAddress(null));
- $this->assertSame('test', \App\Utils::normalizeAddress('TEST'));
- $this->assertSame('test@domain.tld', \App\Utils::normalizeAddress('Test@Domain.TLD'));
- $this->assertSame('test@domain.tld', \App\Utils::normalizeAddress('Test+Trash@Domain.TLD'));
-
- $this->assertSame(['', ''], \App\Utils::normalizeAddress('', true));
- $this->assertSame(['', ''], \App\Utils::normalizeAddress(null, true));
- $this->assertSame(['test', ''], \App\Utils::normalizeAddress('TEST', true));
- $this->assertSame(['test', 'domain.tld'], \App\Utils::normalizeAddress('Test@Domain.TLD', true));
- $this->assertSame(['test', 'domain.tld'], \App\Utils::normalizeAddress('Test+Trash@Domain.TLD', true));
+ $this->assertSame('', Utils::normalizeAddress(''));
+ $this->assertSame('', Utils::normalizeAddress(null));
+ $this->assertSame('test', Utils::normalizeAddress('TEST'));
+ $this->assertSame('test@domain.tld', Utils::normalizeAddress('Test@Domain.TLD'));
+ $this->assertSame('test@domain.tld', Utils::normalizeAddress('Test+Trash@Domain.TLD'));
+
+ $this->assertSame(['', ''], Utils::normalizeAddress('', true));
+ $this->assertSame(['', ''], Utils::normalizeAddress(null, true));
+ $this->assertSame(['test', ''], Utils::normalizeAddress('TEST', true));
+ $this->assertSame(['test', 'domain.tld'], Utils::normalizeAddress('Test@Domain.TLD', true));
+ $this->assertSame(['test', 'domain.tld'], Utils::normalizeAddress('Test+Trash@Domain.TLD', true));
}
/**
@@ -42,14 +83,14 @@
{
$set = [];
- $result = \App\Utils::powerSet($set);
+ $result = Utils::powerSet($set);
$this->assertIsArray($result);
$this->assertCount(0, $result);
$set = ["a1"];
- $result = \App\Utils::powerSet($set);
+ $result = Utils::powerSet($set);
$this->assertIsArray($result);
$this->assertCount(1, $result);
@@ -57,7 +98,7 @@
$set = ["a1", "a2"];
- $result = \App\Utils::powerSet($set);
+ $result = Utils::powerSet($set);
$this->assertIsArray($result);
$this->assertCount(3, $result);
@@ -67,7 +108,7 @@
$set = ["a1", "a2", "a3"];
- $result = \App\Utils::powerSet($set);
+ $result = Utils::powerSet($set);
$this->assertIsArray($result);
$this->assertCount(7, $result);

File Metadata

Mime Type
text/plain
Expires
Thu, Apr 9, 11:55 PM (2 h, 50 m ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
18853623
Default Alt Text
D3674.1775778917.diff (20 KB)

Event Timeline