GoAdmio

dbls

Extract file names and 32-character hashes from a database, and print them or save them to a table

On this page: Overview · Quick start · Syntax · Arguments · Output · Examples · Troubleshooting · Details · See also


Overview

/go/dbls scans the data of one MySQL/MariaDB database (not its table structure) and finds every value that looks like a file name or a hash. It can:

It looks for two kinds of text:

Kind What it matches Example
File names A name, a dot, and an extension of 1 to 4 characters photo.jpg, report.pdf, index.php
Hashes A 32-character lowercase hexadecimal string (MD5-style) d41d8cd98f00b204e9800998ecf8427e

[!NOTE] Despite its name, dbls does not list databases or their sizes. To see database sizes, query information_schema.TABLES directly, or use SHOW DATABASES for names only.


Quick start

# Print every file name and hash found in the database "mydb"
/go/dbls mydb
# Save the same list into the table "files" inside "mydb"
/go/dbls mydb files

Syntax

# Print results
/go/dbls <database>

# Save results into a table
/go/dbls <database> <table>
Form Behavior
/go/dbls <database> Prints the unique file names, then the unique hashes.
/go/dbls <database> <table> Creates <table> if needed and inserts the results. Prints nothing.
Any other number of arguments Does nothing.

The command has no -- options and does not validate its input. A missing or misspelled database name produces a MariaDB error, not a friendly message.


Arguments

Position Argument Required Notes
1 <database> Yes The database to scan. Must already exist.
2 <table> No Table to store the results in. Created in <database> if it doesn't exist.

Use plain names (letters, numbers, underscores). The names are placed into SQL statements without quoting, so a name with a hyphen or other special character can fail.


Output

The output is two sorted, de-duplicated lists, one after the other:

  1. File names
  2. 32-character hashes

There is no heading or separator between them. The example below is illustrative.

backup.sql
cover.jpg
index.php
logo.png
notes.txt
5d41402abc4b2a76b9719d911017c592
d41d8cd98f00b204e9800998ecf8427e

Save mode (two arguments)

Nothing is printed. The results are inserted into <database>.<table>, which has this structure:

Column Type Default Notes
ID INT AUTO_INCREMENT — Primary key.
File CHAR(255) Empty Unique. Holds the file name or hash.
Download INT 0 Counter for your own use.
TimeStamp INT UNIX_TIMESTAMP() When the row was inserted.

The table uses InnoDB with utf8mb4_unicode_ci.

Because File is unique and the insert uses IGNORE, running the command again only adds new values. Existing rows, including their Download counts, are left alone.


Examples

# Quick look at what files a CMS database refers to
/go/dbls wp_shop | less
# Build a file inventory table, then check it
/go/dbls wp_shop inventory
mariadb -e "SELECT COUNT(*) FROM wp_shop.inventory"
# Save the file names to a text file
/go/dbls wp_shop > /tmp/wp_shop-files.txt

Troubleshooting

Symptom Likely cause Fix
Unknown database error The database name is wrong. Check the name with mariadb -e "SHOW DATABASES".
No output The command was run with no arguments or more than two. Pass one or two arguments.
Save mode finishes but the table is empty LOAD DATA INFILE couldn't read /tmp/<database>.file. Possible causes are secure_file_priv restrictions or a private /tmp for the MariaDB service. Check SHOW VARIABLES LIKE 'secure_file_priv' and the service's PrivateTmp setting.
Save mode fails with a syntax error The database or table name has special characters. Use a name with only letters, numbers, and underscores.
Many domains and email parts in the list Values like gmail.com or example.org also look like file names. Filter the output, for example grep -vE '\.(com|net|org)$'.
An extension looks cut off (example.comp) Extensions are matched up to 4 characters. Expected behavior. Longer extensions are truncated.
Slow on a large database The whole database is dumped twice. Run it off-peak, or on a copy of the database.

Details

How it works

flowchart TD
    A["dbls database [table]"] --> L["Append the call to /var/log/go/dbls.log"]
    L --> P["Read the MariaDB root password from GoAdmio's config.php"]
    P --> B{"How many arguments?"}
    B -- "1" --> D1["Dump the data only, extract file names"]
    D1 --> D2["Dump again, extract 32-char hashes"]
    D2 --> O["Print both lists"]
    B -- "2" --> S1["Write file names, then hashes, to /tmp/database.file"]
    S1 --> S2["Create the table if it doesn't exist"]
    S2 --> S3["LOAD DATA INFILE ... IGNORE into the File column"]
    B -- "Other" --> X["Do nothing"]

Matching rules

Rule Detail
Source mariadb-dump --no-create-info, so only row data is scanned, never table definitions.
File name pattern Letters, numbers, _, ., -, then a dot and 1 to 4 letters, numbers, _ or -.
Excluded Anything containing .., and anything ending in a dot plus 1 to 4 digits (version numbers such as v1.2, decimals, and IP-like text).
Hash pattern Exactly 32 lowercase hexadecimal characters as a whole word.
Order Each list is sorted and de-duplicated. File names come first, then hashes.

Side effects

Security

[!WARNING] The MariaDB root password is passed to mariadb and mariadb-dump with -p"<password>" on the command line, so it can be visible in the process list while the command runs. Run this command only on servers where you trust every logged-in user.


See also

Command Description
img-out-db Remove images not found in the MySQL database
table Import a single table from a dump file in MySQL/MariaDB
csv Export a MySQL/MariaDB table to a CSV file
mysql Create a MySQL database
import Import a SQL dump into a MySQL/MariaDB database
repair Repair a MySQL database