What the assignment was
The DBMS lab that semester wanted a working end-to-end demo that used at least one relational store and at least one document store, wired together through some ordinary application layer. The rubric did not care what the app did. It cared that a form on the front end could reach both databases and that the join between them lived in code, not in the query planner.
I picked a food-ordering flow because it decomposes cleanly. A user record with a name and a location is a natural document. A hotel that serves a set of foods and produces orders is a natural set of joined tables. My whole repo is one file, main.py, 265 lines of Streamlit, later touched to 268. The README is one line, # streamlit-dbms. Six commits total, the last real code change is fbd5047 Update main.py, then a Snyk PR pinning mistune>=2.0.3 on top of that. Dependencies: streamlit 1.10.0, pymongo 3.11.0, mysql-connector-python 8.0.29, and matplotlib 3.5.1 that I never imported.## The dual-database split
MongoDB Atlas held the Users collection in a database called Dbms, on a cluster addressed as cluster0.max1z.mongodb.net. The user document has c_id, f_name, l_name, c_location, pwd, and dp_id. The password field is exactly what it sounds like, stored as-is. The dp_id field is the delivery person of the user's most recent order, written back after checkout.
MySQL, hosted on sql6.freemysqlhosting.net in a database called sql6497487, held the transactional side. There is a hotel(h_id, h_name, h_location) table, a food(f_id, f_name, f_price) table, a serves(h_id, f_id) join table that connects the two, a delivery_person(dp_id, f_name, l_name) table, and an orders(h_id, dp_id, o_total_price) table. The schema is what any DBMS textbook would sketch on the first page of the chapter on many-to-many relationships, minus the foreign keys, which the free MySQL host would not have enforced anyway.
The mix ended up load-bearing for the assignment. Users are schemaless enough that Mongo felt honest, especially when I wanted to shove a dp_id back onto the user document without a migration. Hotels, foods, and orders live in a graph of relationships that MySQL joins in one query, and one of the rubric bullets was "demonstrate a subquery." The reason it turned into a split, though, is more embarrassing. Halfway through the project the auth code was pure MySQL. There are still commented-out SELECT c_id, pwd FROM customers WHERE c_id = ... blocks in the file. I ported auth to Mongo mid-project because I could not get the login flow to work against the free MySQL host reliably. The dual-DB story I told in the lab report was true. The reason it happened was that one of the two backends kept timing out.
flowchart LR
A[Streamlit form] --> B{Login or register}
B -->|find_one| C[(MongoDB Atlas Users)]
C --> D[Streamlit hotel picker]
D -->|SELECT serves+food| E[(MySQL sql6497487)]
E --> F[Streamlit checkout]
F -->|INSERT orders| E
F -->|update_one dp_id| CThe random.randint delivery-person picker
On checkout the app has to pick a delivery person. In the code that means one line that reads roughly dp_id = random.randint(0, len(b) - 1), where b is a list of the delivery people pulled from MySQL. I called it a "matching algorithm" in the lab presentation. It is not. It is a uniform random draw from the seeded rows in delivery_person. There is no distance calculation, no availability check, no history. If delivery_person had eight rows and the code somehow drew outside the range, the INSERT into orders would fail on a foreign key, except again there were no foreign keys, so it would silently insert a dp_id that pointed to nothing.
Some part of me knew this. The lab did not.
The credential leak
The part I keep repeating. In main.py, in the public repo, on visible lines, since 2022:
client = MongoClient("mongodb+srv://<user>:<password>@cluster0.max1z.mongodb.net/...")
mysql.connector.connect(host="sql6.freemysqlhosting.net", user="<user>", password="<password>", database="sql6497487")I have replaced the real values with placeholders in the quote above. In the actual file they were the real admin credentials for the Atlas cluster and the real credentials for the free MySQL host. Two databases, two sets of production credentials, one public commit, four years.
This is the same shape as the LeetCode-Heroku scraper I wrote about two days ago. The difference is that this repo leaked two credentials, not one, and the MySQL host was a public shared hosting service whose credentials also served as the account login. If the current me has not already rotated both, the current me needs to, before publishing this post.
The clean rotation:
- Atlas dashboard, Database Access, add a fresh admin user, delete
admin. Network Access, force-disconnect any active session on the old user. - On
freemysqlhosting.net, log in with the leaked credentials, change the password. If the account is dormant or the free tier has expired, cancel it entirely. git grep -E 'mongodb\+srv|freemysqlhosting|mysql://' $(git rev-list --all)to confirm the string does not survive elsewhere.git filter-repo --replace-textto strip it from history, force-push, rotate again.
The lab-report version of me thought hardcoding creds was a code-smell you cleaned up before production. The production version of me knows the smell is the risk. There is no "before production" for a repo that is already public. Every intro tutorial should have a chapter one titled "put your creds in env vars." Almost none do. os.environ["MONGO_URI"] in the code, actual URI in a .env file, .env in .gitignore, done.
SQL injection via f-strings
Every SQL call in main.py is built with an f-string. The pattern for the food-list-per-hotel query looks like this:
cursor.execute(f"SELECT f_id, f_name, f_price FROM food WHERE f_id IN (SELECT f_id FROM serves WHERE h_id = {hotel_id})")hotel_id came from a Streamlit radio widget, so the risk on this exact line is bounded. But the same pattern shows up on the login screen, where the customer id comes straight from a text input. A c_id of 1 OR 1=1 -- would have returned every row, and there was nothing between the widget and the database except a Python string. The right shape, with mysql-connector-python, is:```python
cursor.execute(
"SELECT f_id, f_name, f_price FROM food WHERE f_id IN (SELECT f_id FROM serves WHERE h_id = %s)",
(hotel_id,),
)
Two changes. Placeholder on the query, tuple on the call. The driver escapes the value against the target dialect. No amount of clever quoting in application code matches what the driver does for free.
Every lab I have seen skips this. The rubric wants a working query, so a working query is what you write. The habit of parameterizing is a production habit that shows up when someone gets scared, not when someone is filling in a template. It should show up sooner. It did not for me.
## What I would build today
One Postgres database, not two. Users, hotels, foods, and orders in the same store, joined at the query planner rather than in application code. Mongo made sense for the assignment; it does not make sense for a food-ordering app. Auth via Clerk or Supabase, so the password field is not something the app stores at all. Streamlit is fine for the UI, still, four years later. Parameterized queries end-to-end. Credentials from an env var, sourced from a vault at deploy time, never in the source tree.
The three hardcoded food images in a dict (Dosa, Idly, Parota) with a fallback to a Mongo `Foods` lookup can go. Put the image URL on the `food` row, next to the price and the name.
The lab wanted to see two databases. What it was really asking for was that a student could reason about the shape of data and pick a store to fit it. That reasoning still holds. My 2022 answer was correct in shape and wrong in a hundred small ways that a passing grade papered over. Writing the post now is me marking the paper myself.
## See also
- [/blog/leetcode-heroku-mongo-scraper](/blog/leetcode-heroku-mongo-scraper): same credential-leak story, one database, one year earlier.
- [/blog/college-python-utility-scripts](/blog/college-python-utility-scripts): same one-file-repo shape from the same era.
- [/blog/cipherstack-vault-full-architecture](/blog/cipherstack-vault-full-architecture): where credentials live now so this stops happening.