Article, Software Development

Test Driven Development By Example

Introduction

A lot has been written on the subject of test driven development, and especially on the idea that tests ought to be written first. This is an ideal that I strive for, however, I have a tendency to write the unit tests afterwards.

Some people learn better by example. This article, rather than going in to great length about the principles of test driven development, will walk the reader through the process of building and testing an algorithm by writing the tests first, then changing the method being tested so that it fulfils the tests.

The final code and all the unit tests can be found in the accompanying download. This will require NUnit and Visual Studio 2005.

The specimen problem

I once saw a demo of how to create unit tests up front for a simple method. The method took a positive integer and turned it into roman numerals. So, I’m going to do something similar. I’m going to take an integer and turn it into words, in English. The rules for this may change depending on the language, so if English is not your only language, you may like to try to repeat this exercise in another language.

So, if the integer is 1, the result will be "one". If the integer is 23 the result will be "twenty three" and so on. Also note, I’ll be using British or Commonwealth English. So, for 101 the result in words is "one hundred and one". In American English it would be "one hundred one"

The walk through

The algorithm will also be refined through refactoring techniques. Agile development methodologies, especially eXtreme Programming, suggests that you do the simplest thing possible to get the thing working. So, going by this premise, I’ll work on the solution in bits. First, get it to return "one", then "one" or "two" depending on the input, and so on. Once 21 is reached it should become obvious where some refactoring can take place and so on. The final solution will work for 32 bit integers only.

Getting Started

Visual Studio 2005 has some features that can help with writing the tests first. A test can be written that calls into the class under test and the smart tags will prompt you with an offer to create the message stub for you.

The stub looks like this:

public static string NumberToEnglish(int p)
{
  throw new Exception("The method or operation is not implemented.");
}

If the test is completed to look like this:

[Test]
public void NumberToEnglishShouldReturnOne()
{
  string actual = English.NumberToEnglish(1);
  Assert.AreEqual("one", actual, "Expected the result to be "one"");
}

The test should fail because the stub throws an exception, rather than do what the test expects.

NUnit reports the error like this: "NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnOne : System.Exception : The method or operation is not implemented.".

The next thing to do is to ensure that the code satisfies the demands of the unit test. Agile methodologies, such as XP, say that only the simplest change should be made to satisfy the current requirements. In that case the method being tested will be changed to look like this:

public static string NumberToEnglish(int number)
{
  return "one";
}

At this point the unit tests are re-run and they all work out.

Test "two"

Since the overall requirement is that any integer should be translatable into words, the next test should test that 2 can be translated. The test looks like this:

[Test]
public void NumberToEnglishShouldReturnTwo()
{
  string actual = English.NumberToEnglish(2);
  Assert.AreEqual("two", actual, "Expected the result to be "two"");
}

However, since the method being tested returns "one" regardless of input at this point the test fails:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnTwo :
Expected the result to be "two"
	String lengths are both 3.
	Strings differ at index 0.
	expected: <"two">
	 but was: <"one">
	------------^

Again, keeping with the simplest change principle the code is updated to look like this:

public static string NumberToEnglish(int number)
{
  if (number == 1)
    return "one";
  else
    return "two";
}

The tests now pass.

Test "three" to "twenty"

A third test can now be written. It tests for an input of 3 and an expected return of "three". Naturally, at this point, the test fails. The code is updated again and now looks like this:

public static string NumberToEnglish(int number)
{
  switch (number)
  {
    case 1:
      return "one";
    case 2:
      return "two";
    default:
      return "three";
  }
}

To cut things short the new tests and counter-updates continue like this until the numbers 1 to 20 can be handled. The code will eventually look like this:

public static string NumberToEnglish(int number)
{
  switch (number)
  {
    case 1:
      return "one";
    case 2:
      return "two";
    case 3:
      return "three";
    case 4:
      return "four";
    case 5:
      return "five";
    case 6:
      return "six";
    case 7:
      return "seven";
    case 8:
      return "eight";
    case 9:
      return "nine";
    case 10:
      return "ten";
    case 11:
      return "eleven";
    case 12:
      return "twelve";
    case 13:
      return "thirteen";
    case 14:
      return "fourteen";
    case 15:
      return "fifteen";
    case 16:
      return "sixteen";
    case 17:
      return "seventeen";
    case 18:
      return "eighteen";
    case 19:
      return "nineteen";
    default:
      return "twenty";
  }
}

Test "twenty one" to "twenty nine"

At this point it looks like it will be pretty easy to do 21, but a pattern is about to emerge. After the tests for 21 and 22 have been written, the code is refactored to look like this:

public static string NumberToEnglish(int number)
{
  if (number < 20)
    return TranslateOneToNineteen(number);
  if (number == 20)
    return "twenty";
  return string.Concat("twenty ", TranslateOneToNineteen(number - 20));
}

private static string TranslateOneToNineteen(int number)
{
  switch (number)
  {
    case 1:
      return "one";
    case 2:
      return "two";
    case 3:
      return "three";
    case 4:
      return "four";
    case 5:
      return "five";
    case 6:
      return "six";
    case 7:
      return "seven";
    case 8:
      return "eight";
    case 9:
      return "nine";
    case 10:
      return "ten";
    case 11:
      return "eleven";
    case 12:
      return "twelve";
    case 13:
      return "thirteen";
    case 14:
      return "fourteen";
    case 15:
      return "fifteen";
    case 16:
      return "sixteen";
    case 17:
      return "seventeen";
    case 18:
      return "eighteen";
    default:
      return "nineteen";
  }
}

Now all the tests from 1 to 22 pass. 23 to 29 can be assumed to work because it is using well tested logic.

Test "thirty" to "thirty nine"

30 is a different story. The test will fail like this:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnThirty :
Expected the result to be "thirty"
	String lengths differ.  Expected length=6, but was length=10.
	Strings differ at index 1.
	expected: <"thirty">
	 but was: <"twenty ten">
	-------------^

By using the principle of doing the simplest thing that will work. The public method changes to:

public static string NumberToEnglish(int number)
{
  if (number < 20)
    return TranslateOneToNineteen(number);
  if (number == 20)
    return "twenty";
  if (number <= 29)
    return string.Concat("twenty ", TranslateOneToNineteen(number - 20));
  return "thirty";
}

Naturally, the test for 31 will fail:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnThirtyOne :
Expected the result to be "thirty one"
	String lengths differ.  Expected length=10, but was length=6.
	Strings differ at index 6.
	expected: <"thirty one">
	 but was: <"thirty">
	------------------^

So the code is changed again. This time to:

public static string NumberToEnglish(int number)
{
  if (number < 20)
    return TranslateOneToNineteen(number);
  if (number == 20)
    return "twenty";
  if (number <= 29)
    return string.Concat("twenty ", TranslateOneToNineteen(number - 20));
  if (number == 30)
    return "thirty";
  return string.Concat("thirty ", TranslateOneToNineteen(number - 30));
}

Test "forty" to "ninety nine"

A test for 40 will fail:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnForty :
Expected the result to be "forty"
	String lengths differ.  Expected length=5, but was length=10.
	Strings differ at index 0.
	expected: <"forty">
	 but was: <"thirty ten">
	------------^

The necessary code change starts to draw out a pattern. Of course, the pattern could have been quite easily predicted, but since this code is being built by the simplest change only rule, the pattern has to emerge before it can be acted upon.

The pattern repeats itself until it gets to 99. By this point the public method looks like this:

public static string NumberToEnglish(int number)
{
  if (number < 20)
    return TranslateOneToNineteen(number);
  int units = number % 10;
  int tens = number / 10;
  string result = "";
  switch (tens)
  {
    case 2:
      result = "twenty";
      break;
    case 3:
      result = "thirty";
      break;
    case 4:
      result = "forty";
      break;
    case 5:
      result = "fifty";
      break;
    case 6:
      result = "sixty";
      break;
    case 7:
      result = "seventy";
      break;
    case 8:
      result = "eighty";
      break;
    default:
      result = "ninety";
      break;
  }
  if (units != 0)
    result = string.Concat(result, " ", TranslateOneToNineteen(units));
  return result;
}

Test "one hundred"

The test for 100 will fail. The failure message is:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnOneHundred :
Expected the result to be "one hundred"
	String lengths differ.  Expected length=11, but was length=6.
	Strings differ at index 0.
	expected: <"one hundred">
	 but was: <"ninety">
	------------^

A quick change to the public method allows the test to pass:

public static string NumberToEnglish(int number)
{
  if (number == 100)
    return "one hundred";

  if (number < 20)
    return TranslateOneToNineteen(number);
  // Remainder omitted for brevity
}

Test "one hundred and one" to "one hundred and ninety nine"

What about 101? That test fails like this:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnOneHundredAndOne :
Expected the result to be "one hundred and one"
	String lengths differ.  Expected length=19, but was length=10.
	Strings differ at index 0.
	expected: <"one hundred and one">
	 but was: <"ninety one">
	------------^

At this point it should be easy to see that some of the work that has been done previously can be re-used with a small amount of refactoring. First refactor most of the body of the public method into a class called TranslateOneToNinetyNine. Then re-test to ensure that the refactoring process hasn’t introduced any new problems.

In Visual Studio 2005 it is very easy to highlight some code and extract it into a new method, thus allowing it to be reused by being called from multiple places.

Now the public method looks like the following and all previously successful tests continue to be successful

public static string NumberToEnglish(int number)
{
  if (number == 100)
    return "one hundred";

  return TranslateOneToNinetyNine(number);
}

For numbers from 101 to 199 the pattern is "one hundred and X" where X is the result of the translation between 1 and 99. Because it would take too long to write all those tests, it is possible to write just the edge cases and one or two samples from the middle of the range. That should give enough confidence to continue onwards. In this case, the tests are for 101, 115, 155 and 199.

The code is then re-written to support those tests:

public static string NumberToEnglish(int number)
{
  if (number < 100)
    return TranslateOneToNinetyNine(number);
  if (number == 100)
    return "one hundred";

  string result = string.Concat("one hundred and ",
    TranslateOneToNinetyNine(number - 100));

  return result;
}

Test "two hundred"

The next test to write is for 200. Naturally, this test will fail at this point as the code doesn’t support it. The test looks like this:

[Test]
public void NumberToEnglishShouldReturnTwoHundred()
{
    string actual = English.NumberToEnglish(200);
    Assert.AreEqual("two hundred", actual, "Expected the result to be "two hundred"");
}

The failing test can be predicted. It looks like this:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnTwoHundred :
Expected the result to be "two hundred"
	String lengths differ.  Expected length=11, but was length=22.
	Strings differ at index 0.
	expected: <"two hundred">
	 but was: <"one hundred and ninety">
	------------^

A simple change to the method can get the test passing:

public static string NumberToEnglish(int number)
{
    if (number < 100)
        return TranslateOneToNinetyNine(number);
    if (number == 100)
        return "one hundred";
    if (number == 200)
        return "two hundred";

    string result = string.Concat("one hundred and ",
        TranslateOneToNinetyNine(number - 100));

    return result;
}

Test "two hundred and one" to "two hundred and ninety nine"

Since the next part of the pattern can be seen as very similar to the one hundreds, the two edge cases and a couple of internal cases for the two hundreds are created. Presently, all those tests fail as the method has not been updated to take account of that range of integers.

In order to get those tests working the code is refactored like this:

public static string NumberToEnglish(int number)
{
    if (number < 100)
        return TranslateOneToNinetyNine(number);
    if (number == 100)
        return "one hundred";
    if (number < 200)
        return string.Concat("one hundred and ",
        TranslateOneToNinetyNine(number - 100));
    if (number == 200)
        return "two hundred";

    return string.Concat("two hundred and ",
        TranslateOneToNinetyNine(number - 200));
}

Test "three hundred" to "nine hundred and ninety nine"

From the last change to the method a pattern can be seen to be emerging. The code for dealing with the one hundreds and two hundreds are almost identical. This can be easily changed so that all positive integers between 1 and 999 can be converted into words.

After various unit tests are written to test values from 300 to 999 the code is changed to this:

public static string NumberToEnglish(int number)
{
    if (number < 100)
        return TranslateOneToNinetyNine(number);
    int hundreds = number / 100;
    string result = string.Concat(TranslateOneToNineteen(hundreds),
        " hundred");
    int remainder = number % 100;
    if (remainder == 0)
        return result;

    return string.Concat(result, " and ", TranslateOneToNinetyNine(remainder));
}

Test "one thousand"

As before, the first thing to do is write the test:

[Test]
public void NumberToEnglishShouldReturnOneThousand()
{
    string actual = English.NumberToEnglish(1000);
    Assert.AreEqual("one thousand", actual, "Expected the result to be "one thousand"");
}

Which fails:

NumbersInWords.Test.EnglishTest.NumberToEnglishShouldReturnOneThousand :
Expected the result to be "one thousand"
	String lengths differ.  Expected length=12, but was length=11.
	Strings differ at index 0.
	expected: <"one thousand">
	 but was: <"ten hundred">
	------------^

And the fix:

public static string NumberToEnglish(int number)
{
    if (number == 1000)
        return "one thousand";

    if (number < 100)
        return TranslateOneToNinetyNine(number);
    int hundreds = number / 100;
    string result = string.Concat(TranslateOneToNineteen(hundreds),
        " hundred");
    int remainder = number % 100;
    if (remainder == 0)
        return result;

    return string.Concat(result, " and ", TranslateOneToNinetyNine(remainder));
}

Test "one thousand and one" to "nine thousand nine hundred and ninety nine"

Some forward thinking will reveal that the logic will be similar for the thousands as it was for the hundreds. So, rather than repeat all that in this article, the steps to refactoring the code to work with the range from 101 to 999 can be used similarly with 1001 to 9999. The accompanying download will show all the unit tests.

The final result of this stage is that the public method has been refactored to this:

public static string NumberToEnglish(int number)
{
    if (number < 1000)
        return TranslateOneToNineHundredAndNinetyNine(number);

    int thousands = number / 1000;
    string result = string.Concat(TranslateOneToNineteen(thousands),
        " thousand");
    int remainder = number % 1000;
    if (remainder == 0)
        return result;

    if (remainder < 100)
        return string.Concat(result, " and ",
            TranslateOneToNinetyNine(remainder));

    return string.Concat(result, " ",
        TranslateOneToNineHundredAndNinetyNine(remainder));
}

private static string TranslateOneToNineHundredAndNinetyNine(int number)
{
    if (number < 100)
        return TranslateOneToNinetyNine(number);
    int hundreds = number / 100;
    string result = string.Concat(TranslateOneToNineteen(hundreds),
        " hundred");
    int remainder = number % 100;
    if (remainder == 0)
        return result;

    return string.Concat(result, " and ", TranslateOneToNinetyNine(remainder));
}

Test "ten thousand" to "nine hundred and ninety nine thousand nine hundred and ninety nine"

In the first set of test, that were expected to fail, for the condition that the integer input was a value greater than 9999 actually shows passing tests. This serendipitous circumstance is caused by the TranslateOneToNineteen method being a compatible match for prefixing the "thousand" for a range up to the 19 thousands. Through this code reuse it is possible to get a full range match all the way up to 999999 with only a change to a part of one line of code.

The public method has now changed to:

public static string NumberToEnglish(int number)
{
    if (number < 1000)
        return TranslateOneToNineHundredAndNinetyNine(number);

    int thousands = number / 1000;
    string result = string.Concat(TranslateOneToNineHundredAndNinetyNine(thousands),
        " thousand");
    int remainder = number % 1000;
    if (remainder == 0)
        return result;

    if (remainder < 100)
        return string.Concat(result, " and ",
            TranslateOneToNinetyNine(remainder));

    return string.Concat(result, " ",
        TranslateOneToNineHundredAndNinetyNine(remainder));
}

Test "one million" to "nine hundred and ninety nine million nine hundred and ninety nine thousand nine hundred and ninety nine"

The way the code changes as the number of digits increases should becoming more apparent by now. The amount of code reuse is increasing. The number of necessary tests is decreasing. Confidence is increasing

At the start the first 20 numbers each had their own test. 100% of inputs had a test. From 20 to 99 it was 20 tests. Only 25% of the inputs had tests. From 100 to 999 there were 18 tests. Just 2% of the inputs had tests. From 1000 to 999999 there were, again, just 18 tests. This represents just 2 thousandths of one percent.

At this point the public method has been refactored to look like this:

public class English
{
public static string NumberToEnglish(int number)
{
    if (number < 1000000)
        return TranslateOneToNineHundredAndNinetyNineThousandNineHundredAndNinetyNine(number);

    int millions = number / 1000000;
    string result = string.Concat(TranslateOneToNineHundredAndNinetyNine(millions),
        " million");
    int remainder = number % 1000000;
    if (remainder == 0)
        return result;

    if (remainder < 100)
        return string.Concat(result, " and ",
            TranslateOneToNinetyNine(remainder));

    return string.Concat(result, " ",
        TranslateOneToNineHundredAndNinetyNineThousandNineHundredAndNinetyNine(remainder));
}

private static string TranslateOneToNineHundredAndNinetyNineThousandNineHundredAndNinetyNine(int number)
{
    if (number < 1000)
        return TranslateOneToNineHundredAndNinetyNine(number);

    int thousands = number / 1000;
    string result = string.Concat(TranslateOneToNineHundredAndNinetyNine(thousands),
        " thousand");
    int remainder = number % 1000;
    if (remainder == 0)
        return result;

    if (remainder < 100)
        return string.Concat(result, " and ",
            TranslateOneToNinetyNine(remainder));

    return string.Concat(result, " ",
        TranslateOneToNineHundredAndNinetyNine(remainder));
}

Test "one billion" upwards

The limitations of an integer (Int32) mean that this section reaches the upper limits of 2147483647. Unless an Int64 is used there is no continuation to the trillion range.

Final stages

To this point all positive integers are successfully being translated from an integer into a string of words. At this point, through code reuse, it should be a fairly simple matter to refactor the code to work with negative numbers and zero.

Zero is easy enough. The unit test is put in place:

[Test]
public void NumberToEnglishShouldReturnZero()
{
  string actual = English.NumberToEnglish(0);
  Assert.AreEqual("zero", actual, "Expected the result to be "zero"");
}

And it promptly fails because there is no code to support it. The public method is changed so that it does a quick check at the start:

public static string NumberToEnglish(int number)
{
  if (number == 0)
      return "zero";
  if (number < 1000000000)
      return TranslateOneToNineHundredAndNintyNineMillion...(number);

  int billions = number / 1000000000;
  string result = string.Concat(TranslateOneToNineteen(billions), " billion");

  int remainder = number % 1000000000;
  if (remainder == 0)
    return result;

  if (remainder < 100)
    return string.Concat(result, " and ", TranslateOneToNinetyNine(remainder));

  return string.Concat(result, " ",
    TranslateOneToNineHundredAndNintyNineMillion...(remainder));
}

Now the test passes. Next is to permit negative numbers.

[Test]
public void NumberToEnglishShouldReturnNegativeOne()
{
  string actual = English.NumberToEnglish(-1);
  Assert.AreEqual("negative one", actual, "Expected the result to be "negative one"");
}

This fails so the code is refactored to this:

public static string NumberToEnglish(int number)
{
  if (number == 0)
    return "zero";

  string result = "";
  if (number < 0)
    result = "negative ";

  int absNumber = Math.Abs(number);

  return string.Concat(result,
    TranslateOneToTwoBillion...SixHundredAndFortySeven(absNumber));
  // Method call above contracted for brevity
}

And the test passes. But what about that final edge case? int.MinValue? The test is written but it fails. The reason is thatMath.Abs(int.MinValue) isn’t possible. So, as this is a one off case, the easiest solution is to put in a special case into the public method:

// Special case for int.MinValue.
if (number == int.MinValue)
  return "negative two billion one hundred and forty seven million " +
    "four hundred and eighty three thousand six hundred and forty eight";

Conclusion

This article demonstrates how to unit test and build a piece of code in small increments. The tests continually prove that the developer is on the right path. As the code is built the constant rerunning of existing tests prove that any new enhancements do not break existing code. If, for any reason, it is later found that the code contains a bug, a test can easily be created that exercises that incorrect code and a fix produced.

The full final code is available in the associated download along with a set of NUnit tests.

Downloads

You can download the example code for this article here.

Article

SQL Injection Attacks and Some Tips on How to Prevent Them

Introduction

Security in software applications is an ever more important topic. In this article, I discuss various aspects of SQL Injection attacks, what to look for in your code, and how to secure it against SQL Injection attacks. Although the technologies used here are SQL Server 2000 and the .NET Framework, the general ideas presented apply to any modern data driven application framework, which makes attacks potentially possible on any type of application that depends on that framework.

What is a SQL Injection Attack?

A SQL Injection attack is a form of attack that comes from user input that has not been checked to see that it is valid. The objective is to fool the database system into running malicious code that will reveal sensitive information or otherwise compromise the server.

There are two main types of attacks. First-order attacks are when the attacker receives the desired result immediately, either by direct response from the application they are interacting with or some other response mechanism, such as email. Second-order attacks are when the attacker injects some data that will reside in the database, but the payload will not be immediately activated. I will discuss each in more detail later in this article.

An example of what an attacker might do

In the following example, assume that a web site is being used to mount an attack on the database. If you think about a typical SQL statement, you might think of something like:

SELECT ProductName, QuantityPerUnit, UnitPrice
FROM Products
WHERE ProductName LIKE 'G%'

The objective of the attacker is to inject their own SQL into the statement that the application will use to query the database. If, for instance, the above query was generated from a search feature on a web site, then they user may have inserted the “G” as their query. If the server side code then inserts the user input directly into the SQL statement, it might look like this:

string sql = "SELECT ProductName, QuantityPerUnit, UnitPrice "+
    "FROM Products " +
    "WHERE ProductName LIKE '"+this.search.Text+"%';
SqlDataAdapter da = new SqlDataAdapter(sql, DbCommand);
da.Fill(productDataSet);

This is all fine if the data is valid, but what if the user types something unexpected? What happens if the user types:

' UNION SELECT name, type, id FROM sysobjects;--

Note the initial apostrophe; it closes the opening quote in the original SQL statement. Also, note the two dashes at the end; that starts a comment, which means that anything left in the original SQL statement is ignored.

Now, when the attacker views the page that was meant to list the products the user has searched for, they get a list of all the names of all the objects in the database and the type of object that they are. From this list, the attacker can see that there is a table called Users. If they take note of the id for the Users table, they could then inject the following:

' UNION SELECT name, '', length FROM syscolumns
WHERE id = 1845581613;--

This would give them a list of the column names in the Users table. Now they have enough information to get access to a list of users, passwords, and if they have admin privileges on the web site.

' UNION SELECT UserName, Password, IsAdmin FROM Users;--

Assume that there is a table called Users which has columns called UserName and Password, it is possible to union that with the original query and the results will be interpreted as if the UserName was the name of the product and the Password was the quantity per unit. Finally, because the attacker discovered that there is a IsAdmin column, they are likely to retrieve the information in that too.

Locking down

Security is something that needs to be tackled on many levels because a chain is only as strong as its weakest link. When a user interacts with a piece of software, there are many links in the chain; if the user is malicious, he could attempt to attack these links to find the weak point and attempt to break the system at that point. With this in mind, it is important that the developer does not become complacent about the security of the system because one security measure is put in place, or a set of security measures are in place on only one part of the system.

An intranet website that uses Windows authentication (it takes the user’s existing credentials based on who they are logged in as) and is sitting inside the corporate network and unavailable to Internet users may give the impression that only authorised users can access the intranet web application. However, it is possible for an authenticated user to gain unauthorised access if the security is not taken much beyond that level. Some statistics support the suggestion that most security breaches are insider jobs rather than people attacking the system from outside.

With this in mind, it is important that even if the application permits only valid data through that has been carefully verified and cleaned up, other security measures are put in place. This is especially important between application layers where there may be an increased opportunity for spoofing of requests or results.

For example, if a web application were to request that the user choose a date, then it would be normal that the values for the date are checked in some JavaScript function on the web page before any data was posted back to the server. This improves the user experience by reducing the wait between lots of server requests. However, the value needs to be validated again on the server as it is possible to spoof the request with a deliberately crafted invalid date.

Encrypting data

Starting from the proposition that somehow an attacker has managed to break through all other defenses, what information is so sensitive that it needs to remain a secret? Candidates for encryption include user log in details or financial information such as credit card details.

For items such as passwords, the user’s password can be stored as a “salted hash”. What happens is that when a user creates a password, a randomly generated “salt” value is created by the application and appended to the password, and the password-and-salt are then passed through a one way encryption routine, such as found in the .NET Framework’s helper class FormsAuthentication (HashPasswordForStoringInConfigFile method). The result is a salted hash which is stored in the database along with the clear text salt string.

The value of a salted hash is such that a dictionary attack is not going to work as each dictionary would have to be rebuilt appending the various salt values and recomputing the hash values for each item. While it is still possible to determine the password by brute force, the use of the salt (even though it is known) greatly slows down the process. The second advantage of the salt is that it masks any situations where two independent users happen to use the same password, as the salted hash value for each user would be different if given different salt values.

Least Privilege – Database account

Running an application that connects to the database using the database’s administrator account has the potential for an attacker to perform almost limitless commands with the database. Anything an administrator can do, so can an attacker.

Using the example application above, an attacker could inject the following to discover the contents of the hard disk(s) on the server.

The first command is used to create a temporary store on the database and fill it with some data. The following injected code will create a table with the same structure as the result set of the extended stored procedure that will be called. It then populates the table with the results of the extended stored procedure.

'; CREATE TABLE haxor(name varchar(255), mb_free int);
INSERT INTO haxor  EXEC master..xp_fixeddrives;--

A second injection attack has to take place in order to get the data out again.

' UNION SELECT name, cast((mb_free) as varchar(10)), 1.0 FROM haxor;--

This returns the name of the disks with the available capacity in megabytes. Now that the drive letters of the disks are known, a new injection attack can take place in order to find out what is on those disks.

'; DROP TABLE haxor;CREATE TABLE haxor(line varchar(255) null);
INSERT INTO haxor EXEC master..xp_cmdshell 'dir /s c:';--

And again, a second injection attack is used to get the data out again.

' UNION SELECT line, '', 1.0 FROM haxor;--

xp_cmdshell, by default, is only executable by a user with the sysadmin privilege, such as sa, and CREATE TABLE is only available to sysadmin, db_dbowner or db_dlladmin users. It is therefore important to run the application with the least privileges that are necessary in order to perform the necessary functions of the application.

Least Privilege – Process account

When an instance of SQL Server is installed on a computer, it creates a service that runs in the background and processes the commands from applications that are connected to it. By default, this service is installed to use the Local System account. This is the most powerful account on a Windows machine, it is even more powerful than the Administrator account.

If an attacker has an opportunity to break out of the confines of SQL Server itself, such as through the extended procedure xp_cmdshell, then they could gain unrestricted access to the machine that the SQL Server is on.

Microsoft recommends that during the installation of SQL Server, the service is given a domain account which has the permissions set to only the necessary resources. That way, an attacker is confined by the permission set needed to run SQL Server.

Cleaning and Validating input

In many applications, the developer has side-stepped the potential use of the apostrophe as a way to get access to the system by performing a string replace on the input given by the user. This is useful for valid reasons, such as being able to enter surnames such as “O’Brian” or “D’Arcy”, and so the developer may not even realise that they have partly defeated a SQL injection attack. For example:

string surname = this.surnameTb.Text.Replace("'", "''");
string sql = "Update Users SET Surname='"+surname+"' "+
    "WHERE id="+userID;

All of the previous injection attack examples would cease to work given a scenario like this.

However, many applications need the user to enter numbers and these don’t need to have the apostrophes escaped like a text string. If an application allows the user to review their orders by year, the application may execute some SQL like this:

SELECT * FROM Orders WHERE DATEPART(YEAR, OrderDate) = 1996

And in order for the application to execute it, the C# code to build the SQL command might look like this:

string sql = "SELECT * FROM Orders WHERE DATEPART(YEAR, OrderDate) = "+
    this.orderYearTb.Text);

It becomes easy to inject code into the database again. All the attackers need to do in this instance is start their attack with a number, then they inject the code they want to run. Like this:

0; DELETE FROM Orders WHERE ID = 'competitor';--

It is therefore imperative that the input from the user is checked to determine that it really is a number, and in the valid range. For instance:

string stringValue = orderYearTb.Text;
Regex re = new Regex(@"D");
Match m = re.Match(someTextBox.Text);
if (m.Success)
{
    // This is NOT a number, do error processing.
}
else
{
    int intValue = int.Parse(stringValue);
    if ((intValue < 1990) || (intValue > DateTime.Now.Year))
    {
        // This is out of range, do error processing.
    }
}

Second-Order Attacks

A second-order attack is one where the data lies dormant in the database until some future event occurs. It often happens because once data is in the database, it is often thought of as being clean and is not checked again. However, the data is frequently used in queries where it can still cause harm.

Consider an application that permits the users to set up some favourite search criteria. When the user defines the search parameters, the application escapes out all the apostrophes so that a first-order attack cannot occur when the data for the favourite is inserted into the database. However, when the user comes to perform the search, the data is taken from the database and used to form a second query which then performs the actual search. It is this second query which is the victim of the attack.

For example. If the user types the following as the search criteria:

'; DELETE Orders;--

The application takes this input and escapes out apostrophe so that the final SQL statement might look like this:

INSERT Favourites (UserID, FriendlyName, Criteria)
VALUES(123, 'My Attack', ''';DELETE Orders;--')

which is entered into the database without problems. However, when the user selects their favourite search, the data is retrieved to the application, which forms a new SQL command and executes that. For example, the C# code might look like:

// Get the valid user name and friendly name of the favourite
int uid = this.GetUserID();
string friendlyName = this.GetFriendlyName();
// Create the SQL statement to retrieve the search criteria
string sql = string.Format("SELECT Criteria FROM Favourites "+
    "WHERE UserID={0} AND FriendlyName='{1}'",
    uid, friendlyName);
SqlCommand cmd = new SqlCommand(sql, this.Connection);
string criteria = cmd.ExecuteScalar();

// Do the search
sql = string.Format("SELECT * FROM Products WHERE ProductName = '{0}'",
    criteria);
SqlDataAdapter da = new SqlDataAdapter(sql, this.Connection);
da.Fill(this.productDataSet);

The second query to the database, when fully expanded, now looks like this:

SELECT * FROM Products WHERE ProductName = ''; DELETE Orders;--

It will return no results for the expected query, but the company has just lost all of their orders.

Parameterised Queries

SQL Server, like many database systems, supports a concept called parameterised queries. This is where the SQL Command uses a parameter instead of injecting the values directly into the command. The particular second-order attack above would not have been possible if parameterised queries had been used.

Where the application developer would have constructed a SqlCommand object like this:

string cmdText=string.Format("SELECT * FROM Customers "+
    "WHERE Country='{0}'", countryName);
SqlCommand cmd = new SqlCommand(cmdText, conn);

A parameterised query would look like this:

string commandText = "SELECT * FROM Customers "+
    "WHERE Country = @CountryName";
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add("@CountryName",countryName);

The value is replaced by a placeholder, the parameter, and then the parameter’s value is added to the Parameters collection on the command.

While many second-order attacks can be prevented by using parameters, they can only be used in places were a parameter is permitted in the SQL statement. The application may return a variable sized result set based on user preference. The SQL statement would include the TOP keyword in order to limit the result set, however, in SQL Server 2000, TOP can only accept literal values so the application would have to inject that value into the SQL command to obtain that functionality. For example:

string sql = string.Format("SELECT TOP {0} * FROM Products", numResults);

Using Stored Procedures

Stored Procedures add an extra layer of abstraction in to the design of a software system. This means that, so long as the interface on the stored procedure stays the same, the underlying table structure can change with no noticeable consequence to the application that is using the database. This layer of abstraction also helps put up an extra barrier to potential attackers. If access to the data in SQL Server is only ever permitted via stored procedures, then permission does not need to be explicitly set on any of the tables. Therefore, none of the tables should ever need to be exposed directly to outside applications. For an outside application to read or modify the database, it must go through stored procedures. Even though some stored procedures, if used incorrectly, could potentially damage the database, anything that can reduce the attack surface is beneficial.

Stored procedures can be written to validate any input that is sent to them to ensure the integrity of the data beyond the simple constraints otherwise available on the tables. Parameters can be checked for valid ranges. Information can be cross checked with data in other tables.

For example, consider a database that has the user details for a website, this includes the user name and password. It is important that an attacker is unable to get a list of passwords or even one password. The stored procedures are designed so that a password can be passed in, but it will never put a password in any result set. The stored procedures for registering and authenticating a user for the website might be:

  • RegisterUser
  • VerifyCredentials
  • ChangePassword

RegisterUser takes the user name and password as parameters (possibly along with other information that is necessary for registering on the website) and returns the UserID.

VerifyCredentials would be used for logging into the site by accepting the user name and the password. If there is a match the UserID is returned, if not then a NULL value.

ChangePassword would take the UserID, the old password and the new password. If the UserID and the password match, the password can be changed. A value that indicates success or failure is returned.

The above example shows that the password is always contained in the database and is never exposed.

Stored Procedure Caveat

While stored procedures seem to be a wonderful panacea against injection attacks, this is not necessarily the case. As mentioned above, it is important to validate data to check that it is correct and it is a definite benefit of stored procedures that they can do this; however, it is doubly important to validate data if the stored procedure is going to use EXEC(some_string) where some_string is built up from data and string literals to form a new command.

For instance, if the stored procedure is to modify the data model of the database, such as creating a table, the code may be written as follows:

CREATE PROCEDURE dbo.CreateUserTable
@userName sysname
AS
    EXEC('CREATE TABLE '+@userName+
    ' (column1 varchar(100), column2 varchar(100))');
GO

It is obvious that whatever @userName contains will be appended to the CREATE statement. An attacker could inject into the application some code that sets the user name to be:

a(c1 int); SHUTDOWN WITH NOWAIT;--

which will immediately stop the SQL Server without waiting for other requests to complete.

It is important to validate the input to ensure that no illegal characters are present. The application could be set to ensure that spaces are not permitted as part of the user name and this could be rejected before it ever got as far as constructing the CREATE statement.

If the stored procedure is going to construct a SQL command based on an existing object, such as a table or view, then it should check that such an object exists. For instance:

CREATE PROCEDURE dbo.AlterUserTable
@userName sysname
AS
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME = @userName)
    BEGIN
        -- The table is known to exist
        -- construct the appropriate command here
    END
GO

Error Messages

Error messages are useful to an attacker because they give additional information about the database that might not otherwise be available. It is often thought of as being helpful for the application to return an error message to the user if something goes wrong so that if the problem persists they have some useful information to tell the technical support team. Applications will often have some code that looks like this:

try
{
     // Attempt some database operation
}
catch(Exception e)
{
    errorLabel.Text = string.Concat("Sorry, your request failed. ",
        "If the problem persists please report the following message ",
        "to technical support", Environment.Newline, e.Message);
}

A better solution that does not compromise security would be to display a generic error message that simply states an error has occurred with a unique ID. The unique ID means nothing to the user, but it will be logged along with the actual error diagnostics on the server which the technical support team has access to. The code above would change to something like this instead:

try
{
    // Attempt some database operation
}
catch(Exception e)
{
    int id = ErrorLogger.LogException(e);
    errorLabel.Text = string.Format("Sorry, your request Failed. "+
        "If the problem persists please report error code {0} "
         "to the technical support team.", id);
}

Summary

  • Encrypt sensitive data.
  • Access the database using an account with the least privileges necessary.
  • Install the database using an account with the least privileges necessary.
  • Ensure that data is valid.
  • Do a code review to check for the possibility of second-order attacks.
  • Use parameterised queries.
  • Use stored procedures.
  • Re-validate data in stored procedures.
  • Ensure that error messages give nothing away about the internal architecture of the application or the database.

References and related posts


The only authorised versions of this article are available from: The Code Project and my website.

Article, Software Development

Passing Values Between Forms in .NET

Introduction

I wrote this article in response to an almost overwhelming number of requests on forums on how to pass a variable from a one Windows Form to another. In fact, saying that it is going between one form and another is somewhat of a misnomer because this will work for any type of object not just Forms. This seems to be one area where beginners in C# and VB.NET often get stuck. So, rather than having to repeat myself often in forums I wrote this article so that I could point people at it so that they will have the benefit of a full explanation as well as the answer to their problem.

The code examples that come with this article are available for VB.NET and C# so that neither group feels left out. If you develop software in any of the other languages available for .NET then you can probably follow on too. The techniques are the same for all .NET languages the only difference should be in the language syntax.

Parent to child

Passing a value from a parent to a child class is hopefully one of the easiest techniques and is the one I’ll start with. The idea is simple. The parent class needs to pass some value to the child class. In this example, I have set up a simple parent form with a TextBox control where the user can type any value they like, then they can open the child form and see that value appear there.

First of all, the child class needs some way to receive the value from the parent. I’ve set up a property to receive the value and it will set the Text value of a TextBox on the child form. In reality this could set a field value or get passed to another object and so on.

C#
public string ValueFromParent
{
    set
    {
        this.uxParentValue.Text = value;
    }
}
VB.NET
Public WriteOnly Property ValueFromParent() As String
    Set(ByVal Value As String)
        Me.uxParentValue.Text = Value
    End Set
End Property

Passing the value on construction

To make the initialization of the class easier, the constructor will take a parameter which will be the current value to be passed. The constructor uses the property so that the code can be reused. Admittedly, in this example there is only one line in the property, but as an element of future proofing it means that there is the possibility of adding more functionality to the property in future should it need. That additional functionality will be implemented without having to change a lot of existing code.

C#
public ChildForm(string initialValue)
{
    InitializeComponent();
    ValueFromParent = initialValue;
}
VB.NET
Public Sub New(ByVal initialValue As String)
    MyBase.New()
    'This call is required by the Windows Form Designer.
    InitializeComponent()
    ValueFromParent = initialValue
End Sub

Now, in order for the parent to pass the value to the child, it only has to set the property. For example, when using the child form as a dialog the value from the parent can be passed as follows:

C#
private void uxOpenDialog_Click(object sender, System.EventArgs e)
{
    // Get the value to be passed
    string parentValue = this.uxUserResponse.Text;
    // Create the child form.
    ChildForm child = new ChildForm(parentValue);
    // Show the form which will display the user's value.
    child.ShowDialog();
}
VB.NET
Private Sub uxOpenDialog_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles uxOpenDialog.Click
    ' Get the value that the child will be initialised with
    Dim initialValue As String
    initialValue = Me.uxUserResponse.Text
    Dim childForm As ChildForm
    ' Create the child form.
    childForm = New ChildForm(initialValue)
    ' Show the child dialog.
    childForm.ShowDialog()
End Sub

Passing the value while the form is open

Of course, it may be necessary to pass a value when the child form is already open. This is also possible if the parent form stores a reference to the child form and then uses that to pass updates to the child form using the property in the child form that was set up earlier.

In my example, the parent is going to store a reference to the child as a field in the ParentForm class like this:

C#
private ChildForm uxChildForm;
VB.NET
Private uxChildForm As ChildForm

When creating the child form it is therefore important to pass the reference to the child form into the field variable. For example:

C#
private void uxOpenForm_Click(object sender, System.EventArgs e)
{
    this.uxChildForm = new ChildForm();
    this.uxChildForm.ValueFromParent = this.uxUserResponse.Text;
    this.uxChildForm.Show();
}
VB.NET
Private Sub uxOpenForm_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles uxOpenForm.Click
    Me.uxChildForm = New ChildForm
    Me.uxChildForm.ValueFromParent = Me.uxUserResponse.Text
    Me.uxChildForm.Show()
End Sub

Finally, whenever the parent needs to tell the child that the value is updated it can do so just by passing the new value to the property that was set up in the child form. For example:

C#
private void uxUserResponse_TextChanged(object sender, System.EventArgs e)
{
    if (this.uxChildForm != null)
    {
        this.uxChildForm.ValueFromParent = this.uxUserResponse.Text;
    }
}
VB.NET
Private Sub uxUserResponse_TextChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles uxUserResponse.TextChanged
    If Not Me.uxChildForm Is Nothing Then
        Me.uxChildForm.ValueFromParent = Me.uxUserResponse.Text
    End If
End Sub

The code for this is in the zip file above. The projects are ParentToChildCS (for C# developers) and ParentToChildVB (for VB developers).

Sibling to Sibling

The next most common request seems to be passing a value between two siblings. That is, two child classes sharing the same parent class. This also demonstrates passing between the child and the parent.

First, it is important to note that in most cases the two child classes should probably know nothing about each other. If the two child classes start to communicate directly with each other the coupling in the application increases and this will make maintenance of the application more difficult. Therefore the first thing to do is to allow the child classes to raise events when they change.

In order to raise events, create a delegate to define the signature of the method that will be called when the event is raised. It is a good practice to conform to the same style as the delegates used in the .NET Framework itself. For example, if an event handler for the Click event of a Button is created then it is possible to see that the signature of the method created by the IDE has two parameters, sender and e. sender is an object and is a reference to the object that generated the event. e is the EventArgs (or a something derived from EventArgs).

With that in mind, the delegate created here will have sender and e parameters as well. But for the e parameter a new class will be created to store the specific details of the event.

C#
public class ValueUpdatedEventArgs : System.EventArgs
{
    // Stores the new value.
    private string newValue;
    // Create a new instance of the
    // ValueUpdatedEventArgs class.
    // newValue represents the change to the value.
    public ValueUpdatedEventArgs(string newValue)
    {
        this.newValue = newValue;
    }
    // Gets the updated value.
    public string NewValue
    {
        get
        {
            return this.newValue;
        }
    }
}
VB.NET
Public Class ValueUpdatedEventArgs
    Inherits System.EventArgs
    ' Stores the new value
    Dim _newValue As String
    ' Create a new instance of the
    ' ValueUpdatedEventArgs class.
    ' newValue represents the change to the value.
    Public Sub New(ByVal newValue As String)
        MyBase.New()
        Me._newValue = newValue
    End Sub
    ' Gets the updated value
    ReadOnly Property NewValue() As String
    Get
        Return Me._newValue
    End Get
    End Property
End Class

Next the delegate needs to be defined.

C#
public delegate void ValueUpdated(object sender,
    ValueUpdatedEventArgs e);
VB.NET
Public Delegate Sub ValueUpdated(ByVal sender As Object, _
    ByVal e As ValueUpdatedEventArgs)

Now that the foundation of the mechanism for informing other objects of changes is written, the child classes can implement the raising of events so that observers (i.e. interested parties) can be notified when the value changes.

In the class for the child forms the event ValueUpdated is declared so that interested parties can indicate what they want to observe.

C#
public event ValueUpdated ValueUpdated;

This is one area where C# and VB.NET differ. In C# an event requires a delegate, whereas in VB.NET the signature of the method to be called needs to be declared as part of the event. The advantage for C# is that the delegate only has to be declared once and it can be reused in many places whereas VB.NET has to have essentially the same bit of code, the method signature, rewritten frequently – this can be an area where there can be discrepancies between one class and another.

Next, when the child class is updated it needs to inform its observers (the interested parties, in this case it will be the parent and sibling forms). The following code shows what happens when the text is changed on the form.

C#
private void uxMyValue_TextChanged(object sender, System.EventArgs e)
{
    // Get the new value from the text box
    string newValue = this.uxMyValue.Text;
    // Create the event args object.
    ValueUpdatedEventArgs valueArgs =
    new ValueUpdatedEventArgs(newValue);
    // Inform the observers.
    ValueUpdated(this, valueArgs);
}
VB.NET
Private Sub uxMyValue_TextChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles uxMyValue.TextChanged
    Dim newValue As String
    newValue = Me.uxMyValue.Text
    Dim valueArgs As ValueUpdatedEventArgs
    valueArgs = New ValueUpdatedEventArgs(newValue)
    RaiseEvent ValueUpdated(Me, valueArgs)
End Sub

Now that the child form can inform others of the changes and what the changes are, the parent needs to open the child form and register as an observer so that it can be informed when changes are made. The parent will also ensure that other siblings that are interested get added as observers too.

C#
private void uxOpenChildA_Click(object sender, System.EventArgs e)
{
    // Create the child form
    this.childA = new ChildAForm();
    // Add an event so that the parent form is informed when the child
    // is updated.
    this.childA.ValueUpdated += new ValueUpdated(childA_ValueUpdated);
    // Add an event to each child form so that they are notified of each
    // others changes.
    if (this.childB != null)
    {
        this.childA.ValueUpdated +=
            new ValueUpdated(this.childB.childA_ValueUpdated);
        this.childB.ValueUpdated +=
            new ValueUpdated(this.childA.childB_ValueUpdated);
    }
    this.childA.Show();
}
VB.NET
Private Sub uxOpenChildA_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles uxOpenChildA.Click
    ' Create the child form
    Me.childA = New ChildAForm
    ' Add a handler to this class for when child A is updated
    AddHandler childA.ValueUpdated, AddressOf ChildAValueUpdated
    If Not Me.childB Is Nothing Then
    ' Make sure that the siblings are informed of each other.
        AddHandler childA.ValueUpdated, _
            AddressOf childB.ChildAValueUpdated
        AddHandler childB.ValueUpdated, _
            AddressOf childA.ChildBValueUpdated
    End If
    ' Show the form
    Me.childA.Show()
End Sub

All that remains is to implement the event handlers.

C#
private void childA_ValueUpdated(object sender, ValueUpdatedEventArgs e)
{
    // Update the text box on this form (the parent) with the new
    /// value from the child
    this.uxChildAValue.Text = e.NewValue;
}
VB.NET
Private Sub ChildAValueUpdated(ByVal sender As Object, _
    ByVal e As ValueUpdatedEventArgs) Handles childA.ValueUpdated
    ' Update the value on this form (the parent) with the
    ' value from the child.
    Me.uxChildAValue.Text = e.NewValue
End Sub

And similarly in the child class.

C#
public void childB_ValueUpdated(object sender, ValueUpdatedEventArgs e)
{
    // Update the text box on this form (child A) with the new value
    this.uxOtherValue.Text = e.NewValue;
}
VB.NET
Public Sub ChildBValueUpdated(ByVal sender As Object, _
    ByVal e As ValueUpdatedEventArgs)
    Me.uxOtherValue.Text = e.NewValue
End Sub

The code for this example is in the accompanying zip file. The project is called SiblingToSiblingCS (for the C# version) and SiblingToSiblinVB (for the VB.NET version)

Conclusion

It is quite simple to pass values from one object to another. In some instances it does take a little bit of effort to get it done right. Especially as it is all too easy to couple forms together that really shouldn’t know about each other.

©2005 Colin Angus Mackay. All Rights Reserved – The only authorized websites that may show this article are CodeProject, ColinMackay.co.uk and Developer fusion.