Home > Development, Security > Opening the box: 1 of 2

Opening the box: 1 of 2

To the majority of people, software development is a black-box process. They contact a software development company, provide them with a specification, some money changes hands and some time later a shiney finished bit of software lands on their desktop ready to use.

Every so often, though, the box needs to be opened. They may lose contact with (or faith in) the original development company. If they’re intelligent enough to have negotiated ownership of the intellectual property, they pass the software onto a third party for them to evaluate, then support and extend according to the client’s wishes.

This happened to a client of ours recently, and I was given the task of opening the box. All I knew was that the original developer had provided us with ASP.NET and C# source code, and a SQL Server database. What I found amazed me, and so to avoid overloading this blog with too much magnificance in one step I am splitting my analysis over two separate posts: this first, for the SQL database, and then a second covering the ASP.Net and C#.

For obvious reasons, the name of the client has been substituted or removed in the examples; other than that, these are all genuine screenshots.

The 40MB database backup was a bit daunting at first, but thankfully the file actually contained five separate copies of the database rather than just the one that most people use (nothing like redundancy!) so it wasn’t as bad as it could have been. The first thing I noticed was that they were being very careful on security – anyone who decided to try a case-sensitive attack on the structure would have been easily thwarted right from the start:

I spotted later, after altering the image above, that their login (which prefixes the table names above) also included a numeric in place of a letter. So ‘clientsN4m3’ would be a more accurate representation of their login. Not their password. Their login. It’s even more secure than I realised.

Their coding style was also very efficient – no need to worry about any standards to read through and memorise when you could make up primary key fields at any point and follow any particular naming scheme that came to mind at the time. I especially loved, in the ‘state’ table, the way that they were free to even use different datatypes if they wanted to:

We obviously have a lot to learn from them here, and even more so when you take into account the relationships in the database itself:

Now, personally I’m happy with a straight one-foreign-key-per-relationship, but obviously they wanted to make sure that, when they added a relationship, they meant it. They were obviously very committed to their database designs, and good on them.

Part 2 continues the theme …

  1. No comments yet.
  1. September 22, 2011 at 3:04 pm
  2. September 23, 2011 at 10:02 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: