BrandCultivation/docs/superpowers/specs/2026-03-15-database-bugfix-design.md

Database Bug Fix Design

Date: 2026-03-15 Scope: database/db/mysql.py, database/dao/mysql_dao.py Approach: Surgical bug fixes only (no architectural changes)

Problems

mysql.py

1. connect_database: try/except only wraps connection string construction, not create_engine. Real connection failures are uncaught.
2. load_data_with_page: Uses query.replace("SELECT *", "SELECT COUNT(*)") to build count query. Breaks if query doesn't start with SELECT *.
3. fetch_all / fetch_one: Catch SQLAlchemyError, print, and return None silently. Callers cannot detect failures.

mysql_dao.py

1. get_cust_by_ids, get_shop_by_ids, get_product_by_ids, get_order_by_product_ids: Build IN (...) clause via string concatenation — SQL injection risk.
2. get_product_by_id: Queries a single record using load_data_with_page (pagination overhead for one row).
3. get_cust_list, get_product_from_order: Call fetch_all(text(query)) directly while all other methods use load_data_with_page — inconsistent interface.

Fixes

mysql.py

Fix 1 — connect_database Move create_engine(...) inside the try block so connection failures are caught and re-raised as ConnectionAbortedError.

Fix 2 — load_data_with_page Replace string substitution with subquery wrapping:

SELECT COUNT(*) FROM (<original_query>) AS _count_subq

Works regardless of the original SELECT clause.

Fix 3 — fetch_all / fetch_one Add raise in the except block after printing the error. No interface change.

mysql_dao.py

Fix 4 — SQL injection in IN clauses Replace string concatenation with SQLAlchemy parameterized binding:

from sqlalchemy import bindparam, text
query = text("... AND col IN :ids").bindparams(bindparam("ids", expanding=True))
params = {"city_uuid": city_uuid, "ids": list(id_list)}

Fix 5 — get_product_by_id Replace load_data_with_page with fetch_one, return a single-row DataFrame.

Fix 6 — get_cust_list / get_product_from_order Replace direct fetch_all(text(query)) calls with load_data_with_page(query, params).

Out of Scope

• No architectural changes (no read/write separation, no ORM models)
• No changes to Redis layer
• No changes to DAO method signatures
• No error handling changes beyond re-raising in fetch methods