Question
I am having an issue where I need to perform a raw query using entity framework core that requires a list of strings as its parameter for a Where IN statement and I can get the query to work, but I am worried about SQL Injection attacks. My query will look something like this:
public static string BulkGetColumn3Query
{
get
{
return @"
SELECT Column1, Column2
FROM TABLE
WHERE column3 IN ({0})";
}
}
The method that calls it as I have it now looks something like this:
async Task<List<EntityObject>> GetModelObjectByColumn3(List<string> column3Values)
{
var column3ValuesString = string.Join(",", column3Values);
var query = BulkGetColumn3Query.Replace("{0}", column3ValuesString);
return await DbSetObject
.FromSqlRaw(query)
.AsNoTracking()
.ToListAsync();
}
What can I do to attempt to mitigate SQL injection attacks?
Accepted Answer
This is most definitely a SQL injection concern, because column3ValuesString ends up being sent to the DB directly.
Replace this with a parameterized query:
string BulkGetColumn3Query(List<string> column3Values) {
var selectList = string.Join(", ", column3Values.Select((_, i) => $"@p{i}"));
return $@"SELECT Column1, Column2
FROM TABLE
WHERE column3 IN ({selectList})";
}
async Task<List<EntityObject>> GetModelObjectByColumn3(List<string> column3Values)
{
var query = BulkGetColumn3Query(column3Values);
var sqlParameters = new List<SqlParameter>();
return await Trailers
.FromSqlRaw(query, column3Values.Select((val, i) => new SqlParameter($"@p{i}", val)))
.AsNoTracking()
.ToListAsync();
}
Let's say your list has three items: ["first", "second", "third"]. Then your query string is going to look like this:
SELECT Column1, Column2
FROM TABLE
WHERE column3 IN (@p0, @p1, @p2)
and the parameter list is going to be like this:
[ new SqlParameter("@p0", "first")
, new SqlParameter("@p1", "second")
, new SqlParameter("@p2", "third")]
Since all values are sent as parameters, the query is now safe.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow