Legacy Application Audit Reveals Manual ID Generation and Zero Database Indexes

No Indexes, No Parameters, No Problem

Engineer Conner Phillis inherited a legacy system where a single 9,000-line function caused frequent data loss for active customers. The investigation revealed the system bypassed native RDBMS features in favor of a manual, non-atomic ID generation table.

Why This Matters

This case study demonstrates the extreme technical debt incurred when fundamental database principles—such as atomicity, indexing, and parameterization—are ignored. The failure to use built-in primary key constraints and auto-increment features led to race conditions and significant performance bottlenecks during bulk operations.

Ultimately, the lack of security via parameterized queries left the application vulnerable to SQL injection, while the absence of indexes forced the organization to halt sales. This illustrates how poor architectural choices can escalate from minor performance issues to a total business moratorium and required full-scale rewrite.

Key Insights

• A 9,000-line function served as the primary failure point for data insertion, complicated by a server returning HTTP 200 status codes even when exceptions were swallowed.
• Manual ID generation via a global ‘dbo.ids’ table replaced native SQL auto-incrementing, resulting in non-atomic operations and duplicate ID collisions.
• SQL Server Management Studio (SSMS) audit revealed zero indexes, foreign keys, or unique constraints across the entire production database.
• Query execution relied exclusively on raw string concatenation, leaving the system fully exposed to SQL injection attacks due to the absence of database parameters.
• The application required three separate database round trips for every single row insertion to manage manual ID increments and the final data write.

Working Examples

The function call used to manually fetch the next primary key from a custom ID table.

var id = DbUtils.GetNextId("TableName")

The non-atomic SQL logic used to simulate auto-incrementing primary keys.

SELECT id FROM dbo.ids WHERE tableName = '<table-name>';
UPDATE dbo.ids SET id = id + 1 WHERE tableName = '<table-name>';

Practical Applications

• Use case: Enterprise RDBMS systems should utilize native IDENTITY or SEQUENCE objects to ensure atomic and unique primary key generation.
• Pitfall: Implementing primary keys via manual SELECT and UPDATE cycles leads to race conditions where two concurrent requests receive the same ID.
• Use case: Database administrators must define clustered and non-clustered indexes to prevent linear scan performance degradation during growth.
• Pitfall: Building SQL queries through raw string concatenation creates high-risk vulnerabilities; developers must use parameterized queries to sanitize inputs.

References:

• https://dev.to/connerphillis/no-indexes-no-parameters-no-problem-73k