CODEWITHSUNDEEP
.netentity-frameworksecuritymodel-view-controllersql-injection
Jacek
Jacek

Reputation: 12053

How to inject sql in sp_executesql

I wonder if is possible to attack this simple code by sql-injection.

        string name = Console.ReadLine();
        using (var db = new Db())
        {
            var result = db.Models.Where(p => p.Name == name);
            foreach (var item in result)
                Console.WriteLine(item.Name);
        }

EntityFrameork to execute this command uses sp_executesql stored procedure where name is parameter.

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Models] AS [Extent1]
    WHERE ([Extent1].[Name] = @p__linq__0) OR 
    (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))'
    ,N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'jacek'

My questions are:
1) Does this solution give 100% of defence again SQL-injection?
2) If no, what user should enter into name var to list all entries from table Models(two columns: int id, and string name)

Upvotes: 1

Views: 408

Answers (1)

SilverlightFox
SilverlightFox

Reputation: 33538

Yes, Entity Framework protects against SQLi in much the same way as parameterised queries.

In fact as you can see, EF generates a parameterised query for you, and passes the value in at SQL level.

As said, a simple test is to try a string containing a single quote and look at the dynamic SQL that is generated.

Upvotes: 2

Related Questions