SQL Injection ('SQLi')

ID

csharp.sql_injection

Severity

critical

Resource

Injection

Language

CSharp

Tags

CWE:89, NIST.SP.800-53, OWASP:2021:A3, PCI-DSS:6.5.1

Description

Improper neutralization of special elements in SQL Commands ('SQL Injection' aka 'SQL').

SQL Injection vulnerabilities are predominant in applications that incorporate user input directly into SQL queries without adequate validation or sanitization. This allows malicious users to inject SQL commands that can alter the functionality of the original query.

By launching an SQL injection attack, threat actors may leak sensitive data from the database, alter data for their profit, delete data for denial of service, extract passwords and other credentials for offline dictionary attacks, or gain access to other systems within the network exploiting trust relationships.

Rationale

As with most injection vulnerabilities, inserting external input into SQL code is common practice when dealing with dynamic queries, as the engine may not provide options to code a table or column name(s), values for the IN operator, or dynamic WHERE conditions.

However, the concatenation of unsanitized input into SQL code almost always allows for the modification of the intended query.

In C#, a susceptible code example might look like this:

using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;

[ApiController] [Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly string _connectionString = "...";

    [HttpGet("{username}")]
    public IActionResult GetUser(string username)
    {
        // string concatenation of untrusted input into SQL
        string query = $"SELECT * FROM Users WHERE Username = '{username}'";

        using (SqlConnection connection = new SqlConnection(_connectionString))

        // FLAW - Vulnerable to SQL Injection
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
            // ...
            }
        }
    }
}

In this instance, the input username is concatenated directly into the SQL query string, potentially allowing execution of arbitrary SQL code if the input is not controlled.

Remediation

To prevent SQL Injection vulnerabilities, the recommended approach is to use parameterized queries. These structures inherently separate SQL code from data input, neutralizing the potential for input to alter query logic maliciously.

Furthermore, consider these additional precautions:

  • Implement strong input validation and sanitization to enforce expected data patterns and strip potentially harmful characters.

  • Utilize ORM frameworks that abstract direct SQL query writing, inherently minimizing the risk of injection vulnerabilities.

  • Regularly conduct security testing, including SAST reviews, to detect and address potential vulnerabilities early in the development lifecycle.

  • Educate development teams on secure coding practices to integrate security awareness into the development culture.

Here is the revised, secure code example:

using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;
using System.Data;

[ApiController] [Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly string _connectionString = "...";

    [HttpGet("{username}")]
    public IActionResult GetUser(string username)
    {
        // Parameterized query
        const string query = "SELECT * FROM Users WHERE Username = @Username";

        using (SqlConnection connection = new SqlConnection(_connectionString))

        using (SqlCommand command = new SqlCommand(query, connection))
        {
            // FIXED: Bind parameter safely (use correct length)
            command.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
            // ...
            }
        }
    }
}

The use of parameterized query with @Username parameter plus SqlCommand.Parameters.Add() ensures that the untrusted input is treated strictly as a value, not as part of the SQL command, preventing injection.

When column or table names are dynamic (the most common case is the ORDER BY columns), or when direct values cannot be passed directly (as with the IN operator where the database driver typically does not allow to pass the list of values), the allowed values should be carefully whitelisted from a prefixed map or allowed values if they depend on untrusted input, or constructing placeholders that could be passed as values (for the IN case mentioned before).

// EXAMPLE: IN Operator with dynamic parameters
public List<Product> GetProductsByIds(string idList)
{
    // Parse and validate IDs
    var idStrings = idList.Split(',');
    var ids = new List<int>();

    // ensure that all ids are integer
    foreach (var idStr in idStrings)
    {
        if (int.TryParse(idStr.Trim(), out int id))
        {
            ids.Add(id);
        }
    }

    // If no valid IDs, return empty list
    if (ids.Count == 0) return new List<Product>();

    // Build parameterized query
    var parameters = new List<string>();
    var sqlParameters = new List<SqlParameter>();

    // Prepare parameters and their values
    for (int i = 0; i < ids.Count; i++)
    {
        string paramName = $"@id{i}"; // @id0 ... @idN
        parameters.Add(paramName);
        sqlParameters.Add(new SqlParameter(paramName, ids[i]));
    }

    // dynamic IN, but safe against SQL-i
    string query = $"SELECT * FROM Products WHERE ProductId IN ({string.Join(",", parameters)})";

    using (var connection = new SqlConnection(_connectionString))
    {
        using (var command = new SqlCommand(query, connection))
        {
            // Add all parameters to the command
            command.Parameters.AddRange(sqlParameters.ToArray());

            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                // ... retrieve results
            }
        }
    }
}

Frameworks like Entity Framework and LINQ use parameterized SQL by default, so they are safer unless you do not use direct SQL with concatenation. In any case, stay safe and read the Security Considerations (Entity Framework).

Configuration

The detector has the following configurable parameters:

  • sources, that indicates the source kinds to check.

  • neutralizations, that indicates the neutralization kinds to check.

Unless you need to change the default behavior, you typically do not need to configure this detector.

References