SQL injection - C Sharp SQL API - Csharp

Need

Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context

1. Usage of C# 7.1 for developing applications with advanced language features and improvements
2. Usage of Microsoft.AspNetCore.Mvc for building web applications using the ASP.NET Core MVC framework
3. Usage of Microsoft.Extensions.Configuration for managing application configuration settings
4. Usage of System.Data.SqlClient for interacting with Microsoft SQL Server databases

Description

Insecure Code Example

public class UserController : Controller
{
    private readonly string _connectionString;

    public UserController(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    [HttpGet]
    public async Task<IActionResult> GetUser(string username)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            await conn.OpenAsync();

            using (SqlCommand cmd = new SqlCommand($"SELECT * FROM Users WHERE Username = '{username}'", conn))
            {
                SqlDataReader reader = await cmd.ExecuteReaderAsync();

                if (reader.Read())
                {
                    return Ok(new
                    {
                        Username = reader.GetString(0),
                        Email = reader.GetString(1)
                    });
                }
            }
        }

        return NotFound();
    }
}

In this piece of code, we have a method `GetUser` inside `UserController` which takes a `username` as a parameter and returns the user's details from the database. The vulnerability lies in the SQL command that is being executed to fetch the user's details. The command is a dynamic SQL statement that directly uses the `username` input to construct the SQL query: ```csharp SqlCommand cmd = new SqlCommand($"SELECT * FROM Users WHERE Username = '{username}'", conn) ``` This means that an attacker can manipulate the `username` input to modify the SQL statement being executed, leading to a SQL Injection attack. For example, an attacker could provide a `username` like `admin'; DROP TABLE Users;--` which would result in the Users table being dropped from the database. This is a critical security vulnerability as it can lead to unauthorized access, data corruption, and even data loss. The code does not validate the `username` input nor does it use parameterized queries or stored procedures which are recommended practices to prevent SQL Injection attacks.

Steps

1. Use parameterized queries instead of concatenating user input directly into the SQL statement.
2. Validate and sanitize user input before using it in the SQL query.
3. Implement input validation to ensure that only expected values are accepted.
4. Consider using an ORM (Object-Relational Mapping) framework to handle SQL queries and parameterization automatically.
5. Implement proper error handling and logging to detect and respond to any potential SQL injection attempts.

Secure Code Example

public class UserController : Controller
{
    private readonly string _connectionString;

    public UserController(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    [HttpGet]
    public async Task<IActionResult> GetUser(string username)
    {
        // Validate the input
        if (string.IsNullOrWhiteSpace(username))
        {
            return BadRequest("Invalid username");
        }

        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            await conn.OpenAsync();

            // Use parameterized query to prevent SQL injection
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Username = @Username", conn))
            {
                cmd.Parameters.AddWithValue("@Username", username);

                SqlDataReader reader = await cmd.ExecuteReaderAsync();

                if (reader.Read())
                {
                    return Ok(new
                    {
                        Username = reader.GetString(0),
                        Email = reader.GetString(1)
                    });
                }
            }
        }

        return NotFound();
    }
}

The updated code now uses a parameterized query to prevent SQL injection. This is done by replacing the inline variable in the SQL statement with a parameter (`@Username`) and then adding the value of the parameter using the `AddWithValue` method of the `SqlCommand` object. This ensures that the user input is properly escaped and prevents it from being executed as part of the SQL statement. Additionally, the code now checks if the `username` is null or whitespace before proceeding with the SQL query. This is a basic form of input validation to ensure that only expected values are accepted. Consider using an ORM (Object-Relational Mapping) framework like Entity Framework for more complex applications. ORMs can handle SQL queries and parameterization automatically, reducing the risk of SQL injection. Also, implement proper error handling and logging to detect and respond to any potential SQL injection attempts. This is not shown in the code above but is an important part of secure coding practices.

References

Last updated

2023/09/18