Return Paged Data from an IQueryable<T>

I finally broke down and wrote a standard extension method for IQueryable<T> that returns a single “page” of data from a source set. This method is terminal if it is in an expression tree in that it returns an IEnumerable<T> and not IQueryable<T>. This method works by calling Count() to get the total number of records (which it sends back in the output parameter itemCount), and then uses the normal .Skip() and .Take() method to return a single page. It should work against any IQueryable<T> including LINQ to SQL and Entity Framework sequences.

/// <summary>
/// Gets a single page of items from a sequence.
/// </summary>
/// <typeparam name="T">The data type of the result items.</typeparam>
/// <param name="query">The sequence</param>
/// <param name="pageNumber">The page number to retrieve, starting at 1.</param>
/// <param name="pageSize">The number of items in each page.</param>
/// <param name="pageCount">Provides the total number of pages available.</param>
/// <returns></returns>
public static IEnumerable<T> TakePage<T>(this IQueryable<T> query, int pageNumber, int pageSize, out int pageCount)
{
    int itemCount;
    return TakePage(query, pageNumber, pageSize, out pageCount, out itemCount);
}

/// <summary>
/// Gets a single page of items from a sequence.
/// </summary>
/// <typeparam name="T">The data type of the result items.</typeparam>
/// <param name="query">The sequence</param>
/// <param name="pageNumber">The page number to retrieve, starting at 1.</param>
/// <param name="pageSize">The number of items in each page.</param>
/// <param name="pageCount">Provides the total number of pages available.</param>
/// <param name="itemCount">Provides the total number of items availabe.</param>
/// <returns></returns>
public static IEnumerable<T> TakePage<T>(this IQueryable<T> query, int pageNumber, int pageSize, out int pageCount, out int itemCount)
{
    if (pageNumber < 1)
        throw new ArgumentException("The value for 'page' must be greater than or equal to 1", "pageNumber");

    itemCount = query.Count();

    pageCount = (int)Math.Ceiling((double)itemCount / (double)pageSize);

    if (pageNumber > pageCount)
        pageNumber = pageCount;

    if (pageNumber > 1)
        return query.Skip((pageNumber - 1) * pageSize).Take(pageSize);
    else
        return query.Take(pageSize);
}

How Not to Future-Proof an Application

I was recently troubleshooting a reported application crash for a client along with a fellow engineer from the client (let’s call him Bob).  A message box along the lines of “An Error Happened, Good Luck” showed up when a user clicked a certain button. Google searches for problems with this application typically turn up one post from 2003 that’s totally unhelpful.


http://xkcd.com/979/

This application is a behemoth comprised of some VB6, .NET and who knows what else running against SQL Server 2000.  Bob and I have some experience with this application, so we started a SQL Profiler trace to look at what was happening when the user clicked “The Button”.  As it turns out, when clicked, it was executing a SQL statement that looked a lot like this:

SELECT * FROM SomeDataTable WHERE SurrogateKey =  ORDER BY SomeDataField

So if you know anything about SQL (spend the next few hours here if you don’t), this statement is wrong. Knowing the application, it looked suspiciously like there was code somewhere like this:

command = "SELECT * FROM SomeDataTable WHERE SurrogateKey = " + SurrogateKeyParam + " ORDER BY SomeDataField"

SurrogateKeyParam was obviously empty for some reason. So Bob started running some queries to look at the data in SomeDataTable, which looked like this:

SurrogateKey CreateDate DataField1 DataField2 DataField3 etc…
10000009 12/6/2012  
10000010 12/6/2012  
10000011 12/6/2012  
10000012 12/6/2012  

I halfway jokingly said to him, “Surely that surrogate key isn’t too big for some reason”. I think we both laughed in a scary “That might really bit it” sort of way, and then proceeded to run the numbers in our heads to see if 10,000,000 crossed some computer science wizard boundary somewhere. It was much too big to have crossed Int16 or UInt16 recently, and well below Int32’s maximum value. So we went back to the data from the day when this issue was first reported. It looked something like this:

SurrogateKey CreateDate DataField1 DataField2 DataField3 etc…
9999998 12/5/2012  
9999999 12/5/2012  
10000000 12/5/2012  
10000001 12/5/2012  

Based on the title of the post, you may already know where this is going. The issue was first reported on 12/5, and the values went from 7 to 8 digits on that day. We were both getting a “That can’t be a coincidence” feeling at this point, and Bob, in his East Coast accent, said something to the effect of “That can’t really be it”. We have both known there has to be some unplanned obsolescence in this application somewhere, and we were both hoping this wasn’t it.

So off to the “Test” environment we went. “Test” was well below reaching 8 digits for this value, and clicking “The Button” worked without a problem. Since the surrogate key values are allocated from a sequence table, we artificially set the value up to 9,999,999 and then clicked “The Button”. BAM! The “An Error Happened, Good Luck” message appeared. Confirmation of our worst fear, somewhere in the vast expanse of the GUI code mess that is this application, there was a variable or statement that was arbitrarily limiting the value of SurrogateKey to a length of 7 characters for who-knows-what reason. However, it wasn’t just trimming the value, somehow it was ending up NULL or empty.

Since we don’t have the source, we can only guess this is the result of incorrect data types and/or maximum lengths on an ADO Recordset, or worse (I guess) an = LEFT(SurrogateKey,7) is somewhere in the code. Who knows what it is really.

So after doing a quick search to see if anyone else was hiring so we could both quit before the helpdesk called back to check on our progress, we realized the data in this table is only used temporarily and then deleted.  Nothing else outside the process that “The Button” is used for references the SurrogateKey column.  Our fix was as easy as writing the following query:

UPDATE SequenceTable SET SurrogateKey=1 WHERE TableName=’SomeDataTable’

We also made the necessary sacrifices to the volcano in the event this breaks something, and may the Lords of Kobol help us when (not if), we see this error on a table that needs referential integrity. In the past, tinkering in the database manually has caused numerous problems, but we had no other resolution.

So next time you are thinking to yourself, “I’ll just shove these Int32 values into 7 character strings”, don’t. Plan for your application to be used 5 years past the “Surely they will upgrade by now” or “surely they will never get to X” date. This one is going on 6 years past.

ASP.NET MVC HtmlHelper Extension Method for Menu Highlighting

I built an extension method on the ASP.NET MVC HtmlHelper class to handle the creation of navigation menu links (tabs) that have a different CSS class applied to them if they are active (i.e. the current page). I had been doing this manually in the master layout (_Layout.cshtml) view with a bunch of if blocks. Of course you can roll out a full-fledged sitemap to handle this, but I didn’t want to.

/// <summary>
/// Extension method for <see cref="HtmlHelper"/> to support highlighting the active tab on the default MVC menu
/// </summary>
/// <param name="htmlHelper"></param>
/// <param name="linkText">The text to display in the link</param>
/// <param name="actionName">Link target action name</param>
/// <param name="controllerName">Link target controller name</param>
/// <param name="activeClass">The CSS class to apply to the link if active</param>
/// <param name="checkAction">If true, checks the current action name to determine if the menu item is 'active', otherwise only the controller name is matched</param>
/// <returns></returns>
public static MvcHtmlString MenuLink(this HtmlHelper htmlHelper, string linkText, string actionName, string controllerName, string activeClass, bool checkAction)
{
    string currentAction = htmlHelper.ViewContext.RouteData.GetRequiredString("action");
    string currentController = htmlHelper.ViewContext.RouteData.GetRequiredString("controller");

    if (string.Compare(controllerName, currentController, StringComparison.OrdinalIgnoreCase) == 0 && ((!checkAction) || string.Compare(actionName, currentAction, StringComparison.OrdinalIgnoreCase) == 0))
    {
        return htmlHelper.ActionLink(linkText, actionName, controllerName, null, new { @class = activeClass });
    }

    return htmlHelper.ActionLink(linkText, actionName, controllerName);
    
}

activeClass sets the CSS class name that will be applied, and setting checkAction to true applies the class only if the action name and controller name match.

So my in my _Layout.cshtml view, I changed all the Html.ActionLink calls to Html.MenuLink and added the active and checkAction parameters.

<nav>
    <ul id="menu">                        
        <li>@Html.MenuLink("Home", "Index", "Home", "active", true)</li>
        <li>@Html.MenuLink("About", "About", "Home", "active", true)</li>
        <li>@Html.MenuLink("Contact", "Contact", "Home", "active", true)</li>
        <li>@Html.MenuLink("Something Else", "Index", "Something", "active", false)</li>
    </ul>
</nav>   

The Home, About and Contact actions are all handled by the HomeController, and since they are distinct menu choices, we set checkAction to true. The Something Else option, on the other hand, should be highlighted anytime we hit an action on the SomethingController.

One final tweak was to add an overload to default the checkAction parameter to true.

public static MvcHtmlString MenuLink(this HtmlHelper htmlHelper, string linkText, string actionName, string controllerName, string activeClass)
{
    return MenuLink(htmlHelper, linkText, actionName, controllerName, activeClass, true);
}

And the updated view code:

<nav>
    <ul id="menu">                        
        <li>@Html.MenuLink("Home", "Index", "Home", "active")</li>
        <li>@Html.MenuLink("About", "About", "Home", "active")</li>
        <li>@Html.MenuLink("Contact", "Contact", "Home", "active")</li>
        <li>@Html.MenuLink("Something Else", "Index", "Something", "active", false)</li>
    </ul>
</nav> 

Secure Remote Access through Application Specific and One-time Passwords

This is the first part of a series I will be doing on how I implemented one-time password support for remote access to resources inside my home network such as my webmail client, along with supporting application and device specific passwords for use on my mobile phone, tablet, etc.

I always hate the feeling of using any of my username and password combinations on sketchy public computer somewhere. You know the kind I am talking about, those computers at hotels running Windows XP and IE6, signed as "Adminstrator", with every toolbar and add-on installed from a 9 year old version of Real Player to three different versions of some Internet poker game. There's bound to be a key logger in there someplace.

One time use passwords have been around for a long time to mitigate this type of scenario. As the name suggests a one-time password, is valid only once. In theory if someone sees or captures that password, it's worthless to them. Typically one time passwords are accompanied by a normal password, or a PIN number. This mostly satisfies Two Factor Authentication, which requires something you know (the password or PIN), and something you have (the phone giving you the password). Google started offering one-time-password support for Google accounts through the 2-step verification system and the Authenticator app.

So we have three different surfaces to protect:

  • Apache HTTP access to the Roundcube webmail client.
  • Postfix SMTP access to send mail from an external e-mail.
  • Dovecot IMAP access to retrieve mail from an external e-mail client.

We want to be able to use our one-time password to access #1, but since an e-mail client may login many times during a single session, #2 and #3 are better served by an application specific password that is sufficiently random but never changes.

So I set out by sketching out a few requirements:

  1. Make use of my existing e-mail platform (Ubuntu 12.04 + Postfix + Dovecot + RoundCube on Apache).
  2. Close any existing access points into home network via simple username/password combinations.
  3. Support application/device specific passwords for IMAP and SMTP clients (thunderbird, etc.).
  4. Support one-time passwords to access RoundCube webmail.
  5. Phone app to generate one-time passwords.
  6. Backup codes that can be printed to use in the case where the app is not inaccessible.

Application Specific Passwords

Since the first step is to prevent logins to IMAP and SMTP from outside the firewall with passwords we may be typing in on public computer, we need to provide secure passwords that will only be entered once into a device or application to configure. We don’t need to remember these passwords, so we can revoke them and re-configure an application or device at anytime.

Dovecot is configured to authenticate users against PAM, and PAM is configured to authenticate users with mod-auth-kerb. Postfix is configured to authenticate via SASL to Dovecot. So ultimately, there is a single username/password for all users through my Kerberos database.

Dovecot separates the concept of a user database and password database, so I can keep my existing user database (Linux passwd, LDAP, etc), and just alter the password database. To add additional password validation options to Dovecot, you simply add more passdb options to the configuration file. One of those options happens to be a MySQL, so I went ahead and made a simple database and table to store our application specific passwords.

CREATE TABLE `dovecot_passwords` (
  `username` varchar(100) NOT NULL,
  `appname` varchar(50) NOT NULL,
  `password` varbinary(256) NOT NULL,
  PRIMARY KEY (`username`,`appname`)
)

You will notice username and appname make the unique key here, since we want to have multiple passwords for the same account. The data in this table might look something like this:

username appname password
justin K9 Mail on Phone ********
justin Thunderbird on Laptop ********
justin Thunderbird on Desktop ********
sarah iPhone ********

The value in the password field is the MD5 hash of the password without any whitespace (Yes, it should be salted and maybe using SHA1 instead). In order to make the application specific passwords more secure, I’m using rather long passwords, and so when I generate them I usually format them in blocks of four separated by spaces, such as xRtg Dbea 4d9g aP44. This is easier to type into a mobile keyboard while glancing back and forth between the device and the keyboard. The password database will need to ignore the whitespace, because we don’t care either way if it is there. For now I'll just manage entries in this table manually, but later I plan on writing a fancy CLI or GUI tool. So to insert new records in this table, I generate a random password, and do an insert:

INSERT INTO dovecot_passwords (username, appname, password) VALUES( 'justin', 'smartphone', MD5('xRtgDbea4d9gaP44') );

Now I can configure Dovecot to check this password database instead of PAM by changing my passdb entry to use the SQL driver instead of PAM.

#/etc/dovecot/dovecot.conf
passdb { 
    args = /etc/dovecot/dovecot-sql-other.conf 
    driver = sql 
}

And the associated SQL config file:

#/etc/dovecot/dovecot-sql-other.conf 
driver = mysql 
connect = host=localhost dbname=mail_db user=dovecot password=******** 
default_pass_scheme = PLAIN 
password_query = SELECT NULL AS password,'Y' as nopassword, username AS user 
                FROM dovecot_passwords
                WHERE username = '%u' AND password=MD5(REPLACE('%w',' ',''))

You can read more about how this configuration file works on the Dovecot Wiki page for SQL passdb, but essentially my query is removing any whitespace from the supplied password, and matching the MD5 hash. To really make this secure, we should be adding a password salt into the mix and probably using SHA1 for the hash algorithm. It is worth mentioning, that if you want to support regular username/password authentication via PAM for users on the internal network, but the application specific passwords everywhere else, this is possible by adding the pam_access module into your PAM configuration for Dovecot.

So now we have satisfied requirements #1, #2, and #3. Dovecot (and Postfix by means of SASL) will now authenticate users against the custom password database, and will not authenticate users with their old username/password.

Next up: Implementing #4, #5, #6 to support One-time passwords.

I Broke Remote Access to Hyper-V Server

In the process of setting up a new Hyper-V 2008 R2 server, I accidentally disabled "Host Access" to both network cards, thus killing remote access to the server (which is in my basement). Since I'm running Hyper-V Server which has no full GUI like Server Core installs, I don't have access to the normal Network Connections GUI to unbind the virtual switch protocol from the network cards.

I did some searching around the web, and all the solutions I found involve downloading some scripts or tools, and since I'm too lazy to put one of these tools on a USB drive and walk downstairs to run them, I wanted a solution that I could run from the command line remotely (as I have access to the server via Intel AMT's VNC KVM). I finally found what I was looking for on this blog post at ENIAC KB.

So, the steps are simple:

  1. Remove the virtual switch protocol from all network adapters:
    netcfg -u vms_pp
  2. Reboot
    shutdown /r
  3. Re-install the virtual switch protocol (which will leave it disabled by default):
    netcfg -l c:\windows\winsxs\amd64_wvms_pp.inf_31bf3856ad364e35_6.1.7600.16385_none_beda85050b13680c\wvms_pp.inf -c p -i vms_pp

So if you broke remote access to your Hyper-V server under server core, and you can still get access to the CLI either through a remote KVM or at the physical console, you can uninstall the Virtual switch protocol, reboot, re-install, and continue your network configuration from VMM or the Hyper-V Console remotely.