[Security] Passwords stored and compared in plain text

[github-actions]

Description

The application stores user passwords in plain text in the database and compares them directly in SQL queries. This is a critical security vulnerability.

Location

• backend/routers/auth.py – The login query uses COALESCE(password_hash, password) = %s OR password = %s, comparing plain-text passwords.
• backend/db/database.py – The migration sets password_hash = COALESCE(password_hash, password), storing the same plain-text value in both columns.
• backend/routers/auth.py (signup) – Password is stored as-is in both password and password_hash columns.

Impact

If the database is compromised, all user passwords are immediately exposed. Attackers can use these passwords in credential-stuffing attacks across other services since users often reuse passwords.

Recommendation

Use a strong, slow hashing algorithm such as bcrypt or argon2 (via the passlib library) to hash passwords before storing them.

from passlib.context import CryptContext
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

hashed = pwd_context.hash(plain_password)           # on signup
is_valid = pwd_context.verify(plain_password, hashed)  # on login

Remove the plain-text password column from the users table and only keep password_hash.

Severity

Critical