43

I'm aware that SQL injection is rather dangerous. Now in my C# code I compose parameterized queries with SqlCommand class:

SqlCommand command = ...;
command.CommandText = "SELECT * FROM Jobs WHERE JobId = @JobId;";
command.Parameters.Add("@JobId", SqlDbType.UniqueIdentifier ).Value = actualGuid;
command.ExecuteNonQuery();

Will this automatically make my code immune to SQL injection? Do I have to do something extra?

sharptooth
  • 167,383
  • 100
  • 513
  • 979

5 Answers5

30

I'd say for your particular, and probably canonical, example for parametrized queries, yes it is sufficient.

However, people sometimes write code like this

cmd.CommandText = string.Format("SELECT * FROM {0} WHERE col = @col;", tableName);
cmd.Parameters.Add("@col", ...);

because there is simply no way to pass the tablename itself as a parameter and the desire to do exists sometimes - misguided or not. It seems it is then often overlooked, that tableName (unless maybe only read from a set of static/constant values that do not derive from any input) indeed allows for SQL injection.

Christian.K
  • 47,778
  • 10
  • 99
  • 143
19

According to the Note on this MSDN Article, "Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL."

So I believe you are safe against SQL Injection. There might be some logical risks when using Identifiers like Idendity Values in your URLs but this is another story.

Stephan B
  • 3,671
  • 20
  • 33
8

SQL Injection is mostly dependent on execution of dynamic SQL. In other words, SQL statements constructed by the concatenation of SQL with user-entered values.

To avoid SQL Injection completely,

Protecting yourself against SQL injection attacks is not very difficult. Applications that are immune to SQL injection attacks validate and sanitize all user input, never use dynamic SQL, execute using an account with few privileges, hash or encrypt their secrets, and present error messages that reveal little if no useful information to the hacker. By following a multi-layered approach to prevention you can be assured that if one defense is circumvented, you will still be protected.

From MSDN

GoodGood
  • 93
  • 3
1

Using SqlCommand a very good practice and as long as you don't concatenate SQL strings anywhere (including inside any stored procedures you call -- i.e. avoid dynamic SQL), you will be immune from SQL injection attacks.

Jack Edmonds
  • 31,931
  • 18
  • 65
  • 77
-2

You are not immune to SQL injection if you use dynamic sql, even if you are passing it through parameters. Too bad SQL Server doesn't have a built in function to sanitize parameters

Webcraft
  • 11
  • 1
  • 4
    Citation? Above-referenced MSDN articles appear to contradict your answer. – Conrad Jun 22 '17 at 15:41
  • 1
    With dynamic queries, If you are not sanitizing the parameters before you send them over to the query, you can easily insert a sql injection through the parameter. Try it yourself. – Webcraft Jun 23 '17 at 17:27