Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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

(3/25/2007 update: Fixed the incorrect parameter name the last two examples)

Parameters without Stored Procedures?

Let's assume that for some reason you are not using Stored Procedures. While I can respect your choice in that regard, that doesn't mean that you cannot still use parameters when constructing your SQL statements at the client. There are few absolutes in this world, but along with "never use RIGHT OUTER JOINS" and "Never trust someone with two first names" there is another: Always use parameters when incorporating user input or any variables into your SQL.

I expect that you know about SQL Injection, so that takes care of the security argument for Parameters -- I won't even get into that here (though that reason alone should be enough to convince any reasonably responsible programmer). My focus will be on the other advantages to using Parameters over concatenating things together into one big string. Much like other best practices which people seem to work very hard to avoid, using parameters makes things much easier on yourself and keeps your code clearer, cleaner and often shorter.

Underutilizing a SqlCommand by creating SQL Strings

Let's consider an example of doing things the wrong way, using VB.NET as our client language. This is the article for you if the following code looks familiar:

        Dim ID As Integer = 12

        Dim Name As String = "O'Neil"

        Dim TranDate As Date = Now.Date

 

        Dim cm As New SqlCommand("", YourConnection)

        cm.CommandText = "DELETE FROM YourTable WHERE ID=" & ID & " and Name= " & Name & " and TranDate=" & TranDate

        cm.ExecuteNonQuery()


First, keep in mind that the CommmandText property that we are setting is a String. Every time we use & (or +) to concatenate expressions while constructing our CommandText property, each of those expressions -- which might be Integers or Dates -- must be first be converted to a String. When doing this, we are not creating a SQL command, but simply a string. We are forcing all of our syntax and all of our input data into one long string expression that SQL Server will simply execute for us.

The example above has two implicit conversions happening: ID is an Integer, and TranDate is a Date, so both must be converted to a String before they can be concatenated with the rest of the CommandText.

Now, what happens when the Integer value of 12 is converted to a String? It becomes "12" -- a one followed by a two. This format is acceptable and pretty universal, so this will never cause a problem. However, to avoid implicit conversions which can lead to errors and sometimes unintended results, we will use the ToString() method of the Integer class to convert the integer to a string explicitly. (Many languages force us to do this or the code will not compile.) Easy enough for the integer, but what happens when TranDate is converted to a string? How is it converted?

Well, if you don't explicitly specify it, then it depends on the computer that the code is executing on. It may come out as mm/dd/yyyy, or dd/mm/yyyy, or some other variant. Even if you explicitly use the DateTime's ToShortDateString() or ToLongDateString() methods, it still depends on settings. Or, you may even wish to explicitly provide the format -- say, force it to be mm/dd/yyyy. Either way, the important thing to realize is: A conversion to a String is happening here, and formatting, by definition, must be involved in this conversion.

So, based on that information, when the CommandText property is evaluated, we get something like this:

DELETE FROM YourTable WHERE ID=12 and Name=O'Neil and TranDate=7/21/2006 12:00:00 AM


And that is what is sent to SQL Server to be executed. As you can see, that is not a valid SQL statement. Let's see what we need to fix.

First, a common error which occurred here is that we did not delimit some our non-numeric expressions with ', which results in syntax errors. Remember, by not using parameters, SQL Server doesn't know or care that "O'Neil" came from a variable; it just knows that those characters have been added to a SQL statement that must be executed. So, we need to handle this ourselves when we construct our CommandText property. One way to do it is to include the ' characters before and after we concatenate a non-numeric values to our CommandText:

       cm.CommandText = "DELETE FROM YourTable WHERE ID=" & ID.ToString() & " and Name= '" & Name & "' and TranDate='" & TranDate.ToString() & "'"


A bit harder to read now, and troubleshooting extra or missing ' characters (especially when they are next to " characters) can be quite difficult, but it's necessary when building SQL strings in this manner. Evaluating that expression results in:

DELETE FROM YourTable WHERE ID=12 and Name='O'Neil' and TranDate='7/21/2006 12:00:00 AM'


Next, you might have noticed that the name of "O'Neil" has an apostrophe, which means that the above SQL will still result in a syntax error. To fix this, we will need to replace all single apostrophes in all of our variables with a double apostrophe ('') while creating our SQL String, so let's add that to our CommandText expression:

       cm.CommandText = "DELETE FROM YourTable WHERE ID=" & ID.ToString() & " and Name= '" & Name.Replace("'", "''") & "' and TranDate='" & TranDate.ToString() & "'"


Remember, if we had 4 different string variables that we were concatenating, we'd need to do this Replace() on each of them. We could also write a helper function to do the replace and add the delimiters if we wanted.

Handling the ' with our Replace() method now results in:

DELETE FROM YourTable WHERE ID=12 and Name='O''Neil' and TranDate='7/21/2006 12:00:00 AM'


OK, we are looking good now ... However, what happens if the Date format on the server is dd/mm/yyyy ? The above will cause an error -- there is no month 21. Suddenly, though it did not seem like anything we are doing should depend on date formats at the client or the server, it becomes something that we need to be aware of which we must consider and plan for. So, we need to be sure that we explicitly format our date in a format that is universally acceptable, say yyyy-mm-dd:

       cm.CommandText = "DELETE FROM YourTable WHERE ID=" & ID.ToString() & " and Name= '" & Name.Replace("'", "''") & "' and TranDate='" & TranDate.ToString("yyyy-MM-dd") & "'"


Again, we would need to do this to any DateTime variable that we concatenate with our CommandText. Now, we seem to be in good shape. Our SQL String looks pretty good at this point:

DELETE FROM YourTable WHERE ID=12 and Name='O''Neil' and TranDate='2006-07-21'


Well, congratulations! You now have a working SQL statement, and you've done 5 times as much work as you needed to, dealing with non-numeric delimiters, scrubbing input data, long concatenated string expressions, both client and server date formatting, and implicit and explicit string conversions. Plus, our CommandText expression is pretty darn hard to look at and to work with -- imagine if this DELETE involved more "parameters" or if it was a complicated SELECT with several join expressions? Would this be easy to maintain?

Also, let's recap the journey of our TranDate variable. It started as a DateTime value, got converted to a string in a particular format at the client and then converted back to a DateTime from that string by SQL Server. Surely, there must be a better (and easier) way to pass that DateTime value directly to SQL Server without all this converting and formatting going on.

Using a SqlCommand to create SQL Commands

Remember, earlier I mentioned that if you are writing code in the manner presented above, you are not creating SQL Commands, but rather string expressions. Let's create an actual SQL Command with parameters. We don't need to concatenate anything, our CommandText property is clear and simple and easy to read, and there are no issues with delimiters or date formats or conversions! All we need to do is map our variables to the named parameter placeholders that we specify our CommandText property, just as you would do in a Stored Procedure.

Consider the following code:

        Dim ID As Integer = 12

        Dim Name As String = "O'Neil"

        Dim TranDate As Date = Now.Date

 

        Dim cm As New SqlCommand("", YourConnection)

        cm.CommandText = "DELETE FROM YourTable WHERE ID=@ID and Name=@Name and TranDate=@TranDate"

        cm.Parameters.Add("@ID", SqlDbType.Int).Value = ID

        cm.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name

        cm.Parameters.Add("@TranDate", SqlDbType.DateTime).Value = TranDate

 

        cm.ExecuteNonQuery()


Here, we have just added @ID, @Name and @TranDate to our SQL statement as parameters, using the @ syntax that SQL Server uses. Then, using the Parameters() property of the SqlCommand object, we are able to assign them datatypes and values.

The Add() method of the parameters class has many overloads, but this is the one I like to use. You can specify the parameter name and the datatype, and since the Add() method returns an instance of the parameter, you can immediately access the Value property and set it. Note that we are setting the parameter values directly from our variables -- no conversions, no formatting, no delimiters. We don't need to worry about any of that. Because we have created a command and not just a string, SQL Server can now deal directly with parameters values instead of parsing a big, long string.

What makes this even better is when you need to reference a parameter more than once in your SQL; you just repeat the @ParamName as many times as you like in the CommandText, but you only need to set the parameter once. With concatenation, you need to concatenate that string expression over and over.

Even if you need to create big, long complicated SQL statements dynamically with optional filters and joins based on user input or whatnot, it is still very easy to conditionally use parameters:

        Dim cm As New SqlCommand("", YourConnection)

        cm.CommandText = "DELETE FROM YourTable WHERE ID=@ID "

        cm.Parameters.Add("@ID", SqlDbType.Int).Value = ID

 

        If Name <> "" Then

            cm.CommandText &= " And Name=@name"

            cm.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name

        End If

 

        If TranDate <> DateTime.MinValue Then

            cm.CommandText &= " And TranDate = @TranDate"

            cm.Parameters.Add("@TranDate", SqlDbType.DateTime).Value = TranDate

        End If


The above handles situations where you might want to build a WHERE clause dynamically by excluding optional parameters. As you can see, you are still able to assign and set parameters even in that scenario.

Conclusion

I hope this has given you some good things to think about when it comes to using the SqlCommand class to construct dynamic SQL statements. Even though you might not be using stored procedures, you can still make use of strongly-typed parameters in your SQL, which will make your life much easier and your code less prone to data conversion issues and SQL Injection.

ADO.NET and even older ADO and DAO all support creating parameters when constructing commands, even if you do not use stored procedures. I strongly urge you to make use of this feature.

I welcome your feedback and thoughts, especially if you feel that you can provide a situation where concatenating SQL statements with values all converted to delimited strings has an advantage over using parameters.


see also:

Print | posted on Friday, July 21, 2006 10:43 AM | Filed Under [ T-SQL .NET (C# / VB) Techniques ]

Feedback

Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

The amazing thing is you've said this and people will still go off and use SQL Strings and avoid using ADO / ADO.net alltogether.

Here's the problem at hand, many programmers have a problem, they know they've solved it somewhere (heck its in the code view in my old vb 4 app). Aha there it is...copy and paste...wow it works in vb.net..I'll just keep whats there since it works.

Sure it might work, but will it fail ever. There are plenty of programmers who think this way, I was a lot like that back in the early vb days. Now with the .net framework I generally look for shortcuts, there is almost a class / property for everything.

All in all the article is well written.

Jon
7/21/2006 11:06 AM | Jon
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Jeff,

Nice article. Very clear and well-written.

One additional mention: There's an additional advantage to using parameterized statements. If the statement will be used more than once, it's cached on the server for reuse, where concatenated statements are not. This can be a pretty significant performance detail.

Ken
7/21/2006 12:08 PM | KenW
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Cool post Jeff!

My opinion is that besides not knowing about parameters the main reason for not using them is pure laziness :)
7/21/2006 12:10 PM | Mladen
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

>>My opinion is that besides not knowing about parameters the main reason for not using them is pure laziness :)

Yet, ironically, as I point out in the post, it is much easier to use parameters than to not use them ... So, they are lazy but they work harder then they need to! Quite the contradiction, though unfortunately it's pretty common in programming.


7/21/2006 12:30 PM | Jeff
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Ken -- great point, especially if you, say, create an INSERT command with parameters and then need to call it in a loop to add data to a table.
7/21/2006 12:33 PM | Jeff
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Jeff / Ken

Don't you have to "Prepare()" the command in order to use the same copy of the procedure/script multiple times?

Mike Rod
7/21/2006 5:09 PM | Mike Rod
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

>>One additional mention: There's an additional advantage
>>to using parameterized statements. If the statement will >>be used more than once, it's cached on the server for >>reuse, where concatenated statements are not. This can
>>be a pretty significant performance detail.

I don't think this correct.. The SQL Server cache is based on the query plan and not the sql text
7/22/2006 3:19 AM | DavidM
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

I hope this post is read by Microsoft SQL Server Team + ADO.NET Team & they add this as part of Best Practice in

1. Books Online
2. Code Analyzer tool
7/23/2006 3:26 AM | PP
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Another reason not to use yyyy-mm-dd is that it isn't (contrary to popular belief) universally acceptable.

Did someone say DATEFORMAT?
7/23/2006 10:07 PM | Matthew
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Matthew, I've reread this entire page twice not and I can't find the first reason not to use yyy-mm-dd which I assume exists somewhere fiven that you said "another reason not to use".

I was not aware that the format was not universally understandable: where is yyyy-dd-mm used?
7/24/2006 2:58 PM | Bob
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

I have experienced performance problems using parameters when the data is badly distributed - a table with a million rows where 100 rows are of type RDY (the rest are FIN)

The system incorrectly tried a table scan to get the result as it based the query plan on AVERAGE values rather than the value actually passed in the parameter.

Using constants fixes this problem.

Using stored procedures has the same style of problem, however the query plan is based on the FIRST time you run the stored proc. This results in a bad query plan until a recompile.

I have seen major performance improvements in my production servers using technique.

Does anyone have real figures regarding performance improvement using parameters?
7/24/2006 9:41 PM | Brett Shearer
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

yyy-mm-dd is actually the *only* universally accepted date format. It is called iso 8601.
7/25/2006 3:28 AM | Slevdi Davoteca
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

... I meant 'not available' in the previous post. Sorry.
7/25/2006 3:46 AM | Slevdi Davoteca
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Brett: just google "parameter sniffing"
Use internal variables to mask your parameters...
7/25/2006 2:06 PM | GBN
Gravatar

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

A very good article by Jeff Smith over at SQL Team on Using Parameters when using dynamic SQL from .NET...
8/3/2006 6:06 PM | Strate SQL
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

A timely kick in the pants to push me away from the bad habits of slovenly laziness. Thank you so much!
8/8/2006 9:19 AM | Ashamed to be lazy
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

What exactly do you have against RIGHT OUTER joins?
10/26/2007 1:00 AM | Datagod
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

>> What exactly do you have against RIGHT OUTER joins?

Logic, readability, maintainability. RIGHT OUTER JOINS can and should always be rewritten as LEFT OUTER JOINS. A clean, efficient, well-structured SQL statement will always select FROM the "base" and outer join TO auxiliary data. There is no FROM condition that cannot be expressed this way. RIGHT OUTER JOINS violate this simple rule and do things "backwards".

e.g., If I want ALL orders and ANY shippingCodes that match, what makes more logical sense to you:

select FROM orders OUTER JOIN TO shippingCodes

or

select FROM shippingCodes OUTER JOIN TO orders

?

This may come off as insulting, and if so then I apologize, but if you have any decent amount of SQL knowledge, experience, and skill, you've never coded a RIGHT OUTER JOIN and you never will.
10/26/2007 2:29 PM | Jeff
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Hi Jeff,
Just wondering if I build the Sql in Stored Procedure (Dynamic Sql) based on the parameters passed to stored proc, will it still be a possible candidate for Sql Injection? Basically I want to build the Sql in the stored procedure instead of doing it in .Net code as displayed in the article.

Dim cm As New SqlCommand("", YourConnection)
cm.CommandText = "DELETE FROM YourTable WHERE ID=@ID "
cm.Parameters.Add("@ID", SqlDbType.Int).Value = ID

If Name <> "" Then
cm.CommandText &= " And Name=@name"
cm.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name
End If
If TranDate <> DateTime.MinValue Then
cm.CommandText &= " And TranDate = @TranDate"
cm.Parameters.Add("@TranDate", SqlDbType.DateTime).Value = TranDate
End If
7/16/2008 11:25 PM | karuna
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Karuna -- answered here:

http://weblogs.sqlteam.com/jeffs/archive/2008/07/16/60652.aspx
7/17/2008 9:01 AM | jeff
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

Thanks
8/31/2008 2:23 PM | rüya tabiri
Gravatar

# re: Always Use Parameters. Even if you don't use Stored Procedures.

I have a situation where I must use embedded sql only.
That means NO store procedures, parameterized queries, etc are allowed - period.
In other words, my hands are tied!
Anyhow, I wrote this routine to prevent SQL Injection.
I think this routine is bullet proof.
Can anybody break it?

Function getSafeValue(ByVal userInput As String) As String

userInput = Trim(userInput)
userInput = userInput.Replace("'", "''")
userInput = userInput.Replace("""", "''")
Return IIf(userInput = "", "NULL", "'" & userInput & "'")

End Function
9/22/2008 5:00 PM | Brent Jenkins
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET