FromSql(): Query string building from string list to prevent injection

.net-core c# entity-framework-core sql-injection sql-server

Question

I'm building an SQL search in EF Core. Microsoft recommends you do not concat string because it leaves the application vulnerable to SQL injection as detailed in Microsoft Documentation: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql.

Always use parameterization for raw SQL queries: In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. APIs that accept a raw SQL string such as FromSql and ExecuteSqlCommand allow values to be easily passed as parameters. Overloads of FromSql and ExecuteSqlCommand that accept FormattableString also allow using string interpolation syntaxt in a way that helps protect against SQL injection attacks.

If you are using string concatenation or interpolation to dynamically build any part of the query string, or passing user input to statements or stored procedures that can execute those inputs as dynamic SQL, then you are responsible for validating any input to protect against SQL injection attacks.

The information that's stored in this database is not sensitive, but obviously I would not like to leave the database vulnerable.

I have a param List<string> searchTerms that I need to iterate through and build a query based this list.

I'm going to and the strings together with my SQL query, but I can only see how to do this with concatenation. Right now my code looks like this.

var query = String.Format("SELECT ... where MySqlField like '%{0}%'", searchTerm[0]);

for (int i = 1; i < searchTerm.Count(); i++)
{
    query += String.Format(" and MySqlField like '%{0}%'", searchTerm[i]);
}

var results = context.MySqlTable.FromSql(query);

Even though I'm using interpolation, would extra validation be enough here? Am I missing anything?

Is there a linq query that can do the same thing with a list?

1
0
4/15/2019 10:42:03 PM

Accepted Answer

You're code should be good enough with a bit of modification:

var query = String.Format("SELECT ... where 1=1 ");

for (int i = 0; i < searchTerm.Count(); i++)
{

    query += $" and MySqlField like '%'+{{{i}}}+'%'";
}

var results = context.MySqlTable.FromSql(query, searchTerm.ToArray());
0
4/15/2019 10:02:43 PM

Popular Answer

There is few options:

  1. Pass your values in XML (or JSON if using newer SQL Server) and then write static XML/JSON query.

  2. create a temporary table, insert all search values in a temp table and then execute static query.



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