CS 499 Milestone Four: Databases Enhancement Narrative

Student Name: Trevor Hegge Date: December 10, 2025 Course: CS 499 - Computer Science Capstone

Artifact Description

The artifact I selected for the databases category is an Inventory Management System originally created in CS 340 - Database Systems. This Python application uses SQLite to manage product inventory and supplier information, providing functionality to add, search, update, and delete products and suppliers, calculate inventory values, and track low stock items. The original version was developed as a course project to demonstrate basic database operations, but it contained catastrophic security vulnerabilities including SQL injection flaws at lines 46, 58, 70, 102, and 124 where string concatenation was used to build SQL queries, missing database constraints that failed to enforce data integrity, absence of indexes causing slow query performance, and hardcoded credentials exposing sensitive configuration information.

Justification for Inclusion

I selected this artifact for my ePortfolio because it provided a critical opportunity to demonstrate my understanding of database security, a skill that distinguishes professional developers who build production-ready systems from those who create academic prototypes. The enhancement process showcases my ability to identify and eliminate security vulnerabilities that could lead to catastrophic data loss, implement industry-standard database design principles that ensure data integrity, and optimize query performance for scalability. The SQL injection vulnerabilities in the original code were not merely theoretical risks—an attacker entering '); DROP TABLE products; -- as a product name would have executed that SQL command, permanently deleting the entire products table and destroying all inventory data. Fixing this critical flaw by implementing parameterized queries demonstrates my security-first mindset and understanding of the OWASP Top 10 most critical web application security risks.

The specific components that showcase my database abilities include the complete refactoring from string concatenation to parameterized queries using ? placeholders throughout every database operation, the comprehensive database schema redesign that adds FOREIGN KEY constraints ensuring products reference valid suppliers with ON DELETE and ON UPDATE cascading rules, NOT NULL constraints preventing empty required fields, CHECK constraints enforcing business rules like price greater than zero and quantity non-negative, and UNIQUE constraints preventing duplicate supplier names. The performance optimization demonstrates my understanding that database design impacts application scalability—creating indexes on products name category and supplier_id transforms searches from O(n) table scans to O(log n) index lookups, potentially 1,000 times faster for large datasets. The query optimization replacing lines 149-154’s Python loop with a single SQL SUM aggregation shows I understand that databases should perform data operations rather than application code.

Course Outcomes Achievement

This enhancement successfully meets the course outcomes I planned to address in Module One. Course Outcome 4 (Demonstrate ability to use well-founded and innovative techniques, skills, and tools in computing practices) is evidenced by my implementation of parameterized queries that leverage SQLite’s built-in query sanitization, context managers using Python’s with statement for automatic transaction management that ensures ACID properties, and strategic index creation based on query access patterns rather than arbitrarily indexing every column. Course Outcome 5 (Develop a security mindset that anticipates adversarial exploits in software architecture) is demonstrated through my comprehensive elimination of all SQL injection attack vectors—I didn’t just fix the obvious string concatenation at line 46, but systematically reviewed every database interaction and secured lines 58, 70, 81, 102, 113, and 124, showing thoroughness in security remediation. The implementation of database-level constraints demonstrates defense-in-depth, recognizing that application-level validation alone is insufficient because direct database access could bypass those checks. Course Outcome 2 (Design and evaluate computing solutions using computer science practices) is addressed through my database normalization decisions, understanding when to use foreign keys versus embedding data, and my documentation explaining trade-offs like using ON DELETE SET NULL versus ON DELETE CASCADE.

Reflection on the Enhancement Process

The process of enhancing this artifact taught me that database security vulnerabilities often stem from convenience choices made during development that introduce catastrophic risks. My most significant learning occurred when I initially attempted to use f-strings for parameterized queries, thinking they would be safer than string concatenation. I quickly realized that f-strings still perform string interpolation before the SQL engine sees the query, meaning f"SELECT * FROM products WHERE id = {product_id}" remains vulnerable if product_id contains malicious SQL. This taught me that only true parameterized queries with ? placeholders provide security because the database driver separates the query structure from the data values, preventing any possibility of SQL injection regardless of input content. This distinction between what looks safe and what is actually safe represents a crucial professional understanding.

Another key learning came from implementing foreign key constraints in SQLite. Unlike many database systems, SQLite requires explicitly enabling foreign key enforcement with PRAGMA foreign_keys = ON at the connection level—without this pragma, foreign key constraints are silently ignored, giving a false sense of security. I discovered this when testing constraint validation and finding that inserting products with invalid supplier IDs succeeded despite my FOREIGN KEY definition. This experience reinforced that understanding database-specific behavior is essential, and assumptions based on SQL standards may not hold across all implementations. It also taught me the importance of comprehensive testing that actually attempts to violate constraints rather than just testing the happy path.

The primary challenge I faced was designing the foreign key cascade behavior—deciding between ON DELETE CASCADE, ON DELETE SET NULL, and ON DELETE RESTRICT. Initially, I implemented ON DELETE CASCADE, thinking it made sense to automatically delete all products when a supplier is deleted. However, I realized this could cause unintended data loss if a supplier is mistakenly deleted, permanently losing valuable inventory records. I changed to ON DELETE SET NULL, which preserves product records but removes the supplier association, allowing investigation and potential supplier restoration. This decision-making process taught me that database design involves anticipating operational scenarios, not just modeling relationships.

Another significant challenge was optimizing the calculateInventoryValue function while maintaining transaction safety. The original implementation retrieved all products into memory and summed in Python, which works but doesn’t scale. My enhanced version uses SQL SUM(price * quantity) executed within a transaction context manager. However, I initially forgot that SQLite transactions lock the entire database file, meaning a long-running calculation could block other operations. This led me to research isolation levels and understand that for read-only operations like calculations, I could use read uncommitted isolation if needed, though for this application the default serializable isolation is appropriate given that calculations are instantaneous. This taught me that query optimization isn’t just about execution speed but also about concurrency and locking considerations.

I also learned about the importance of consistent error handling and user feedback. When foreign key violations occur, the generic sqlite3.IntegrityError doesn’t clearly communicate the problem to users. I implemented error message parsing to detect whether the IntegrityError was due to foreign key violations, unique constraints, or check constraints, providing specific feedback like “Supplier ID 999 does not exist” rather than cryptic database errors. This attention to user experience shows that database expertise includes translating technical errors into actionable guidance.

Overall, this enhancement transformed my understanding of databases from data storage mechanisms into critical security boundaries that require defense-in-depth protection through multiple layers: parameterized queries prevent injection, constraints enforce integrity, transactions ensure consistency, and proper error handling maintains availability. The artifact now represents production-grade database integration that I would confidently deploy in a real business environment, and it effectively demonstrates my database security and design capabilities for potential employers reviewing my ePortfolio.