Entity Framework Core 2.0 FromSql and SQL Injection

Question

I need to know the correct way to handle SQL Injection when using the FromSQL command.

At runtime, I need to dynamically create a where statement. So I am using FromSql to create the SQL command. Now I know that using use string interpolation is the way to go. However, I need to step through a list of "Where Parameters" to generate the command. Simple enough to do;

foreach (var x in wp)
{
    if (!string.IsNullOrEmpty(results))
        results = $"{results} and {x.Field} = {x.Value}";
    if (string.IsNullOrEmpty(results))
       results = $"where {x.Field} = {x.Value}";
}

Problem is that this return a simple string and would not be string interpolation. How can I do this correctly?

Kris.J · Accepted Answer · 2018-10-25 04:04:00Z

1

Entityframework will parameterize your queries if you put it in the following format:

db.something.FromSql("SELECT * FROM yourTable WHERE AuthorId = {0}", id)

Is x.Field a form field that has a fixed number of possibilities? i.e. title, firstname etc. If so then something like the following:

        var sqlstring = new StringBuilder();
        var sqlp = new List<SqlParameter>();

        var i = 0;
        foreach (var x in wp)
        {
            var param = "@param" + i.ToString();
            if (i!=0)
            {
                sqlstring.Append($" AND {x.Field} = " + param);
                sqlp.Add(new SqlParameter(param, x.Value));
            }
            if (i==0)
            {
                sqlstring.Append($"WHERE {x.Field} = " + " @param" + i.ToString());
                sqlp.Add(new SqlParameter(param, x.Value));
            }
            i++;
        }

You'd then need to do something like this:

db.something.FromSql(sqlstring.ToString(), sqlp.ToArray())

Might be a better/cleaner way but that should work.

answered Oct 25, 2018 at 4:04

Kris.J

3681 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xaphann Over a year ago

Thanks! I basically came up with your solution just without the StringBuilder. The problem was that the IDE actually gave a SQL injection warning. Your solution does not

bbsimonbb · Accepted Answer · 2018-12-05 11:06:33Z

1

My solution to this problem is a VS extension, QueryFirst. QueryFirst generates a C# wrapper for sql that lives in a .sql file. As such, parameters are the only way to get data into your query and SQL injection is near impossible. There are numerous other advantages: you edit your sql in a real environment, it's constantly validated against your db, and using your query in your code is very simple.

answered Dec 5, 2018 at 11:06

bbsimonbb

29.3k18 gold badges92 silver badges129 bronze badges

1 Comment

Xaphann Over a year ago

I have moved from this problem but that looks exactly like what I wanted. Hope I can circle back and use this

Collectives™ on Stack Overflow

Entity Framework Core 2.0 FromSql and SQL Injection

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related