SQL injection with Entity Framework Database.SqlQuery

Question

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?

I suggest you avoiding all form of string concatenation in SQL statements, even inside a stored procedure. Additionally you can sanitize input by filtering words so that those SQL keywords are removed in EF side before executing query. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented May 29, 2017 at 8:33
I think you cannot prevent such thing with EF itself. I would consider looking at the bigger picture msdn.microsoft.com/en-us/library/cc716760(v=vs.110).aspx — Edgaras
– Edgaras, Commented May 29, 2017 at 8:35
Why are you using such a stored procedure instead of letting EF generate a secure query ? If Table is mapped to an entity why don't you write an EF query to return it, using different Where() clauses as necessary? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 29, 2017 at 9:04
@Marco what are you trying to do? Why not write a simple context.Entities.Where(condition1) or context.Entities.Where(condition2) in LINQ? Or even var query=context.Entities.Where(condition1);query=query.Where(condition2) if you want to add multiple conditions dynamically? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 29, 2017 at 9:09

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 29, 2017 at 9:00

nvoigt

77.7k28 gold badges103 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marco Over a year ago

Thanks for your answer, but I can't rewrite my stored procedure. There is a library to prevent Sql Injections? Can I use a regular Expression to validate input parameter?

Panagiotis Kanavos Over a year ago

@Marco your stored procedure itself is the SQL injection. You'll have to drop it. No amount of hacking (that's what those "sanitization" libraries are) is going to make it secure. Why are you trying to use a stored procedure when you have EF? EF and LINQ already allow you to specify criteria dynamically

nvoigt Over a year ago

@Marco No. You can apply all kind of band aids to make sure your parameters do not contain SQL code, but in the end, that's an arms race. You will always be one step behind and constantly updating.

Marco Over a year ago

This is a sample. The real Stored Procedure works with a big dataset and the performance is better than EF query.

nvoigt Over a year ago

Whoever is in charge of that procedure, call it with D'Artagnan as last name and file a bug against his or her bugtracker.

h.munawar · Accepted Answer · 2017-05-29 08:38:38Z

0

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.

answered May 29, 2017 at 8:38

h.munawar

664 bronze badges

Collectives™ on Stack Overflow

SQL injection with Entity Framework Database.SqlQuery

2 Answers 2

You cannot

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You cannot

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related