T09 · Insecure Skill Coding Practices
- Location
src/memory.py:351- Finding
Deleted Facts Remain in the Full-Text Search Index
- Content
View full analysis
Vulnerability Details
File Location:
src/memory.py:160-162,src/memory.py:215-217, andsrc/memory.py:351-373
Vulnerability Type: Incomplete deletion of plaintext memory records
Risk Level: MediumVulnerable Code
python # Full-text search index for facts cursor.execute(""" CREATE VIRTUAL TABLE IF NOT EXISTS facts_fts USING fts5(content, tags, tokenize='porter') """)python # Add to FTS index cursor.execute(""" INSERT INTO facts_fts (rowid, content, tags) SELECT rowid, content, tags FROM facts WHERE id = ? """, (fact_id,))python def forget(self, fact_id: str): """Permanently delete a fact.""" conn = sqlite3.connect(self.db_path) cursor = conn.cursor() cursor.execute("DELETE FROM facts WHERE id = ?", (fact_id,)) conn.commit() conn.close() def forget_stale(self, days: int = 30, min_access_count: int = 1): """ Remove facts that haven't been accessed in N days and have low access counts. """ cutoff = (datetime.utcnow() - timedelta(days=days)).isoformat() conn = sqlite3.connect(self.db_path) cursor = conn.cursor() cursor.execute(""" DELETE FROM facts WHERE last_accessed < ? AND access_count <= ? AND superseded_by IS NULL """, (cutoff, min_access_count)) deleted = cursor.rowcount conn.commit() conn.close() return deletedTechnical Analysis
Each fact's content and tags are stored twice: once in the
factstable and again in the standalonefacts_ftsFTS5 virtual table. The FTS table is not configured as an external-content table, and the schema does not install synchronization triggers.Both deletion methods remove records only from
facts. They do not delete the correspondingfacts_ftsrow. Consequently, methods documented as permanently deleting facts or cleaning up stale information do not erase all copies of the content.Parameterized SQL protects these paths from SQL in ...[truncated 1630 chars]
- Remediation
View remediation
Remediation Suggestions
- Delete the corresponding FTS row in the same transaction as the base fact:
python def forget(self, fact_id: str): conn = sqlite3.connect(self.db_path) try: cursor = conn.cursor() cursor.execute("SELECT rowid FROM facts WHERE id = ?", (fact_id,)) row = cursor.fetchone() if row: cursor.execute("DELETE FROM facts_fts WHERE rowid = ?", (row[0],)) cursor.execute("DELETE FROM facts WHERE id = ?", (fact_id,)) conn.commit() except Exception: conn.rollback() raise finally: conn.close()-
For bulk cleanup, collect the affected
rowidvalues and delete their FTS rows before deleting the base rows, all within one transaction. -
Prefer an external-content FTS5 table tied to
facts, with insert, update, and delete triggers that keep both structures synchronized automatically. -
Add regression tests that:
- Store a fact.
- Invoke
forget()andforget_stale()separately. - Query both
factsandfacts_fts. - Assert that neither table retains the deleted content.
-
Provide a migration or repair routine that removes existing orphaned FTS rows and rebuilds the index from active facts.
-
Document that logical deletion from SQLite does not necessarily guarantee forensic erasure from database pages, journals, WAL files, or backups. If strong erasure is required, use appropriate SQLite secure-deletion settings and securely manage backups and journal files.
-
As defense in depth for the plaintext database, create the default memory directory with mode
0700and the database with mode0600.