This is a problem we have been warning about for some time. Islington Council were fined £70,000 for a similar incident in 2012. In light of this fresh incident we again urge all public authorities to take care when preparing data for release.
As with the Islington incident, the information was in parts of an Excel spreadsheet that were not immediately visible. It was automatically published on 14th November when Hackney Council sent it in response to a Freedom of Information request, as part of the normal operation of the WhatDoTheyKnow website. All requests sent via the website make it clear that this will happen.
This particular breach involved a new kind of hidden information we hadn’t seen before – the released spreadsheet had previously been linked to another spreadsheet containing the private information, and the private information had been cached in the “Named Range” data in the released spreadsheet.
Although it was not straightforward to access the information directly using Excel, it was directly visible using other Windows programs such as Notepad. It had also been indexed by Google and some of it was displayed in their search previews.
The breach was first hit upon by one of the data subjects searching for their own name. When they contacted us on 25th November to ask about this, one of our volunteers, Richard, realised what had happened. He immediately hid the information from public view and notified the council.
We did not receive any substantive response from the council and therefore contacted them again on 3rd December. The council had investigated the original report but not understood the problem, and were in fact preparing to send a new copy of the information to the WhatDoTheyKnow site, which would have caused the breach to be repeated.
We reiterated what we had found and advised them to consult with IT experts within their organisation. The next day, 4th December, we sent them a further notification of what had happened, copying the Information Commissioner’s Office (ICO). As far as we are aware, this was the first time the ICO was informed of the breach.
From our point of view it is very disappointing that these incidents are still happening. Freedom of Information requests made via WhatDoTheyKnow are a small fraction of all requests, so it is very likely that this kind of error happens many more times in private responses to requesters, without the public authority ever becoming aware.
Our earlier blog post has several tips for avoiding this problem. These tips include using CSV format to release spreadsheets, and checking that file sizes are consistent with the intended release. Either of these approaches would have averted this particular breach.
We would also urge the ICO to do as much as possible to educate authorities about this issue.
You may be familiar with WhatDoTheyKnow, our website which simplifies the process of making a freedom of information request.
mySociety also provides the underlying software as a service for councils: it sits on the council website, templated and branded to fit their site’s style. When someone submits a request, it goes directly into the council’s own back-end processes.
Just like WhatDoTheyKnow, the system publishes all requests, and their answers, online. This helps the council show a commitment to transparency – it also has the effect of cutting down on duplicate requests, since users can browse previous responses.
Brighton and Hove Council are the first council to implement the software.
Now, ordinarily, when we sign off a new project for a client, we write up a case study for our blog. But this time, we were delighted to read an interview by Matt Burgess on FOI Directory, which has done all the hard work for us. With Matt’s permission, we are reproducing the piece in full.
The number of Freedom of Information requests public authorities receive is generally rising and central government dealt with more requests in 2012 than in any year since the Act was introduced. One council has decided to try and open up access to their requests using custom software from mySociety.
Brighton and Hove City Council have implemented a custom version of the popular WhatDoTheyKnow website where more than 190,000 requests have been made.
The council hope it will allow others to easily browse requests that have been made and make them more accountable.
We spoke to council leader Jason Kitcat about why the council decided to implement the new system – which was soft-launched at the beginning of November.
Why did you decide to implement the new system?
JK: I personally, and we collectively as a Green administration, believe passionately in openness and transparency. That’s the primary motivation. So digital tools to support making it easier for citizens to access council information I think are strongly in the interest of our city and local democracy.
We also were seeing an increase in the number of FOI requests, many of them similar. So using a system like this helps people to find the information that’s already published rather than submitting requests for it, when it’s actually already been published.
How does it work?
JK: It’s a customised version of the mySociety WhatDoTheyKnow site, delivered by mySociety for us in the council’s branding. It allows anyone to submit their FOI request in a structured way through the web and others can see the requests and any responses. The requests are linked in with the main WhatDoTheyKnow site to help further reduce duplication of requests and enable consistent commenting.
Behind the scenes it also offers workflow management to assist the council team who are responding to the requests.
What benefits will the system have to those answering and making FOI requests?
JK: It opens up the process, helps others to see what is going on even if they aren’t making requests themselves. Particularly important is that it by default puts requested information out there on the web without any more effort by the council or those making the requests.
Were there any obstacles in setting the system up and how much did it cost the council?
JK: Obstacles were mainly stretched resources within the council to prepare for the changed workflow, making sure our information governance was ready for this and that our web team could support the minor integration work needed.
Given this is a web-based ’software as a service’ offering it’s pretty straightforward to implement in the grand scheme of things. I don’t have the final costs yet as we’ve been doing some post-launch tweaks but, as is the way with nimble organisations like mySociety, I think pricing is very reasonable.
Do you think it will improve the council’s performance in responding to FOI requests and make the council more transparent to the public?
JK: Yes absolutely. Not only will the council’s FOI performance be more publicly accountable but I’m hoping we can reduce duplicate requests through this so that our resources are better focused.
Would you say it has been worth creating and why should other public authorities follow suit?
JK: Yes it’s worth it. I think we as councils have to be ever more open by default, use digital tools for transparency and relentlessly publish data. I believe this will result in better local democracy but also is one of the ways we can truly challenge cynicism in the whole political system.N.B.: The website current shows a large number of requests that appear to be unanswered. We asked about these and it includes the number of historic requests that were loaded into the site.————————————Many thanks to Matt of FOI Directory for allowing us to reproduce this interview in full.
WhatDoTheyKnow is mySociety’s Freedom of Information site. You can use it to make FOI requests, and it publishes them – and the responses you receive – for everyone to see.
You might think that making a Freedom of Information request is something that only journalists or investigators do. But actually, one of WhatDoTheyKnow’s aims is to show that anyone can access this right. If there’s something you want to find out, and the information is held by a public body, WhatDoTheyKnow makes it very easy for you to request it.
WhatDoTheyKnow is mySociety’s most-visited site, with around 100,000 people viewing the information on it every week. Not all of those people make FOI requests, but they are all benefiting from the information uncovered by those who do.
And who are those ‘people who do’?
Jonathan works for a digital company in Brighton, as a project manager. He first became aware of WhatDoTheyKnow at a local conference on open data in the city.
I make FOI requests as a Brighton citizen. Mostly I ask about data that is held by the council. For example, I’ve recently made requests about parking revenue, council pay levels – that sort of thing.
These are topics that are of clear interest to everyone in the city – but why does he make these requests?
It is about getting the data into the public domain to start an informed debate.
Public authorities don’t always provide data that is requested (and not always because they are being difficult, or inefficient – there are a number of situations where they are not obliged to). So, has Jonathan received the information he has asked for?
The most important data that I have asked the council to release has been refused. But I am still hopeful they will eventually release it.
All of mySociety’s websites hope to lower the barriers to civic participation; we hope that we encourage people to access channels of communication that they may never have previously considered open to them.
In Jonathan’s case, he says that if WhatDoTheyKnow wasn’t available, he would have made his requests by email – he’s already switched on to the existence and potential of the FOI act. But, he says, WhatDoTheyKnow is “a fantastic resource”.
When information is requested via email, it stays almost entirely hidden from view, unless the recipient chooses to publicise it. But on WhatDoTheyKnow, information becomes fully visible to everyone – all part of starting that ‘informed debate’ that Jonathan mentioned.
Thanks very much to Jonathan for telling us how he uses WhatDoTheyKnow.
This post is part of a mini-series, in which we meet people who regularly use mySociety’s websites.
- See also: FixMyStreet user, Steve and WriteToThem user, Kate.
- If you are a regular user of any of our sites, do drop us a line – we’d love to profile you too.
mySociety’s Freedom of Information website WhatDoTheyKnow is used to make around 15 to 20% of FOI requests to central government departments and in total over 160,000 FOI requests have been made via the site.
Occasionally, in a very small fraction of cases, public bodies accidentally release information in response to a FOI request which they intended to withhold. This has been happening for some time and there have been various ways in which public bodies have made errors. We have recently, though, come across a type of mistake public bodies have been making which we find particularly concerning as it has been leading to large accidental releases of personal information.
What we believe happens is that when officers within public bodies attempt to prepare information for release using Microsoft Excel, they import personally identifiable information and an attempt is made to summarise it in anonymous form, often using pivot tables or charts.
What those working in public bodies have been failing to appreciate is that while they may have hidden the original source data from their view, once they have produced a summary it is often still present in the Excel workbook and can easily be accessed. When pivot tables are used, a cached copy of the data will remain, even when the source data appears to have been deleted from the workbook.
When we say the information can easily be accessed, we don’t mean by a computing genius but that it can be accessed by a regular user of Excel.
We have seen a variety of public bodies, including councils, the police, and parts of the NHS, accidentally release personal information in this way. While the problem is clearly the responsibility of the public bodies, it does concern us because some of the material ends up on our website (it often ends up on public bodies’ own FOI disclosure logs too).
We strive to run the WhatDoTheyKnow.com website in a responsible manner and promptly take down inappropriately released personal information from our website when our attention is drawn to it. There’s a button on every request thread for reporting it to the site’s administrators.
As well as publishing this blog post in an effort to alert public bodies to the problem, and encourage them to tighten up their procedures, we’ve previously drawn attention to the issue of data in “hidden” tabs on Excel spreadsheets in our statement following an accidental release by Islington council; one of our volunteers has raised the issue at a training event for police FOI officers, and we’ve also been in direct contact with the Information Commissioner’s office both in relation to specific cases, and trying to help them understand the extent of the problem more generally.
Some of our suggestions:
- Don’t release Excel pivot tables created from spreadsheets containing personal information, as the source data is likely to be still present in the Excel file.
- Ensure those within an organisation who are responsible for anonymising data for release have the technical competence to fulfil their roles.
- Check the file sizes. If a file is a lot bigger than it ought to be, it could be that there are thousands of rows of data still present in it that you don’t want to release.
- Consider preparing information in a plain text format, eg. CSV, so you can review the contents of the file before release.
The local press in Islington has just reported the accidental release of quite a bit of sensitive personal data by Islington council.
One of our volunteers, Helen, was responsible for spotting that Islington had made this mistake, and so we feel it is appropriate to set out a summary of what happened, to inform journalists and citizens who may be interested.
On 27th May a user of our WhatDoTheyKnow website raised an FOI request to Islington Borough Council. On the 26th June the council responded to the FOI request by sending three Excel workbooks. Unfortunately, these contained a considerable amount of accidentally released, private data about Islington residents. In one file the personal data was contained within a normal spreadsheet, in the two other workbooks the personal data was contained on four hidden sheets.
All requests and responses sent via WhatDoTheyKnow are automatically published online without any human intervention – this is the key feature that makes this site both valuable and popular. So these Excel workbooks went instantly onto the public web, where they seem to have attracted little attention – our logs suggest 7 downloads in total.
Shortly after sending out these files, someone within the the council tried to delete the first email using Microsoft Outlook’s ‘recall’ feature. As most readers are probably aware – normal emails sent across the internet cannot be remotely removed using the recall function, so this first mail, containing sensitive information in both plain sight and in (trivially) hidden forms remained online.
Unfortunately, this wasn’t the only mistake on the 26th June. A short while later, the council sent a ‘replacement’ FOI response that still contained a large amount of personal information, this time in the form of hidden Excel tabs. As you can see from this page on the Microsoft site , uncovering such tabs takes seconds, and only basic computer skills.
At no point on or after the 26th June did we receive any notification from Islington (or anyone else) that problematic information had been released not once, but twice, even though all mails sent via WhatDoTheyKnow make it clear that replies are published automatically online. Had we been told we would have been able to remove the information quickly.
It was only by sheer good fortune that our volunteer Helen happened to stumble across these documents some weeks later, and she handled the situation wonderfully, immediately hiding the data, asking Google to clear their cache, and alerting the rest of mySociety to the situation. This happened on the 14th July, a Saturday, and over the weekend mySociety staff, volunteers and trustees swung into action to formulate a plan.
The next working day, Monday 16th July, we alerted both Islington and the ICO about what had happened with an extremely detailed timeline.
The personal data released by Islington Borough Council relates to 2,376 individuals/families who have made applications for council housing or are council tenants, and includes everything from name to sexuality. It is for the ICO, not mySociety, to evaluate what sort of harm may have resulted from this release, but we felt it was important to be clear about the details of this incident.
Since its launch in 2005, WriteToThem has always covered all parts of the United Kingdom, and the Northern Ireland Assembly was the first body added to TheyWorkForYou after the UK Parliament, in late 2006. So whilst we certainly have not ignored Northern Ireland, it had always been an irritant of mine (and a cause of infrequent emails) that FixMyStreet only covered Great Britain.
This was due to the way it had originally been funded and set up, but those issues were in the past, due to a myriad of changes both internal and external, and it was now more a case of being able to find the resources to implement the necessary work. Late last year, mySociety worked with Channel 4 on the website for their series of programmes on The Great British Property Scandal. This used, in part, code similar to FixMyStreet to let people report empty homes, and it was required to work in all parts of the UK. So as part of that process, code was written or generalised that let aspects of FixMyStreet like the maps and place name lookup work for Northern Ireland locations.
It’s taken a few months since then to allocate the time, but we’ve now been able to take the code written back then, add various other bits, and incorporate it into FixMyStreet – which now covers the 26 councils of Northern Ireland, and the central Roads Service. Issues such as potholes, graffiti, and broken street lighting can be reported to Antrim or Newry and Mourne as easily as Aberdeen or Wyre Forest, and just as in the rest of the UK you can sign up for alerts based around your location or to your council.
How do you get everyone working together when the community needs it most – like when there’s a heavy snowfall?
Recently, we posted a conversation with Chris Palmer of Barnet Council, where he talked about integration of FixMyStreet with the council website.
Barnet also use another mySociety tool – Pledgebank – and Chris explained how it helps them within the Barnet communities.
Turning complaints into action
“We took on Pledgebank in the belief that the council needs to get out of people’s way. Online communities are good at complaining about things: it’s easy to get instant outrage on the web, and actually we need mechanisms that allow people to get together creatively.
“One of the issues we had during the heavy winter of 2010 was that people complained the council wasn’t coming round and clearing their paths. Well, the council never came round and cleared the pavement outside those particular houses.
“Many people said, well if the council allowed us to, we would do it ourselves. Pledgebank allowed us to get parents at 25 schools to sign up last year. They pledged to come and spread grit and clear the snow from outside just in return for free shovels and a ton of grit.
“That kind of thing encourages residents to be active, it frees them from the frustrations that the political system gives them. If people feel, ‘Oh, there’s a legal process stopping me doing this’, it moves the council forward, to being an enabler rather than a provider of services.
“A parent can spend 15 minutes in the morning and then be confident their child will be at school for the day and that they can go off to work, so for the parents, it’s win-win.
“One of the things that surprised us was the response of local residents who live in the street but don’t necessarily have children at the school. They felt that they should be helping to clear the snow. It gave a group of active residents who we hadn’t even asked, a chance to be involved”.
Tapping into community interest
Why do you think that is? Is it just that people just want to contribute within their community?
“I genuinely think people just aren’t interested in councils. I couldn’t tell you the name of my council leader where I live, never mind the name of cabinet members. However, I am very interested in the services the council provides: the only public meeting I’ve ever been to was about parking, because it directly affected my street. And I’d probably say there’s a rule, where people will take responsibility for the space outside their own house, and be prepared to extend that a few houses either side. And this just gives people a mechanism to be involved in their local community.
“With Pledgebank, we can leave people to do things amongst themselves, with the understanding that the council is not just a provider of services, but a catalyst to people doing those things themselves”.
What else have you done with Pledgebank?
“We’re hoping residents will play a part in keeping their streets tidy with our Adopt-a-Street scheme. There’s a real sense of ownership if somebody controls the green space outside their house: do they plant the bottom of trees in the street with wild flowers, do they plant bulbs in what’s currently a grass verge? We can give them that element of ownership, and give them control of their local environment.
“So with Adopt-a-Street, we found one or two people locally with an interest in doing it, and we’re looking now at how we encourage them to leaflet their neighbours, get in contact with their neighbours.
A challenge for the marketing department
“It’s worth adding, though, that Pledgebank has taken us a lot of learning. It’s quite easy to imagine that anything you bung up on the web suddenly becomes viral: it doesn’t.
“One of the challenges for us is how we link into what we’re doing, how we publicise what we’re doing with Pledgebank and the web. So we have to look at it not so much as, here’s an interesting web device, but here’s a device that enables residents to do things. But the council has a responsibility to publicise it.
“The key challenge for us is making information available to the relevant people. It’s all about defining communities, and making information available to those communities – and mySociety has been tremendously helpful with that.
“It’s changed the way we’re using our information now and it’s fair to say it’s informed how we’ve built our new website.”
Barnet have been inventive with Pledgebank. As well as using it during the snows, they’ve managed street parties for the Jubilee and Royal Wedding; got volunteers to give IT training to residents; and encouraged visits to carehomes.
If you’re from a council and you think Pledgebank might work for you, drop us a line to find out more.
Image credits: Snow Big Dig by Shashi Bellamkonda, Lakeside Daisy by Matt MacGillivray, and Diamond Jubilee Street Party on Kenyon Clough by Dave Haygarth, all used with thanks under the Creative Commons licence.
Councils all around England have been busy getting ready to comply with the new duty to provide e-Petitions which kicks in today, 15th December. This means that on council sites across England you should now be able to make petitions which will be formally considered by the councils, in accordance with their chosen policies.
At mySociety we’ve spent a lot of time over the last twelve months helping councils to cope with this new duty by offering them a commercial petitions service that is really good for users and easy to administer for councils. Some of the sites have been live for months, but many of the 35 council e-petitions sites we’re currently contracted to supply launch today.
mySociety’s core developers Matthew Somerville and Dave Whiteland deserve huge credit for all the work they did re-purposing the No10 Petitions codebase and doing dozens of council customisations and rebrands. I’ve just seen one council officer email “Yippeee” at the prospect of launching, so I reckon they’ve done a pretty good job – well done gents, everyone in mySociety owes you a debt of gratitude for a time consuming job well done.
Here’s the current list of live local petitions sites. We’ll be adding more as they go up. Happy petitioning!
Blackburn with Darwen http://petitions.blackburn.gov.uk/
East Cambridgeshire http://petitions.eastcambs.gov.uk/
East Northants http://petitions.east-northamptonshire.gov.uk
Forest Heath http://petitions.forest-heath.gov.uk
New Forest http://petitions.newforest.gov.uk
Reigate & Banstead http://petitions.reigate-banstead.gov.uk
South Holland http://petitions.sholland.gov.uk
St Edmundsbury http://petitions.stedmundsbury.gov.uk
Suffolk Coastal http://petitions.suffolkcoastal.gov.uk/
Surrey County Council http://petitions.surreycc.gov.uk
Surrey Heath http://petitions.surreyheath.gov.uk
Royal Borough of Windsor and Maidenhead http://petitions.rbwm.gov.uk
I was just talking to someone in a local council about the fact that they’d opened up the location of 27,000 streetlights in their council area. They wanted to know if FixMyStreet could incorporate them so that problem reports could be more accurately attached.
This conversation reminded me that we’ve had an informal wish list of geodata for FixMyStreet for some time. What we need is more data that lets us send problems to the correct entity when the problem is not actually a council responsibility.
I’m just posting these up to see if anyone knows a guy who knows a girl who knows a dog who knows how to get hold of any of these datasets. In some vector data format, if possible, please!
- Canals and responsible authorities
- Supermarkets (esp car parks) and responsible companies
- Network Rail’s land
- Council owned land
- Land and roads controlled by the Highways agency
- Shopping malls
- National parks
- BT phone boxes (the original problem which inspired FixMyStreet)
So, do you know someone who might know someone who can help us improve FixMyStreet? And guess what, if we do add this to our web services, you’ll probably be able to query them too.
We’ve added a variety of new features to our postcode and point administrative area database, MaPit, in the past month – new data (Super Output Areas and Crown dependency postcodes), new functionality (more geographic functions, council shortcuts, and JSONP callback), and most interestingly for most people, a way of browsing all the data on the site.
- Firstly, we have some new geographic functions to join touches – overlaps, covered, covers, and coverlaps. These do as you would expect, enabling you to see the areas that overlap, cover, or are covered by a particular area, optionally restricted to particular types of area. ‘coverlaps’ returns the areas either overlapped or covered by a chosen area – this might be useful for questions such as “Tell me all the Parliamentary constituencies fully or partly within the boundary of Manchester City Council” (three of those are entirely covered by the council, and two overlap another council, Salford or Trafford).
- As you can see from that link, nearly everything on MaPit now has an HTML representation – just stick “.html” on the end of a JSON URI to see it. This makes it very easy to explore the data contained within MaPit, linking areas together and letting you view any area on Google Maps (e.g. Rutland Council on a map). It also means every postcode has a page.
- From a discussion on our mailing list started by Paul Waring, we discovered that the NSPD – already used by us for Northern Ireland postcodes – also contains Crown dependency postcodes (the Channel Islands and the Isle of Man) – no location information is included, but it does mean that given something that looks like a Crown dependency postcode, we can now at least tell you if it’s a valid postcode or not for those areas.
- Next, we now have all Lower and Middle Super Output Areas in the system; thanks go to our volunteer Anna for getting the CD and writing the import script. These are provided by ONS for small area statistics after the 2001 census, and it’s great that you can now trivially look up the SOA for a postcode, or see what SOAs are within a particular ward. Two areas are in MaPit for each LSOA and MSOA – one has a less accurate boundary than the other for quicker plotting, and we thought we might as well just load it all in. The licences on the CD (Conditions of supply of SOA boundaries and Ordnance Survey Output Area Licence) talk about a click-use licence, and a not very sraightforward OS licence covering only those SOAs that might share part of a boundary with Boundary-Line (whichever ones those are), but ONS now use the Open Government Licence, Boundary-Line is included in OS OpenData, various councils have published their SOAs as open data (e.g. Warwickshire), and these areas should be publicly available under the same licences.
- As the UK has a variety of different types of council, depending on where exactly you are, the postcode lookup now includes a shortcuts dictionary in its result, with two keys, “council” and “ward”. In one-tier areas, the values will simply by the IDs of that postcode’s council and ward (whether it’s a Metropolitan district, Unitary authority, London borough, or whatever); in two-tier areas, the values will again be dictionaries with keys “district” and “council”, pointing at the respective IDs. This should hopefully make lookups of councils easier.
Phew! I hope you find this a useful resource for getting at administrative geographic data; please do let us know of any uses you make of the site.