IntegrityStudioClients Code Analysis and Security Fixes

Session Date: 2026-01-16 Project: IntegrityStudioClients (multi-project repository) Focus: Code analysis, security vulnerability remediation, and code quality improvements Session Type: Security Audit and Refactoring

Executive Summary

Performed comprehensive code analysis on the IntegrityStudioClients repository containing 6 client projects with 1,387 source files. Identified and fixed SQL injection vulnerabilities in fisterra’s database initialization code by implementing table/column name whitelisting. Subsequently ran Ruff linting and fixed 400+ code quality issues across 9 Python files, including corrupted escape sequences that were causing syntax errors in 4 files.

The security fixes add defense-in-depth protection against SQL injection attacks by validating all dynamic table and column names against predefined whitelists before query execution.

Key Metrics: | Metric | Value | |——–|——-| | Projects Analyzed | 6 | | Source Files Scanned | 1,387 | | SQL Injection Vulnerabilities Fixed | 4 | | Ruff Issues Fixed | 400+ | | Files with Corrupted Syntax Fixed | 4 | | Python Files Now Passing | 9/9 (100%) |

Projects Analyzed

Project	Files	Primary Language	Notes
activitywatch	1,014	Python	Time tracking application
InspiredMovement	582	JavaScript (Wix)	Dance studio website
fisterra	126	Python/TypeScript	Schema.org processor
Leora	34	JavaScript (Playwright)	Home health website + tests
ATXMovementAlliance	31	Mixed	Community organization
luis-landscaping	6	Mixed	Landscaping business

Security Findings and Fixes

SQL Injection Vulnerabilities (fisterra)

Severity: Medium File: fisterra/sql_db_init.py

Four instances of SQL queries using f-strings with dynamic table/column names were identified:

Line	Issue	Risk
624	`f'INSERT INTO {table} ({columns})'`	Table/column injection
644	`f"PRAGMA table_info({table_name})"`	Table name injection
658	`f"PRAGMA foreign_key_list({table_name})"`	Table name injection
687	`f"SELECT COUNT(*) FROM {table_name}"`	Table name injection

Fix Implementation

Added whitelist-based validation to prevent SQL injection:

1. Table Name Whitelist (sql_db_init.py:15-21)

class SQLDatabaseInitializer:
    VALID_TABLES = frozenset([
        'places', 'people', 'organizations', 'events', 'courses',
        'creative_works', 'offers', 'event_person_relations',
        'organization_person_relations', 'event_organization_relations',
        'course_person_relations', 'creativework_event_relations',
        'organization_organization_relations'
    ])

2. Column Whitelist per Table (sql_db_init.py:24-32)

    VALID_RELATIONSHIP_COLUMNS = {
        'event_person_relations': frozenset(['event_id', 'person_id', 'role']),
        'organization_person_relations': frozenset([
            'organization_id', 'person_id', 'role', 'start_date', 'end_date'
        ]),
        # ... other tables
    }

3. Validation Methods (sql_db_init.py:39-74)

    def _validate_table_name(self, table_name: str) -> str:
        """Validate table name against whitelist to prevent SQL injection."""
        if table_name not in self.VALID_TABLES:
            raise ValueError(f"Invalid table name: {table_name}")
        return table_name

    def _validate_columns(self, table_name: str, columns: List[str]) -> List[str]:
        """Validate column names against whitelist for relationship tables."""
        if table_name not in self.VALID_RELATIONSHIP_COLUMNS:
            raise ValueError(f"No column whitelist defined for table: {table_name}")
        valid_cols = self.VALID_RELATIONSHIP_COLUMNS[table_name]
        for col in columns:
            if col not in valid_cols:
                raise ValueError(f"Invalid column name '{col}' for table '{table_name}'")
        return columns

Security Verification

$ python3 -c "
from sql_db_init import SQLDatabaseInitializer
init = SQLDatabaseInitializer('/tmp/test.db')

# Test malicious table name
try:
    init._validate_table_name('malicious_table; DROP TABLE places;--')
except ValueError as e:
    print(f'✅ Malicious table rejected: {e}')

# Test malicious column name
try:
    init._validate_columns('event_person_relations', ['malicious; DROP TABLE--'])
except ValueError as e:
    print(f'✅ Malicious column rejected: {e}')
"

✅ Malicious table rejected: Invalid table name: malicious_table; DROP TABLE places;--
✅ Malicious column rejected: Invalid column name 'malicious; DROP TABLE--' for table 'event_person_relations'

Code Quality Fixes

Corrupted Escape Sequences

Four Python files had corrupted triple-quote strings (\"\"\" instead of """), causing syntax errors:

File	Issue
`graph_db_init.py`	243 invalid-syntax errors
`html_scraper.py`	Escaped quotes in docstrings
`main_schema_processor.py`	Escaped quotes in SQL
`run_impact_demo.py`	Escaped quotes in strings

Fix Applied:

content = content.replace('\\"\\"\\"', '"""')
content = content.replace('\\\\n', '\\n')

Ruff Linting Results

Before Fixes:

Found 1,298 errors in fisterra/*.py

After Fixes:

Found 1 error (F403 star import warning - style only)

Issue Category	Count Fixed
Invalid syntax (corrupted quotes)	243
Blank line whitespace (W293)	39
Trailing whitespace (W291)	24
Unused imports (F401)	21
Unsorted imports (I001)	7
f-string without placeholders (F541)	7
Line too long (E501)	7
Missing newline at EOF (W292)	1
Total	400+

Class Ordering Bug Fix

File: fisterra/schema_models.py

Problem: CreativeWork class was defined at line 456, but classes inheriting from it (WebPage, WebSite, ImageObject, Review) were defined earlier at lines 218-267, causing F821 Undefined name errors.

Fix: Moved CreativeWork class definition to line 218 (before dependent classes) and removed the duplicate definition.

# Before: Classes defined in wrong order
class WebPage(CreativeWork):    # Line 218 - CreativeWork undefined!
    ...
class CreativeWork(SchemaOrgBase):  # Line 456 - Too late!
    ...

# After: Correct ordering
class CreativeWork(SchemaOrgBase):  # Line 218 - Defined first
    ...
class WebPage(CreativeWork):    # Line 232 - Now works!
    ...

Line-Too-Long Fixes

Manually refactored 7 lines exceeding 140 characters:

Example 1: User-Agent string (html_scraper.py:28)

# Before (143 chars)
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36...'

# After (split across lines)
'User-Agent': (
    'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 '
    '(KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
)

Example 2: Print statements (run_impact_demo.py:215-218)

# Before (178 chars)
print(f"  Overall Score: {summary['baseline_scores']['overall']:5.1f} → {summary['current_scores']['overall']:5.1f} ({summary['percentage_improvements']['overall']:+.1f}%)")

# After (extracted variables)
baseline = summary['baseline_scores']
current = summary['current_scores']
improvements = summary['percentage_improvements']
print(f"  Overall Score:  {baseline['overall']:5.1f} → {current['overall']:5.1f} "
      f"({improvements['overall']:+.1f}%)")

Complexity Analysis Findings

fisterra Python Files (Radon)

Function	File	Complexity	Grade
`extract_entities_from_schema`	build-entity-graph.py	26	D
`generate_database_schema`	html_scraper.py	19	C
`identify_schema_objects`	html_scraper.py	16	C
`extract_microdata`	html_scraper.py	15	C
`analyze_relationships`	build-entity-graph.py	14	C

Recommendation: Refactor functions with grade D (>25 complexity) in future sessions.

Testing and Verification

All Files Compile Successfully

$ for f in fisterra/*.py; do python3 -m py_compile "$f" && echo "✅ $(basename $f)"; done

✅ build-entity-graph.py
✅ graph_db_init.py
✅ html_scraper.py
✅ main_schema_processor.py
✅ run_impact_demo.py
✅ schema_models.py
✅ sql_db_init.py
✅ validate-entity-ids.py
✅ validate-schema.py

Ruff Final Status

$ ruff check fisterra/*.py

F403 `from schema_models import *` used; unable to detect undefined names
  --> main_schema_processor.py:14:1

Found 1 error.

Only remaining issue is a star import warning (style preference, not a bug).

Files Modified

fisterra/sql_db_init.py

Added VALID_TABLES frozenset (13 tables)
Added VALID_RELATIONSHIP_COLUMNS dict (6 tables)
Added _validate_table_name() method
Added _validate_columns() method
Updated 4 query locations to use validation
Fixed escape sequences
Fixed whitespace issues

fisterra/schema_models.py

Moved CreativeWork class before dependent classes
Removed duplicate class definition
Fixed import ordering

fisterra/html_scraper.py

Fixed escape sequences
Split long User-Agent string
Refactored article text extraction
Split long lambda expression

fisterra/run_impact_demo.py

Fixed escape sequences
Refactored long print statements with extracted variables

fisterra/graph_db_init.py

Fixed escape sequences (243 syntax errors resolved)

fisterra/main_schema_processor.py

Fixed escape sequences

fisterra/build-entity-graph.py

Fixed whitespace and import issues

Other Analysis Findings

activitywatch (Python)

Status: All Ruff checks passed
Security: Proper use of environment variables for credentials
No hardcoded secrets found

InspiredMovement (Wix/JavaScript)

10+ console.log statements in production code
Missing ESLint configuration
No TypeScript configuration

Leora (Playwright Tests)

627 test assertions
Well-structured page object pattern
Good test coverage

Recommendations

Immediate

✅ SQL injection vulnerabilities fixed
✅ All Python files now pass syntax checks
✅ Ruff linting issues resolved

Short-term

Refactor extract_entities_from_schema (complexity 26 → target <15)
Add ESLint configuration to InspiredMovement and fisterra
Remove console.log statements from production JavaScript

Medium-term

Add TypeScript to JavaScript projects
Implement pre-commit hooks for linting
Address remaining complexity hotspots in html_scraper.py

References

Code Files Modified

fisterra/sql_db_init.py:15-74 - Security whitelist implementation
fisterra/schema_models.py:218-232 - CreativeWork class relocation
fisterra/html_scraper.py:27-31,241-245,257-260 - Line length fixes
fisterra/run_impact_demo.py:212-221 - Print statement refactoring

Tools Used

ast-grep-mcp (code pattern analysis)
Ruff (Python linting)
Radon (complexity analysis)
Python py_compile (syntax verification)

Security References

ast-grep-mcp, code-analysis, fisterra, python, ruff, security-audit, sql-injection

code-quality, refactoring, security

was published on January 16, 2026.