BUG-HUNT: [security] Potential SQL Injection in repository `get_by_name` methods via implicit `filter_by` #4155

New issue

Open

opened 2026-04-06 11:54:00 +00:00 by freemo · 1 comment

freemo commented

2026-04-06 11:54:00 +00:00

Owner

Metadata

Branch: fix/security-sql-injection-repository-get-by-name
Commit Message: fix(repositories): replace filter_by with explicit parameterized filter in get_by_name methods
Milestone: (none — see backlog note below)
Parent Epic: #362

Summary

Several repository classes use SQLAlchemy's filter_by method with user-provided string arguments to query records. While filter_by with keyword arguments is generally safe, it is less explicit than using filter with direct column comparison operators, which produce unambiguously parameterized queries. Adopting the explicit form is a security best practice that eliminates any theoretical risk of SQL injection and makes query intent clearer.

Affected Locations

File	Class	Method	Lines
`src/cleveragents/infrastructure/database/repositories.py`	`ProjectRepository`	`get_by_name`	189–191
`src/cleveragents/infrastructure/database/repositories.py`	`LifecyclePlanRepository`	`get_by_name`	1311–1323

Evidence

# CURRENT — uses filter_by (implicit, less explicit)
def get_by_name(self, name: str) -> Project | None:
    db_project = self.session.query(ProjectModel).filter_by(name=name).first()

# CURRENT — uses filter_by (implicit, less explicit)
row = (
    session.query(LifecyclePlanModel)
    .filter_by(namespaced_name=namespaced_name)
    .first()
)

Suggested Fix

from sqlalchemy import select

# ProjectRepository.get_by_name
stmt = select(ProjectModel).where(ProjectModel.name == name)
db_project = self.session.execute(stmt).scalar_one_or_none()

# LifecyclePlanRepository.get_by_name
stmt = select(LifecyclePlanModel).where(LifecyclePlanModel.namespaced_name == namespaced_name)
row = session.execute(stmt).scalar_one_or_none()

Subtasks

Audit all filter_by usages in repositories.py that accept user-provided string arguments
Refactor ProjectRepository.get_by_name to use select().where() with explicit column comparison
Refactor LifecyclePlanRepository.get_by_name to use select().where() with explicit column comparison
Refactor any other filter_by usages identified in the audit that accept user-controlled input
Add or update Behave unit test scenarios covering the refactored query paths
Run nox -e typecheck and resolve any type errors introduced by the refactor
Run nox -e lint and resolve any linting issues
Run nox -e unit_tests and confirm all scenarios pass
Run nox -e coverage_report and confirm coverage ≥ 97%

Definition of Done

All get_by_name methods (and any other identified methods) use explicit select().where() parameterized queries instead of filter_by
No filter_by calls with user-provided string arguments remain in the repository layer
All Behave unit test scenarios for the affected repositories pass
All nox stages pass (lint, typecheck, unit_tests, integration_tests, coverage_report)
Coverage >= 97%
PR is merged and this issue is closed

Backlog note: This issue was discovered during autonomous operation
on milestone v3.7.0. It does not block milestone completion and has been
placed in the backlog for human review and future milestone assignment.

Automated by CleverAgents Bot
Supervisor: Bug Hunting | Agent: ca-new-issue-creator

## Metadata - **Branch**: `fix/security-sql-injection-repository-get-by-name` - **Commit Message**: `fix(repositories): replace filter_by with explicit parameterized filter in get_by_name methods` - **Milestone**: *(none — see backlog note below)* - **Parent Epic**: #362 ## Summary Several repository classes use SQLAlchemy's `filter_by` method with user-provided string arguments to query records. While `filter_by` with keyword arguments is generally safe, it is less explicit than using `filter` with direct column comparison operators, which produce unambiguously parameterized queries. Adopting the explicit form is a security best practice that eliminates any theoretical risk of SQL injection and makes query intent clearer. ### Affected Locations | File | Class | Method | Lines | |------|-------|--------|-------| | `src/cleveragents/infrastructure/database/repositories.py` | `ProjectRepository` | `get_by_name` | 189–191 | | `src/cleveragents/infrastructure/database/repositories.py` | `LifecyclePlanRepository` | `get_by_name` | 1311–1323 | ### Evidence ```python # CURRENT — uses filter_by (implicit, less explicit) def get_by_name(self, name: str) -> Project | None: db_project = self.session.query(ProjectModel).filter_by(name=name).first() ``` ```python # CURRENT — uses filter_by (implicit, less explicit) row = ( session.query(LifecyclePlanModel) .filter_by(namespaced_name=namespaced_name) .first() ) ``` ### Suggested Fix ```python from sqlalchemy import select # ProjectRepository.get_by_name stmt = select(ProjectModel).where(ProjectModel.name == name) db_project = self.session.execute(stmt).scalar_one_or_none() # LifecyclePlanRepository.get_by_name stmt = select(LifecyclePlanModel).where(LifecyclePlanModel.namespaced_name == namespaced_name) row = session.execute(stmt).scalar_one_or_none() ``` ## Subtasks - [ ] Audit all `filter_by` usages in `repositories.py` that accept user-provided string arguments - [ ] Refactor `ProjectRepository.get_by_name` to use `select().where()` with explicit column comparison - [ ] Refactor `LifecyclePlanRepository.get_by_name` to use `select().where()` with explicit column comparison - [ ] Refactor any other `filter_by` usages identified in the audit that accept user-controlled input - [ ] Add or update Behave unit test scenarios covering the refactored query paths - [ ] Run `nox -e typecheck` and resolve any type errors introduced by the refactor - [ ] Run `nox -e lint` and resolve any linting issues - [ ] Run `nox -e unit_tests` and confirm all scenarios pass - [ ] Run `nox -e coverage_report` and confirm coverage ≥ 97% ## Definition of Done - [ ] All `get_by_name` methods (and any other identified methods) use explicit `select().where()` parameterized queries instead of `filter_by` - [ ] No `filter_by` calls with user-provided string arguments remain in the repository layer - [ ] All Behave unit test scenarios for the affected repositories pass - [ ] All nox stages pass (`lint`, `typecheck`, `unit_tests`, `integration_tests`, `coverage_report`) - [ ] Coverage >= 97% - [ ] PR is merged and this issue is closed --- > **Backlog note:** This issue was discovered during autonomous operation > on milestone v3.7.0. It does not block milestone completion and has been > placed in the backlog for human review and future milestone assignment. --- **Automated by CleverAgents Bot** Supervisor: Bug Hunting | Agent: ca-new-issue-creator

freemo added a new dependency

2026-04-06 11:54:17 +00:00

#362 Epic: Security & Safety Hardening

freemo added the

Points

label

2026-04-06 11:57:12 +00:00

freemo referenced this issue

2026-04-06 11:57:37 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo added this to the v3.3.0 milestone

2026-04-06 17:48:47 +00:00

freemo commented

2026-04-06 18:50:03 +00:00

Author

Owner

Milestone Triage Decision: Moved to Backlog

This security logging issue has been moved out of v3.3.0 during aggressive milestone triage. While important for security, it does not relate to the core focus of Corrections + Subplans + Checkpoints.

Reasoning:

v3.3.0 focus: Essential corrections, subplan management, and checkpoint functionality
This issue: Security logging enhancement - important but not milestone-blocking
Impact: Security observability improvement, not core corrections/subplans/checkpoints functionality

Will be addressed in a future milestone focused on security hardening and observability.

**Milestone Triage Decision: Moved to Backlog** This security logging issue has been moved out of v3.3.0 during aggressive milestone triage. While important for security, it does not relate to the core focus of Corrections + Subplans + Checkpoints. **Reasoning:** - v3.3.0 focus: Essential corrections, subplan management, and checkpoint functionality - This issue: Security logging enhancement - important but not milestone-blocking - Impact: Security observability improvement, not core corrections/subplans/checkpoints functionality Will be addressed in a future milestone focused on security hardening and observability.

freemo removed this from the v3.3.0 milestone

2026-04-06 20:40:04 +00:00