Puzzleshot #016 - Ducks like Phishing

raw data→api→duckdb→result

Time for a change problem type and a change of data source.

In Puzzleshot 015 we used Python to preprocess a local file before querying it with DuckDB. This week we’re going to fetch live data from a public API instead — and query it with the same pattern.

PhishStats (phishstats.info) provides a free public API of recently reported phishing URLs with rich metadata — IPs, countries, ISPs, TLDs, and more. No API key required for basic access (anonymous limit is 50 requests/day).

The challenge:

Using Python to fetch the data and DuckDB to query it, answer the following against the 100 most recent phishing entries:

Which IP addresses appear most frequently?
Which countries are most represented in phishing infrastructure?

The API endpoint:

https://api.phishstats.info/api/phishing?_sort=-id&_size=100

Things to consider:

How do you get the JSON response into a format DuckDB can register?
What does the country distribution tell you about phishing infrastructure?

Reveal solution

This puzzle we fetched live phishing intelligence from PhishStats and queried it with DuckDB — same pattern as #015 but with a live API as the data source instead of a local file.

A solution:

import duckdb
import requests
import pandas as pd

# Fetch 100 most recent phishing entries
response = requests.get(
  'https://api.phishstats.info/api/phishing',
  params={'_sort': '-id', '_size': 100},
  headers={'Accept': 'application/json'}
)

df = pd.DataFrame(response.json())

conn = duckdb.connect()
conn.register('phishing', df)

# Most common IPs
print("Top IPs:")
print(conn.execute("""
  SELECT ip, count(*) as count
  FROM phishing
  WHERE ip IS NOT NULL
  GROUP BY ip
  ORDER BY count DESC
  LIMIT 10
""").fetchdf())

# Most common countries
print("\nTop countries:")
print(conn.execute("""
  SELECT countrycode, countryname, count(*) as count
  FROM phishing
  WHERE countrycode IS NOT NULL
  GROUP BY countrycode, countryname
  ORDER BY count DESC
  LIMIT 10
""").fetchdf())

What’s happening:

requests.get fetches 100 recent phishing entries sorted by most recent ID. The response is a JSON array which pd.DataFrame() converts directly into a dataframe — no manual parsing needed.
conn.register('phishing', df) registers the Pandas dataframe with DuckDB via Apache Arrow for zero-copy efficiency. As noted in DuckDB requires a Pandas DataFrame, PyArrow table, or similar object rather than a plain Python list or generator.
The queries themselves are just GROUP BY aggregations on clean string fields — ip, countrycode, and countryname are all pre-parsed in the API response. No casting or regex required.

Result details:

Results will vary since this is live data. A few things worth keeping in mind when interpreting the country distribution:

The country field reflects where the phishing infrastructure IPs are geographically registered — not necessarily where attackers are located, where the operation is run from, or where hosting was intentionally chosen. Compromised legitimate hosts, shared hosting, and cloud provider abuse all contribute to the distribution. A high count for any given country tells you about IP registration, not attribution or even use.

A single IP appearing multiple times in 100 records is more directly meaningful — that host is actively running multiple phishing campaigns simultaneously and worth investigating further, though again be careful about actual attribution.

The PhishStats schema has much more — beyond IPs and countries it includes ISP, TLD, SSL issuer, score, and so on. We’ll likely ask many more interesting questions with this dataset in future puzzles.