FromSqlRaw parameter from a List that is safe from sql injection

c# entity-framework-core

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?

1
3
1/15/2020 5:42:14 PM

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.

2
1/15/2020 5:39:20 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow