String.Format in Database.SQLQuery command and SQL Injection

Question

I have to follow rule that stored procedure has to be executed in "parameterized way". I believe that its about SQL Injection protection. I want to know if following code can be considered as "parameterized execution" and is SQL Injection protected.

return Database.SqlQuery<Program>(
              String.Format("usp_GetPrograms {0},{1},{2},{3}", 
                              pID, pDisplayStart, pDisplayLength, pSearchString)
    ).ToList();

Otherway i can rewrite it to something like that

context.Database.SqlQuery<myEntityType>(
    "mySpName @param1, @param2, @param3",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    new SqlParameter("param3", param3)
);

Iam asking because the program is complete and tested and ready to production and i dont want to rewrite the code and risk that something will broke.

What do you think about these two approaches ?

Additional question: Is SqlParameter() necessary according to

https://msdn.microsoft.com/en-US/data/jj592907

"Loading entities from stored procedures" section ?

Additional question2: How can i pass Nullable parameter ?

Andrey Korneyev · Accepted Answer · 2015-07-09 11:25:01Z

3

First attempt you've show has nothing to do with parameterized queries. It is simply concatenated query, and it is defenseless against sql injection.

You have to use second approach you've shown if you really care about sql injection.

answered Jul 9, 2015 at 11:25

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

6 Comments

Muflix Over a year ago

additional question is, can i send somehow null or default to the SqlParameter() function ?

Andrey Korneyev Over a year ago

@Muflix you can use DBNull.Value to pass null into stored procedure.

Muflix Over a year ago

Thank you Andy, but how can i insert DBNull.Value into existing parameter which is for example string ?

Muflix Over a year ago

I tried ? operator but it does not work :/ new SqlParameter("p1", ( ID == null ? ID : DBNull.Value))

Andrey Korneyev Over a year ago

@Muflix you can't use ?: operator in this case since it is returning objects of different types from conditional branches. But you can use ?? operator in this case. Something like new SqlParameter("p1", (object)ID ?? DBNull.Value)

|

Collectives™ on Stack Overflow

String.Format in Database.SQLQuery command and SQL Injection

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related