0

I need to (re)develop a tool for the users of the application I work on, which is in ASP.NET 4.5.2 / MVC 5.2.3 and uses Entity Framework 6.

This tool is supposed to allow authorized users to edit SQL queries, and lower-priviledged ones to run them as well. I'm worried about the security of such a tool, even if the web application should only be accessible as an intranet.

Any given query is only allowed to be a SELECT and can have JOINs on multiple tables.

Is there any EF6 specific function that allows me to check or even restrict a sql string to only do one SELECT?
Should I have a specific user inside the database that's only allowed to do SELECTs and use a separate connection for those queries as this user?
Is there anything else about the security of such a tool I might have overlooked?

I know this tool is asinine and should not even exist, but I'm not in charge of the decisions, and we're planning to remove that tool as soon as we can.

Eregrith
  • 4,263
  • 18
  • 39
  • 1
    I'd certainly restrict the users to read only at database level. This is just good management anyway! – nik0lai May 29 '15 at 13:07

1 Answers1

0

I have an idea. You can define the available entities and properties to construct such query. For example, for the Customer entity, the user can choose: Name, Surname, Orders.Items.Name, Orders.OrderDate (navigation properties).

The user sends the parameters as string to the server, which converts the parameters to lambda expression (using lambda expressions runtime constructors) and then attach it in a query, like Customers.Select(selectExp).ToList()

Does that help you?

Fabio
  • 11,892
  • 1
  • 25
  • 41
  • Sadly, not really. This is almost what we have in mind for the future of this tool, being able to select entities, joined entities, and properties of these entities; all as a graphical, easy and nice web gui. The main problem is the needs from the business guys are not fixed, not well defined and change often, and we don't have much time to spend on that well-thought tool. The only thing we can afford is awful ugly direct queries from the users that I want as secure as can be. – Eregrith May 29 '15 at 13:29
  • As long as you lock the user account down to read only you should be fine. You could always search for keywords in the string that are restricted as a bit of a safe guard, although as you already know injection is an issue but doesn't seem like you have much choice! – nik0lai May 29 '15 at 13:34
  • I agreed with @nik0lias You can search for denied sql structions in the final query, but it will never be secure because you can't imagine all possible commands. Lock the user account to read only is a good choice in my humble opinion – Fabio May 29 '15 at 13:37