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

Convert input explicitly at your client; don't rely on the database to "figure it out"

A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc).  I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled.

In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value (say, from a TextBox on a web form) and uploads that value to the database written like this:

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values ('" + txtDate.Text + "')";
c.ExecuteNonQuery();

Now, I think that hopefully even most beginners will agree that this is bad code.   The primary issue, of course, is SQL Injection.  Avoiding SQL Injection is very easy to do using Parameters.  So, let's say that you rewrite this code using parameters like this:

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.AddWithValue("@DateVal",txtDate.Text);
c.ExecuteNonQuery();

Looking at that, it seems we have done quite a bit better and should be happy with the code.  It works well, and no injection is possible.  But there is still an issue!  Why?  The txtDate.Text property returns a string, not a DateTime!   And, since we are not setting the data type of the parameter explicitly, the parameter being passed is a string (i.e., VARCHAR or NVARCHAR) value, not a true DateTime value.  This means that SQL Server must implicitly cast your string to a DateTime to store it in your table, and this may or may not work successfully, or as expected, depending on how the string is formatted. 

I've said it over and over and I'll say it again:  The concept of formatting dates should never be something that your database code should ever worry about.  The database layer should be accepting DateTime data from clients, and returning DateTime data to your clients. Where and how the client got the data before passing it to the database, or what the client does with the data in terms of formatting after receiving it from the database is of no concern to the database itself. 

So, we might decide that to fix this, we can simply declare the data type of the parameter explicitly:

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = txtDate.Text;
c.ExecuteNonQuery();

It appears that now we are in good shape, right?  Actually -- no! There is still an implicit conversion happening, because we are still passing a string value -- the txtDate.Text property -- to the parameter, not a true DateTime!

Let's try one more time.  How can we avoid these implicit conversions?  The answer that question is always the same: Convert explicitly!  Your client application is fully capable of handling the parsing, validation, and conversion of that string to a true DateTime value, so go ahead and do it:

DateTime dateval = DateTime.Parse(txtDate.Text); // plus more code to validate, of course

SqlCommand c = new SqlCommand();
c.CommandText = "insert into SomeTable (DateCol) values (@DateVal)";
c.Parameters.Add("@DateVal", SqlDbType.DateTime).Value = dateval;
c.ExecuteNonQuery();

Now we are in business!  Before we even create the SqlCommand object, we have a true DateTime value that we are ready to pass along to SQL Server.  Our SQL code doesn't need to worry about formatting, parsing, converting, or anything -- it is being passed a completely valid piece of data with the correct type.   In short, we can now be sure that whatever value we came up with for the date in our client code is exactly the value that will be stored in our database.  That's the idea, right?

So, please, don't rely on your database code to validate your input.    Don't just pass along generic string data and "hope" that at the end of the day the database can "handle it".  Eliminate the chance of anything going wrong and write your code to explicitly cast and convert and validate any and all input before the database even comes into the picture.  

Print | posted on Thursday, July 24, 2008 9:12 AM | Filed Under [ T-SQL .NET (C# / VB) Techniques DateTime Data ]

Feedback

Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

I was about to comment you missed something, but looking a second time I saw
DateTime dateval = DateTime.Parse(txtDate.Text);

Anyway I used to do something like this (vb.net)

If IsDate (txtDate.Text) Then
c.Parameters.AddWithValue ("@DateVal", CDate (txtDate.Text))
ELSE
c.Parameters.AddWithValue ("@DateVal", DBNull.Value)
END IF

Good point.... dates and string used to be a real pain.
7/24/2008 11:55 AM | Henri Koppen
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

Jeff, I can appreciate the fact that validating input/parameters makes good sense as a best practice (avoid potential casting errors, etc) but would you happen to know if there is a performance benefit to this approach as well? Working off of your example, can SQL implicitly convert a varchar/nvarchar to a datetime variable efficiently or is this an operation better left to the application layer for performance reasons?
7/24/2008 12:47 PM | Ben Griswold
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

>>Working off of your example, can SQL implicitly convert a varchar/nvarchar to a datetime variable efficiently or is this an operation better left to the application layer for performance reasons

It can usually convert it, but that is the issue -- a conversion must appear somewhere. And what if you pass in a format that SQL Server cannot convert, or that is misunderstands? (i.e., mm/dd/yyyy versus dd/mm/yyyy and so on) You shouldn't need to write data validation code in T-SQL, and worry about somehow returning error messages like "The date format supplied is not valid" from an in-line SQL statement or stored procedure.... That's what your client application is there for. If your client can accept the input, it can also convert it to a proper DateTime, and then all that SQL Server needs to do is accept the value and store it.

In terms of raw performance, it may not be any different, but in terms of actual results, the difference is huge.
7/24/2008 1:44 PM | jeff
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

Your point is understood. Thanks for the additional detail.
7/24/2008 10:35 PM | Ben Griswold
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

For performance, I think there are several important things to note:
- Web Servers are primarily memory intensive, data validation in this environment would involve one server in the stack versus multiple
- For web, most validation in .NET is re-rendered as javascript events, so you don't even consume a post back to the web server
- Regardless of web or a windows app, to send an unformatted object down the pipe and potentially receive an error consumes memory, processing cycles and network traffic over potentially two systems (and at least two services if they are on the same box) versus just handling it UI side
7/28/2008 11:48 AM | Sid Atkinson
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

In terms of date validation, one handy tip is to pass in dates formatted as dd mmm yyyy. This avoids any ambiguity between dd/mm/yyyy and mm/dd/yyyy.

And I totally agree - validate and format at the client!

Ian Logan
7/28/2008 11:49 AM | Ian Logan
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

Data should always be validated before trying to store it in the database. If you are going to validate it you might just as well do the type conversions right there while you are at it. Most of us are doing desktop applications and there can be some distance, network wise, between your user and the server. Doing these type conversions can reduce network traffic by allowing the SQL client to package the querry in the best manner. I do a lot of work on mobile devices where there can only be one user at a time and NO distance at all. Still, doing the conversions in my app takes the work off of the already over taxed small processor.
7/28/2008 11:53 AM | Charles Kincaid
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

Thanks for brining up an important subject!

Ian, just some thoughts on your suggestion...

Be very careful with the ??-mmm-?? syntax! This works fine to eliminate the issue with american vs. english dates (mm/dd/yy vs. dd/mm/yy) but it will render the month as the abbreviated name.

Unless explicitly specified during the conversion, the current culture settings on the client will be used to generate this month name, and you might see some rather nice bugs when the server's culture is different. (This is really based on current user and connection). E.g. in norwegian, Jan, Feb, Mar, Apr, Jun, Jul, Aug, Sep and Nov are the same while May, Oct and Dec are spelled different, as Mai, Okt, Des. Fun when your application works 9 out of 12 months :)

In my opinion, this scores pretty high on the bug-cost-meter, based on the theory that the cost to fix a bug increases with time from it left the fingertips of the developer. (If you haven't, think about this! When your app compiles without errors, spend a few minutes asking what can go wrong...)

A long time ago, we discovered this as a problem and did something really bad... we decided the format 'yyyy-MM-dd' which we thought whould be universal, and at the same time made sure our servers was always running english-us culture settings. As you might imagine, this does not eliminate the problem, just gives less and even more expensive errors... Try this:

set language us_english
select cast('2008-09-13' as datetime)
set language british
select cast('2008-09-13' as datetime)
set language norwegian
select cast('2008-09-13' as datetime)
set language german
select cast('2008-09-13' as datetime)


Furtunately, we know better now :) There are two formats that can be safely used for converting from a string to a datetime, unseparated ISO 8601 and full ISO 8601:
yyyyMMdd
yyyy-MM-ddTHH:mm:ss.nnnnnnn

See also http://msdn.microsoft.com/en-us/library/ms180878(SQL.100).aspx

Regards,
Roar
7/29/2008 2:06 AM | Roar Fredriksen
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

There is an important difference in the choice of where to do data conversions and data validations: Data conversions can be done at any point in the chain, and doing them away from the database server does make sense.

But data validation, if done in the client, still MUST also be done on the server! Why? Because you should never trust the bits that are running on an untrusted machine (i.e. the users machine.) So while validation at the client can save roundtrips to the server, you must also validate either at the web app, or (preferably also) in the database server. If there is any chance of an input from an untrusted source, (and client code can never be trusted to communicate with the app server unless there is heavy encryption or some other way to authenticate where the bits are coming from), the data must be revalidated on the server side, since you can't be sure it was your client code, or someone else's substitute client or even bot, that is manipulating the app server.

http://www.colonywiki.com/The_difference_between_converting_and_validating_data_in_a_client_verses_a_server
7/29/2008 5:33 PM | Garnet
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

Garnet -- If the database is interfaced via stored procedures with parameters, and the parameters are properly declared with correct data types, then the database doesn't need to validate anything. It can accept the input and store it in the database. There will be no conversion errors, no sql injection, no need to escape or delimit or do anything.

Now, due to business rules database may not want to accept a datetime parameter that is greater than a certain date, or is not the first day of the month, or an integer that isnot positive, that is correct. But in terms of validating data types in general, if you use parameters, you don't need to validate anything other than business rules at the database layer.
7/30/2008 9:00 AM | jeff
Gravatar

# re: Convert input explicitly at your client; don't rely on the database to "figure it out"

In terms of date validation, one handy tip is to pass in dates formatted as dd mmm yyyy. This avoids any ambiguity between dd/mm/yyyy and mm/dd/yyyy.
5/20/2009 2:40 AM | convert miles to km
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET