Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

Peter and I spent more than a week researching and discussing the best ways to deal with SqlParameter Value validation. The challenges we faced and address in this chapter are:

• When a new SqlParameter object is instantiated, the DbType and SqlType are set to the specified value in the constructor and match the datatype of the Value (if it's supplied) or NVarChar if it's not supplied. However, since the Value property is often (usually) reset based on user input or by program logic just before the SqlCommand is executed, it's essential that the supplied input variable datatype match the SqlParameter object's assigned datatype. Since the TextBox control (a typical source of user input) manages its Text property as a String, the value can be virtually anything, so you'll need some technique to validate the TextBox Text to ensure it conforms to the correct datatype.

• Since user-provided values are usually the source of incorrect data, it's essential that input (or edited) data be validated for datatype, range, business rules, and "reasonableness" while the user is focused on the input control. This means that the TextBox, DataGridView, MaskedEdit, ComboBox, or whatever UI control is used to capture input needs to be programmed to accept only valid data. This way, if an error occurs, the user can easily change the data to match the criteria. Later in this chapter, I illustrate ways to add properties to the TextBox control to validate the datatype and ensure that data provided falls within a specified range.

• Data also needs to be restricted and validated based on a number of other ever-changing criteria, such as minimum and maximum permitted values, precision, scale, and format. These can be considered business criteria, but as often as not, they're simply common-sense rules. For example, while a human's age in years is always a positive value between 0 and 120 (with a few historical or mythical exceptions), if you're working with adults that can fly an airplane, the range is far narrower. It's important to separate the criteria that is likely to change from time to time (more or less frequently) from those rules that really never change. This way, you can more easily adapt the application to evolving business rules without redeploying.

• Data is often supplied in specially formatted structures. For example, in the U.S., Social Security numbers are formatted as 999-99-9999 and phone numbers are formatted as (999) 999-9999. While these formatting regimens are different in other cultures or business, every application has to work with one formatting schema or another. Data must not only conform to these structures, but also the formatting characters can be stripped or left in place when passed to the Parameter Valueyour datatype must be set to accommodate the chosen approach.

Over the years, the Hitchhiker's Guide has discussed ways to manage business rules of all kinds. One approach that still makes sense uses Extended Properties that are exposed on all SQL Server objects. These properties can define a virtually unlimited set of criteria for individual database objects such as the table, the view, or (more relevant to this discussion) the table's column. For example, you can set Extended Properties to indicate minimum, maximum, suggested, default, mask, description, caption, font, color, or anything else you can imagine. When your application starts, it can easily fetch these properties and use them to tune the business rule logic or the UI in your application.

Writing Code to Validate the Value

Let's take a look at a few techniques that can be used to validate the Value property. The trick here is to choose the "right" time to perform this validation. The approach I settled on^[4] validates keystrokes as the data is entered and revalidates the Text property against a selected data type and range. I implemented this by creating a custom data-entry control (inherited from the TextBox control) that monitors keystrokes passed to the control and permits only key codes that apply to the desired data value based on the data type. For example, for number-type data types, I accept only numbers. Since I trap the KeyDown event and filter for numbers, what about the decimal point? If I accept a floating-point number, I need to permit a decimal point. And what if the user makes a mistake and presses the Backspace, Delete, or arrow keys? I need to accept those, too.

^[4] See "ValidatingTextBox" on the DVD.

To permit the developer to set the acceptable datatype within a specified range, I added properties to the TextBox control to expose SqlType, Min, and Max properties, as shown in Figure 10.25.

IMHO

It's best not to trust data from any sourceinternal or external until it's validated. All data is evil until proven innocent.

Since I added these properties to the TextBox control, I can program it to accept (only) data that conforms to structure and scale of the appropriate datatype based on the type of data I expect to capture. For example, if I expect a date, I can set the SQLType property to DateTime or Smalldatetime. Because I'm using the Framework to validate the data, I don't have to write complex Case (or Switch) statements to test for specific types and the corresponding system datatype.

Once I set the SQLType property in my custom TextBox control (which exposes the property as a drop-down list of known properties), I can trap the keystrokes entered in each instance of the control. The routine shown in Figure 10.26 illustrates how I test for acceptable KeyValue values returned from the keyboard. If the key isn't acceptable (based on the datatype set), I tell the control to ignore the keystroke (e.SuppressKeyPress).

Once the user moves the focus from the custom TextBox control, the custom Validating event fires (which overrides the TextBox control's base class Validating event). Here, I further evaluate the string entered by the userensuring that the TextBox Text property is correct based on the datatype selected and falls within the specified range. There is another consideration you need to make when working with TextBox controlswhat happens when the Text property is changed programmatically? Well, the TextBox base class does not fire "Validating" when data is provided by setting the Text property in code, so it's up to your custom TextBox control to handle this.

Based on the SQLType property I've set in the control instance, I call an appropriate TryParse method against the target datatype. The advantage to this approach is that it (should) return Boolean False if the TextBox.Text does not conform to the constraints of the datatype. If TryParse returns True (and does not throw an exception), I know the TextBox Text property is type conformant. Sure, this Validating routine as shown in Figure 10.27 might need a few more Case statements to deal with other datatypesone for each datatype you expect to validate.

If the custom Validating TextBox control finds that the TextBox Text property is not set to a value that can be interpreted as the selected datatype, it changes the control's BackColor property and enables the ErrorProvider. The ErrorProvider control is a handy way to show users that there are issues with the data they've entered. It flashes on the form near the control in question and permits you to set a "tooltip"-style message to clarify the problem, as shown in Figure 10.28.

The next step in the validation process is range checking, as shown in Figure 10.29. Here the control compares the custom TextBox.Text property against the Min and Max custom properties I added to the control. Again, I use the ErrorProvider and BackColor to indicate a problem. Sure, you might not want to do both, but I kinda like the belt and suspenders approach.

There is a wealth of third-party and freeware controls you can acquire to provide far more sophisticated validation. Many developers working on more complex projects create a suite of custom controls designed to deal with special-case fields. Some actually auto-generate forms based on the columns returned by a query. By placing this common validation code in custom controls, you can dramatically reduce the complexity of the applications you build. It also makes it easier to build additional applications more quickly and more cheaply. No, I don't suggest you write these controls yourselfnot before searching for third-party or freeware controls that might do everything you need (and probably more). Since these freeware controls are provided with source, you don't have to worry about supportability or security issuesas long as you review the code. If you see an obscure block of code connecting to a strange website, you might want to recode or consider another vendor. Greg Low^[5] tells us he's writing an MSDN article on a suite of controls he's written. I suggest you look for it along with other sources, including Billy Hollis's validator control samples^[6]. Both of these gentlemen provided guidance as I wrote this chapter.

^[5] Greg Low wants folks to find him through his blog at http://msmvps.com/greglow

^[6] I have included Billy's validator controls on the DVD.

Handling Complex Parameter Design Issues

There are a few issues that I need to discuss before I'm finished touring the Parameters collection and executing dynamic queries.

Working with Unicode Literals

If you get Visual Studio or SQL Management Studio to generate code for you, you'll doubtless see your string literals prefixed with an "N". This simply means that the string literal is to be treated as a Unicode value and stores 16 bits per character. This approach is essential when working with Unicode character sets used by a host of languages. Note that Unicode-typed columns (and parameters) consume twice as much space as their ANSI (8 bits per character) equivalents.

Duplicate Parameter Names

If you execute a multi-part query, you might find that the parameters passed to the various queries and (possibly) action commands have the same parameter names. For example, if your code executes the same stored procedure more than once, you'll face this problem. Sure, it's a great idea to bundle many operations in the same batch, but this won't work if the Parameters collection can't isolate the individual SqlParameter objects by name. Since the parameter names have to match the names in the stored procedure, there is no work-around besides breaking up the operations into separate batches.

Ensuring Your Command Rights

Regardless of the approach you use to execute your T-SQL, the query you execute requires that your application's credentials (whether inherited from the current user or hard-coded) are sufficient to execute the query against the specified table(s) and no more. These access rights can extend right down to the table column. You should never grant a user more rights than they absolutely need. If you take this approach and your code or application is penetrated, the damage can be limited to a far smaller surface area. That is, if the application credentials permit only read-only access to a specific set of tables or (better yet) views, the malicious act can do only what the application was designed to do in the first placereturn rows from these read-only areas; it can't change anything anywhere in the database. If you need read-write access, plan to use stored procedures to perform these changes. That way, the hacker can change the database only via carefully protected stored procedures. When you code action commands, another world of issues is exposed. I'll talk about update queries in Chapter 12.

Handling Nulls

Every SQL Server developer will learn at one point in time or another how to handle a data vacuum. That is, when you don't have a value to apply to a database column you're forced into a quandarycreate a tag that means "data is not (yet) known" or use NULL. In the 1970s (and before), database developers were faced with this problem, but 80-column cards and paper tape didn't really have a special hole for the absence of data. If a column were left blank, it was coded as an EBCDIC 0x20so it was stored as a space. I made do with that until disk- and tape-based systems encouraged us to come up with something better. In a relational database, I use the concept of NULL to indicate that the data is simply not known or unavailable. This raises a number of issues. How does NULL average in with other data? Can one compare a "dunno" with a valid entry? I'll leave these questions up to the books on computer science. I won't get into a long-winded discussion about the wisdom of using NULLs, but I do think NULL is a better placeholder than BHOM (Beats the Hell Out of Me), which Bill used when he taught COBOL programming for the U.S. Army.

In those situations where you need to pass a NULL to a SqlParameter or test a data value for NULL, you'll have to step off the boards a bit and use a number of special notations to let ADO.NET know what you want done. These tasks are complicated somewhat with the variations in Visual Basic .NET and C#, and I'll touch on those as well.

Testing for NULL Values in Visual Basic .NET

Visual Basic .NET (and the .NET Framework CLR languages in general) supports many techniques for handling NULL values, as the following code examples show. Figure 10.30 starts the exercise by creating a simple SqlDataReader that returns a NULL value for the Year_Born column. Notice how I set the SqlParameter Value to SqlTypes.SqlInt32.Null. Each of the SqlTypes exposes a Null property that can be used to generate a type-correct NULL value.

At this point, if you try to reference the value returned in the DataTable rows, you'll discover that the CLR types don't support NULL type conversion. This means your code throws an exception as you try to retrieve a value from a DataTable or DataReader rowset that's set to NULL. This means you must add code for each and every column you expect to contain NULL to test for this contingency. Figure 10.31 illustrates several techniques you can use to test for a NULL column value. Note that you can't use the "=" operator because you can't "compare" nothing with anything.

Using Dynamic SQL

Another (far more dangerous) approach to difficult parameter query issues is to pass the components of the query to the server to be compiled in situ. That is, your CommandText can call a stored procedure that takes the inbound parameters and constructs an entirely new SQL query and uses the T-SQL EXECUTE (EXEC) operator to compile and execute it. In this case, you can pass any or all of the T-SQL you want to execute as parameters, assemble it on SQL Server using string concatenation, and use the EXEC operator to compile and execute the code. Let's attack my IN expression challenge with the EXEC approach. Figure 10.32 illustrates how to code a stored procedure that accepts the list of acceptable prices as a delimited string. In this case, the entire compile phase is repeated each time the stored procedure is executed. This approach also permits users to (easily) inject SQL via the single @PricesWanted string parameterwhich is why this approach is not recommended.

IMHO

Using dynamic SQL can make SQL injection a lot easier. At least the hackers will thank you.

Understanding SqlCommand Events

There are only two events exposed on the Command objectDisposed and StatementCompleted. I'll be talking about both of these Events in Chapter 11 when I discuss executing SqlCommand objects.