fetchByIpBin('ip_enrichment', $ipBin); if ($row === null) { return null; } return [ 'country_code' => $row['country_code'] !== null ? (string) $row['country_code'] : null, 'asn' => $row['asn'] !== null ? (int) $row['asn'] : null, 'as_org' => $row['as_org'] !== null ? (string) $row['as_org'] : null, 'enriched_at' => $row['enriched_at'] !== null ? (string) $row['enriched_at'] : null, ]; } /** * Batched form of `findByIpBin`. Single SELECT with `IN (…)` over the * bin set, returning a map keyed by raw `ip_bin` so callers can * dereference per-row without an extra round-trip per IP. * * SEC_REVIEW F32: the admin IPs list previously called * `findByIpBin` per page row; at `page_size=200` that's 200 * round-trips just for enrichment. Bind binary IN-list params with * `LARGE_OBJECT` so MySQL treats them as octets, matching `upsert`. * * @param list $ipBins * @return array */ public function findByIpBins(array $ipBins): array { if ($ipBins === []) { return []; } $names = []; $params = []; $types = []; foreach ($ipBins as $i => $bin) { $name = 'b' . $i; $names[] = ':' . $name; $params[$name] = $bin; $types[$name] = ParameterType::LARGE_OBJECT; } $sql = 'SELECT ip_bin, country_code, asn, as_org, enriched_at FROM ip_enrichment ' . 'WHERE ip_bin IN (' . implode(', ', $names) . ')'; /** @var list> $rows */ $rows = $this->connection()->fetchAllAssociative($sql, $params, $types); $out = []; foreach ($rows as $row) { $out[(string) $row['ip_bin']] = [ 'country_code' => $row['country_code'] !== null ? (string) $row['country_code'] : null, 'asn' => $row['asn'] !== null ? (int) $row['asn'] : null, 'as_org' => $row['as_org'] !== null ? (string) $row['as_org'] : null, 'enriched_at' => $row['enriched_at'] !== null ? (string) $row['enriched_at'] : null, ]; } return $out; } /** * Insert or update one enrichment row. Driver-aware: SQLite uses * `ON CONFLICT(ip_bin) DO UPDATE`, MySQL uses `ON DUPLICATE KEY`. */ public function upsert(string $ipBin, EnrichmentResult $result): void { $platform = $this->connection()->getDatabasePlatform()::class; $isMysql = stripos($platform, 'mysql') !== false || stripos($platform, 'mariadb') !== false; if ($isMysql) { $sql = 'INSERT INTO ip_enrichment (ip_bin, country_code, asn, as_org, enriched_at) ' . 'VALUES (:ip_bin, :country, :asn, :as_org, :enriched_at) ' . 'ON DUPLICATE KEY UPDATE ' . 'country_code = VALUES(country_code), asn = VALUES(asn), ' . 'as_org = VALUES(as_org), enriched_at = VALUES(enriched_at)'; } else { $sql = 'INSERT INTO ip_enrichment (ip_bin, country_code, asn, as_org, enriched_at) ' . 'VALUES (:ip_bin, :country, :asn, :as_org, :enriched_at) ' . 'ON CONFLICT(ip_bin) DO UPDATE SET ' . 'country_code = excluded.country_code, asn = excluded.asn, ' . 'as_org = excluded.as_org, enriched_at = excluded.enriched_at'; } $stmt = $this->connection()->prepare($sql); $stmt->bindValue('ip_bin', $ipBin, ParameterType::LARGE_OBJECT); $stmt->bindValue('country', $result->countryCode); if ($result->asn === null) { $stmt->bindValue('asn', null, ParameterType::NULL); } else { $stmt->bindValue('asn', $result->asn, ParameterType::INTEGER); } $stmt->bindValue('as_org', $result->asOrg); $stmt->bindValue('enriched_at', $result->enrichedAt->format('Y-m-d H:i:s')); $stmt->executeStatement(); } /** * IPs known to the system (reports OR manual_blocks) but missing * from `ip_enrichment` (or whose enriched_at was cleared by * ?reenrich=true). Ordered by oldest first-seen so backlogs catch * up before newer arrivals. * * @return list ip_bin values, length ≤ $limit */ public function findPending(int $limit): array { $sql = <<connection()->prepare($sql); $stmt->bindValue('limit', $limit, ParameterType::INTEGER); /** @var list> $rows */ $rows = $stmt->executeQuery()->fetchAllAssociative(); $out = []; foreach ($rows as $row) { $out[] = (string) $row['ip_bin']; } return $out; } /** * Clear `enriched_at` on every row. Used only by the `?reenrich=true` * flag on refresh-geoip; lets `findPending` re-pick all rows up. * Returns the affected row count for the job's `items_processed`. */ public function clearAllEnrichedAt(): int { return (int) $this->connection()->executeStatement( 'UPDATE ip_enrichment SET enriched_at = NULL' ); } /** * Distinct country codes seen so far with their populations. * Powers the IPs-list country dropdown. * * @return list */ public function countryCounts(): array { $rows = $this->connection()->fetchAllAssociative( 'SELECT country_code AS code, COUNT(*) AS cnt FROM ip_enrichment ' . 'WHERE country_code IS NOT NULL GROUP BY country_code ORDER BY country_code' ); $out = []; foreach ($rows as $row) { $out[] = [ 'code' => (string) $row['code'], 'count' => (int) $row['cnt'], ]; } return $out; } }