AboutMe.xml

I have a pet peeve and like most pet peeves it’s an irrelevant petty little annoyance, not quite a huge, humanity, oppressing problem.

My pet peeve is filling out the same information; name, address, city, etc… on paper forms. All that standard information at every doctor’s office, school, activity registration form for my kids, etc… I mean why do I need to keep writing this stuff? And why does somebody else have to take the time to retype it into their system?

Really! In all seriousness … what a waste of time! 5 minutes I’ll never get back, every time I start a new relationship with any organization.

But wait … I have a vision! Not a big glorious, save humanity vision, it’s more of a save each person 5 minutes of writers cramp, kind of vision. Yes! That kind of glorious vision!

I was originally inspired with this in the mid 1990’s. It started out as a question; why can’t doctor’s receptionist retrieve this information from the province when they scan my health card. But since the likelihood of getting the government to add an API for this is slim, it was reduced to something simpler. Like; Why can’t I hand the receptionist at my new doctor a diskette with an ‘aboutme.txt’ file on it, where she can load it into her PC, and give me my diskette back? This would free me up to spend an extra 3-5 minutes browsing the 4 year old magazines during my 76 minute wait to see the doctor.

Over the years, this vision has transformed from an aboutme.txt file on a 3.5” diskette to an aboutme.ini file on a diskette to an aboutme.ini file on a website to an aboutme.html file on website to an aboutme.xml file on a website to an aboutme.xml file on a USB memory stick. I’m not even going to go into ideas I had for RFID, bar coding, or carrying around printed labels in my wallet.

I’ll agree; this isn’t a big problem, but it’s an irritating little annoyance which can be easily overcome with a very simple programming solution. Surely, this would become a reality. Surely, this simple idea would be recognized by others, and implemented.

But alas, the obvious was never realized and because it would be impractical for any organization to expect you to have this aboutme.* file in your back pocket when nobody else had one or was asking for it. It’s the typical chicken / egg scenario; you need one to start the other.

But now I’m inspired again … by Open ID, or possibly another similar centralized authentication mechanism.

When I log into a new site via MyOpenID, I can chose the persona I want revealed to the site I’m logging into for the first time. One of these personas could easily contain standard address information like that required in the types of situations listed previously.

As Open ID reaches critical mass, with more people understanding and adopting it, providing and/or recommending software functionality to accept basic information via an Open ID login will become more realistic.

It’s easy enough to imagine a plausible working process, so I won’t bore you with that. However, there would be serious security concerns regarding logging into a critical authentication mechanism like Open ID from a shared kiosk, so the user would want to log in via their personal cell phone (or laptop or PC or …). And mainstream user adoption has a long way to go before something like this would even be offered, not because of technology, but due to slowly shifting paradigms.

There are obstacles to overcome before this could ever become a reality, but with centralized authentication schemes like Open ID, expecting most people to have an electronic copy of their basic information available will eventually be reasonable, and generic business software applications will start consuming that information.

And one day, hopefully before I die, I won’t have to fill out another one of those stupid forms.

Copyright © John MacIntyre 2009, All rights reserved

WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.

How To Write Dynamic SQL AND Prevent SQL Injection Attacks

One of my pet peeves is when general rules are taken as gospel, and declared as the only acceptable practice regardless of the circumstance.

One of the big ones is Dynamic SQL. There’s a heck of a good reason for this, and it’s called an SQL Injection Attack, and if you are not familiar with it, I would strongly urge you to leave this post right now, and read up on it.

Anyway, Dynamic SQL is not inherently evil, it’s the appending of user entered text that is evil. Appending user entered text is just lazy and can be easily avoided with parameterization.

The trick is to create dynamic SQL with parameters.

Interestingly, I’ve never seen anybody else do this. I am constantly hearing people recommending stored procedures … even when are clearly not flexible enough to meet the required functionality. Don’t get me wrong, stored procedures have a lot of benefits, but flexibility isn’t one it’s popular for.

And now for some code …

I created a console app which queries the SQL Server sample database AdventureWorks. The following static method was added to the Program class.

---

public static int GetOrderCount(string productPrefix,
     SqlConnection cn)
{
  // initialize SQL
  string starterSql = "SELECT count(*) FROM Production.Product";
  StringBuilder sbSql = new StringBuilder(starterSql);

  // add parameters
  if( !String.IsNullOrEmpty(productPrefix))
    sbSql.Append( " where [name] like @namePrefix");

  // initialize the command
  SqlCommand cmd = new SqlCommand(sbSql.ToString(), cn);
  if (cmd.CommandText.Contains("@namePrefix"))
    cmd.Parameters.AddWithValue("@namePrefix", productPrefix + "%");

  // get count
  return Convert.ToInt32( cmd.ExecuteScalar());
}

---

Basically, the function queries the number of orders where the product name starts with a certain prefix.

The strength of doing this via dynamic SQL is we only need to filter on the product name when a valid prefix parameter is passed in. So, if the optional parameter (productPrefix) exists and is valid, the filter condition is added to the SQL and the parameter is added to the SqlCommand object.

In this overly simplified example, we could manage the same thing by just setting the productPrefix variable to the ‘%’ wild card, but then we’d be doing a filter for nothing. Not to mention things might be a little more difficult if the operator were ‘equals’ instead of ‘like’, or if there were multiple optional parameters. Creating SQL dynamically means we don’t need to write some funky kludge and our SQL is always nice, simple, and doing minimal work.

To execute my function, I added the following code to the Main(…) method.

---

// get total count
Console.WriteLine( "There are {0} products in total.",
      Program.GetOrderCount( null, cn));

// get totals for various prefixes
string[] prefixes = new string[6] { "a", "b", "c",
            "'; drop table Production.Product;--",
            "d", "e" };
foreach(string prefix in prefixes)
{
  Console.WriteLine("There are {0} products"
      + " prefixed with '{1}'.",
      Program.GetOrderCount(prefix, cn), prefix);
}

---

First we call GetOrderCount(…) without a name prefix to test it without the parameter, then we traverse the array of possible prefixes (this would be the user entered data in a real app). Notice the fourth item? Pretty menacing eh? Don’t worry, it’s safe.

Here are the results

There are 504 products in total.

---

There are 3 products prefixed with ‘a’.
There are 4 products prefixed with ‘b’.
There are 12 products prefixed with ‘c’.
There are 0 products prefixed with ”; drop table Production.Product;–’.
There are 3 products prefixed with ‘d’.
There are 9 products prefixed with ‘e’.

---

Notice the ‘d’ and ‘e’ prefixes were searched, and items found, proving the ‘drop table’ statement was not injected into the command.

You’d be surprised how much I use this. Many of my objects have a static GetList(…) method, and this method usually has multiple overloads. Keeping with the DRY principle, I prefer to keep all my logic in one place, so this method will usually have one overload with every possible filter parameter, and all the other overloads will just call this one. Surprisingly, the overload with the code, is not overly complex, and is actually pretty simple.

What do you think? Will you use parameterized dynamic sql in the future?

Copyright © John MacIntyre 2009, All rights reserved

WARNING – All source code is written to demonstrate the current concept. It may be unsafe and not exactly optimal.