5 Steps to Secure Legacy Code from SQL Injection

SQL injection is a major threat to legacy systems, often caused by outdated dependencies, complex architectures, and poor documentation. To protect your code, follow these 5 steps:

Find Weak Points: Use manual code reviews and tools like Burp Scanner or sqlmap to identify vulnerabilities.
Clean User Inputs: Validate and sanitize all inputs with allow-listing, parameterization, and data type checks.
Add Query Parameters: Replace string concatenation in SQL queries with parameterized queries or prepared statements.
Limit Database Access: Apply strict user permissions (e.g., read-only for reporting) and remove unnecessary privileges.
Watch for Threats: Monitor logs, database activity, and apply regular updates using tools like Acunetix or Imperva.

Quick Tip: Always combine manual reviews with automated tools for thorough security checks. Implement these steps to reduce SQL injection risks and safeguard your legacy systems.

How To Prevent SQL injection – Explained In Less Than 5 Minutes

Step 1: Find Weak Points

Start by identifying SQL injection vulnerabilities using a mix of manual reviews and automated tools. With 65% of companies experiencing SQL injection attacks, this step is crucial. Focus on spotting risky patterns in your code.

Code Review Steps

String Concatenation Review
Look for string concatenations in queries, such as String query = "SELECT * FROM users WHERE username='" + userInput + "'", which are prone to injection risks.
Input Validation Analysis
Check how inputs are handled in form fields, URL parameters, and API endpoints to ensure proper validation.
Error Message Inspection
Review error messages to replace detailed SQL errors with generic ones, preventing exposure of backend details.

After identifying potential weak points manually, use scanning tools to confirm vulnerabilities.

Security Scanning Tools

Automated tools can quickly highlight vulnerabilities. Here’s a comparison of some popular options:

Tool	Key Features	Best For	Annual Cost
Burp Scanner	Advanced detection, custom rules	Enterprise teams	$449
sqlmap	Multiple database support, automation	Small teams	Free
Invicti	Real-time testing, detailed reports	Mid-size companies	Custom pricing
Acunetix	Comprehensive scanning, low false positives	Large organizations	Custom pricing

"A SQL injection attack consists of insertion or ‘injection’ of a SQL query via the input data from the client to the application." – OWASP

When choosing a tool, think about its compatibility with your system, ability to detect both basic and complex injection patterns, accuracy in reducing false positives, and how well it integrates with your current security processes.

For the best results, combine automated scans with manual reviews to confirm and address vulnerabilities efficiently.

Step 2: Clean User Inputs

Securing user inputs is crucial to prevent SQL injection, especially in legacy systems. Once you’ve identified vulnerabilities in older code, the next step is to sanitize inputs. As noted by CISA, properly sanitized inputs ensure user data is treated as data, not executable SQL code, significantly lowering injection risks.

Input Cleaning Methods

Effective input cleaning involves multiple validation layers:

Validation Layer	Purpose	Implementation Method
Allow-listing	Restricts inputs to safe, predefined values	Pattern matching, strict data type enforcement
Parameterization	Keeps SQL logic separate from data	Prepared statements with bound parameters
Data Type Validation	Ensures inputs are in the correct format	Strong typing, range checking
Size Limitations	Prevents buffer overflows	Setting maximum length restrictions

Server-side validation should always be your main line of defense. While client-side validation can improve user experience, it’s not foolproof and can be bypassed.

Here’s an example of proper input validation in PHP:

$stmt = $pdb->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_GET['id']]);

This approach ensures SQL code and user input are kept separate, effectively blocking injection attempts.

Common Input Validation Mistakes

Avoid these pitfalls when implementing input validation:

Incomplete Validation Coverage
Validate inputs across all entry points, including web forms, APIs, and file uploads. Skipping any of these can leave vulnerabilities.
Relying on Blocklisting
Blocklisting is less effective than allow-listing. As OWASP states: "Syntactic validation should enforce correct syntax of structured fields, while semantic validation ensures values are appropriate within the business context."

Generic Sanitization
Tailor validation to the specific data type. For example:

Data Type	Validation Approach	Common Mistakes
Numeric IDs	Range checking, integer typing	Allowing negative values or floats
Email Addresses	Format verification, domain validation	Simple pattern matching without RFC compliance
File Uploads	MIME type checking, size limits	Relying only on file extensions
JSON/XML Data	Schema validation, structure checks	Ignoring nested object validation

These methods are especially important when dealing with legacy code, which often lacks modern security measures.

"During the design and development of a software product, developers should use parameterized queries with prepared statements to separate SQL code from user-supplied data to prevent this class of vulnerability. This separation ensures the system treats user input as data and not executable code, thereby eliminating the risk of malicious user input being interpreted as a SQL statement." – CISA

Once inputs are thoroughly sanitized, proceed to update your queries with parameterization in the next step.

sbb-itb-608da6a

Step 3: Add Query Parameters

Once user input is sanitized, the next step is to secure your query layer by using parameterized SQL. This approach builds on sanitized inputs, adding an extra layer of protection against threats like SQL injection.

Updating SQL Queries

To improve security, update legacy SQL queries to use parameterized queries. Here’s a comparison of common patterns:

Legacy Pattern	Parameterized Version	Security Benefit
String concatenation	Placeholder parameters	Blocks code injection
Dynamic SQL building	Prepared statements	Uses pre-compiled execution plans
Direct value insertion	Bound parameters	Ensures type and length validation
Combined concatenations	Multiple parameters	Preserves data integrity

Here’s an example of transforming vulnerable code into a secure version:

// Vulnerable legacy code
string query = "SELECT * FROM Users WHERE DisplayName = '" + userInput + "'";

// Secure parameterized version
string query = "SELECT * FROM Users WHERE DisplayName = @displayName";
command.Parameters.AddWithValue("@displayName", userInput);

Why Parameterized Queries Are Better

Switching to parameterized queries not only secures your application but also brings other advantages:

Performance Boost: SQL servers can cache execution plans, improving query speed.
Simpler Code Maintenance: Avoids messy string concatenation and escaping issues.
Type Safety: Parameters ensure data is handled with the correct type.

Best Practices for Implementation

Follow these best practices to get the most out of parameterized queries:

Best Practice	Implementation Method	Risk Reduction
Use Stored Procedures	Define procedures with parameters	Adds an access control layer
Specify Parameter Types	Explicitly declare data types	Prevents type manipulation
Set Parameter Sizes	Define maximum lengths	Limits overly large inputs
Prepare Statements	Pre-compile queries	Lowers execution time

To further enhance security, combine stored procedures with explicit parameter definitions. This approach ensures both flexibility and strong safeguards.

Step 4: Limit Database Access

Once you’ve parameterized queries, the next step is to restrict database access. This reduces potential damage in case other security measures fail.

Setting User Permissions

Follow the principle of least privilege: every account should have only the permissions it needs to perform its tasks.

Here’s a quick guide:

Permission Level	Access Type	When to Use
Read-only	SELECT statements	Reporting and display functions
Write-limited	INSERT, UPDATE on specific tables	Managing user data
Stored Procedure	EXECUTE on specific procedures	Handling complex operations
View-restricted	Access to specific views	Limiting access to parts of tables

To implement this effectively:

Audit current permissions to find and remove excessive access.
Create separate accounts for different application functions.
Remove outdated users and privileges.
Disable default admin accounts (like SA) to reduce risks.

Permission Examples

Here’s how you can tailor permissions based on application needs:

Application Function	Required Access	Implementation Method
Product Catalog	Read-only product data	Use views to hide sensitive fields.
User Registration	Insert new users only	Grant INSERT permission on the users table.
Order Processing	Update order status	Restrict access to specific stored procedures.
Report Generation	Read across multiple tables	Create consolidated views with pre-applied filters.

Advanced Controls to Consider:

View-Based Access Control: Design views that display only the necessary data columns. Assign permissions to these views instead of the base tables.
Stored Procedure Restrictions: Grant permissions to execute specific stored procedures instead of allowing direct table access.
Application-Specific Users: Create distinct database users for each application. This ensures a breach in one area doesn’t compromise other systems.

Step 5: Watch for Threats

Once you’ve set up database access controls, the next step is keeping an eye out for SQL injection attempts. This means using a mix of automated tools and focused monitoring strategies to catch potential threats.

Monitoring Tools

Here’s a quick comparison of some top tools for enterprise environments:

Tool Type	Recommended Solution	Key Features	Annual Cost
Enterprise WAF	Imperva	Real-time detection, automated mitigation	Custom quote
Vulnerability Scanner	Acunetix	Scans complex apps, integrates with SDLC	Custom quote
Developer Tool	Burp Suite Professional	Manual/automated testing, detailed reports	$449
Open Source	sqlmap	Command-line utility, extensive testing options	Free

For effective threat detection, focus on these areas:

Server Logs: Check logs and query patterns for unusual activity.
Database Activity: Keep an eye on unauthorized access to system tables.
User Behavior: Look for unusual data access patterns that might indicate a breach.

Pair these tools with a consistent schedule for applying security updates to stay ahead of vulnerabilities.

Security Update Plan

A well-structured plan ensures your security measures stay effective. Here’s a simple schedule to follow:

Timeframe	Action	Priority
Daily	Review error logs and alerts	Critical
Weekly	Apply critical security patches	High
Monthly	Conduct vulnerability scans	Medium
Quarterly	Audit security configurations	Medium

"When experiencing a SQL injection attack, a delay in your ability to identify and respond can be disastrous as well as costly." – SolarWinds

To maintain strong security:

Subscribe to vendor security advisories for updates on new threats.
Test all patches in a controlled environment before rolling them out.
Document every security incident and response for future learning.
Set up automated alerts to flag suspicious activities.

Tools like Security Event Manager (SEM) can help you connect the dots between suspicious activities and user behavior, making it easier to spot insider threats or compromised accounts early.

Conclusion

Here’s a quick recap of the five-step plan to protect legacy code from SQL injection.

Summary

This guide walks you through five key steps: identifying vulnerabilities, sanitizing inputs, using query parameters, limiting database access, and monitoring threats. Each step plays a crucial role in improving security:

Step	Key Action	Outcome
1. Find Weak Points	Conduct code reviews and use security scanning tools	Pinpoint risky SQL queries
2. Clean User Inputs	Validate and sanitize all inputs	Block harmful data entries
3. Add Query Parameters	Use parameterized queries	Keep code and data separate
4. Limit Database Access	Set user permissions carefully	Reduce the risk of breaches
5. Watch for Threats	Regularly monitor and update security measures	Stay ahead of potential attacks

By following these steps, you’ll build a strong foundation for securing your systems.

Next Steps

Consider these actions to stay proactive:

Run automated vulnerability scans across all environments
Regularly apply security patches to your tech stack
Provide ongoing security training for your development team

If you need additional expertise, professional support can make a big difference.

OneNine Services

OneNine

For organizations seeking expert help, OneNine offers a US-based team specializing in security monitoring, performance tuning, and regular maintenance to keep legacy systems safe.