Security: Hash session tokens at rest and rotate the PHP session on authentication

[fvent01]

Finding

The application stores the full bearer-style session token directly in the SQLite sessions.id column and places the same raw token in the browser cookie. The PHP native session used for CSRF/flash state is not regenerated when login succeeds.

Risk

Anyone who reads a database copy, backup, filesystem snapshot, diagnostic bundle, or leaked SQLite file can immediately replay every still-valid session token without cracking anything. Not rotating the PHP session identifier on login also weakens protection against session fixation and keeps pre-authentication CSRF/session state across the privilege transition.

Evidence

• AuthService::generate_token() creates a 256-bit token.
• Session::create() inserts that raw token as the primary key.
• Middleware::Auth queries sessions using the raw request token.
• AuthController::handleLogin() sets the API token cookie but does not call session_regenerate_id(true) or rotate the CSRF token.

Recommended remediation

• Store only a SHA-256 or keyed-HMAC digest of each session token in SQLite.
• Show/use the raw token only in the browser cookie and hash it before database lookup.
• Add a token identifier/version if operational session listing is needed.
• Regenerate the PHP native session ID after login and logout.
• Rotate the CSRF token at authentication state changes.
• Invalidate all sessions after password reset, account deactivation, or explicit "log out all devices".
• Ensure backups inherit restrictive permissions because they still contain private notes and account metadata.

Acceptance criteria

• A leaked sessions table does not contain replayable session credentials.
• Existing sessions can be migrated or safely invalidated during upgrade.
• PHP session IDs and CSRF tokens rotate at login and logout.
• Password changes and account deactivation invalidate existing sessions.
• Tests cover token hashing, replay of database values, fixation attempts, and session rotation.

Priority

P0 — session credential protection.