Ayende @ Rahien

Hi!
My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,968 | Comments: 44,484

filter by tags archive

Chasing the SQL Injection that never was


So, I am sitting there quietly trying to get EF Prof to work in a way that I actually like, when all of a sudden I realize that I am missing something very important, I can’t see the generated queries parameters in the profiler.

Looking closely, I started investigating what appear to be a possible SQL injection issue with EF. My issue was that this query:

entities.Posts.Where(x=>x.Title == “hello”)

Generated the following SQL:

SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent1].[Text] AS [Text],
[Extent1].[PostedAt] AS [PostedAt],
[Extent1].[BlogId] AS [BlogId],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[Posts] AS [Extent1]
WHERE N'hello' = [Extent1].[Title]

It literally drove me crazy. Eventually I tried this query:

var hello = "hello";
entities.Posts.Where(x=>x.Title==hello);

Which generated:

SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent1].[Text] AS [Text],
[Extent1].[PostedAt] AS [PostedAt],
[Extent1].[BlogId] AS [BlogId],
[Extent1].[UserId] AS [UserId]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[Title] = @p__linq__1

This was more like it.

It seems (and Julie Lerman confirmed it) that EF is sticking constant expressions directly into the SQL, and treating parameters differently.

I am not quite sure why, but from security standpoint, it is obviously not a problem if it does so for constants. It have a lot less hair now, though.


Comments

Dmitry

Yes, that is normal behavior for EF.

Remco Ros

So.. would there be a special reason for this design?

Rob Conery

Yep - Linq to Sql does the same thing. If it sees a ConstantExpression it just puts the value straight in with the thinking that it was set programmatically. So with our NHibernate stuff we did the other day where we set the CategoryID==33, it would put 33 in the query.

A bit weird when you consider the mandate to scrub everything, but at the same time I think it's generally OK.

Ayende Rahien

Rob,

NHibernate does not do it that way, though.

It makes everything a parameter

Frank

If the constant parameter contains a single quote, I guess it will nicely escape it in the SQL query itself.

If something is constant, then it is better to actually use it as a constant in the SQL query itself, instead of a parameter. When a constant is used, the statistics of the column will be included in deciding on which path to take. In case of a parameter, only the index selectivity will be used in deciding on the query plan.

Dmitry

I have done a lot of logging for Linq-to-SQL and it does not do this. In fact, Linq-to-Entities is the only ORM Linq provider I have seen that does that.

I think it is a bad decision because you will get different execution plans for different length constants.

P
P

So everything rob said was wrong?...

Alex James

I learned of this distinction for the first time while showing some Generated SQL to some SQL Server MVPs at the MVP summit in 2007!

A mild panic attack followed. But all is well that ends well.

I personally think both inlining or using a parameter for constants is fine.

Alex

Ayende Rahien

Alex,

Yes, it isn't a major issue, although I think that I can get it to generate a SQL injection if I was using MySQL.

It is not much of an injection, though, if it has to be compiled to get there.

Do you have any idea what was the reasons for this decision?

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. Career planning (6):
    24 Jul 2015 - The immortal choices aren't
  2. Production postmortem (4):
    23 Jul 2015 - The case of the native memory leak
  3. API Design (7):
    20 Jul 2015 - We’ll let the users sort it out
  4. What is new in RavenDB 3.5 (3):
    15 Jul 2015 - Exploring data in the dark
  5. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats