Skip to content
Advertisement

FromSqlRaw injection EF Core 3.0

I am wondering how safe the fromSqlRaw method is. I am doing the following in my code, where the person id is a parameter from the method itself:

string sql = String.Format("SELECT * FROM [users].[user] WHERE Id LIKE {0}", id)

var list = this.context.Person.FromSqlRaw<Person>(sql).ToList();

Is this code safe to SQL injection? And is there other security vulnerabilities that I should know of when using this?

Advertisement

Answer

Use proper parametrization for your input.

After clarifications in comments, it seems that your parameter is user-input string, this is a wide door opened for injection attacks.

Usually, you can create a SqlCommand, and provide some SqlParameter in it.

In EFCore, FromSqlRaw and FromSqlInterpolated (in 3.0, replacement for FromSql in EFCore < 3.0) allow you to shorten this syntax, see the documentation.

string sql = "SELECT * FROM [users].[user] WHERE Id LIKE {0}"
var list = this.context.Person.FromSqlRaw<Person>(sql, "42")

Note that this looks very similar to what you did in the question… But the difference is clearly emphasized in the documentation:

Warning

Always use parameterization for raw SQL queries

When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. In addition to validating that such values don’t contain invalid characters, always use parameterization which sends the values separate from the SQL text.

In particular, never pass a concatenated or interpolated string ($””) with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

Indeed, in your case, the string was first interpolated as a string (without any sanity-check), then executed as-is.

FromSqlRaw had no idea that the “Id” part was coming from a parameter.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement