T09 · Insecure Skill Coding Practices
- Location
scripts/memory_search.py:12- Finding
SQL Injection Through the Category Command-Line Argument
- Content
View full analysis
Vulnerability Details
File Location:
scripts/memory_search.py, lines 12–19
Vulnerability Type: SQL injection caused by direct interpolation of untrusted command-line input
Risk Level: HighVulnerable Code
python def search_structured(query, category=None): """搜索结构化记忆""" where = "" if category: where = f"WHERE category = '{category}'" cmd = [ "psql", "-h", "localhost", "-p", "5432", "-U", "damien", "-d", "postgres", "-c", f"SELECT title, content FROM memory_structured {where};" ]Technical Analysis
The value supplied through the
--categorycommand-line option is inserted directly into a PostgreSQL statement without parameter binding, escaping, or allowlist validation. Althoughsubprocess.runreceives an argument list and therefore does not invoke a shell, that protection only prevents shell injection. It does not prevent injection into the SQL passed topsql -c.PostgreSQL accepts multiple statements in the command string provided to
psql -c. An attacker can terminate the category string, append another SQL statement, and comment out the remainder. For example, a category resembling:text x'; DELETE FROM memory_structured; --produces a command containing:
sql SELECT title, content FROM memory_structured WHERE category = 'x'; DELETE FROM memory_structured; --';Exploitation requires the ability to invoke this script or influence its command-line arguments.
Attack Path
- An attacker obtains the ability to invoke
memory_search.pydirectly or control arguments passed to it by another process. - The attacker supplies a crafted value after
--category. search_structured()embeds the value into the SQL statement without validation.- The statement is passed to the local
psqlclient through its-coption. - PostgreSQL executes the injected statements using ...[truncated 844 chars]
- An attacker obtains the ability to invoke
- Remediation
View remediation
Remediation Suggestions
-
Replace the
psql -csubprocess call with a PostgreSQL driver such aspsycopg. -
Use a parameterized query so the category is always handled as data:
python import psycopg ALLOWED_CATEGORIES = { "goals", "decisions", "status", "preferences", "reference", "archived", } def search_structured(query, category=None): if category is not None and category not in ALLOWED_CATEGORIES: raise ValueError("Unsupported category") sql = "SELECT title, content FROM memory_structured" params = () if category is not None: sql += " WHERE category = %s" params = (category,) with psycopg.connect( host="localhost", port=5432, user="memory_reader", dbname="postgres", ) as connection: with connection.cursor() as cursor: cursor.execute(sql, params) return cursor.fetchall() -
Validate category names against a fixed allowlist even after parameterization.
-
Use a dedicated read-only database role for search operations.
-
Revoke unnecessary privileges from that role, including write, schema modification, and access to unrelated tables.
-
Add tests containing quotes, semicolons, comment markers, and Unicode edge cases to verify that input cannot change query structure.
-