Does using parameterized SqlCommand make my program immune to SQL injection?

Question

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?

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 4

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.

View more solutions

Comments

[Conrad]

Citation? Above-referenced MSDN articles appear to contradict your answer.

[Webcraft]

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.