fetchByIpBin('manual_blocks', $ipBin); if ($row === null) { return null; } // The base helper matches WHERE ip_bin = ?, but `manual_blocks` // stores network entries with `ip_bin = NULL` and a separate // `network_bin`, so we additionally filter to kind=ip rows. if (($row['kind'] ?? '') !== ManualBlock::KIND_IP) { return null; } return self::hydrate($row); } public function findById(int $id): ?ManualBlock { /** @var array|false $row */ $row = $this->connection()->fetchAssociative( 'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id ' . 'FROM manual_blocks WHERE id = :id', ['id' => $id] ); return $row === false ? null : self::hydrate($row); } /** * @param array{kind?: ?string} $filters * @return list */ public function list(?int $limit, ?int $offset, array $filters = []): array { $sql = 'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id ' . 'FROM manual_blocks'; $params = []; $types = []; $where = []; if (isset($filters['kind']) && $filters['kind'] !== null) { $where[] = 'kind = :kind'; $params['kind'] = $filters['kind']; } if ($where !== []) { $sql .= ' WHERE ' . implode(' AND ', $where); } $sql .= ' ORDER BY id DESC'; if ($limit !== null) { $sql .= ' LIMIT :limit'; $params['limit'] = $limit; $types['limit'] = ParameterType::INTEGER; if ($offset !== null) { $sql .= ' OFFSET :offset'; $params['offset'] = $offset; $types['offset'] = ParameterType::INTEGER; } } /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative($sql, $params, $types); return array_map(self::hydrate(...), $rows); } public function count(?string $kindFilter = null): int { if ($kindFilter !== null) { return (int) $this->connection()->fetchOne( 'SELECT COUNT(*) FROM manual_blocks WHERE kind = :kind', ['kind' => $kindFilter] ); } return (int) $this->connection()->fetchOne('SELECT COUNT(*) FROM manual_blocks'); } public function createIp( IpAddress $ip, ?string $reason, ?DateTimeImmutable $expiresAt, ?int $createdByUserId, ): int { $this->insertRow('manual_blocks', [ 'kind' => ManualBlock::KIND_IP, 'ip_bin' => $ip->binary(), 'network_bin' => null, 'prefix_length' => null, 'reason' => $reason, 'expires_at' => $expiresAt?->format('Y-m-d H:i:s'), 'created_by_user_id' => $createdByUserId, ], ['ip_bin' => ParameterType::LARGE_OBJECT]); return (int) $this->connection()->lastInsertId(); } public function createSubnet( Cidr $cidr, ?string $reason, ?DateTimeImmutable $expiresAt, ?int $createdByUserId, ): int { $this->insertRow('manual_blocks', [ 'kind' => ManualBlock::KIND_SUBNET, 'ip_bin' => null, 'network_bin' => $cidr->network(), 'prefix_length' => $cidr->prefixLength(), 'reason' => $reason, 'expires_at' => $expiresAt?->format('Y-m-d H:i:s'), 'created_by_user_id' => $createdByUserId, ], ['network_bin' => ParameterType::LARGE_OBJECT]); return (int) $this->connection()->lastInsertId(); } public function delete(int $id): void { $this->connection()->executeStatement('DELETE FROM manual_blocks WHERE id = :id', ['id' => $id]); } /** * IDs of blocks whose `expires_at` has passed. Used by a future cleanup * job; the caller decides whether to delete or just flag. * * @return list */ public function findExpired(DateTimeImmutable $now): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT id FROM manual_blocks WHERE expires_at IS NOT NULL AND expires_at < :now', ['now' => $now->format('Y-m-d H:i:s')] ); return array_map(static fn (array $r): int => (int) $r['id'], $rows); } /** * @return list */ public function listSubnets(): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id ' . 'FROM manual_blocks WHERE kind = :kind ORDER BY id', ['kind' => ManualBlock::KIND_SUBNET] ); return array_map(self::hydrate(...), $rows); } /** * @return list */ public function listIps(): array { /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative( 'SELECT id, kind, ip_bin, network_bin, prefix_length, reason, expires_at, created_at, created_by_user_id ' . 'FROM manual_blocks WHERE kind = :kind ORDER BY id', ['kind' => ManualBlock::KIND_IP] ); return array_map(self::hydrate(...), $rows); } /** * @param array $row */ private static function hydrate(array $row): ManualBlock { $tz = new DateTimeZone('UTC'); $createdAt = isset($row['created_at']) && $row['created_at'] !== null ? new DateTimeImmutable((string) $row['created_at'], $tz) : new DateTimeImmutable('now', $tz); $expiresAt = isset($row['expires_at']) && $row['expires_at'] !== null ? new DateTimeImmutable((string) $row['expires_at'], $tz) : null; $kind = (string) $row['kind']; $ip = null; $cidr = null; if ($kind === ManualBlock::KIND_IP && $row['ip_bin'] !== null) { $ip = IpAddress::fromBinary((string) $row['ip_bin']); } elseif ($kind === ManualBlock::KIND_SUBNET && $row['network_bin'] !== null) { $cidr = Cidr::fromBinary((string) $row['network_bin'], (int) $row['prefix_length']); } return new ManualBlock( id: (int) $row['id'], kind: $kind, ip: $ip, cidr: $cidr, reason: $row['reason'] !== null ? (string) $row['reason'] : null, expiresAt: $expiresAt, createdAt: $createdAt, createdByUserId: $row['created_by_user_id'] !== null ? (int) $row['created_by_user_id'] : null, ); } }