lundi 13 février 2017

.NET / SQL Server ORMs and SQL Injection

Vote count: 0

There are already lots of questions related to .Net and SQL injection, but I haven't found any that adequately address this particular concern of mine, so please bear with me :)

I understand that Entity Framework and other modern ORMs do a fine job preventing SQL injection attacks, so long as they are used correctly. But here is what my team cannot get past - the use of such a framework requires the very existence of a login/user in SQL Server with permission to execute arbitrary SQL statements.

So even if our app "does the right thing" with respect to its ORM usage, there's no guarantee that the underlying login won't be repurposed and misused elsewhere, and not necessarily with malicious intent, but sometimes just inexperience and/or lack of understanding of the implications. Yes, we need to adequately secure and control our connection strings to prevent that from happening, but that ultimately ends up being just a policy that everyone must learn, understand, and remember to follow, rather than a hard constraint enforced by the system. It's not difficult to imagine this breaking down occasionally on large, fast-moving teams with the usual rate of turnover.

So the data access strategy we've employed for years is relatively vanilla ADO.Net that only calls stored procedures. In the database we add our connection string users to the db_denydatareader and db_denydatawriter roles, so the only thing they can do is call stored procedures they have been explicitly granted execute permissions to. Even if the login does find its way into uses we didn't initially design/plan for, there is no chance of it just being able to do "whatever" in the database (barring unvalidated dynamic SQL within the stored procedures themselves, of course).

So my question to everyone using ORMs, and in particular the very popular Entity Framework to generate SQL queries for you in the application layer - how can we reconcile allowing this vulnerability in our production environments, with what has been #1 on the OWASP Top 10 list forever? Is there some other technique we can use to enjoy the benefits of these frameworks, while still being able to sleep at night without fear of our employer/customers ending up in the news as the next victim of a high-profile "hack"?

The reason I'm asking, given that we're otherwise happy with our current approach, is there is one frequently-recurring scenario where it would be most helpful to have an ORM's ability to dynamically generate queries in the application layer - large data lists and grids with user-defined filters, sorting, and paging. Handling this in stored-procedure-only apps tends to result in overly complicated and situation-specific code (i.e. not very reusable) and/or poor performance when working with larger data sets.

asked 43 secs ago

Let's block ads! (Why?)

.NET / SQL Server ORMs and SQL Injection

Aucun commentaire:

Enregistrer un commentaire