Skip to main content

SQL injection - Code

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.0 for modern language features and enhancements
  • Usage of Microsoft.AspNetCore.Mvc for building web applications with ASP.NET Core
  • Usage of Microsoft.EntityFrameworkCore for data access and management in .NET applications

Description​

Non compliant code​

public IActionResult GetUser(string userId)
{
    string sqlQuery = "SELECT * FROM Users WHERE UserId = " + userId;
    var user = _context.Users.FromSqlRaw(sqlQuery).ToList();
    return View(user);
}

The above C# code is an example of a SQL injection vulnerability in an ASP.NET Core application.

The GetUser method takes a userId as a parameter and then constructs a SQL query by concatenating the userId directly into the query string. This is a major security risk because it allows an attacker to manipulate the SQL query by providing a malicious userId.

For example, if an attacker provides 1; DROP TABLE Users; as the userId, the resulting SQL query would be SELECT * FROM Users WHERE UserId = 1; DROP TABLE Users;, which would delete the entire Users table from the database.

This vulnerability could lead to data loss, data corruption, unauthorized access, and other serious consequences. It's essential to never construct SQL queries by concatenating user inputs directly into the query string. Instead, use parameterized queries or stored procedures to prevent SQL injection attacks.

Steps​

  • Use parameterized queries or prepared statements to ensure that user input is properly sanitized and escaped.
  • Avoid concatenating user input directly into SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Consider using an ORM (Object-Relational Mapping) framework to handle database operations, as they often provide built-in protection against SQL injection.
  • Implement input validation and data validation to ensure that only valid input is accepted.
  • Implement proper authentication and authorization mechanisms to prevent unauthorized access to sensitive data.
  • Regularly update and patch your application and database software to protect against known vulnerabilities.
  • Perform regular security audits and penetration testing to identify and address any potential vulnerabilities.

Compliant code​

public IActionResult GetUser(string userId)
{
    string sqlQuery = "SELECT * FROM Users WHERE UserId = @userId";
    var user = _context.Users.FromSqlRaw(sqlQuery, new SqlParameter("@userId", userId)).ToList();
    return View(user);
}

The original code was vulnerable to SQL Injection attacks because it was concatenating user input directly into a SQL query. This could allow an attacker to manipulate the SQL query by providing specially crafted input, potentially leading to unauthorized access to data, data corruption, or even data loss.

The revised code mitigates this risk by using a parameterized query. In a parameterized query, placeholders are used in the SQL query, and the actual user input is passed as a separate argument. This ensures that the user input is properly escaped and sanitized, preventing it from being interpreted as part of the SQL command.

In the revised code, @userId is used as a placeholder in the SQL query, and the actual userId value is passed as a separate argument using new SqlParameter("@userId", userId). This ensures that the userId value is properly escaped and cannot be used to manipulate the SQL query.

In addition to using parameterized queries, it's also important to validate and sanitize user input before using it in SQL queries, implement proper authentication and authorization mechanisms, regularly update and patch your application and database software, and perform regular security audits and penetration testing.

References​

Last updated on