Monday, 29 August 2011

Automated data syncing with SQL Data Compare and TeamCity

Monday, 29 August 2011

For a while now, I’ve been putting off a task to configure a sync process for a particular piece of enterprise data. This data is populated into a single table in a production environment on a nightly basis but also needed to be synced down into the test and development environments every now and then. Without going into too much detail about the nature of the data, it consists of about 700,000 records which change either via updates or insertions. Normally I don’t like taking production data down into other environment (there are simply better ways), but the nature of this data called for keeping the environments in sync so the developers could do their job.

I’d been sitting on this requirement for a while as I wasn’t relishing what could become a time consuming, laborious task. This was a table with over 40 columns including lots of foreign keys (one of them self-referential), and a calculated field so as well as being careful not to create any referential integrity problems, I needed it to happen fast so as not to play havoc with a production environment. In my mind, it was going to mean manually writing some form of ETL either directly in TSQL or via SSIS or even going down a SQL replication route. It was possibly even a SQL MERGE task but these particular environments were still stuck on SQL 05 so that route was out.

One quick caveat: I’m not a DBA, I’m a developer who works with databases. There may have been other angles to come at this from, but the solution I arrived at is fast, simple and easy to monitor. The fact that it ended up being a 15 minute job on my weekend and I didn’t mind giving up a little of my valuable Sunday on this particular task was a very nice result!

Read more

Monday, 15 August 2011

I’m sorry, but were you actually trying to remember your comical passwords?

Monday, 15 August 2011

I love a good XKCD comic; Randall Munroe has a unique way of cutting right to the crux of technology issues and always doing it in a humorous fashion. Little Bobby Tables remains an all-time classic and it’s amazing how many times you’ll see it quoted in security discussions – it’s now well and truly embedded in pop culture (well, at least in the little app-sec corner of the world).

Last week’s password strength comic was no exception; very funny stuff about the pain people will go to in order to create a strong password which they’ll ultimately forget. Anyway, the crux of the comic was this piece about using four random words as a way of creating a password that is both memorable and strong:

Constructing a password from four words

It goes on to calculate the bits of entropy in this password versus shorter versions using(unmemorable) character substitutions and concludes that:

Through 20 years of effort, we’ve successfully trained everyone to use passwords that are hard for humans to remember, but easy for computers to guess.

Bravo, but there’s a bit more to it than that…

Read more

Wednesday, 10 August 2011

Overcoming SQL 08’s globally insensitive time zones using .NET

Wednesday, 10 August 2011

I seem to spend a lot of time involved with web apps which end up having a lot of geographical diversity. Either they sit in a server in one country then get used by folks somewhere else or more often than not, they face audiences of a global nature spread out across varying time zones. And even if they do end up co-located, chances are it won’t always stay that way so there’s always a desire to add in a little future-proofing.

When SQL 08 came along there seemed to be some new hope for making this process a little easier through the introduction of a few new date and time related data types, particularly the datetimeoffset type. Unfortunately all that glitters is not gold in this case and the new data type can be a real “gotcha”. Here’s how to build in that geo-awareness from the ground up using the new datetime2 data type and without getting caught with your metaphorical pants down.

Read more

Monday, 1 August 2011

OWASP Top 10 for .NET developers part 8: Failure to Restrict URL Access

Monday, 1 August 2011

As we begin to look at the final few entries in the Top 10, we’re getting into the less prevalent web application security risks, but in no way does that diminish the potential impact that can be had. In fact what makes this particular risk so dangerous is that not only can it be used to very, very easily exploit an application, it can be done so by someone with no application security competency – it’s simply about accessing a URL they shouldn’t be.

On the positive side, this is also a fundamentally easy exploit to defend against. ASP.NET provides both simple and efficient mechanisms to authenticate users and authorise access to content. In fact the framework wraps this up very neatly within the provider model which makes securing applications an absolute breeze.

Still, this particular risk remains prevalent enough to warrant inclusion in the Top 10 and certainly I see it in the wild frequently enough to be concerned about it. The emergence of resources beyond typical webpages in particular (RESTful services are a good example), add a whole new dynamic to this risk altogether. Fortunately it’s not a hard risk to prevent, it just needs a little forethought.

Read more