Skip to main content

SQL injection - C Sharp SQL API

Need​

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

Context​

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

Description​

Non compliant code​

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:

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​

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

Compliant code​

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 on