DEV Community

Cover image for Hunting Typosquats in 300K Newly Registered Domains With DuckDB (No Database Server)
WhoisFreaks profile image Sameer Sheikh
Sameer Sheikh for WhoisFreaks

Posted on • Edited on

Hunting Typosquats in 300K Newly Registered Domains With DuckDB (No Database Server)

#python #security #cybersecurity #tutorial

Last month Palo Alto's Unit 42 published something that stuck with me. They asked two LLMs for the "official" URLs of 913 well-known brands, ran hundreds of thousands of queries, and roughly 37% of the URLs the models returned pointed at domains that did not exist. Normalize the duplicates and you are left with about 250,000 unique phantom domains. Plausible-looking, brand-adjacent, and sitting there unregistered. Anyone who runs the same prompt can register them first and wait for the traffic to arrive.

That is a lot of names to keep an eye on. And the window to catch a bad registration is narrow. Reputation feeds often take a day or three to flag a fresh domain, while a phishing kit can be serving pages within hours of the registrar confirming it.

So here is the question I actually care about. Around 250K to 300K new domains hit the internet every single day. How do you sift that daily haul fast enough to be useful, without standing up Postgres, without a schema, without a pipeline you have to babysit?

The answer I keep coming back to is boring in the best way. One CSV file and DuckDB. It runs on my laptop, the queries finish before I lift my finger off Enter, and there is no server anywhere.

Pipeline: a daily NRD feed flows into DuckDB, which runs levenshtein and jaro_winkler scoring and returns a ranked list of lookalike domains

The data: a daily file, not an API loop

I use the WhoisFreaks Newly Registered Domains feed for this. It is a once-or-twice daily dump of every domain registered across 1,500-plus TLDs, and you can pull it as plain CSV. Each row is a domain with its creation date, registrar, name servers, status flags, and, on the WHOIS tiers, whatever registrant fields survived GDPR redaction.

You do not need a paid plan to follow along. There is a free daily sample of about 1,000 gTLD domains you can grab with a single request, and it has the same column layout as the full file. It comes down gzipped, so save it with a .csv.gz name. That is what I built this on. The field definitions and download endpoints are all in the WhoisFreaks NRD documentation if you want the full column list.

Grab the sample:

curl -o nrd_sample.csv.gz \
  "https://files.whoisfreaks.com/download/domainer/sample/gtld?whois=true"
Enter fullscreen mode Exit fullscreen mode

When you move to the full daily file, it is the same shape with an API key and an optional date:

curl -o "nrd_$(date +%F).csv.gz" \
  "https://files.whoisfreaks.com/v3.3/download/domainer/gtld?apiKey=YOUR_KEY&whois=true"
Enter fullscreen mode Exit fullscreen mode

That second file is the real thing. Hundreds of thousands of rows, tens of columns. DuckDB reads the gzip straight off disk, so there is no unzip step to wait on.

Why DuckDB and not pandas

I reached for pandas first, out of habit. It works, but you feel the weight. read_csv on a wide multi-hundred-megabyte file pulls the whole thing into memory before you have asked a single question of it.

DuckDB flips that. It is an in-process SQL engine, so there is no server and no port. You pip install it and you are done. It reads CSV, gzipped CSV, Parquet, and JSON straight off disk, and it only touches the columns your query actually needs. It is columnar and vectorized under the hood, which is a technical way of saying it chews through a few hundred thousand rows in the time pandas spends clearing its throat.

pip install duckdb
Enter fullscreen mode Exit fullscreen mode

Load and look around

First thing I do with any new file is ask it two questions: how many rows, and what are the columns actually called. Column names in vendor CSVs are never quite what you assume, so check rather than guess.

import duckdb

con = duckdb.connect()

# how many domains in this file?
con.sql("SELECT COUNT(*) AS domains FROM 'nrd_sample.csv.gz'").show()

# what columns do we actually have?
con.sql("DESCRIBE SELECT * FROM 'nrd_sample.csv.gz'").show()
Enter fullscreen mode Exit fullscreen mode

There is no import step, no CREATE TABLE, no loading. You point a query at the filename and DuckDB figures out the types itself. Match the column names in the queries below to whatever DESCRIBE prints for your file, since the exact field names vary by feed tier.

If you have been hoarding a week of files, a glob reads them all as one virtual table:

con.sql("""
    SELECT create_date, COUNT(*) AS registered
    FROM 'nrd_*.csv.gz'
    GROUP BY create_date
    ORDER BY create_date
""").show()
Enter fullscreen mode Exit fullscreen mode

Aggregate the whole file

Before hunting for anything specific, I like to get a feel for the day. Which registrars were busiest, which TLDs dominated. This is a plain GROUP BY over the full file.

con.sql("""
    SELECT
        domain_registrar_name,
        COUNT(*) AS domains
    FROM 'nrd_sample.csv.gz'
    GROUP BY domain_registrar_name
    ORDER BY domains DESC
    LIMIT 10
""").show()
Enter fullscreen mode Exit fullscreen mode

Running that against a real daily file (279,653 domains the day I ran it) returns this:

┌───────────────────────────────────────────┬─────────┐
│           domain_registrar_name            │ domains │
├───────────────────────────────────────────┼─────────┤
│ Dynadot Inc                                │  36,334 │
│ NameCheap, Inc                             │  34,195 │
│ GoDaddy.com, LLC                           │  33,239 │
│ Spaceship, Inc                             │  23,236 │
│ HOSTINGER operations, UAB                  │  14,076 │
│ GMO Internet Group, Inc. d/b/a Onamae.com  │  13,404 │
│ Cloudflare, Inc                            │  11,991 │
│ NameSilo, LLC                              │   5,964 │
│ Porkbun LLC                                │   5,819 │
│ Squarespace Domains II LLC                 │   5,589 │
│ ...                                        │     ... │
└───────────────────────────────────────────┴─────────┘
Enter fullscreen mode Exit fullscreen mode

Four registrars soak up more than 125,000 of those 280,000 domains in a single day. That concentration is worth remembering later.

The TLD split is just as quick. Pull the part after the last dot and count it.

con.sql(r"""
    SELECT
        regexp_extract(domain_name, '\.([^.]+)$', 1) AS tld,
        COUNT(*) AS domains
    FROM 'nrd_sample.csv.gz'
    GROUP BY tld
    ORDER BY domains DESC
    LIMIT 10
""").show()
Enter fullscreen mode Exit fullscreen mode

Which comes back like this:

┌────────┬─────────┐
│  tld   │ domains │
├────────┼─────────┤
│ com    │ 119,585 │
│ shop   │  15,548 │
│ top    │  11,837 │
│ org    │   8,932 │
│ online │   8,399 │
│ net    │   8,161 │
│ xyz    │   7,017 │
│ icu    │   6,470 │
│ cyou   │   6,322 │
│ cfd    │   6,116 │
│ ...    │     ... │
└────────┴─────────┘
Enter fullscreen mode Exit fullscreen mode

.com dominates, no surprise. But look at what follows it. .shop, .top, .icu, .cyou, .cfd. These cheap new gTLDs punch far above their size in abuse reports, and here they are near the top of a single day's registrations. That is a filter worth keeping.

None of this is the point yet. It is orientation. The interesting part is next.

The typosquat hunt

Here is what actually makes this dataset worth reading. Somewhere in today's registrations are names built to impersonate a brand. paypa1, paypal-secure, paypaI with a capital i standing in for the L. Picking those out by hand is hopeless at this volume. But it is a text-distance problem, and DuckDB ships the functions for it built in.

Two of them do most of the work. levenshtein counts how many single-character edits turn one string into another, so a value of 1 or 2 means "almost identical." jaro_winkler_similarity returns a score from 0 to 1 and rewards strings that share a prefix, which is exactly how most typosquats are built.

But one pass is not enough. Edit distance catches misspellings and character swaps. It completely misses paypal-login, because that string is many edits away from the bare brand. So I run a second pass that looks for the brand sitting next to a common phishing keyword. The two passes catch different things.

Two-pass detection: an edit-distance pass catches paypall.org, paypai.com and paypa1.co, while a brand-plus-keyword pass catches paypal-login.net and secure-paypal.com

Pass one, the edit-distance lookalikes. I pull out the registrable label (the part before the first dot), compare it to the brand, and keep the near-misses.

brand = "paypal"

con.sql(f"""
    SELECT
        domain_name,
        levenshtein(split_part(domain_name, '.', 1), '{brand}')            AS edits,
        jaro_winkler_similarity(split_part(domain_name, '.', 1), '{brand}') AS similarity
    FROM 'nrd_sample.csv.gz'
    WHERE levenshtein(split_part(domain_name, '.', 1), '{brand}') BETWEEN 1 AND 3
    ORDER BY similarity DESC
    LIMIT 25
""").show()
Enter fullscreen mode Exit fullscreen mode

The BETWEEN 1 AND 3 is deliberate. Zero edits is just the real brand, and anything past three edits is usually a different word that happens to share letters. That band is where the lookalikes live.

Here is the top of what came back for paypal on a real day:

┌───────────────┬───────┬────────────┐
│  domain_name  │ edits │ similarity │
├───────────────┼───────┼────────────┤
│ paypill.icu   │     2 │      0.910 │
│ paypayhq.com  │     3 │      0.892 │
│ pagpals.com   │     2 │      0.879 │
│ payzap.shop   │     2 │      0.876 │
│ payzap.xyz    │     2 │      0.876 │
│ payspark.biz  │     3 │      0.874 │
│ papar.online  │     2 │      0.858 │
│ payflax.com   │     3 │      0.848 │
│ paytag.app    │     2 │      0.844 │
│ pay-pin.com   │     3 │      0.822 │
│ ...           │   ... │      ...    │
└───────────────┴───────┴────────────┘
Enter fullscreen mode Exit fullscreen mode

paypill.icu is a believable PayPal squat, and it sits on .icu, one of the cheap TLDs from the earlier list. But be honest about the rest. Further down the ranking, edit distance starts dragging in strings like playjl.lol that are three edits away and share some letters but have nothing to do with PayPal. On a six-letter brand, a three-edit window is loose. More on that in a second.

That split_part trick is a simplification. It treats everything before the first dot as the label, which is fine for paypill.icu but not for a login.paypal.evil.com style host. For a daily scan of freshly registered second-level domains it holds up. For deeper subdomain analysis you would want to parse against the public suffix list, which is a post of its own.

The full script wraps both passes, auto-detects the file's columns, and takes a --brand argument. Link at the bottom.

What I noticed in one day's file 

Using domain column: domain_name   registrar column: domain_registrar_name

Top 25 registrars
----------------------------------------
    36,334  Dynadot Inc
    34,195  NameCheap, Inc
    33,239  GoDaddy.com, LLC
    23,236  Spaceship, Inc
    14,076  HOSTINGER operations, UAB
    13,404  GMO Internet Group, Inc. d/b/a Onamae.com
    11,991  Cloudflare, Inc
     5,964  NameSilo, LLC
     5,819  Porkbun LLC
     5,589  Squarespace Domains II LLC
     5,405  Name.com, Inc
     5,174  Tucows Domains Inc
     4,991  Zhengzhou Century Connect Electronic Technology Development Co., Ltd
     4,562  PDR Ltd. d/b/a PublicDomainRegistry.com
     4,203  Squarespace Domains LLC
     3,817  Dynadot LLC
     3,811  IONOS SE
     3,233  NameMart Pte. Ltd
     2,831  Wix.com Ltd
     2,602  Gransy, s.r.o
     1,895  Dominet (HK) Limited
     1,821  Network Solutions, LLC
     1,800  Realtime Register B.V
     1,760  Namecheap, Inc
     1,609  Gname.com Pte. Ltd

Top 25 TLDs
----------------------------------------
   119,585  .com
    15,548  .shop
    11,837  .top
     8,932  .org
     8,399  .online
     8,161  .net
     7,017  .xyz
     6,470  .icu
     6,322  .cyou
     6,116  .cfd
     5,942  .garden
     5,784  .app
     5,746  .site
     4,881  .info
     4,402  .store
     3,537  .vip
     2,843  .click
     2,734  .pro
     2,713  .bet
     2,386  .biz
     2,299  .sbs
     1,737  .club
     1,618  .space
     1,618  .buzz
     1,288  .live

Edit-distance lookalikes for 'paypal' (1-3 edits)
------------------------------------------------------------
  0.910  edits=2  paypill.icu
  0.892  edits=3  paypayhq.com
  0.879  edits=2  pagpals.com
  0.876  edits=2  payzap.shop
  0.876  edits=2  payzap.xyz
  0.876  edits=3  payle.org
  0.874  edits=3  payspark.biz
  0.858  edits=2  papar.online
  0.858  edits=2  papa9.wiki
  0.848  edits=3  payflax.com
  0.848  edits=3  paynela.homes
  0.848  edits=3  payport.systems
  0.844  edits=2  paytag.app
  0.825  edits=3  payz.club
  0.822  edits=3  partpl.com
  0.822  edits=3  papaii.com
  0.822  edits=2  pahwal.com
  0.822  edits=3  payeazy.store
  0.822  edits=3  pacpza.com
  0.822  edits=3  pay-pin.com
  0.800  edits=3  pata.today
  0.800  edits=3  playjl.lol
  0.800  edits=3  playjl.quest
  0.800  edits=3  playjl.cam
  0.800  edits=3  playjl.lat

  scanned in 3.237s

Brand+keyword combinations for 'paypal'
------------------------------------------------------------
  (none found)
Enter fullscreen mode Exit fullscreen mode

A handful of things jumped out once the numbers were real.

  • Registration is wildly concentrated. Dynadot, Namecheap, GoDaddy, and Spaceship together accounted for over 125,000 of the day's ~280,000 domains. Four names, nearly half the internet's daily growth.
  • The data is not perfectly clean. Namecheap shows up twice under two spellings, NameCheap, Inc with 34,195 and Namecheap, Inc further down with another 1,760. If you build alerting on the registrar field, normalize it first. This part took me a minute to spot.
  • Edit distance on a short brand is noisy. paypill.icu is a real catch. But a three-edit window on a six-letter word also pulls in playjl.lol, which shares letters and means nothing. For short brands, drop to --max-edits 1, or once your candidate list is small, require the brand to sit at the start of the label.
  • The two passes really do disagree. On this particular day the brand-plus-keyword pass returned nothing for paypal, while edit distance returned plenty. Another brand, or another day, flips that. Which is the point of running both.
  • Registration is not the same as reach. Plenty of these names resolve to nothing on day one. They sit parked, waiting. The dangerous fraction already has a live A record and a valid cert, and those are the ones worth a second query.

Performance, since that was the whole promise

I timed the typosquat scan on the full daily file, ~280,000 rows, on an ordinary laptop.

import time

t = time.perf_counter()
con.sql("""
    SELECT domain_name
    FROM 'nrd_2026-07-18.csv.gz'
    WHERE levenshtein(split_part(domain_name, '.', 1), 'paypal') BETWEEN 1 AND 3
""").fetchall()
print(f"{time.perf_counter() - t:.3f}s")
Enter fullscreen mode Exit fullscreen mode

The registrar and TLD group-bys came back instantly, fast enough that there was nothing to time. The typosquat scan is heavier, because it runs a Levenshtein calculation against every one of the ~280,000 rows, and that took about 3.2 seconds. Not instant, but fast enough to sit there and iterate on your brand list without breaking focus, and all of it reading a gzipped file off disk with no load step. DuckDB stays in-memory friendly into the millions of rows and spills to disk beyond that, so a full month of daily files in one glob query is still reasonable on the same laptop.

Handing off to pandas for the parts SQL is bad at

DuckDB is the wrong tool for charts. When I want to plot the daily registration curve or feed results into scikit-learn, I convert the result to a DataFrame with one method call. No copy back through disk.

df = con.sql("""
    SELECT create_date, COUNT(*) AS registered
    FROM 'nrd_*.csv.gz'
    GROUP BY create_date
    ORDER BY create_date
""").df()

df.plot(x="create_date", y="registered")
Enter fullscreen mode Exit fullscreen mode

You get the SQL engine for the heavy filtering and pandas for the last mile. Use each for what it is good at.

Going further

There are a few directions this naturally grows into.

The obvious next step is enrichment. Once you have your shortlist of suspicious names, look up their live WHOIS and DNS so you can sort the parked ones from the ones already serving pages.

You can widen the hunt from one brand to a keyword set, and you can add homoglyph normalization so paypaI and pаypal with a Cyrillic a stop slipping through a plain edit-distance filter.

And if you would rather not run a cron job and a distance-scoring script forever, this is a solved product. WhoisFreaks Brand Monitoring watches new registrations for your brand across those 1,500-plus TLDs twice a day and handles the homoglyph and keyword-permutation matching for you, then emails or JSON-exports the hits for a takedown workflow.

Why go to the trouble at all, when the big reputation feeds will flag most of these eventually? Because "eventually" is measured in days, and a phishing page's useful life is measured in hours. Reading the raw registrations yourself is how you close that gap.

Full source

The complete script, including the keyword-permutation variant and column auto-detection, is on GitHub: github.com/WhoisFreaks/nrd-typosquat-hunter. Clone it, point it at the free sample, and swap in your own brand.

Top comments (0)