次の方法で共有

Avoiding Query Injection Attacks with the EF

  • [アーティクル]

In a recent blog post, Julie Lerman brought up some really important topics around parameterized queries and query injection (commonly called SQL injection) attacks. This is a topic that we in DP at Microsoft have some experience with ;-) and gave a lot of consideration to while we were designing the entity framework. Here are a few thoughts which I hope will help promote understanding and the creation of secure, efficient solutions with the Entity Framework:

The first thing to realize about the design for all of this is that we have attempted to make LINQ to Entities and Entity SQL behave very much like SQL commands do with “classic” ADO.Net. That is, it is possible to “hard code” values into a query and these are not translated into parameters (but if they are truly hard coded then there isn’t any risk of an injection attack from them) while things which are stored in variables are parameterized in the eventual SQL queries executed against the database so that they are protected from injection attacks.

In Julie’s initial example working with LINQ, she had the following query

var query = from c in context.Customers where c.lastname=='Smith' select c;

and was surprised that the generated SQL didn’t use a parameter for Smith, but because the value was hard coded in the query and couldn’t vary, no parameterization was needed and there was no risk of an injection attack. When she changed the code to

string myVar="Smith";
var query = from c in context.Customers where c.lastname== myvar select c;

the generated SQL ended up parameterized because LINQ to Entities detects the variable reference and parameterizes it. The underlying point here is that any kind of query that is sent to the server is potentially open to an injection attack which is why I use the term Query Injection rather than SQL Injection because we’re not necessarily talking about just SQL.

As Julie goes on to point out, there are similar things which you must take into account with Entity SQL queries. If you build an Entity SQL query string on the fly in your program, then you must be very careful about the way it is constructed—especially with regard to any user input that becomes part of the query. You can, of course, use parameters in your Entity SQL queries, and they will make their way down into the generated SQL used when talking to the database so they are safe from injection, but if you need, for instance, to determine a sort order dynamically (something that can’t be done with a parameter), then you must be very careful about the possibility that you might take a string from the user and add it to your query. Yes, the strings involved are Entity SQL rather than regular SQL so some naïve hackers might have more trouble breaking your system since their initial attacks may not work, but the underlying vulnerability is still there and hackers won’t stay ignorant for long.

The good news is that the query builder methods we put on ObjectQuery<T> are designed to facilitate just these dynamic query building scenarios in a safer way. Rather than fully creating your own Entity SQL string and working through all of the validation and careful composition rules yourself, you can build up an EntitySQL string by calling a series of builder methods to add filters, sorting, projection, etc. and the methods do their best to protect you from common pitfalls. They also support adding query parameters for those bits of input which can be parameterized.

Summary: You must be just as vigilant to protect EF queries from injection attacks as you have always been, but the system is designed to help you write secure code while still being as efficient as possible (no extra parameterization when you have hard coded values, Entity SQL query builder methods, etc.).

- Danny

Comments

  • Anonymous
    February 08, 2009
    PingBack from http://www.clickandsolve.com/?p=4854

  • Anonymous
    February 08, 2009
    Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Anonymous
    April 28, 2009
    Entity Framework sigue siendo un desconocido a&uacute;n para muchos. Quiz&aacute;s la versi&oacute;n