index.php and create_ticket_api.php use their own raw mysqli connections instead of Database::getConnection() — missing charset/timezone sync on most page traffic #103

New Issue

2026-09-01T00:21:11-04:00

jared commented

2026-09-01 00:21:11 -04:00

Severity: High

index.php (~lines 20-27) and create_ticket_api.php (~lines 38-43) each open their own new mysqli(...) connection instead of using Database::getConnection() — and unlike Database.php's connection, neither ever calls set_charset('utf8mb4') or syncs the MySQL session time_zone to the app's configured TIMEZONE.

Impact — this is significant because of scope:

index.php's raw $conn is what gets passed into DashboardController/TicketController for every non-API web page — the entire dashboard, ticket view, and ticket-create UI, including StatsModel. This directly contradicts an earlier audit conclusion that "'Today' date boundaries are timezone-safe because helpers/Database.php pins the session time_zone" — that check verified Database.php's own connection-creation code is correct, but never verified it's actually the connection serving the dashboard. It isn't. Any NOW()/CURDATE()-based query run through this connection (most of the dashboard/stats/ticket-view SQL) uses the DB server's default session timezone, not the app's configured one.
No set_charset() call means this connection uses whatever character set the server negotiates by default instead of the explicit utf8mb4 every other connection gets. TicketController::create()'s non-JS POST fallback writes a ticket directly through this connection — if the default charset isn't utf8mb4, multi-byte characters (emoji, non-Latin scripts, accented characters) typed into the title/description via this path get mis-encoded/corrupted at write time, permanently.
Same gap in create_ticket_api.php — the hwmonDaemon-facing endpoint's ticket-creation connection never syncs time_zone, so created_at on an API-created ticket is stamped using the DB server's default session tz while every other ticket (created via Database::getConnection()-backed endpoints) uses the app's configured TIMEZONE. If those two differ, hwmonDaemon-created tickets can silently land on the wrong 'day' for created_at-based stats/filters (e.g. the dashboard's 'Today' widget, SLA elapsed-time calculations).

Fix: Route both index.php and create_ticket_api.php through Database::getConnection() instead of hand-rolling a second new mysqli(...), eliminating the duplicate connection-creation code path and its charset/timezone drift entirely.

**Severity:** High `index.php` (~lines 20-27) and `create_ticket_api.php` (~lines 38-43) each open their own `new mysqli(...)` connection instead of using `Database::getConnection()` — and unlike `Database.php`'s connection, neither ever calls `set_charset('utf8mb4')` or syncs the MySQL session `time_zone` to the app's configured `TIMEZONE`. **Impact — this is significant because of scope:** - `index.php`'s raw `$conn` is what gets passed into `DashboardController`/`TicketController` for **every non-API web page** — the entire dashboard, ticket view, and ticket-create UI, including `StatsModel`. This directly contradicts an earlier audit conclusion that "'Today' date boundaries are timezone-safe because `helpers/Database.php` pins the session `time_zone`" — that check verified `Database.php`'s own connection-creation code is correct, but never verified it's actually the connection serving the dashboard. It isn't. Any `NOW()`/`CURDATE()`-based query run through this connection (most of the dashboard/stats/ticket-view SQL) uses the DB server's default session timezone, not the app's configured one. - No `set_charset()` call means this connection uses whatever character set the server negotiates by default instead of the explicit `utf8mb4` every other connection gets. `TicketController::create()`'s non-JS POST fallback writes a ticket directly through this connection — if the default charset isn't `utf8mb4`, multi-byte characters (emoji, non-Latin scripts, accented characters) typed into the title/description via this path get mis-encoded/corrupted **at write time**, permanently. - Same gap in `create_ticket_api.php` — the hwmonDaemon-facing endpoint's ticket-creation connection never syncs `time_zone`, so `created_at` on an API-created ticket is stamped using the DB server's default session tz while every other ticket (created via `Database::getConnection()`-backed endpoints) uses the app's configured `TIMEZONE`. If those two differ, hwmonDaemon-created tickets can silently land on the wrong 'day' for `created_at`-based stats/filters (e.g. the dashboard's 'Today' widget, SLA elapsed-time calculations). **Fix:** Route both `index.php` and `create_ticket_api.php` through `Database::getConnection()` instead of hand-rolling a second `new mysqli(...)`, eliminating the duplicate connection-creation code path and its charset/timezone drift entirely.

jared added the data-integrity priority/high labels 2026-09-08 10:15:50 -04:00

jared referenced this issue from a commit

2026-09-11 11:43:00 -04:00

Route index.php and create_ticket_api.php through Database::getConnection() (#103, #104)

jared referenced this issue from a commit

2026-09-11 11:54:37 -04:00

Merge development into main: connection/security hardening batch (#85, #94, #103, #104)

jared commented

2026-09-11 11:54:45 -04:00

Fixed and verified against real MariaDB and real running endpoints (session-based CSRF flow, health-check states, connection charset/timezone sync). Merged to main in commit bcc732e.

Fixed and verified against real MariaDB and real running endpoints (session-based CSRF flow, health-check states, connection charset/timezone sync). Merged to main in commit bcc732e.

jared closed this issue

2026-09-11 11:54:45 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: LotusGuild/tinker_tickets#103