Standardize Database Query Parameter Handling

[marrobi]

Summary

Inconsistent query parameter handling across repository classes needs standardization to follow best practices and ensure reliable data access patterns.

Current Issues

1. Mixed Query Construction Patterns

   • Some methods use parameterized queries (secure pattern)
   • Others use string interpolation (potential issues)
   • Inconsistent parameter validation between API routes

2. Specific Problems Identified

   Critical (Direct API Exposure):

   • user_resources.py: user_resources_query() - service_id not validated as UUID4 in API route
   • user_resources.py: get_user_resource_by_id() - string concatenation for resource_id

   Protected by UUID4 Dependencies (Lower Risk):

   • workspace_services.py: workspace_services_query(), get_workspace_service_by_id()
   • workspaces.py: get_workspace_by_id() - string interpolation for workspace_id
   • shared_services.py: shared_service_query() - string interpolation for shared_service_id
   • resources.py: get_resource_by_id() - string concatenation for resource_id

   Internal Usage (Template Names & Paths):

   • resource_templates.py: _template_by_name_query() - template name interpolation
   • resource_templates.py: active_templates_query() - resource_type interpolation
   • resources.py: get_resource_dependency_list() - resource path in CONTAINS()
   • resources.py: get_resource_by_template_name() - template name interpolation
   • resources_history.py: resource_history_query() - resource ID (has UUID validation)

   Additional Query Concatenations:

   • Multiple + f' AND c.id = "{id}"' patterns across repositories

Proposed Solution

1. Standardize Parameterized Queries

Convert all repository query methods to use consistent parameterized query pattern:

# Current problematic pattern:
return f'SELECT * FROM c WHERE c.id = "{resource_id}"'

# Target secure pattern:
query = 'SELECT * FROM c WHERE c.id = @resourceId'
parameters = [{'name': '@resourceId', 'value': resource_id}]
return {'query': query, 'parameters': parameters}

2. Fix API Parameter Validation

Ensure all resource ID parameters use UUID4 validation:

# Add missing UUID4 validation
async def retrieve_user_resources_for_workspace_service(
    workspace_id: UUID4 = Path(...),
    service_id: UUID4 = Path(...),  # Currently missing validation
    ...

3. Update Query Method Usage

Modify repository methods to properly handle parameterized query dictionaries:

# Update from:
query_string = self.user_resources_query(workspace_id, service_id)
await self.query(query=query_string)

# To:
query_spec = self.user_resources_query(workspace_id, service_id)
await self.query(query=query_spec['query'], parameters=query_spec['parameters'])

Files to Update

High Priority (Direct API Exposure):

• api_app/db/repositories/user_resources.py - Convert to parameterized queries
• api_app/api/routes/workspaces.py - Add UUID4 validation for service_id parameter

Medium Priority (Protected by Dependencies):

• api_app/db/repositories/workspace_services.py - Convert to parameterized queries
• api_app/db/repositories/workspaces.py - Convert to parameterized queries
• api_app/db/repositories/shared_services.py - Convert to parameterized queries
• api_app/db/repositories/resources.py - Convert to parameterized queries

Lower Priority (Internal Usage):

• api_app/db/repositories/resource_templates.py - Convert to parameterized queries
• api_app/db/repositories/resources_history.py - Already has UUID validation but convert pattern

Test Files (Update Expected Queries):

• api_app/tests_ma/test_db/test_repositories/*.py - Update test assertions for new query patterns

Acceptance Criteria

• All repository query methods use parameterized queries
• All API route parameters have consistent UUID4 validation
• Repository method callers updated to handle new query structure
• Existing functionality preserved
• Unit tests verify parameter handling