Programming Entity Framework

Last week, I was demoing some basic Entity Framework queries to a client and explaining that the store queries are parameterized, alleviating one area of concern.

But when we looked at SQL Profiler the query was not parameterized. Why was this?

The query I had used was:

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

I had hard coded the string for lastname into the query which resulted in the following SQL:

SELECT TOP (1) 
[Extent1].[CustomerID] AS [CustomerID], 
[Extent1].[NameStyle] AS [NameStyle], 
[Extent1].[Title] AS [Title], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[MiddleName] AS [MiddleName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[Suffix] AS [Suffix], 
[Extent1].[CompanyName] AS [CompanyName], 
[Extent1].[SalesPerson] AS [SalesPerson], 
[Extent1].[EmailAddress] AS [EmailAddress], 
[Extent1].[Phone] AS [Phone], 
[Extent1].[PasswordHash] AS [PasswordHash], 
[Extent1].[PasswordSalt] AS [PasswordSalt], 
[Extent1].[rowguid] AS [rowguid], 
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE N'Smith' = [Extent1].[LastName]

If I used a variable instead, as in:

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

then, a parameterized query will be constructed:

exec sp_executesql N'SELECT TOP (1) 
[Extent1].[CustomerID] AS [CustomerID], 
 ...
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE [Extent1].[LastName] = @p__linq__2',
N'@p__linq__2 nvarchar(5)',@p__linq__2=N'Smith'

Notice the nvarchar(5) in the parameter list? This is an issue with how SqlClient generates queries for Entity Framework. If I had searched for "Lerman", that would be nvarchar(6) and SQL Server treats that as a completely different query in SQL's query cache than the one with nvarchar(5). This is a very different topic from this post, but I have hammered on it a bit and can find no way to trick SqlClient into generating reusable queries. Adam Machanic pointed this out to me initially last fall.

What about Entity SQL?

Although you can build parameterized ObjectQueries, a straight ESQL expression does not get realized as a parameterized query in the store.

When building the ESQL expression, there is no difference between a hardcoded parameter and a variable because in the end, you are still building a single string.

var esql = "select value c from AdventureWorksLTEntities.Customers as c where c.LastName = 'Smith'";

vs.

string myVar="Smith";
var esql = "select value c from AdventureWorksLTEntities.Customers as c where c.LastName = ' + myVar + "'";

The resulting store query is:

SELECT 
[Extent1].[CustomerID] AS [CustomerID], 
 ... 
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE [Extent1].[LastName] = 'Smith'

So then, what about SQL injection in this case? It's important to realize that the types of strings used to create a SQL Injection will create invalid Entity SQL. The expression won't even be parsable. For example, a classic sql injection appends "; SELECT * from LOGINS" to an input value perhaps in a text box.

When the query is built, the expression becomes

select value c from AdventureWorksLTEntities.Customers as c where c.LastName = 'Smith '; SELECT * FROM LOGINS WHERE firstname LIKE '%'

Remember that this is Entity SQL, not T-SQL. The expression is invalid (because of the semi-colon, not just the asterisk which can be replaced with field names)  and an EntitySqlException will be thrown during query compilation.

But you're not out of the woods.

What about an injection that looks like this:

The Entity SQL expression will end up as

select value c from AdventureWorksLTEntities.Customers as c where c.LastName = 'Smith ' or 'a' = 'a'

That is valid Entity SQL and parses to valid TSQL as

SELECT 
[Extent1].[CustomerID] AS [CustomerID], 
...
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE ([Extent1].[LastName] = 'Smith ') OR ('a' = 'a')

As with any application, validating user input is your first line of defense. With Entity Framework, you can also use a parameterized ObjectQuery or Entity Client queries to avoid this.

Entity SQL Injection Attacks?

There's one other possibility to consider. If someone has access to your conceptual model, can they inject Entity SQL syntax?

Entity SQL expressions cannot be combined the way SQL can. Therefore, if the user enters the following into the text box:

Smith; SELECT c.SocialSecurityNumber from MyEntities.Contacts as c

it will not result in valid Entity SQL.

Other than the example above with "Smith' OR 'a' = 'a", I can't think of way to take advantage of Entity SQL. If you had a nested query, there are more possibilities. Or if you were dynamically constructing the projection...perhaps. But why would a user be typing in "lastname"? They should be selecting fields from options provided by the application.

Either way, do consider parameterized queries for ObjectQuery (using ObjectParameters) or Entity Client (using EntityParameters).