MySQL: Bar Charts

 Working Notes  No Responses »
May 192014
 

So there I was, contemplating whether I could produce a nice and simple bar chart showing the number of films I’ve watched per month. Using just MySQL.

I knew it was easy to produce a simple numerical count with count(*) and a group by clause, but a bar chart? Turns out it was easy with the repeat string function.

mysql> select date_format(whence, "%Y-%m") "Month", 
          repeat('*', count(*)) "Number of Films" 
          from films group by date_format(whence, "%Y-%m");
+---------+------------------------------------------------+
| Month   | Number of Films                                |
+---------+------------------------------------------------+
| 2007-05 | ****                                           |
| 2007-06 | ******                                         |
| 2007-07 | ******                                         |
| 2007-08 | *****                                          |
| 2007-09 | ******                                         |
| 2007-10 | ***                                            |
| 2007-12 | ****                                           |
| 2008-01 | *****                                          |
| 2008-02 | ****                                           |
| 2008-03 | *                                              |
| 2008-04 | *****                                          |
| 2008-05 | *****                                          |
| 2008-06 | ******                                         |
| 2008-07 | ****                                           |
| 2008-08 | *****                                          |
| 2008-09 | *****                                          |
| 2008-10 | ****                                           |
| 2008-11 | *****                                          |
| 2008-12 | ***                                            |
| 2009-01 | ***                                            |
| 2009-03 | **                                             |
| 2009-04 | ***                                            |
| 2009-05 | **                                             |
| 2009-06 | ****                                           |
| 2009-07 | ****                                           |
| 2009-08 | *                                              |
| 2009-09 | **                                             |
| 2009-10 | ****                                           |
| 2009-11 | **                                             |
| 2009-12 | **                                             |
| 2010-01 | **********                                     |
| 2010-02 | **********                                     |
| 2010-03 | *************************                      |
| 2010-04 | *****************************                  |
| 2010-05 | ********************************************** |
| 2010-06 | ***********************                        |
| 2010-07 | ****************                               |
| 2010-08 | **********                                     |
| 2010-09 | ************                                   |
| 2010-10 | **********                                     |
| 2010-11 | ********                                       |
| 2010-12 | *********                                      |
| 2011-01 | *******************                            |
| 2011-02 | *************                                  |
| 2011-03 | **                                             |
| 2011-04 | ************                                   |
| 2011-05 | ********                                       |
| 2011-06 | ***                                            |
| 2011-07 | ****                                           |
| 2011-08 | *********************                          |
| 2011-09 | *                                              |
| 2011-10 | **                                             |
| 2011-11 | ***************                                |
| 2011-12 | ********************                           |
| 2012-01 | ********************                           |
| 2012-02 | *******                                        |
| 2012-03 | *******                                        |
| 2012-04 | *****                                          |
| 2012-05 | ******                                         |
| 2012-06 | *******                                        |
| 2012-07 | **************                                 |
| 2012-08 | ************                                   |
| 2012-09 | ***************                                |
| 2012-10 | *******************                            |
| 2012-11 | ****************                               |
| 2012-12 | *******                                        |
| 2013-01 | *************                                  |
| 2013-02 | *************                                  |
| 2013-03 | ******************                             |
| 2013-04 | ******************                             |
| 2013-05 | ********                                       |
| 2013-06 | ************                                   |
| 2013-07 | **************                                 |
| 2013-08 | ***                                            |
| 2013-09 | *************                                  |
| 2013-10 | ********                                       |
| 2013-11 | ***************                                |
| 2013-12 | ***********************                        |
| 2014-01 | *************************                      |
| 2014-02 | ********                                       |
| 2014-03 | *************                                  |
| 2014-04 | ****************                               |
| 2014-05 | ************                                   |
+---------+------------------------------------------------+
83 rows in set (0.02 sec)

The Heartbleed Vulnerability Panic?

 Computing, Security  No Responses »
Apr 092014
 

The interwebs are all a flutter over the latest vulnerability announcement – an OpenSSL vulnerability that has been termed the heartbleed vulnerability. But is it that serious? And what is it anyway?

What Is It?

OpenSSL is a very widely used software component that adds encryption – a web server will very likely use OpenSSL to allow it to encrypt communications between yourself and it. The vulnerable versions of OpenSSL come equipped with new functionality – a “heart beat” that is used to keep connections alive and open.

When this functionality is not disabled and you are using a vulnerable version of OpenSSL, an attacker can make a connection to your server and ready up to 64Kbytes of the process memory. For each and every request.

This is a classic information leakage issue, and the attacker can trawl through a collection of 64Kbyte “chunks” of binary data looking for interesting information. In theory, these chunks of information can contain anything the process (the web server, the mail server, etc) contains within itself. Some examples include :-

  1. A researcher has used this vulnerability to expose Yahoo Mail account passwords.
  2. It is believed to be possible to extract a server’s private key to allow an attacker to decrypt communications traffic and/or impersonate the server.

Whilst trawling through binary chunks of data looking for interesting data is the sort of activity that seems to normal people to be so difficult that it would be almost impossible for someone. However it is possible, and for something like passwords is even easy. And for private keys, there are hints out there on how to do it.

But How Does This Affect Me?

If you are not a server administrator, this will all seem a bit geeky and not have much meaning for you.

It is probably better to ask: What should I do about this? And the answer is to do nothing unless you are advised to do so by a trusted source. Whatever damage has taken place already and service providers will be busy fixing the vulnerability.

The only addition to that is to make sure you update your software on your computers – your laptop, phone, tablet, etc. Whilst the media is concentrating on the server side of the problem, OpenSSL is also used on client machines, and that means that your computers are vulnerable in some way – whilst no exploits are known to exist today, it is still worth being proactive in making sure you apply updates.

Because sooner or later, attackers will use this vulnerability to attack you directly rather than via servers.

But How Serious Is This?

Very.

But perhaps not as much as some of the more extreme possibilities might suggest.

There is a great deal of probability involved here. For example, was it possible that this vulnerability was known to the “bad people” before the announcement this week? The vulnerability has existed for a year or two so it is possible it was known about. But probably not widely known.

Was it exploited? Possibly, but it’s probable that it wasn’t widely exploited – the activities of “bad people” tends to leak. If it was exploited, it was quite possible that it was limited to the NSA and GCHQ.

As to over-reaction, there was a comment on a blog entry about this that claimed that his Yahoo Mail account password had been compromised three times in the last month by this method. Well, possibly but it seems far more likely that his password had been compromised via other methods – such as using a weak password. Using this method against Yahoo’s servers may reveal some account passwords, but it is likely to reveal random account passwords each time. Meaning that an attacker will find it quite hard to compromise the password for a single account more than once.

Going forwards, it is very likely that this vulnerability will be used by “bad people” – there are already indications that they may be starting to try this.

So it is important and urgent for server administrators to look at this problem and fix :-

  1. Update vulnerable OpenSSL versions.
  2. Revoke the old SSL certificates
  3. Issue new SSL certificates.
  4. If passwords are known to have been compromised, issue a notice to suggest people change their passwords.

It is also important that client machines are updated as and when fixes are released.

AMD’s “Secret” 32-Bit ABI

 Computing  No Responses »
Mar 132014
 

This blog posting talks about machine code and whether 32-bit code or 64-bit code is more appropriate. When AMD released the Opteron way back in 2003, it was the first processor to support the x86-64 instruction set for supporting 64-bit code whilst maintaining backward compatibility with the old IA32 code. Or in other words, the Opteron could run both 32-bit code and 64-bit code.

Everybody leaped onto the 64-bit bandwagon without thinking too much about it – it was faster.

But if we look at the other processors that made the transition from 32-bit code to 64-bit code – such as the SPARC, MIPS, etc., we find something interesting. Much of the code running on the relevant operating systems remained 32-bit – not as a transitional measure, but because the 32-bit code was faster. If you look at a relatively modern Solaris system the contents of /bin contain 500-odd binaries that are 32-bit and just 11 that are 64-bit (most of which aren’t in fact part of Solaris but another add-on package).

It turns out that in general, 64-bit code is slower than 32-bit code. In the case of x86-64, 64-bit code is faster not because it is 64-bit, but because of the architectural changes that were also introduced – including (probably most significantly) extra registers.

How do we know this? Apart from it being obvious to those who have lived through the 32-bit to 64-bit transition multiple times, it turns out that people have been experimenting. As it turns out, the x86-64 architecture does allow for 32-bit code to be run with all the features of the x86-64 architecture and that architecture has been labelled as X32.

It turns out that X32 code can be anywhere from 5-40% faster than 64-bit code. The largest increases come from code that makes very heavy use of pointers, and at present no benchmarks of “ordinary” software have been released.

The “downside” of X32 is of course that the software is limited to 4Gbytes of memory, but most programmes don’t need that much memory because 4Gbytes is a lot. Forget that huge video editor you’re playing with – that will quite possibly need 64-bit pointers, but what about all the other software running on your machine?

There are over 400 processes running on my workstation, and none of those processes really requires more than 4Gbytes of memory. Sure I run software that does require more than 4Gbytes of memory, but not all the time.

And running things 10% quicker would be useful … or alternatively running things quicker means the processor can spend more time asleep making battery life longer.

Reshelving ….

 Computing  No Responses »
Feb 232014
 

Just an experiment in producing a video :-

Not especially good because I’m too lazy to go back and even out the exposure (so the lighting doesn’t keep changing).

Time To Name And Shame The “Bad” ISPs – It’s Not Just NTP DDoS!

 Computing, Security  No Responses »
Feb 222014
 

Having had a wee bit of fun at work dealing with an NTP DDoS attack, I feel it is long past time to tackle the root cause of the problem – the ISP’s who have neglected to implement ingress/egress filtering despite it being considered best practice for well over 15 years. Yes, longer than most of us have been connected to the Internet.

It is easy to point at the operators of NTP services that allow their servers to be used as attack amplifiers. And yes these insecure NTP servers should be fixed, but given the widespread deployment of NTP in everything it could take up to a decade for a fix to be universally deployed.

And what then? Before the widespread use of NTP for the amplification distributed denial of service attacks, DNS was commonly used. And after NTP is cleaned up? Or even before? There are other services which can be exploited in the same way.

But the way that amplification attacks are carried out involves two “vulnerabilities”. In addition to the vulnerable service, the attacker forges the packets they send to the vulnerable service so that the replies go back to the victim. Essentially they trick the Internet into thinking that the victim has asked a question – millions of times.

Forging the source address contained within packets is relatively easy to do, and it has been known about for a very long time and the counter-measure has also been known for nearly as long. To put it simply, all the ISP has to do is to not allow packets to exit their network(s) which contain a source address that does not belong to them. Yet many ISPs – the so-called “bad” ISPs – do not implement this essential bit of basic security. The excuse that implementing such filters would be impossible with their current routers simply doesn’t wash – routers that will do this easily have been on the market for many years.

It is laziness pure and simple.

These bad ISPs need to be discovered, named, and shamed.

 Older Entries  Newer Entries