ObjectDataSource QueryStringParameter Input Validation

Procrastination will only get you so far before you have to finally just put it off and get your ass to work. Today I finally get back into the swing of things with my first post since my sites (still incomplete) migration to SiteFinity Content Management System. I know there is still much to do with existing content, like fixing broken links and all that nonsense, but I’m taking a positive, constructive approach and getting things started with a revival of my now merged Software Blog. Today we talk about validating input parameters for the asp.net objectdatasource control.

As you likely don’t already know, I’ve just recently launched the new City of McAllen Website (a post on the impact this opportunity has had on my life is coming soon), and in one section in particular, I needed to make use of the QueryString to input parameters to an ObjectDataSource for selecting specific items from our Database. Straightforward enough yes?

The Problem

The problem is that I did not have any way to validate input to ensure that the parameters were of the correct type. Of course I made use of stored procedures and strongly-typed parameters to ensure that invalid input isn’t processed. However, these only prevent bad code from executing, and don’t actually do anything to prevent an invalid argument exception if users make an error (or worse, attempt to inject malicious code, the bastards). As a result, if the QueryString does not resolve automatically to the expected type (in this particular case Int32), an exception is thrown, and the user is shown a generic error message. Not very user friendly for a city that prides itself on its high levels of satisfaction from its citizens!

The simplest solution to implement seemed to be to check the querystring on Page_Load, which for all practical purposes should have worked just fine. This particular page, however, has multiple uses, and does not ALWAYS require a querystring. So I would have to account for this situation when checking, first verifying that I’m the correct mode before validating the querystring. Not only does this add overhead to the implementation cost, but it isn’t practical, since the querystring isn’t coupled to the Page_Load, it is coupled to the input parameters of the ObjectDataSource, so it is there that the validation should occur.

The Solution

Naturally, this lead me to the ObjectDataSource’s OnSelecting event, which occurs right before the datasource Select Command is executed, a natural place to perform validation! Using the now famous Int32.TryParse, it’s a no-brainer to ensure that the requested parameter is an integer:

int bID;
        if (!Int32.TryParse((string)e.InputParameters[“ID”], out bID))
            e.InputParameters[0] = bID;

As you can see, the input parameter is checked for validity, and modified if it doesn’t resolve to an integer. Additionally, since on failure, Int32.TryParse resolves the output integer parameter to a zero, I can use this as a dummy value to ensure that the FormView to which this ObjectDataSource is attached renders the EmptyViewTemplate, informing the user that the requested item could not be found. Much more informative and user-friendly than some random “OOPS” message!

The Caveat

Unfortunately, this didn’t work! At least, not yet. When I tested the page, passing an invalid, non-integer in the querystring, the page continued to throw an exception, showing that the input string was not valid. I knew that I was handling the validation in the right spot, and I knew that it should resolve all input to a valid integer, 0 in the case of an error… However, running the site in Debug mode, I discovered that the OnSelecting event wasn’t even firing! Something was definitely wrong here… of to The Google I went…

Interestintly enough, the first link I found was to a great article by Erwyn Van der Meer on this very subject, reaching the exact same conclusion as I did. Confident that I was at least on the right track, I reviewed his article to find where I might have gone wrong…

I didn’t find the explicit reason for why things weren’t working, but something Erwin wrote caught my attention:

…you can even specify that a parameter should be of type Int32, but the page crashes nonetheless.

This immediately reminded me that I had declared the QueryStringParameter to explicitly be Int32. This means that the ObjectDataSource itself automatically expects and assumes that the input parameter will be of the correct, int32 parsable type, and will attempt to resolve the parameter itself before it executes its Select Command! This is not what I want; I want my code to validate the input and cast the parameter to an appropriate value. So I needed simply to go into the page’s source code and modify the parameter declarations from this:

<asp:QueryStringParameter Name=”ID” QueryStringField=”ID” Type=”Int32″ />

to this

<asp:QueryStringParameter Name=”ID” QueryStringField=”ID” />

I made the changes, saved, and success! The page successfuly executed the OnSelecting code, invalidating my bad input and forcing the value to zero, showing me (and more importantly, the general public) a much more helpful message.

The Lesson

Remember when handling the OnSelecting event for parameter validation to ensure that the input parameters are not explicitly cast in the source view, but instead are handled by your own validation code. Til next time, happy coding!

Rounding to the Nearest Ten

I’m not sure if the Microsoft .NET Framework has support for rounding to the nearest ten, hundreds, thousands, etc, so I built a quick snippet of code to accomplish this. The basic algorithm is to mod the value by 10 (can be modified to handle 100, 1000, etc), then if less than the median (in this case 5), subtract that from the original value. If the mod is greater than or equal to the median, we round up by adding the DIFFERENCE between the remainder and the rounding base (10 in this case). If there is a built in function, please feel free to link it to me in the comments section. If not, I might encapsulate this into my own SelArom.Math class and allow users to specify the base. Hope this was helpful to someone!

// get remainder

int rem = numToRound % 10;

if (rem < 5)

// round down, subtract remainder

rounded -= rem;

else

//round up, add diff b/t 10 and remainder

rounded += (10 - rem);

 

return rounded;

OneNote 2007 Checklist Workaround

I’ve been wanting a checklist system for a long time to keep track of projects and To-Do lists for my programs and web projects. Unfortunately, Outlook 2007 doesn’t support subtasks!I scoured the web looking for an alternative, but found nothing that I liked… Then I remembered OneNote, it’s a great utility for keeping notes and ideas about projects, and I will definitely be using it to log my progress. However, it doesn’t have a checklist feature either! But I found a neat little way to simulate the idea, though not perfect it does the job. All I do is add a table to keep track of the tasks, where each row is a task, and tab over to create a new column on the right for storing the “completed date”. I can indent to add subtasks, and when a task is complete, I highlight it and simply click the “Strikethrough” button so that it is “crossed off” the list. then I tab over click the handy “Date and Time” button. Now I have a log of all my tasks and when they were completed! neat huh? I would like to develop an add-in that will automate this process, but I don’t know how. I’ll be researching it tho, and if I ever figure it out, I’ll post it here! -SelArom

Paging Data in SQL Server 2000

Up until now I’ve been blowing off adding paging support to my web projects. This is very bad practice and I figured it was time to stop. Now I know enough SQL to make a SELECT command (if I have a program that can generate it for me :P) so I didn’t even know where to BEGIN with this one.

Thankfully, the knowlegable folks over at 4GuysFromRolla.com have already done the work for me and gave detailed steps in this handy tutorial that made creating the paging stored procedure a SNAP!

Thanks to their detailed tutorial I was able to modify their stored procedure to make a custom pager for my own projects, and it works GREAT!

Now that I have this procedure I’m going to have to work on the actual page logic to create the paging links and the code to pass the current page into the procedure. I know that the new SQL Server 2005 has newer features that make this simpler to implement, but my host still runs SQL Server 2000, and it’s good to know how to do it in the old school way anyway in case it comes up again, and God knows it will!

-SelArom

Select Query: Retrieve Fixed Number of Rows Part 2

In my last entry, I touched on a way to dynamically select a specific number of rows from a table in a SELECT SQL query. It works great if you are going to supply the value directly when you call the procedure. But what if you are using a static value that is stored in the database itself? I have a Settings table that stores Property/Value pairs, and one of those properties is the NumItems that I want to retrieve from the SELECT query!

I could create a public shared property that will retreive the value from the database, then pass that to the procedure call as a parameter, but that’s a lot of overhead, both in creating a new public property and then issuing two separate calls to the database. It would be a much better idea to inject the code to retrieve that value from within the stored procedure itself, since encapsulating the SELECT command to retrieve the latest entries (or whatever the case may be) logically makes more sense.

It took me a while to learn the correct syntax for decalring variables within SQL (it’s not my best language) but here’s the basic idea (notice I’m still using the SET ROWCOUNT method mentioned before):

 


BEGIN
DECLARE @NumItems int
SET @NumItems = (SELECT [Value] FROM cms_Settings WHERE CMSTable='News' AND [Property]='NumItems')
SET ROWCOUNT @NumItems

 

SELECT ID, Title, Icon, BodySummary, BodyExtended, DateEntered, Author, CategoryID, Lock, AllowComments, AllowRating
FROM cms_News
ORDER BY DateEntered DESC

-- Reset rowcount
SET ROWCOUNT 0

END
GO

 

notice that this method couples the procedure directly to the intended table, preventing it from being used in a general method. But since this is a feature that is exclusively needed for the News table, I think the trade-off is worth the convenience.

As I was writing this, it occured to me that perhaps I could take advantage of the new My namespace in VB.NET to create a Settings entry, but I’ve never used that with a Web Application. I will investigate this strategy at a later time and if it works out I’ll report my findings in a new post.

-SelArom

Select Query: Retrieve Fixed Number of Rows

As I’m beginning work on my senior project, I have discovered the need to select a specific number of rows from a SELECT query in SQL Server 2000. Although the new 2005 version has the built in ability to pass a variable for the TOP clause, no such functionality exists in SQL SERVER 2000. Fortunately I found a great resource showing various ways to accomplish it. The best one in my opinion is to pass the variable into the SET ROWCOUNT command, which can take a variable as a value:

 


CREATE PROCEDURE dbo.getFoo
@top INT
AS
BEGIN
SET ROWCOUNT @top

SELECT foo
FROM blat
ORDER BY foo DESC

-- never forget to set it back to 0!
SET ROWCOUNT 0
END
GO

 

this preserves the security your stored procedure (vs building it as a string) and keeps things relatively tidy. It’s a shame it took them this long to add a built-in mechanism into SQL SERVER but I’m just glad there’s always a workaround 🙂

-SelArom

Editing The Datasource

Ahh it’s all coming back to me now… the headaches that is 😛

One thing that I would always forget how to do with the new asp.net 2.0 data controls is how to enable editing using the datasource. I set up the sql statements for select, insert, update, and delete, but only select would work. when I update something and save, it just reverts back…

the problem is that I forgot to set the Datakeys property for the control which I want to edit. It must be set to the primary key of the table in order to enable “out of the box” editing.

back to work

-SelArom