CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-frameworksql-injection
Marco
Marco

Reputation: 71

SQL injection with Entity Framework Database.SqlQuery

In my project I have defined a stored procedure with a example code below:

CREATE PROCEDURE [dbo].[Stored]
    @ParameterA AS varchar(128),
    @ParameterB AS varchar(128),
    @ParameterC AS varchar(400)
AS
BEGIN
    DECLARE @query AS Varchar(MAX)

    SET @query = 'SELECT *
                  FROM Table  
                  WHERE A = '''+ @ParameterA + ''

    IF @ParameterB = 'B'
    BEGIN
        SET @query = @query + ' AND C=''' + @ParameterC + ''
    END 

    EXECUTE sp_executesql @query
END

I call this procedure with Entity Framework through the following code:

DBContext.Database.SqlQuery<Object>("Stored",
new SqlParameter("@p0", Param0),
new SqlParameter("@p1", Param1),
new SqlParameter("@p2", Param2)).ToList();

If I call a stored procedure with the string below, I generate a SQL injection:

Param2 = "ABC' ; DROP TABLE Table2"

How can I prevent this with Entity Framework?

Upvotes: 1

Views: 4474

Answers (2)

nvoigt
nvoigt

Reputation: 77294

You cannot

The underlying SQL procedure is faulty and a security nightmare. There is no way you can repair that on the layer on top of it. You are doing the best you can in EntityFramework, but it's still unsafe. You need to repair the problem (SQL proc) and not apply band aids to the layer using it.

sp_executesql seems to be a good starting point for a procedure that needs to have dynamic SQL and bind parameters.

Upvotes: 1

h.munawar
h.munawar

Reputation: 66

you are creating a dynamic query, where you are concatenating parameters. this is causing issue.

do not use dynamic query, or validate parameters (if it contains any keywords or characters)

you can also rewrite your query into IF-ELSE structure on basis of parameters, so you do not need dynamic query.

Upvotes: 0

Related Questions