T09 · Insecure Skill Coding Practices
Warning
- Location
- scripts/attendance_query.py:17
- Finding
- Plaintext and Indefinite Local Storage of Employee Attendance Data<![CDATA[ ## Vulnerability Details **File Location**: `scripts/attendance_query.py`, lines 17-101 **Vulnerability Type**: Sensitive data stored without access-control or retention safeguards **Risk Level**: Medium ### Vulnerable Code ```python DEFAULT_CACHE_DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "cache.db") DEFAULT_CACHE_TTL_SECONDS = 7 * 24 * 3600 # 7 days def _get_cache_conn(db_path: str = DEFAULT_CACHE_DB_PATH) -> sqlite3.Connection: conn = sqlite3.connect(db_path) conn.execute( """ CREATE TABLE IF NOT EXISTS kv_cache ( key TEXT PRIMARY KEY, value TEXT NOT NULL, ts REAL NOT NULL ) """ ) conn.execute( """ CREATE TABLE IF NOT EXISTS attendance_history ( work_date TEXT NOT NULL, user_id TEXT NOT NULL, user_name TEXT NOT NULL, result_type TEXT NOT NULL, count INTEGER NOT NULL, queried_at REAL NOT NULL, PRIMARY KEY (work_date, user_id, result_type) ) """ ) conn.commit() return conn def _save_attendance_history( fail_table: Dict[str, "collections.Counter"], user_names: Dict[str, str], work_date: str, db_path: str = DEFAULT_CACHE_DB_PATH, ) -> None: """Save abnormal attendance records in the attendance_history table.""" conn = _get_cache_conn(db_path) try: now = time.time() for userid, counter in fail_table.items(): name = user_names.get(userid, userid) for result_type, count in counter.items(): conn.execute( """INSERT OR REPLACE INTO attendance_history (work_date, user_id, user_name, result_type, count, queried_at) VALUES (?, ?, ?, ?, ?, ?)""", (work_date, userid, name, result_type, count, now), ) conn.comm ...[truncated 2687 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Store the database in a dedicated per-user application-data directory rather than beside the script. 2. Create the containing directory with mode `0700` and enforce database permissions of `0600` immediately after creation. 3. Refuse to use a database file that is owned by another user or has unsafe permissions. 4. Add a configurable retention period for `attendance_history` and delete records older than that period. 5. Minimize stored fields. Avoid retaining stable user IDs or names when aggregate results are sufficient. 6. Make historical persistence opt-in, or provide a `--no-history` option for one-time queries. 7. Add an explicit command to purge all cached identifiers and attendance history. 8. Exclude `cache.db`, SQLite journal files, and backup copies from source control, build artifacts, and Skill packaging. 9. Document the exact fields retained, their retention duration, and the local parties that may access them. 10. Where the deployment threat model includes untrusted local users or broadly accessible backups, encrypt sensitive records using keys stored separately from the database. ]]>