SLD Injection, it’s a thing

Reading Time: 4 minutes

I’m in the middle of preparing a session about security, and one topic you regularly bump into when thinking about security and writing code is SQL Injection. Although it is the year 2016, there are still people writing code which is vulnerable against SQL Injection.

But I’m not going to focus on SQL Injection here, I’m going to take it one step further. If you want to read up on SQL Injection, Bing/Google is your friend. Or just take a look at good old Bobby Tables.

People often think: “It’s called SQL Injection, but I’m not using SQL here, so I’m safe.”

Wrong.

From the moment you’re using any type of query or filtering language construct, you can introduce some form of SQL Injection in your code:

Another fun example uses the System.Linq.Dynamic NuGet package. This little gem allows you to use string expressions to filter on IEnumerable’s. And because it makes our developer life easier, we could use this to filter data in a Web API, for example. Take a look at this piece of code:

Yes, I know this is not the best code because you’re fetching all users from the database and then you reduce the result set, but bear with me. I’m making a statement here. Besides, this kind of code is still being written in production as well!

As you can see, you’re applying a filter to a list of User instances using System.Linq.Dynamic. That filter could be, for example, this:

This would reduce the results down to all users with an odd ID and where the surname starts with “n”. But someone could circumvent our fixed “odd ID” filter part, which would ignore our little security measure, by changing the filter a bit:

This filter value would make fullFilter look like this:

Because we’re missing parentheses around the user filter part, the ID filter will be ignored. One solution could be to add these parentheses.

Actually, you could solve that problem by moving the fixed filter part. That way, no matter what your user is requesting, he or she can’t get by the fixed filter anymore:

And to complete the story, you can look at using parameterized queries, which is also supported by System.Linq.Dynamic. Using parameters in your filter would take away some of the filtering power from the consumer of your API, but you could reintroduce that power by defining your own filters. This example shows a basic attempt at implementing this:

 

  • Interesting topic. It seems wildly complicated though creating your own filter sets. Wouldn’t it be better to implement a few most common query patterns as plain SQL queries, rather than try to create the most flexible query builder? I guess it depends on the requirements though.

    It’s just that I often see people attempting this kind of flexible query builder without checking if it is required.

    • Wesley

      Sure! That should be the first rule: if you don’t need it, don’t build it.
      By the way, your comment reminds me of the age where we would only have a BaseRepository and just inject a dependency on IBaseRepository, instead of creating specific contracts. AKA, the pre-SOLID age 😉

      But in the presented case, I’m talking about a Web API (though only briefly). And a Web API could offer advanced filtering to end-users, which makes it difficult to translate that filter into custom queries. That’s where a lib like System.Linq.Dynamic comes in handy. And of course, the code in this sample is far from complete: if you search for “firstName eq \”James Earl\””, it would crash because the filter is being split on spaces and doesn’t have a decent tokenization algorithm applied to it.

      • Yeah, I can see how that can be useful. However, I usually follwo a bit of a different approach for this:

        I just accept a query-object as a POST-parameter (I know it’s not pure REST, but I don’t care 🙂 ). From that query-object I then concatenate a SQL string and execute it with Dapper or a different MicroORM. It’s probably not as flexible as your approach, but again it depends on the reqs.