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.