Archive for December, 2007
When SQL Server replication eats disk space
Part of my job involves revising our SQL Server architecture. My plan includes the addition of a read-only reporting SQL pair for non-critical enquiries and reports. This allows the heavy and unpredictable load from reporting away from from the primary SQL pair responsible for critical operations (shipping orders).
We utilised SQL Server’s publisher-subscriber replication on the required databases which, given their legacy nature, had some cross-database dependencies that were added without due consideration.
The reporting SQL pair needed to be rebuilt so the subscriptions were removed whilst they were being rebuilt. Within three days our primary servers were out of disk space and we had a problem that needed to be solved quickly.
Our first step was to remove the new publications. This released a small amount of space in the distribution database (now 100GB) and allowed the replicated databases logs to be truncated and shrunk. We now had a little breathing room.
One publication would not remove in a reasonable time (10+ hours) and even trying to remove individual articles caused a slow-running operation sat on PAGEIOLATCH_SH (which Microsoft describe as waiting on disk IO). A quick investigation revealed a complex query on internal system tables with no indexes – presumably for fast insert performance.
In the mean-time this slow-running operation would block the replication agents which are also used by IBM’s DataMirror product to replicate data back to the iSeries (AS/400) in real time. This blocking would prevent us shipping orders and was therefore unacceptable.
The database in question is a home-grown data warehousing application rebuilt each night and on reflection was a terrible idea to replicate using this method. Each night it generated over twice it’s own size in replication activity. Given we have a 72 hour replication retention and the size of the database that alone was enough to wipe out the disk space on our server within 3 days.
Reinitialize All Subscriptions was the magic bullet which ran in just a minute allowing the publication to be subsequently instantly deleted. The replicated databases transaction logs were then truncated and shrunk giving us ample breathing space on the storage front.
The distribution database was still huge but heading into Replication Monitor’s Common Jobs and starting the Distribution clean up: distribution job was our final step.
The job might take all day but did not block any other replication activity :)
[)amien
Friends Reunited violates own privacy policy to spam users
Hello Damien
You have received this email as a customer of our sister company, Friends Reunited.
Stared back at me from my inbox yesterday purporting to be from UK television company ITV. It was curious because I always de-select the marketing option. I visited Friends Reunited site to confirm the option was indeed off which it was. More interestingly it stated it would be for their own marketing messages, not third parties which their privacy policy clarified:
We, and (where relevant) our credit card processing agents, will not supply your personal data to a third party for commercial exploitation – thus, you can rest assured that we will not sell the lists of our Members’ email addresses to a third party; and
Yet here they are doing exactly what they claim not to.
Two subsequent emails to their support team resulted in one stating they have switched my third-party emailing option off (there is no such option, they claim never to do it at all) and when I clarified this for them another stating:
All members comments and suggestions are noted and passed onto our management team for consideration.
Such blatent disregard for their own policies and European regulation should not be ignored. If you live in the UK and a UK company is spamming you then complain to the Information Commissioners Office.
[)amien
ASP.NET MVC preview available
The first public preview of Microsoft’s ASP.NET MVC (model view controller) framework is now available.
Download ASP.NET 3.5 Extensions (EXE) (3.7 MB)
Download MVC Toolkit (ZIP) (400 KB)
The project takes cues from Ruby on Rail’s success and looks to address dissatisfaction with the testability and maintainability of WebForms applications and provides an alternative approach that is centered around views, models, controllers with a clear separation of concern and the ability to mock test the individual elements.The official documentation is online and there is a great four-part series over at Scott Guthrie’s blog which covers:
Phil Hack and Rob Conery are both now at Microsoft and working on the framework, they have some interesting things to say on it too:
- Extending to add conventions
- Test-driven development and dependency injection
- Using user interface (HTML) helpers
- Using RESTful architecture
- Using Ajax
A few other people have already written about the subject too:
- Jeffrey Palermo’s podcast interview
- Fredrol Normén on exception handling
- Brad Abrams on creating an RSS feed with LinqToSql
- Dino Esposito on architecture
- MVC Contrib open-source additions & helpers
Most of the examples and many of the routines/helpers fail to encode output which opens them up to HTML and script injection vulnerabilities. Remember to HttpUtility.HtmlEncode output and use Reflector if you’re unsure whether a function is encoding correctly.
The CTP requires Visual Studio 2008 to get the most out if it so either head over to MSDN Subscriber Downloads or grab a 90-day trial edition if you don’t already have it installed.
[)amien
How dangerous is HTML injection?
A few years ago I believed that HTML and SQL injection vulnerabilities were headed for extinction. Thanks to object-relational mapping tools SQL injection continues to die but HTML and script injection vulnerabilities are as popular as ever.
Part of the problem stems from the “back-to-basics” approach to rendering web pages, throwing out classes and controls for string-based libraries (primitive obsession) and helpers which do not encode HTML or even offer a concise simple syntax to do so.
MonoRail was one such project but they took feedback on board and addressed the issue although I was surprised it had got as far as release candidate 2 with such a serious oversight.
Other projects have been less reactive when advised of the problem and I can’t help but wonder if I am not getting the severity of the issue across. This isn’t just an annoyance but a real security problem.
- HttpUtility.HtmlEncode (.NET)
- Server.HtmlEncode (ASP)
- htmlentities/htmlspecialchars (PHP)
- html_escape (Rails)
- {! } (MonoRail Brail)
and your web apps output data then they are likely open to HTML & script injection vulnerabilities.
Vulnerable code often looks like this:
myLabel.Text = Request.Form["Something"];
Response.Write(Request.Cookies["AddedProduct"]);
<%= myDataReader[0] %>
<? php echo get_the_title() ?>For more ASP.NET examples check out 5 signs your ASP.NET application may be vulnerable to HTML injection.
Let’s start by considering the actors involved:
Visitor to visitor
If your site stores input from an external user (visitor) and displays it to another then you could be exposed to this scenario. Many sites do this although it is not always immediately recognised – an internet banking site does not seem an obvious candidate until you consider that you may put a textual reference on payments made to another person. If you know they use a vulnerable internet banking solution…
A worst-case scenario here would be that one visitor could steal another’s login credentials and exploit whatever rights that might give him – anything from posting messages to stealing funds.
Visitor to staff
Not all sites exchange data between users but if your site collects information from visitors chances are it presents this information to staff. Internal systems used to examine it are often considered less vulnerable which is a mistake. Remember *all* data provided from a user should be considered to be a potential avenue for a dangerous payload, e.g. even the language-accepts or user-agent strings.
When exploited internal systems can reveal information in bulk about the users, the system and the administration accounts used to manage it. Gaining access to these details brings all the privileges those accounts have to offer which can be catastrophic.
Staff to visitor
It is easy to forget that many frauds are perpetuated by people on the inside. A staff member given the ability to present text to the user via a website has the ability to modify any page that the content is presented on which if it includes a login page (perhaps for system status messages) then capturing login details to a server of their own choice is easy.
Security operators with access to reset (but not view) passwords would find this attack particularly enticing given that they do not need to reset the users account and therefore raise any awareness. An insider can perpetuated the fraud and may be in a position to further conceal it within the organisation.
Next steps?
I can envisage a sequence of steps that start with discovery of injectable systems through detection of script-enabled into form capture-and-forward and async logging of passwords through XmlHttp.
Detailing those steps would certainly raise awareness and help developers appreciate the severity of the issue but how do I make sure that information isn’t abused?
Disclosure is a double-edged sword but then you can’t have security through obscurity… I wonder how many crackers/black hackers already utilise these techniques for nefarious means.
.NET developers might like to check out the slides from the Web Application Security talk I gave at the Guernsey Software Developer Forum which demonstrates exploitable, exploits and safe alternatives for preventing HTML and SQL injection.
[)amien