Iain Buchanan
Buchanan.dev

Buchanan.dev

SQL Injection is alive and well

Photo by Mick Haupt on Unsplash

SQL Injection is alive and well

Iain Buchanan's photo
Iain Buchanan
·Jul 19, 2022·

1 min read

Table of contents

  • Fixing it

Any mid-level+ developer should be aware of SQL injection, and any production web app written in the last few years should use some simple tools to avoid it.

And yet I just found a .NET EF Core project, in production, using this:

string keywordSearchSql = "SELECT FROM ... INNER JOIN ...";

//Start generate where conditions
List<string> whereConditions = new List<string>();
if (!string.IsNullOrEmpty(request.Keywords))
    whereConditions.Add($@"t.Description like '%{request.Keywords}%'");
// after much more of the same sort of string manipulation:
keywordSearchSql = keywordSearchSql + "WHERE " + String.Join(" AND ", whereConditions) + " ";
// and finally
_context.MyTable.FromSqlRaw(keywordSearchSql);

Yes, it's real. I've changed a few names and simplified the code, but obviously if we searched for a keyword such as '; DROP TABLE MyTable then goodbye table, hello backups.

Fixing it

Unfortunately the query is quite large and doesn't map to EF Core easily, there are no unit tests, no integration tests, and no UAT test scripts, so it's not as simple as rewriting the queries (the joy of maintenance!).

If I had the choice I would do something like this:

_context.MyTable.Where(t => EF.Functions.Like(t.Description, "%request.Keywords%"));

A secondary option would be to keep .FromSqlRaw and parameterize the query as seen in the docs:

    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)

but again in my case I need to unwind some complex function calls. It's never trivial.

 
Share this