- CI system used - GitHub Actions
- Languages of the repository - C# (.net8)
- Private Repo
- Quality Gate used (Default)[Sonar way (legacy)]
- Quality Profiles used C# Sonar way
I used SonarQube developer-edition on-prem solution, that version is v2025.1 (102418). My quality gate and profile are built-in default values.
SonarQube only finds SQL injection in the GetAlertListDirectSql method. I want to wait for SQL injections because it is a critical issue.
My controller code is below.
using Microsoft.AspNetCore.Mvc;
using AlertApi.Models;
using AlertApi.Services;
using Microsoft.EntityFrameworkCore;
using AlertApi.Data;
using System.Data.SqlClient;
using Dapper;
namespace AlertApi.Controllers
{
[ApiController]
[Route("api/alerts")]
public class AlertController : ControllerBase
{
private readonly IAlertService _alertService;
private readonly TsDbContext _context;
public AlertController(IAlertService alertService, TsDbContext context)
{
_alertService = alertService;
_context = context;
}
[HttpPost("list-direct")]
public IActionResult GetAlertListDirect([FromBody] AlertSearchModel model)
{
try
{
string query = "SELECT AlertID, AlertType, AlertStatus, Title, InsertDate, InsertUser, Detail FROM TblAlert";
if (model!= null && !string.IsNullOrEmpty(model.Search))
{
query += $" WHERE Title LIKE '%{model.Search}%' OR Detail LIKE '%{model.Search}%'";
}
var result = _context.Alerts.FromSqlRaw(query).ToList();
var alerts = result.Select(q => new AlertDataModel
{
AlertId = q.AlertId,
Title = q.Title,
Detail = q.Detail,
InsertUser = q.InsertUser
}).ToList();
return Ok(alerts);
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
[HttpPost("list-direct-sql")]
public IActionResult GetAlertListDirectSql([FromBody] AlertSearchModel model)
{
try
{
var alerts = new List<AlertDataModel>();
string? connectionString = _context.Database.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = "SELECT AlertID, AlertType, AlertStatus, Title, InsertDate, InsertUser, Detail FROM TblAlert";
if (model != null && !string.IsNullOrEmpty(model.Search))
{
query += " WHERE Title LIKE '%" + model.Search + "%' OR Detail LIKE '%" + model.Search + "%'";
}
using (SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
alerts.Add(new AlertDataModel
{
AlertId = reader.GetInt32(0),
Title = reader.GetString(3),
Detail = reader.IsDBNull(6) ? null : reader.GetString(6),
InsertUser = reader.GetString(5)
});
}
}
}
}
return Ok(alerts);
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
[HttpPost("list-direct-dapper")]
public IActionResult GetAlertListDirectDapper([FromBody] AlertSearchModel model)
{
try
{
string? connectionString = _context.Database.GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = "SELECT AlertID, AlertType, AlertStatus, Title, InsertDate, InsertUser, Detail FROM TblAlert";
if (model != null && !string.IsNullOrEmpty(model.Search))
{
query += " WHERE Title LIKE '%" + model.Search + "%' OR Detail LIKE '%" + model.Search + "%'";
}
var alerts = connection.Query<AlertDataModel>(query)
.Select(q => new AlertDataModel
{
AlertId = q.AlertId,
Title = q.Title,
Detail = q.Detail,
InsertUser = q.InsertUser
}).ToList();
return Ok(alerts);
}
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
[HttpPost("list-direct-interpolated")]
public IActionResult GetAlertListDirectInterpolation([FromBody] AlertSearchModel model)
{
try
{
// WARNING: This is intentionally vulnerable to SQL injection!
// DO NOT use this in production code!
var baseQuery = "SELECT AlertID, AlertType, AlertStatus, Title, InsertDate, InsertUser, Detail FROM TblAlert";
var result = model != null && !string.IsNullOrEmpty(model.Search)
? _context.Alerts.FromSqlInterpolated(
$"{baseQuery} WHERE Title LIKE '%{model.Search}%' OR Detail LIKE '%{model.Search}%'")
.ToList()
: _context.Alerts.FromSqlInterpolated($"{baseQuery}").ToList();
var alerts = result.Select(q => new AlertDataModel
{
AlertId = q.AlertId,
Title = q.Title,
Detail = q.Detail,
InsertUser = q.InsertUser
}).ToList();
return Ok(alerts);
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
[HttpPost("list-direct-raw-async")]
public async Task<IActionResult> GetAlertListDirectRawAsync([FromBody] AlertSearchModel model)
{
try
{
string query = "SELECT AlertID, AlertType, AlertStatus, Title, InsertDate, InsertUser, Detail FROM TblAlert";
if (model != null && !string.IsNullOrEmpty(model.Search))
{
query += " WHERE Title LIKE '%{0}%'";
await _context.Database.ExecuteSqlRawAsync(query, model.Search); // no sql injection
}
else
{
await _context.Database.ExecuteSqlRawAsync(query);
}
return Ok(new { message = "Query executed successfully" });
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
[HttpPost("list-direct-raw-async-in")]
public async Task<IActionResult> UpdateAlertStatusDirectRawAsyncIn([FromBody] AlertSearchModel model)
{
try
{
// if the client provides more than one insert users, the IN and parametric version of ExecuteSqlRawAsync won't work!
string query = "UPDATE TblAlert SET AlertStatus = 0 WHERE InsertUser IN ({0})";
int rowsAffected = await _context.Database.ExecuteSqlRawAsync(query, string.Join(",", model.InsertUsers));
return Ok(new { message = "Query executed successfully: " + rowsAffected });
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
[HttpPost("list-direct-raw-async-direct")]
public async Task<IActionResult> UpdateAlertStatusDirectRawAsyncInDirect([FromBody] AlertSearchModel model)
{
try
{
if(String.IsNullOrEmpty(model.Search))
{
return NotFound(new {message = "Please provide non empty Search"});
}
string query = "UPDATE TblAlert SET AlertStatus = 0 WHERE InsertUser = {0}"; // works and I'm not talking about SQLi
//string query = "UPDATE TblAlert SET AlertStatus = 0 WHERE InsertUser = '{0}'"; // won't work and I'm not talking about SQLi
int rowsAffected = await _context.Database.ExecuteSqlRawAsync(query, model.Search);
return Ok(new { message = "Query executed successfully: " + rowsAffected });
}
catch (Exception ex)
{
return StatusCode(500, new { message = ex.Message });
}
}
}
}
model is below.
public class AlertSearchModel
{
public AlertSearchModel(){
InsertUsers = new List<string>();
}
public string? Search { get; set; }
public List<int>? Status { get; set; }
public List<int>? AlertType { get; set; }
public List<string> InsertUsers {get;set;}
}
