Why Parameters are a Best Practice

Perhaps the single most important SQL Server application development Best Practice is the consistent use of parameters in application code.  Parameterized database calls are more secure, easier to program and promote query plan reuse.  Yet I continue to see code with SQL statements constructed by concatenating literal strings with variable values.  I think this is largely out of ignorance rather than design so I decided to show how easy it is for .NET application developers to use parameters and peek into the internals to show why parameters are so important.

How to Use Parameters

Below is a C# snippet of a SqlClient query built and executed using variable concatenation.  This is an example of how not to supply variable values to a SQL query.

string myTableId = 1;

 

SqlCommand selectCommand =

    new SqlCommand("SELECT LastName, CreateDate, Amount FROM dbo.MyTable " +

    "WHERE MyTableID = " + myTableId.ToString(), connection);

 

Here is a parameterized equivalent of the same query.  The only changes are the parameter token in the query string and that the parameter value was added to the parameter collection instead of embedding in the query text:

int myTableId = 1;

 

SqlCommand selectCommand =

    new SqlCommand("SELECT LastName, CreateDate, Amount FROM dbo.MyTable " +

    "WHERE MyTableID = @MyTableID", connection);

 

selectCommand.Parameters.AddWithValue("@MyTableID", myTableId);

 

So all an application developer needs to do is specify parameter tokens in a query string and add values to the parameter collection.  In the case of a parameterized stored procedure call, no tokens are necessary; simply specify the procedure name as the CommandText, specify CommandType.StoredProcedure and add parameters to the collection:

int myTableId = 1;

 

SqlCommand selectCommand =

    new SqlCommand("dbo.usp_SelectMyTableData", connection);

selectCommand.CommandType = CommandType.StoredProcedure;

 

selectCommand.Parameters.AddWithValue("@MyTableID", myTableId);

 

Parameters Under the Covers

The real differences between a parameterized and non-parameterized statement can be gleaned from a SQL and network trace.  Here is the associated trace event captured with SQL Profiler of the non-parameterized code example:

Profiler trace showing SQL:BatchCompleted

Note the “SQL:BatchCompleted” event.  The SqlClient API simply sent the query to SQL Server as a batch (text containing one or more SQL statements) because the SqlCommand.Parameters collection was empty.

Digging deeper with a tool like Microsoft Network Monitor, we can see the TDS (Tabular Data Stream) protocol data that SqlClient sent to SQL Server for the request:

TDS Packet with SQL statement batch

The TDS packet contains the entire Unicode SQL statement string, including the text value of 1 (Unicode character 0x0031, boxed in blue) that the application passed in the CommandText.

Now let’s take a look at the parameterized query trace event:

Profiler trace showing RPC:Completed

The fact that the EventClass shows “RPC:Completed” event instead of “SQL:BatchCompleted” indicates that SqlClient didn’t really wrap the query in sp_executesql to achieve parameterization like the TextData seems to show.  A RPC (Remote Procedure Call) is a specialized type of SQL request in which parameter values are passed separately from the SQL statement in the TDS request and in native form.  The SqlClient API knew to use a RPC instead of a batch because the non-empty parameters collection.

We can see from the dump of the TDS packet that it contains no reference to sp_executesql nor does it contain the string value 1 for the parameter value that the Profiler trace showed (see SQL Trace Parameter values are not always as they seem for more info).

TDS packet showing RPC

The important point with the RPC request is that the value 1 (binary integer value, boxed in blue) was passed separately from the query text.  This fact has a number of important implications for security, performance and programming. The difference between the TDS packets shows the essence of parameters in communication between the application code and SQL Server. 

Use Parameters for Security

If you don’t know what SQL injection is, Google is your friend.  Basically, injection vulnerability allows a user to manipulate the intended SQL by supplying mal-formed values.  Injection can allow a malicious user to retrieve sensitive data or change database information, limited only by the database security context.

Parameterized calls protect against SQL injection because the executed SQL statement is constant regardless of the supplied values.  As long as no dynamic SQL is constructed, the intended SQL statement cannot be manipulated.  An application developer only needs to ensure that the desired parameter values can be passed (e.g. key value from session state instead of from http query string) to guard against unintended data disclosure or manipulation.  Using stored procedures (with CommandType.StoredProcedure) always results in an RPC call and provides an additional security layer.

Use Parameters for Performance

Parameterized statements are cached to facilitate reuse and avoid compilation overhead, which is especially important in high-volume OLTP environments.  Furthermore, using stored procedures further promotes caching and reuse. 

The only downside to parameters from a performance perspective is that a given plan may be optimal for some values but not others.  In cases where sub-optimal cached plans are an issue, techniques such as RECOMPILE hints or compromise plan guides can help avoid a sub-optimal execution plans due to varying parameter values.  See article Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 for more information.

Use Parameters for Cleaner Code

Parameters are easier to program and provide cleaner code because there is no need to escape (double-up) quotes, format datetime strings or use “.” as a decimal separator.   Remember that the API takes care of passing parameter values in native form so string formatting is not needed when parameters are used.

String lastName = “O’Leary";

DateTime hireDate = DateTime.Now;

decimal amount = 12.34m;

 

SqlCommand insertCommand =

    new SqlCommand(

        "INSERT INTO dbo.MyTable " +

            "(LastName, CreateDate, Amount) " +

            "VALUES('" + lastName.Replace("'", "''") + "', '" +

            DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff") + "', " +

            amount.ToString("0.00") + ")",

        connection);

 

string lastName = "O'Leary";

DateTime createDate = DateTime.Now;

decimal amount = 12.34m;

 

SqlCommand insertCommand =

    new SqlCommand(

        "INSERT INTO dbo.MyTable " +

            "(LastName, CreateDate, Amount) " +

            "VALUES(@LastName, @CreateDate, @Amount)",

        connection);

 

insertCommand.Parameters.AddWithValue("@LastName", lastName);

insertCommand.Parameters.AddWithValue("@CreateDate", createDate);

insertCommand.Parameters.AddWithValue("@Amount", amount);

 

posted @ Monday, February 18, 2008 2:43 PM

Print

Comments on this entry:

# re: Why Parameters are a Best Practice

Left by sebbi at 2/20/2008 11:07 AM
Gravatar
What about unicode?

Without Procedure:
INSERT INTO tblTest (column) VALUES (N'myValue')
the "N" is for unicode

the right command will be
create procedurename (
@myValue nvarchar(50)
as
INSERT INTO tblTest (column) VALUES (@myValue)

but how can i say to the procedure that @myValue is unicode?

thx for reply
sebbi

# re: Why Parameters are a Best Practice

Left by Dan Guzman at 2/20/2008 9:18 PM
Gravatar
Hi, Sebbi.

Strings in .NET are Unicode so the AddWithValue method will create a nvarchar parameter with a length of the actual data value when you specify a string.

This brings up a good point, though. AddWithValue implicitly creates a parameter based on the specified data type and length. Although AddWithValue is a convenient shortcut, it's often a good idea to specify the desired SqlDbType and maximum length. This can be done after adding the parameter to the collection with AddWithValue or by creating the parameter using other techniques.

I plan to blog about this in more about this and the reason why shortly.

# re: Why Parameters are a Best Practice

Left by Jeff at 2/21/2008 12:10 PM
Gravatar
Great Post, Dan. I posted a similar article a ways back that covers many of the same points, but from a slightly different angle here:

Always use Parameters. Even if you don't use Stored Procedures.

The two articles make nice companion pieces. Glad to see someone else out there preaching best practices not just because "I said so", but explaining in detail *why* they are best practices and just how easy things are when you do it right!

# re: Why Parameters are a Best Practice

Left by Ricky Wang at 2/24/2008 6:23 AM
Gravatar
Hi,
where is the "string value 1 for the parameter value" info sent to SQL server? Is it encrypted?

# re: Why Parameters are a Best Practice

Left by Dan Guzman at 2/24/2008 10:47 AM
Gravatar
Hi, Ricky.

The parameter data type of @MyTableID is an integer so there is no string value involved. The actual parameter value is the 4-byte binary value of 1 you see in the TDS packet dump.

The point here is that no "string" was was sent for the parameter value because parameter values are passed natively. This is in contrast to a non-parameterized statement where values must be converted to/from literal strings embedded in the SQL statement regardless of data type.

# re: Why Parameters are a Best Practice

Left by adrian4b at 2/25/2008 1:13 PM
Gravatar
So, ignoring security part am I going to see big improvements in highly repetitive queries like this: (Sorry I don’t have the proper tools to test this!)

string myTableId = 1;
SqlCommand selectCommand =
new SqlCommand("exec sp_executesql N’SELECT LastName, CreateDate, Amount FROM dbo.MyTable " +
"WHERE MyTableID = @MyTableID’ N’@MyTableID int‘, @MyTableID =" + myTableId.ToString(), connection);

# re: Why Parameters are a Best Practice

Left by Dan Guzman at 2/25/2008 7:18 PM
Gravatar
Hi, Adrian.

I wouldn't expect a "big" performance improvement unless the query is complex or causing much more useful plans to get flushed from cache.

# re: Why Parameters are a Best Practice

Left by Dan Guzman at 7/29/2009 7:33 AM
Gravatar
Adrian, to add to my previous response, the performance improvement for non-trivial queries in a high-volume OLTP system can be dramatic. See my post on Forced Parameterization:

http://weblogs.sqlteam.com/dang/archive/2009/06/27/Forced-Parameterization-A-Turbo-Button.aspx

# re: Why Parameters are a Best Practice

Left by Esra at 2/29/2012 11:10 AM
Gravatar
It dedpnes on the database to which you're trying to connect, the method by which you created the connection, and the version of Excel that you're using. (Also, most probably, the version of the relevant ODBC driver on your computer.)The following examples are using SQL Server 2008 and Excel 2007, both on my local machine.When I used the Data Connection Wizard (on the Data tab of the ribbon, in the Get External Data section, under From Other Sources), I saw the same thing that you did: the Parameters button was disabled, and adding a parameter to the query, something like select field from table where field2 = ?, caused Excel to complain that the value for the parameter had not been specified, and the changes were not saved.When I used Microsoft Query (same place as the Data Connection Wizard), I was able to create parameters, specify a display name for them, and enter values each time the query was run. Bringing up the Connection Properties for that connection, the Parameters button is enabled, and the parameters can be modified and used as I think you want.I was also able to do this with an Access database. It seems reasonable that Microsoft Query could be used to create parameterized queries hitting other types of databases, but I can't easily test that right now.
Comments have been closed on this topic.