T09 · Insecure Skill Coding Practices
- Location
modules/real-world-applications.md:39- Finding
SQL Injection-Prone Query Construction
- Content
View full analysis
Vulnerability Details
File Location:
modules/real-world-applications.md, lines 39–47
Vulnerability Type: SQL injection through direct string interpolation
Risk Level: Highpython async def get_user_data(db, user_id: int) -> dict: # Fetch related data concurrently user_task = db.fetch_one(f"SELECT * FROM users WHERE id = {user_id}") orders_task = db.execute(f"SELECT * FROM orders WHERE user_id = {user_id}") profile_task = db.fetch_one(f"SELECT * FROM profiles WHERE user_id = {user_id}") user, orders, profile = await asyncio.gather(user_task, orders_task, profile_task) return {"user": user, "orders": orders, "profile": profile}Technical Analysis
The example inserts
user_iddirectly into three SQL statements using Python f-strings. A type annotation such asuser_id: intis not enforced at runtime and does not prevent a caller from supplying a string or another object whose string representation contains SQL syntax.If an application copies this pattern and passes request-controlled data to
get_user_data, an attacker may alter the structure of all three queries. The exact payload and available SQL operations depend on the database engine, driver behavior, support for stacked statements, and permissions assigned to the database account.Parameterized queries are required because they transmit SQL syntax and values separately, preventing supplied values from being interpreted as executable query syntax.
Attack Path
- An application exposes an endpoint or operation that accepts a user identifier.
- The application passes the supplied value to
get_user_datawithout strict runtime validation. - The value is interpolated directly into the SQL statements.
- An attacker supplies SQL metacharacters and additional clauses instead of a normal numeric identifier.
- The database parses the resulting text as modified SQL rather than treating the input exclusi ...[truncated 788 chars]
- Remediation
View remediation
Remediation Suggestions
- Replace every interpolated SQL value with the database driver's supported parameter-binding mechanism.
- Validate
user_idat the application boundary and reject values that are not valid identifiers. Validation should supplement, not replace, parameterization. - Use a read-only or otherwise least-privileged database account for operations that do not require modification privileges.
- Disable stacked statements where supported.
- Add tests that submit SQL metacharacters and verify they are treated as literal values.
- Document the correct placeholder syntax for the selected database library.
For a named-parameter API, the example could be structured as follows:
python async def get_user_data(db, user_id: int) -> dict: parameters = {"user_id": user_id} user_task = db.fetch_one( "SELECT * FROM users WHERE id = :user_id", parameters, ) orders_task = db.fetch_all( "SELECT * FROM orders WHERE user_id = :user_id", parameters, ) profile_task = db.fetch_one( "SELECT * FROM profiles WHERE user_id = :user_id", parameters, ) user, orders, profile = await asyncio.gather( user_task, orders_task, profile_task, ) return {"user": user, "orders": orders, "profile": profile}The precise placeholder format must be adapted to the actual database driver.