Upcoming talks

My SQL Injection Attack prevention talk is on the road. I’ve already given the talk in Dundee, Newport and Nuneaton. And in the coming months I’ll be delivering it in Glasgow, London and Newcastle.

If you want to come and learn about securing your database from a developer perspective you can come along.

SQL Injection Attacks and Tips on How to Prevent Them

I’m giving a talk in Dundee on the topic of SQL Injection Attacks. If you are interested in the subject then the registration link is at the bottom of the page.

Wednesday, 28th October 2009 at 19:00 – 21:00
Queen Margaret Building, Dundee University

The Talk

In light of some recent events, such as the man who was convicted of stealing 130 million credit card details through a SQL Injection attack, it is imperative that developers understand what a SQL Injection Attack is, how they are carried out, and most importantly, how to defend your code against attack.

In this talk I’ll demonstrate a SQL Injection Attack on an application in a controlled environment*. I’ll show you where the vulnerable code lies and what you can do to harden it.

Although this talk uses C# as the application language and Microsoft SQL Server 2008 as the database engine many of the concepts and prevention mechanisms will apply to any application that accesses a database through SQL.

* Demonstrating an attack on a real system without the owner’s consent is a breach of the 1990 Misuse of Computers Act, hence the controlled environment.

The Venue

We are meeting in the Queen Mother Building at Dundee University. After the meeting we normally retire to the the bar at Laing’s

The Agenda

18:45 Doors Open
19:00 Welcome
19:10 The Talk (Part 1)
19:55 Break
20:05 The Talk (Part 2)
20:45 Feedback & Prizes
21:00 Repair to the Pub


Space is limited, we would therefore ask that you sign up.

Where's My Data? An introduction to Spatial Queries in SQL Server 2008

The slide deck used for my presentations to the

There is also a set of demo code to go along with the slide deck variants.

Further information

The following blog posts may also be useful:

An introduction to mock objects

Colin Mackay presenting Mock Objects (Grok Talk)Sometimes when you are unit testing you might get to a point where you say “That’s too difficult to unit test so I’m just going to leave it”. This is where mock objects come in. Mock objects are stand-in dummy objects that don’t have any functionality behind them, they just return the values to the application that the real object would have returned.

Unit tests are supposed to test isolated bits of code. A single method on a class, or a single public method that calls some private methods on the class. Nothing more than that. Mock objects ensure that if the code you are testing calls something external to the class being tested you don’t have to worry about what the actaul real class does. This has some useful advantages.

When mocks are used the external class is not used, therefore any bugs in the external class do not affect the code being tested. The tests for that other class should pick up the bugs. If the external class is non-deterministic then mock objects can be used to ensure that the unit test received known values repeatedly. It would be insane to provide random values in to a test because it may fail intermittently. If you have a bug that causes the code to fail then knowing the values that cause the failure is necessary. If these are known a new unit test that exercises the failing code can be written so that it can be seen when it is fixed. If it starts to fail again then it will be seen instantly.

There are many unit frameworks out there, for this demo I’m using Rhino Mocks by Ayende Rahien. Other frameworks include NMock2, Easy Mock and Type Mock. Mock object frameworks are not strictly necessary. It is possible to set up a mock object just by implementing the interface of the object to be mocked, however this can take a bit more work.

As a general rule classes that are outside the class under test (CUT) should be mocked. There are some exceptions like extremely simple classes such as data transfer objects, whose sole purpose is to transfer data without any functionality.

If your class interacts with external systems then the access to those external systems can be abstracted out. Then the interaction with the external system can be mocked. In general this kind of abstraction would normally take place anyway when separating the application into its appropriate modules, assemblies, layers or tiers. For example, if your application relies on some configuration setting, a configuration class would be created that knows what should be in the config file for the application, extracts it and transforms it into values the application can use. If the code being tested needs to access a configuration value, a mock configuration object can be supplied and a strictly defined value will be available for the test. This saves tedious setting up of files in advance of the test run. The same can be achieved with database access, web service access and so on. It is also possible to mock out the user interface if the presenter pattern is used.

At the heart of most mock objects is simply that they provide inputs into the code that is being tested.

The most obvious input into a method is the parameters that are passed in. But the fields of a class are also inputs into the method, and calls to methods and properties are inputs into the method. Mock objects ensure that inputs into a method generated by code outwith the class under test are setup specifically by the unit test.

Let’s take the example of a piece of code that calls into a D.A.L (Data Abstraction/Access Layer). This might be mocked for many reasons.

  • The database may not be available because it is yet to be created or the data hasn’t been migrated yet from an older system.
  • The database may be shared among the developers. In this case the state of the database cannot be guaranteed as other developers may be changing it while tests are running.
  • The database may take too long to set up, or the set up process may have some manual steps. In order for unit tests to be used effectively they need to be easy and quick to run. If they are not then their value quickly diminishes as developers will stop running them so frequently.
  • The unit tests are likely to be run in many different environments and the availability or consistency of the database in those environments cannot be guaranteed. Typically each developer will be running the unit tests on their own machine. In a large team there may be dedicated testers who will be running the unit tests. There may be a system such as CruiseControl that will automatically build and run unit tests whenever it detects a change in the source control database.

Mock object frameworks, such as Rhino Mocks, typically work by setting up a series of commands that it expects to be called, then those commands are replayed into the code that is being tested. Once the code under test is completed then the mock framework verifies that the commands were called. If for example an expectation was that method A is called, but wasn’t the Verify process will throw an exception that the unit test framework will use to report a problem. If, on the other hand, the code calls method B, but that wasn’t expected then the mock object framework will throw a slightly different exception to say that B was not expected to be called.

In the demonstration solution there is a somewhat contrived example of how mocks work. The ProductsFactory class needs to call the Dal in order to work out what products should be instantiated. There are two mock objects at work here. One is the Dal itself and the other is the IDataReader. The unit test code shows the mocks being created and the expectations being set up. It then replays the commands and runs the code that is to be tested. Once the code is run it verifies that the methods have been called. In the second test, where multiple products are returned from the factory, the expectations are set up slightly differently. In this case an additional expecation is being set up that demands that the methods are called in a strict order. In the first unit test the methods could have been called in any order, just so long as they were called.

Further reading/watching