How does Command.Parameters.Add() prevent SQL injection? C#

Question

I have now read a lot about SQL injection and trying to prevent it. I've come across several articles about how and why to use

Command.Parameters.Add()

like explained here.

But I am still looking for an answer how and why it is preventing SQL injection.

MSDN doesn't have very much information about this.

Here is a example how I use it:

SqlCommand myCommand = new SqlCommand("SELECT * FROM table WHERE name = @name ", myConnection);
myCommand.Parameters.Add("@name", SqlDbType.NVarChar, 20).Value = "MaMu2";

Is my code even safe towards SQL injection, or am I wrong?

EDIT: main question is: how does it work internally?

Answer 1

The short version: Basically, the sql engine tearts sql parameters as placeholder for values.
This means that even if your @Name parameter would be name';drop table tableName;-- you end up with a query like this:

SELECT * FROM table WHERE Name = 'name'';drop table tableName;--'

As apposed to if you would have concatenate strings to create the sql - that would create a query like this:

SELECT * FROM table WHERE Name = 'name';drop table tableName;--

As you can see, the first query is safe, while the second one is not. It allows you to add harmful code to your sql.

While if you are using parameters, your harmful code will not be treated as code.
Important note: If you are sending parameters to a stored procedure that use these parameters to create and run dynamic sql, you are still not safe - since it is concatenating strings inside the stored procedure.

As a side note, some database engines such as oracle will allow only one command in each execute, but can still be hacked using name' OR 1=1 that will create

SELECT * FROM table WHERE Name = 'name' OR 1=1