A Curious Database: Vulgar Words and Digital Clues

Sometimes, in the world of digital forensics, you stumble across something unexpected—and oddly amusing. During a recent iOS extraction, I found a database with the file path:

\private\var\mobile\Library\Keyboard\VulgarWordUsage.db

Yes, VulgarWordUsage.db. At first glance, I thought it was a lighthearted find with little investigative value. But then, the database turned out to be more interesting than its name suggests.

---

What’s in the VulgarWordUsage.db?

This database isn’t just a log of profanity. It records when specific words are used, which application they were used in, the recipient (if applicable), and how many times the word has been used.

While some words are harmless or humorous—like "butt" (Josh Hickman, I’m looking at you)—others can hold more weight in an investigation. For example, on certain suspect devices, I have found words like "Loli" and "Pedo," which carry significant implications depending on the context.

---

Peeking Inside

Using Josh Hickman’s iOS 17 public image for screenshots (no offense, Josh, but your virtual potty mouth seems mild), I took a closer look. The database contains three tables:

1. properties: Tracks the database version.

2. sqlite_sequence: Holds a name and a seq column.

3. vword_usage: The real treasure trove of information.

In the vword_usage table, I found records of specific words, the apps they were used in, timestamps, and usage counts.

---

Questions Still Unanswered

While exploring this database, I found myself wondering:

• How long is the data stored?

• It’s unclear. In one case, the database had over 1,200 entries spanning about six months. In another, there were 750+ entries covering over a year. Gaps in the ROWID column suggest that some data may have been removed or overwritten.

• Does the data persist if messages are deleted or apps uninstalled?

• Again, this is unknown. Missing ROWIDs imply that some records are erased, but the exact reason remains unclear.

• What about WAL or SHM files?

• Interestingly, I didn’t find any associated Write-Ahead Logging (WAL) or Shared Memory (SHM) files in my cases.
  

It seems the sqlite_sequence table increments each time a word is used, but the sum of usage counts in the vword_usage table doesn’t perfectly match. There’s still more to uncover here.

---

Useful SQL Queries

Here are a couple of handy queries for analyzing this database:

• To find the most frequently used words (across all apps):

SELECT
    vword, 
    SUM(usage_count) AS 'total' 
FROM vword_usage 
GROUP BY vword 
ORDER BY SUM(usage_count) DESC;

• To view word usage by timestamp, app, and recipient:

SELECT
    ROWID, 
    app, 
    recipient, 
    vword, 
    usage_count, 
    datetime('2001-01-01', "last_use_timestamp" || ' seconds') AS 'date' 
FROM vword_usage 
ORDER BY last_use_timestamp DESC;

---

Why It Matters

While this database might not be the smoking gun to crack a case, it has potential. Words with specific connotations or frequency patterns could help corroborate other findings. In certain scenarios, it might even provide insights into user intent or behavior.

For now, the VulgarWordUsage.db remains a quirky, fascinating database with untapped potential. If nothing else, it’s a reminder that in forensics, you never know what you’ll find—or how valuable it might be.

What’s the most unexpected database you’ve encountered in an investigation? Share your stories—I’d love to hear them!