ASP.NET Choosing Parameters.Add or Parameters.AddWithValue

If you are adding parameters to a SqlCommand object then you can use either Parameters.Add or Parameters.AddWithValue. The Parameters.Add syntax requires you to explicitly specify the DataType of the parameter you are passing, while the Parameters.AddWithValue syntax implicitly attempts to convert the parameters you pass in.

When choosing which method you want to use, you should be aware that although there is no difference in the functionality of either method, but Microsoft added Parameters.AddWithValue because overloads for Parameters.Add resulted in possible confusion of which specific overload was being used. I do find myself using Parameters.AddWithValue for its convenience and clarity, but the fact that you are leaving the datatype for the compiler to implicitly convert is not a good thing.

Some Reasons Why it is a Bad Idea to Implicitly Type Parameters:

Implicitly typing your parameters doesn’t affect your ASP.NET code as much as it affects your actual database.

Implicit Data Conversions Cause Data Errors

You can accidentally introduce data errors if you use the Parameters.AddWithValue syntax to send a date datatype to your database, then you are at the mercy of the database to identify how it will store your date value. This can lead to problems since there are numerous ways of entering dates: mm/dd/yyyy in North America, dd/mm/yyyy in the UK, and yyyy-mm-dd as an upcoming standard. The database will look at the date value of 1/2/2012 that you are sending and can handle the ’1′ as either the day or as the month.

Implicit Data Conversions Cause Invisible Performance Bottlenecks

As another example, if you pass a String datatype value to the database, then SQL Server defaults the String value to the NVarchar datatype rather than VarChar.  This can result in undesirable performance decreases if your database is expecting a Char or VarChar value and then needs to cast the NVarchar value from your application to a Char or VarChar value.

On a side note: if your database design uses a mix of NVarChar and VarChar columns, then you will see a significant performance hit on queries that join NVarchar table columns with Varchar table columns.

Also note that a disadvantage to NVarchar table columns is that you cannot use indexes on them if the NVarchar column is larger than 450 since the maximum index size is 900 bytes. I just ran a test of this on an instance of SQL Server 2008 and received the error: Changing a column data type results in an index that is too large.

Myth: Explicitly Declared DataTypes Help Against SQL Injection Attacks

Both Parameters.Add and Parameters.AddWithValue methods provide solid protection against SQL Injection attacks. The implicit type conversion of variables via Parameters.AddWithValue does not expose a system to SQL injection any more than the Parameters.AddWithValue method does. However, in my opinion every bit helps when it comes to hardening your code against attacks, so the less ambiguity there is in your code, the safer your code becomes.

Parameter Examples:

1) Using Parameters.Add

Below I’m giving a simple example of how to write a SQL parameter in a single line of code using Parameters.Add.

This is both best practice and convenient. The convenience comes from the statement taking just a single line of code. It is best practice because it 1) explicitly declares the type of data being sent to the database and 2) abstracts the SQL within a Stored Procedure

Dim aDateVariable As String = "1/2/2012"
commandObject.Connection = conn
commandObject.CommandType = CommandType.StoredProcedure
commandObject.Parameters.Add("@aDateParameter", SqlDbType.DateTime).Value = "Value for the Database"

2) Using Parameters.AddWithValue

Here is an example of using Parameters.AddWithValue. As you can see the datatype is not declared and the database needs to identify and convert the parameter when it is received. In terms of security is is good that the statement uses a stored procedure, but not so good that the type of data is not identified.

Dim aDateVariable As String = "1/2/2012"
Dim commandObject As New SqlCommand()
commandObject.Connection = conn
commandObject.CommandType = CommandType.StoredProcedure
commandObject.Parameters.AddWithValue("@aDateParameter","1/2/2012" )

3) Concatenated In-Line Query

Here is the least safe method of  adding your variables directly to a SQL statement. In terms of safety this is completely open to a variety of attacks on your application:

Dim aDateVariable As String = "1/2/2012"
commandObject.Connection = conn
commandObject.CommandType = CommandType.Text
commandObject.CommandText = "SELECT * FROM MyTable WHERE aDateValue=" & aDateVariable

4) In-Line SQL with Parameters

Here is a more secure in-line SQL statement that uses parameters. Although in-line SQL by nature is less secure than using stored procedures, this syntax is significantly more secure than simply concatenating the values into the statement.

Dim aDateVariable As String = "1/2/2012"
commandObject.Connection = conn
commandObject.CommandType = CommandType.Text
commandObject.CommandText = "SELECT * FROM aTable WHERE aDateValue=@aDateParameter" 
commandObject.Parameters.Add("@aDateParameter", SqlDbType.DateTime).Value = "1/2/2012"
About these ads

9 thoughts on “ASP.NET Choosing Parameters.Add or Parameters.AddWithValue

  1. Good post. I once spent several hours tracking down a poor performing SQL query to a piece of code that was passing a string using AddWithValue. The stored proc parameter was a varchar, but the defaulting to nvarchar totally messed up the query plan.

    • Thanks for visiting and thanks for your feedback!

      It’s true, AddWithValue can be convenient to the developer, but the default nVarchar can definitely create surprise bottlenecks.

  2. Pingback: ASP.NET Machine Generated Code

  3. I think the SQL Injection point is invalid, as long as you’re using parametrized queries, addWithValue shouldn’t make you any more vulnerable. I believe the others but would have liked to have seen some real numbers so I could better decide for myself if the shortcut is worth the cost or not. If Microsoft thought you should never use it they would have added warnings to the msdn page.

  4. when searching for the difference between AddWithValue and Add method i reached here on the last but i got most useful information here.Now this post has cleard my doubts. I also have a blog where i also post such useful informations for the developers.Keep it up..Thanks

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s