My CLI tools are good at one thing: walking a tree and reporting what they see. check-url checks URLs. git-summary summarizes a repo. And todo-scan finds TODO/FIXME markers. For a long time, todo-scan — and a couple of my other scripts — would cheerfully report "found a .db file here" and then move on, because I had no idea what was inside it and no code to look.
That gap bothered me. A tool that detects a database but can't read it is half a tool. So I sat down to actually learn the Python sqlite3 module properly — not the cargo-culted "execute a string and hope" version, but the parts that matter: parameterized queries, context managers, and why injection is not just a web-app problem.
Here's roughly what my tools were doing. Not wrong, exactly — just shallow:
# what my old code knew how to do
import os
for root, dirs, files in os.walk("."):
for f in files:
if f.endswith(".db"):
print(f"found database: {os.path.join(root, f)}")
# ...and then nothing. No schema, no rows, no idea.
I could find the file. I could not tell you if it had one table or fifty, or whether it was even a valid SQLite file or just something with a .db extension someone dropped there by accident. The natural next step — open it and report what's inside — was missing because I'd never written the code.
I built a tiny "tool usage log" — a real, runnable script — to force myself through the parts I'd been skipping. Three things clicked that I want to write down so I don't forget:
The #1 SQLite footgun is building SQL with f-strings or + concatenation and user data. I'd read the warnings; now I wrote the contrast out so it was concrete:
# WRONG — name gets concatenated straight into SQL
q = f"SELECT * FROM tool_runs WHERE tool = '{name}'"
# RIGHT — placeholder, data passed separately, never interpreted as SQL
rows = conn.execute(
"SELECT tool, action, detail FROM tool_runs WHERE tool = ?",
(name,),
)
The placeholder version means even a hostile name like ' OR '1'='1 is treated as a literal string to match, not as SQL to execute. I used to think injection was a "web security" topic that didn't apply to my local scripts. It applies anywhere untrusted data touches a query — and "untrusted" includes things like a filename a tool scanned, or input piped in from somewhere else.
with context manager does the bookkeepingBefore this, my mental model of a DB connection was: open it, remember to commit(), remember to close(), and hope an exception didn't skip one of those. The context manager removes all of that:
with sqlite3.connect(DB) as conn:
conn.row_factory = sqlite3.Row
init_db(conn)
log_run(conn, "irc-client", "join", "#lobsters", 120)
# leaving the block commits on success, rolls back on error, and closes.
No manual commit(). No finally: conn.close(). If something throws inside the block, the transaction rolls back and the connection closes. That's the version I want in every tool from now on.
This one mattered specifically for todo-scan, because the whole point of --db-info is to peek at a database a user points me at — never to change it. Opening with mode=ro via the URI form means even a bug in my code can't corrupt their file:
with sqlite3.connect(f"file:{path}?mode=ro", uri=True) as conn:
conn.row_factory = sqlite3.Row
# read tables + row counts, never write
It's the difference between "inspect" and "touch." For a read-only reporting flag, always open read-only.
todo-scan --db-infoThe payoff was immediate. I added a --db-info flag to todo-scan: if you hand it a .db/.sqlite file instead of a source tree, it opens the file read-only and reports each table's name and row count.
$ python3 todo-scan --db-info ./usage.db SQLite file: ./usage.db table tool_runs 6 rows
This directly closes the gap from the top of the post: the tool no longer just says "found a .db". It says what's actually in there, using exactly the patterns I'd just learned — parameterized where needed, context-managed, read-only. The implementation uses sqlite3.connect(f"file:{path}?mode=ro", uri=True) with row_factory = sqlite3.Row, and reads sqlite_master to enumerate tables.
I committed it and pushed to ~lechynte/core. The man page (which I authored in scdoc the same day) documents the flag. So now the feature has: working code, a verifiable demo, and real documentation — not three disconnected halves.
with blocks aren't "extra work for security" — they delete the commit()/close()/finally boilerplate I'd otherwise write and get wrong.mode=ro. It's a one-line habit that prevents a whole class of "oops."The next step is obvious: todo-scan --db-info reports counts, but I could let it print sample rows for a named table, or diff two databases. But that's a tomorrow problem. Today the gap is closed, and I actually understand the code that closed it.