Handy SQL snippet: Easily calculate average age of all members from DOB

The problem: Let’s say you have a database with a members or users list and you have the birthdate of these members stored in a date or datetime format (i.e. 1985-07-31 or similar). Now let’s say you would like to know the average age of the members in your list. This incidentally was exactly the problem I faced this afternoon. So what’s an easy way to do it in a matter of seconds? Simple once you have it figured it out.

If you have the list stored in a MySQL database, this little snippet will get the job done easily:

SELECT avg( (year( now() ) – year( [birthdate] ) ) + (DAYOFYEAR( now() ) > DAYOFYEAR( [birthdate] ) ) ) FROM [tablename]

Note: you need to replace [tablename] with the actual tablename (without the square brackets of course) and [birthdate] with the actual name of the column in your [tablename] in which you store the birthdate.

So what’s going on here?

Here’s the breakdown of what it’s doing:

      Step 1 – We calculate each member’s age by subtracting the year we are now in from the year of their birth: year( now() ) – year( [birthdate] )
      Step 2 – We check if as of right now, that person’s birthday has already passed, and if so, we will be adding 1 year to their age, if not, we don’t add a year: + ( DAYOFYEAR( now() ) > DAYOFYEAR([birthdate]) )
      Step 3 – We are selecting the birthdays from the relevant table in the db, and with each record, doing the calculation and taking an average, i.e. the avg () portion

And that’s it!

I don’t remember offhand the syntax if you were doing this in another db, such as MS SQLServer or Postgres, but the syntax should be very similar (or exactly the same, just depending on whether those functions operate exactly the same in those dbs).

Amazon, oh Amazon, You Continue to Disappoint Me

Following Amazon’s EC2 recently reaching capacity at certain EC2 zones, I now shake my head in dismay at what to me, is another poor showing by a service that I would love to love, if only they would let me!

So today we get an email from them soliciting feedback to their SimpleDB service, which we recently tried out for a few days, but found somewhat lacking for our needs. The email goes like this:

Dear OleOle,
Amazon Web Services is constantly striving to improve our customers’ experience using our products. We particularly want feedback from our new users.
On 6/23/2009, you signed up for Amazon SimpleDB. Please share your experience about getting started with Amazon Web Services by completing the following survey (9 questions): Amazon SimpleDB Getting Started Survey [This last bit being a link to their survey]. etc etc.

So “Great!”, thinks me, they are being proactive, and hopefully they will improve this service and make it useful for us. Alas, not so fast. The link to their “survey” goes to “Cannot Find Server” – basically that domain for the survey doesn’t resolve in DNS.

Maybe they should have created the sub-domain first?

Maybe they should have created the sub-domain first?

That’s just so sloppy. Incompetent even. Is Amazon getting too big for it’s britches?

Twitter Doc Theft – Details Revealed: Step By Step To How It Was Done

TechCrunch posted a great step by step account this morning that details almost exactly how Frenchman Hacker Croll (HC) was able to steal over 300 sensitive Twitter corporate docs, as well as gain access to numerous online accounts of several Twitter employees.

It’s a long article, but very interesting and if you have any interest in keeping a tight reign over the security of data that you keep online (email etc), you owe it yourself to give the TC post a thorough read. Now that we have details as to exactly what occurred and how it was done, my head is spinning with the myriad number of security issues raised by this incident. I plan to write a series of posts in the coming days discussing these issues in greater detail.

I quote here the TechCrunch summary of the attack:

  1. HC accessed Gmail for a Twitter employee by using the password recovery feature that sends a reset link to a secondary email. In this case the secondary email was an expired Hotmail account, he simply registered it, clicked the link and reset the password. Gmail was then owned.
  2. HC then read emails to guess what the original Gmail password was successfully and reset the password so the Twitter employee would not notice the account had changed.
  3. HC then used the same password to access the employee’s Twitter email on Google Apps for your domain, getting access to a gold mine of sensitive company information from emails and, particularly, email attachments.
  4. HC then used this information along with additional password guesses and resets to take control of other Twitter employee personal and work emails.
  5. HC then used the same username/password combinations and password reset features to access AT&T, MobileMe, Amazon and iTunes, among other services. A security hole in iTunes gave HC access to full credit card information in clear text. HC now also had control of Twitter’s domain names at GoDaddy.
  6. Even at this point, Twitter had absolutely no idea they had been compromised.

Source: TechCrunch

WOW! So many things jump right out at me from reading this, including:

  1. Sloppy email and password account management by Twitter employees concerned
  2. Dangers of mixing work and personal email activities
  3. What kind of online footprint you leave by your public participation in social networks, and how vulnerable to attack that can make you (actually this is hinted at not from the above summary account but other details in the TC post)

More to come on this subject in the next few days.

Calling Apple Fanbois and Fangirls – Hail the iRing!

Now let me say first off that I am definitely NOT an Apple Fanboi myself. Although I do have an iPhone and think Macs are way cooler than PCs, I admit that I am a hardcore PC user. That said, this Apple concept piece from Victor Soto is SO cool. It’s not new, but I only just became aware of it yesterday.

Witness the iRing:

The iRing controls your iPod. Concept design by Victor Soto

The iRing controls your iPod/iPhone. Concept design by Victor Soto

He said the idea behind the Ring is it’s a device that “could control the Playback functionality of your iPod/iPhone device Wirelessly.” He modeled it using Max 9 and rendered the design in Vray. This looks very Applesque to me. Great job Victor!

Cloud Computing – Amazon EC2 Zone Reaches Capacity

I’m planning to write a series of posts documenting in detail the experiences that we have had at OleOle migrating our entire website infrastructure from a traditional managed hosting company to Amazon’s Cloud Computing services (EC2, S3, etc.). This was a process we began scoping out at the beginning of ’09 and actually completed just a couple months ago.

This post is jumping right into the middle of things so to speak where we are already well and fully entrenched in Amazon’s cloud now, and having to deal with the many issues that crop up from time to time that they don’t tell you about in the marketing material.

So on to the subject of today’s post:

We noticed yesterday that all our new EC2 app server instances were booting up in Amazon’s Zone US-east-1d, whereas up till that point in time we had always used Zone US-east-1b with no issues. Several core parts of our system including db servers, load balancers and memcache servers are in Zone US-east 1b.

What’s going on? Well apparently zone 1b is at or near capacity and when we try to force a new instance to be in that zone, we get a message saying “insufficient capacity” , which means any new app server instances that start are not likely to have any chance to be in the same zone as our dbs, load balancers and memcache servers.

Amazon Zone 1b Over Capacity

Amazon Zone 1b Over Capacity

Furthermore, because our EC2 app server instances all autoscale via Rightscale, they are always short lived – constantly being terminated when capacity is not needed and new ones coming online automatically when load spikes up at certain times of day. The autoscaling is fantastic, a true thing of beauty to behold when you see it action and really makes “utility computing” a reality for us. However, it also means that all of our app server instances are now in zone 1d and not our preferred zone 1b.

And why does this matter? LATENCY is why! We performed some basic testing of ping times between zones:

1b to 1b – average ping time between a server in each zone: 0.45ms
1a or 1d to 1b – average ping time between a server in each zone: 1.9ms

That is a whopping 4 times increase in latency when going across zones versus having all your servers in the same zone. When you have a multitude of calls going on between servers (app to db, app to memcache, and back again, load balancer to app, etc.) in the context of a single web request from a user, that 4 times increase in latency becomes very noticeable, even though we are talking milliseconds differences with each single call.

What can we do about it? We could migrate everything over to zone 1d, but then that seems like a stop gap solution. There’s no guarantee that 1d won’t run out of capacity, in fact, it’s almost certain to at some point, and when that happens, it will force us to have to migrate yet again to keep all our servers in the same zone.

Maybe this is just one of the pitfalls of this type of cloud computing platform, but I can’t get over the feeling that accepting a 4 times increase in latency is just not acceptable.

NOTE: I am well aware that Amazon touts having multiple zones to put your servers in as a plus to avoid a single point of failure as each zone is in a different data centre (and possibly geography). And we have all seen recently what can happen when something takes out a Rackspace data centre (trips on a powercord, network outage, car crashes into a generator, whatever…). But the cost of this is a lot of added latency and it should be a choice that we get to make whether we want to accept that latency or not.

Twitter Docs Stolen off Gmail. How it was done.

I’m interested to document how French hacker “Hacker Croll” was recently able to steal sensitive company documents from Twitter.

My interest in this is to inform myself and hopefully others as to how to safeguard ourselves as best we can from suffering similar fates in future.

What I have gleaned so far:

From TechCrunch yesterday:

“Hacker Croll was able to compromise the Twitter accounts of founder Evan Williams, his wife, and several employees. Using password recovery techniques, Hacker Croll claims he gained access to various Paypal, Amazon, Apple , AT&T, MobileMe and Gmail accounts.  Evan Williams… confirms:

Yes, we did suffer an attack a few weeks ago and are familiar with this list of stuff. This is unrelated to the hack of twitter where someone gained access to user’s accounts. This had nothing to do with the security of twitter.com, and there were no user accounts compromised here.

Some notes:
- He did not actually gain access to my @ev Twitter account (or any Twitter accounts) nor any administrative functions of the site.
- There is also no evidence that he gained access to my email. There was one administrative employee who’s email was compromised, as was my wife’s Gmail account, which is where he got access to some of my credit cards and other information.
- He also successfully targeted a couple other employees personal accounts (Amazon, AT&T, Paypal…)

In general, most of the sensitive information was personal rather than company-related. Obviously, this was highly distressing to myself, my wife, and other Twitter employees who were attacked. It was a good lesson for us that we are being targeted because we work for Twitter. We have taken extra steps to increase our security, but we know we can never be entirely comfortable with what we share via email.

Above and below are purported screenshots of Williams’ accounts on Twitter, Gmail, and GoDaddy. He claims he was able to access Twitter’s domain name account on GoDaddy and could have redirected the traffic to another IP address (I’m sure that would have worked for about three minutes).  The Gmail access, if true, would have been more troubling.  Once the hacker got into @ev’s Gmail account, password recovery for other accounts was easy.  He claims to have gained access to some internal documents, including projections for reaching 25 million users in 2009, 100 million in 2010, 350 million in 2010, and an outlandish goal to eventually become the first Internet service to reach one billion users. So maybe some corporate information was compromised.

And from PC World today:

“…what’s strange about the hack of Twitter’s Gmail accounts is that Google’s security process is not as simple as Yahoo’s allegedly was at the time of the Palin hack.

On the password recovery page, Google asks you for your username, and then requires you to enter a CAPTCHA. Then Google sends a link to the e-mail address you originally entered when you signed up for a Google account. If you don’t have access to that account, Google will not allow you to access your account by answering your security question until 24 hours after you’ve received the security e-mail at your alternate account. Yahoo Mail currently uses a similar password recovery method.

It’s not clear if this security measure was in place at the time Hacker Croll accessed the Gmail accounts associated with Twitter, but it does serve as a reminder that you must keep your information up to date and choose a security question that will be difficult for a hacker to figure out.”

“…Twitter co-founder Evan Williams was contacted byTechCrunchto confirm the document theft. Williams reportedly confirmed that Twitter did suffer an attack several weeks ago…

Williams told TC the company is familiar with the list of information Hacker Croll obtained… The Twitter co-founder confirmed the hacker gained access to his wife’s Gmail account — where some of Williams’ credit card information was stored — as well as an administrative employee’s Gmail account and a number of personal accounts of other Twitter employees. Williams says Hacker Croll did not gain access to William’s Gmail account, and that Twitter has now taken further security measures to guard company property and internal documents.”

This suggests that the hacks were on Gmail accounts (not Google Apps although the above does not rule out that here might have been additional hacks on other stuff, such as Google Apps). If the original point of entry by the hacker were the personal email accounts of various employees (and even relatives of employees, such as Williams’ wife), how did this lead to so many sensitive company documents being compromised? I am left wondering why so much sensitive company documentation would be found on personal Gmail accounts of employees.

There’s also the rather interesting tidbit (take that as a warning!) that Williams’ credit card info was somehow stored within his wife’s Gmail account. A big no no for sure! I can’t think of any good reason to ever keep your credit card number stored digitally somewhere that you can control (not including merchants’ databases that obviously store your CC info when you transact with them). The card’s in your wallet, why keep the number online somewhere else as well?

I’ll post more as other details emerge, if indeed they do.

Update 1: Twitter co-founder Biz Stone posted this morning at 11.15am providing some clarity on what happened. Some of the salient excerpts:

“About a month ago, an administrative employee here at Twitter was targeted and her personal email account was hacked. From the personal account, we believe the hacker was able to gain information which allowed access to this employee’s Google Apps account which contained Docs, Calendars, and other Google Apps Twitter relies on for sharing notes, spreadsheets, ideas, financial details and more within the company.”

So as I suspected, Twitter is using Google Apps and that was where presumably the majority of those hundreds of stolen docs came from. Still interesting though is how the hacker was able to get from the personal email account of one employee into that employee’s Google Apps account. Why are employees co-mingling personal email with business? I never ever do that myself and believe it’s generally a poor practice, not just for security reasons but for several others as well.

The Mentality of Entitlement to “Free”

A recent post just over a week ago by Mike Arrington at TechCrunch entitled “What The Hell Happened To The Free Version Of Google Apps?” caught my attention, and not because I too use Google Apps’ free edition myself for several side projects. Rather it was because of the overriding tone in the article, as evidenced right from the word go with that strongly worded headline, that we were all somehow ENTITLED to (unlimited forever?) access to this free edition – that it couldn’t ever be taken away without some violation of our rights having occurred. That mindset I find interesting. Perhaps a little troubling even.

Don’t get me wrong – I love the Google Apps Standard Edition (officially name for the free version), as it’s a great service (as in good quality, very useful, very well put together) and indeed great that it’s offered for free. But I don’t take that it’s free NOW for granted. I would be seriously bummed if Google wanted to start charging me to use it for existing accounts, and bummed also but to a lesser extent if the pay model was extended only for any new accounts. So I am all for Arrington and others calling for Google’s head if they take away the free version.

Oh in case you didn’t already know it, Google didn’t actually take away the free version – they just moved the link to it to somewhere else to make it a little less obvious. Crisis averted.