Worst Idea in .NET Core - Entity Framework Migrations?

ASP.NET Core is generally a secure framework, where the makers of the framework have made it so some security features are turned on by default, like encoding to prevent XSS, or at the very least is easy to put in, such as anti-CSRF tokens. It's not perfect, as you can see in my blog post about password tracking, but it's good and keeps getting better. There is one feature, though, that is pretty inexcusable from a security perspective. And unlike the password tracking issue, which seems to have grown out of laziness, Microsoft intentionally put this feature in. As you may have guessed from the title, I'm referring to Entity Framework Migrations, specifically the functionality in which a website can update its own database.

Layered Security

Before I get too far into why EF Migrations are a problem, I need to discuss a term in security called layered security. Layered security refers to the notion that you should add multiple defenses that protect you if a hacker gets through the first layer of protections. For instance, all of your production servers sit on a different network that your development machines (right? please?) because if someone on your team falls for a phishing attack and installs malware on your company network, hackers will need to get through another layer of security to get to your production servers.

Applying this idea to websites and databases, a website should do everything it can to prevent hackers from getting into your database (using a technique called SQL injection), but a website developer should assume that a hacker can get through and exploit the data connection to do more than the developer intended. To accomplish this, a well-designed website would utilize database connections that only possess the minimum number of connections needed to do the job at hand. Specifically, a typical database connection should only have permissions to read and write data to the database. (And for sites that need extra security, different pages would use different connections to further limit the amount of damage a hacker could do with a hijacked database connection.)

Entity Framework and Broken Security Layers

As previously mentioned, a well-designed database connection would only need the minimum number of permissions needed to make the website function, typically only read/write permissions on relevant tables. But according to Microsoft's own documentation, the intended usage of the database connection is much more than that (emphasis mine):

Visual Studio doesn't do anything with the database during the deployment process while it is copying your project to the destination server. When you run the deployed application and it accesses the database for the first time after deployment, Code First checks if the database matches the data model. If there's a mismatch, Code First automatically creates the database (if it doesn't exist yet) or updates the database schema to the latest version (if a database exists but doesn't match the model). If the application implements a Migrations Seed method, the method runs after the database is created or the schema is updated.

So, Microsoft encourages you to ask the website to do more than just read and write data, which means if a hacker hijacks your connection, he/she has access to much more than just reading and writing your data on just the one database. No security professional worth their pay would see this and think "that's not a big deal, move along".

Security Protections in Entity Framework

This begs the question: why would Microsoft allow a product to go to market with such an obvious security hole? While I haven"t talked to the EF team about this, my assumption is that the EF team put faith in the security behind SQL Parameters. For those of us who were around for ADO.NET, SQL Parameters are there to help SQL Server differentiate between commands and data. The theory is that as long as any user-supplied input is added to a parameter rather than the command text, no user-supplied commands could ever be executed in the database. So far this has proved true. And since Entity Framework uses SQL Parameters behind the scenes, Entity Framework should be safe regardless of the permissions you give to the connection. In order for this to be a problem, Microsoft would need to add the ability to bypass safety measures to make unsafe calls to the database for this to really be a problem.

Apparently, at this point, someone at Microsoft said "hold my beer".

Exploiting Overly-Permissive Connections

Entity Framework makes it easy to execute arbitrary queries against your database. This opens the door to any mistake you make in coding could lead to complete hacker takeover of your database. (Though in fairness to Microsoft, EF Core 3.0 includes several improvements that make it easier for developers to prevent SQL Injection attacks.) All it takes is one mistake, though, and hackers could go in and pull or delete whatever data they want, create a backup and move it off-site, or even create their own users so they have access regardless of whether you change the password of the user they've hijacked.

Preventing Attacks

Fortunately, there are multiple things that you can do to use Entity Framework (even use Entity Framework Migrations) and still be secure. First, things you can do to push database changes to production (or test environments):

• Instead of using EF Migrations to push database changes, use the Schema Compare functionality within Visual Studio or use Redgate's excellent SQL Compare tool.
• If you still want to use Migrations for whatever reason, you can use Powershell during your build process to deploy changes, then remove any permissions the website might have over making schema changes to your database.

As for writing your own SQL within your app, your best bet hasn't changed in the last 20 years or so — make sure you use SQL Parameters to pass in data. As mentioned earlier, .NET Core 3.0 has methods for you to do this safely, but I'd caution against these options for two reasons. One, you're hoping that all of your developers (both all the ones you have now plus all the ones you might hire in the future who might support this product) know the difference between the safe FromSqlInterpolated and the unsafe FromSqlRaw. By explicitly adding parameters you're increasing the likelihood that developers will continue making database calls safely. Two, assuming you’re doing periodic manual security code reviews on your product, you're going to save your reviewers time and headaches by making parameters explicit rather than using new functionality.

Good luck and code safely!