I’ve been teaching a lot more about SQL Injection lately (including blog posts). I’ve been doing this because, despite this being a 21 year-old problem with well defined solutions, we’re still dealing with it. Recently, while sitting in the speaker room at Techorama Netherlands (fantastic event, strongly recommended), I had the opportunity to spend a little time with Niko Neugebauer. I was freaking out because my demos were failing (fixed ’em finally). Niko was talking to me about the new Feature Restrictions and their effect on SQL Injection in SQL Server 2019. I didn’t know what he was talking about, so I had to look it up. Of course, top resource, Niko’s blog.
Feature Restrictions in SQL Server 2019
The Feature Restrictions in SQL Server 2019 are actually being added there from Azure SQL Database, where they already exist (Azure first, for so much). Microsoft has some documentation on the Feature Restrictions available here. Beyond that, and Niko’s blog, there isn’t much available on the internet yet (one exception, more on that in a minute).
The concept is simple. A new command has been created:
sp_add_feature_restrictionWith this you can disable, or re-enable, two different behaviors:
User information in error messages
Waitfor command
Why these two? Let’s take a minute and talk about SQL Injection.
SQL Injection Attacks
Traditionally, this is where a link to the XKCD cartoon of “Little Bobby Tables” should be. I’m going to let you look it up if you don’t know about it.
Instead, let’s talk about some of the common vectors of SQL Injection. Obviously, building and executing strings is the biggest issue. Appropriate use of parameters will do more to the fix the problem than almost any other step. However, it’s also enhanced by bad code on the front-end which doesn’t appropriately clean the data, inappropriate error handling, bad security, bad data isolation, and more.
The keys to the attack are to get back a few bits of information, usually in error messages in the case of a normal attack, or, through the use of the WAITFOR command in a blind attack (for more detail, I’m talking about this stuff at the PASS Summit). Getting error messages with information about the database makes it easier for me to hack your system (if I was evil). Knowing that I have a SQL Injection vector through the WAITFOR command helps me target appropriate systems (if I was evil).
So, what has Microsoft given us in the Feature Restrictions in SQL Server 2019?
Mitigation From the Feature Restrictions in SQL Server 2019
Thanks to the addition of these Feature Restrictions, we have the ability to better control some aspects of error handling directly from the database. Further, since most of us will never, ever, have a use for WAITFOR in production code, we can turn that bit off.
Therefore, with the inclusion of these two Feature Restrictions in SQL Server 2019, we get some SQL Injection mitigation.
However, it’s only mitigation, and not much of that.
Mild Mitigation
I really dislike SQL Injection fairly intensely. It’s such a well known problem with a whole bunch of easily implemented fixes. There really are no longer excuses for anyone, let alone major corporations with sophisticated IT departments, to be suffering from SQL Injection attacks in this day and age.
Therefore, anything that helps fix this, helps fix this. However, if you go and read this blog post by Solomon Rutzky, you’re going to find two things. First, someone who dislikes SQL Injection possibly more than I do. Second, a whole list of issues around just how mild, almost to the point of uselessness, the level of mitigation offered by the Feature Restrictions in SQL Server 2019 are.
Conclusion
There are two things that you absolutely must do to address SQL Injection: Parameterize your queries and clean your inputs. However, let’s face it. With all the code out there that is building and executing dynamic T-SQL, the chances of this happening are near zero. Therefore, some degree of mitigation is vital. Therefore, as much as I agree with Solomon on just how little this helps, I’m going to say that the Feature Restrictions in SQL Server 2019 are a good thing. Why? Because even a little bit of help is better than no help at all.
We should be screaming from the rooftops to fix the bloody code and architectures that have landed us in this fix. However, also asking for more of these kinds of mitigations from Microsoft and other vendors, may ultimately lead to at least radically reducing the problem.
UPDATE: Seems the RTM has disabled this functionality. For more follow the link below in the comments.
Hi Grant. Thanks for this perspective on “Feature Restrictions”. Just to be clear about the point that I was making in my post (the one linked to in the “Mild Mitigation” section), it’s not just that this feature offers so little. That, by itself, would be worthy of just a foot note or a short post to say, “Meh!”. The (main) problem that I have with this feature is that by offering so little, _and_ by there being a way to still carry out each type of attack even with this feature being enabled, it’s actually misleading and will cause people to not try so hard to fix existing bad code, or to report to management that such types of attacks are now prevented when in fact they certainly are not. Here are some scenarios that I fear will result from this “feature”:
1. Tech debt project to clean up old code to prevent SQL Injection will now be given a lower priority due to someone claiming that all they really need to do is enable this and then move on to other revenue generating projects. I’m sure there will be some SQL Server 2019 sales based on such a conversation, and mid-level managers who are neither tech-savvy nor good enough managers to ask their staff to evaluate such claims.
2. Someone enables this feature and reports to their supervisor / manager / tech lead that at least some attack vectors have been prevented, believing that they have been. Some time later such an attack occurs on their system (using one of the work-arounds I mentioned or one I haven’t thought of) and this person gets fired / demoted / written-up / something-not-good for claiming that this has been fixed).
3. SQL Server itself getting a bad reputation and devalued as a viable platform by folks burned by the above scenarios, or possibly other scenarios I haven’t thought of.
I completely agree that we could use some help in this area given how much Dynamic SQL is out there and how much of it is poorly written. BUT, “Feature Restrictions” is _not_ the answer we need. We would be better off to not have this feature and still be pushing those tech debt projects. Sure, there are some projects that cannot be cleaned up for a variety of reasons, but this feature isn’t going to truly help. All it it is doing, really, is taking time away from implementing an actual solution, such as expanding the existing permissions infrastructure.
In the end, this feature amounts to false advertising. I once interviewed someone for a DBA position and they claimed on their résumé to have experience with the newest version of SQL Server that had just been released. So, I asked them what they had done with it. Their answer: “got part-way through the install” (didn’t even complete the install!). Technically, that is experience on some level. More than people who never tried to install it. But that’s just splitting hairs / semantics. That’s not _actual_ experience. Similarly, “Error messages” are _not_ prevented: only _system_ error messages are. And “delay” attacks are _not_ prevented: only `WAITFOR` attacks are.
The other problem I have with “Feature Restrictions” is that it’s clearly new infrastructure (square peg) jammed into an existing system (round hole). It looks like permissions, but isn’t, and doesn’t show up like all other permissions do, and isn’t traceable like a `DENY`. This is now the second time in two releases that a new permissions “feature” has been added that sits outside of / was slapped on top of, the existing infrastructure. It almost feels like there’s a new group that doesn’t have a lot of experience with SQL Server that’s now trying to “update” security but not following the conventions of the existing system, and to a degree not even knowing how the existing system works (which is how we ended up with entirely unnecessary “trusted assemblies” in SQL Server 2017). This is a disturbing trend that does not serve SQL Server well in the long-run.
So, I agree that “some degree of mitigation is vital.” But, I would argue that this is one of the few cases where “a little bit of help” is actually _worse_ then “no help at all.” We need something, but this is not that something.
Take care,
Solomon…
Thanks for the comment. No arguments on my part. I agree. This is not the answer. Without actually addressing the code debt, I don’t think any answer will be adequate.
Hi Grant. Just to follow up on this, “Feature Restrictions” have been disabled as of the RTM release of SQL Server 2019. The system procs and view are still there, but executing the procs returns an error. I have this documented at the end of my post:
https://sqlquantumleap.com/2019/08/05/feature-restrictions-in-sql-server-2019-are-worse-than-useless-a-false-sense-of-security-and-wasted-opportunity/#update_20191028
Well darn. Can’t say I’m bummed, but I’m not excited. Thanks for the info.
I understand. I’m hoping that something along these lines comes back, implemented in a more reasonable manner. But for the moment, I just noticed that 2 weeks ago, on Nov. 25th, the Azure documentation for “Feature Restrictions” (i.e. the URL you linked to in your post) was fully removed. I linked to the 2 commits that removed the documentation at the end of my post (same URL as in my previous comment).