AddWithValue sql injection safe? Why? [duplicate]

Question

I hope this is the right place to ask, how does parameters.addwithvalue work? I am thinking in a way to protect against SQL injection? I have been looking quite a lot on Stackoverflow, and a lot of people say "//Against sql injection". I have been using it blindly, but now that I have to hand in a paper about my assignment I need to explain why it is protection. I have been trying to find something on MSDN, found this one:

SQL injection But it uses the parameters.add. I then read that they replace .Add with .AddWithValue, is that true? Any official on this then?

So basically, anyone better in searching for some official paperwork that it protect against SQL injection? Or can tell me how it works?

I am not trying to make you do my work, I just can't find it my self.

I am using it like this:

using (SqlConnection conn = new SqlConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
        conn.Open();
        String queryString = "DELETE FROM dbo.SecurityAccess WHERE Username = ' @Username ";
        cmd.CommandText = queryString;
        cmd.Parameters.AddWithValue("@Username", Username);
        cmd.ExecuteNonQuery();
}

Answer 1

From SQL Injection point of view using parameters is usually safe (subject to what you do with those parameters in the SQL...). Your example is safe. How one adds the parameters makes no difference from the SQL Ibjection point of view, but makes a lot of difference from ADO.Net and SQL performance point of view. AddWithValue is an anti-pattern because of performance problems related to parameter type and size. In your example the @UserName will be a parameter of type NVARCHAR, which will likely make the WHERE Username=@UserName predicate unsarg-able (will not use an index on Username). The execution result would be dreadful.

A potential solution to the datatype conversion is to use the explicit Add method instead of AddWithValue, which takes the datatype as second parameter. More details on this here.

For more details I urge you to read How Data Access Code Affects Database Performance.

Answer 2

In Short parameters allow for type safe and length checks on the data. Enabling a defense against SQL injection, they do not prohibit SQL injection completely you still need to check your inputs.

SO Answer on similar topic.

Good article explaining how parameters do not prevent SQL injection 100%

SQL Injection Example (Taken from MSDN:)

Consider what happens when a user types the following string in the SSN text box, which is expecting a Social Security number of the form nnn-nn-nnnn. ' ; DROP DATABASE pubs --

Using the input, the application executes the following dynamic SQL statement or stored procedure, which internally executes a similar SQL statement.

// Use dynamic SQL
SqlDataAdapter myCommand = new SqlDataAdapter(
          "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + 
          SSN.Text + "'", myConnection);

// Use stored procedures
SqlDataAdapter myCommand = new SqlDataAdapter(
                                "LoginStoredProcedure '" + 
                                 SSN.Text + "'", myConnection);

The developer's intention was that when the code runs, it inserts the user's input and generates a SQL the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'

However, the code inserts the user's malicious input and generates the following query.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''; DROP DATABASE pubs --'

Common ways to avoid Injection attacks.

•Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.

•Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.

•Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.

•Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user.