Monthly Archives: January 2007

Out Of Control

News

Yesterday Allen Jasson, a 55 year old IT worker from Melbourne was denied access onto a Qantas flight destined for London because of his shirt. The mans shirt had a picture of George W. Bush, the President of the United States of America with an overlay saying something to the effect of ‘Worlds #1 Terrorist’.

Qantas have stated they refused Allen Jasson onto the flight as they considered it a security risk and that it was offensive to other passengers on the flight. How they managed to take that rather large leap into terrorist, I don’t understand. Unless you were an absolute advocate of George W. Bush, I don’t even think that it could have been considered offensive.

When will the bullshit stop.

PostgreSQL Dynamic SQL & Quote_ident Gotchas

Database, Programming

PostgreSQL provides two useful functions to aid in the safe development of dynamic SQL:

quote_ident
The quote_ident function accepts and returns a text type. Whatever text you pass into quote_ident will be suitably escaped such that you could safely use it as an identifier within a dynamic SQL statement.

According to the documentation, you should always pass table and column identifiers into the quote_ident function for safety. Calling quote_ident('mytable') will return mytable, however calling quote_ident('MyTable') would return "MyTable".

quote_literal
The quote_literal function accepts and returns a text type. Whatever text you pass into the quote_literal function will be escaped so that you can safely use them in dynamic SQL.

You should always pass values or literals into the quote_literal function. By doing so, all special characters such as quotes will be safely dealt with. Calling quote_literal('mycolumn') would return 'mycolumn' whilst quote_literal('my\'column') would return 'my''column'.

Both of these functions work a treat, however there is a caveat with the quote_ident function which isn’t well documented. When creating objects in PostgreSQL, they object names are automatically lowercased unless you create the object using double quotes. As a simple example:

  1. CREATE TABLE MyTable (id integer, name varchar); would result in an object mytable being created.
  2. CREATE TABLE "MyTable" (id integer, name varchar); would result in an object MyTable being created; note the casing.

Now lets assume you wanted to create some dynamic SQL to fetch information out of the first example table above. If you issued quote_ident('mytable'), your dynamic SQL statement will execute because the value returned from quote_ident is lowercase which matches the table name. If you called quote_ident('MyTable'), your dynamic SQL statement will report an error stating that it cannot find the table or relation.

Creating dynamic SQL in PostgreSQL to fetch data out of the second example above, you would run into the reverse scenario. Issuing quote_ident('mytable') would produce an error, while quote_ident('MyTable') would execute without error.

If you create your database objects without using double quotes, then it’s important to remember to not pass capitalised parameters into quote_ident. The opposite is of course true as well, if you create your objects using double quotes then you must remember to pass in the same casing to quote_ident. If quote_ident applies double quotes (be it from capitised letters, spaces or special characters), the SQL engine within PostgreSQL will assume that an object exists with the explicit name matching the returned value of the quote_ident function.

Year In Review

General, Life

It’s hard to believe that 2006 is already a memory, the time seems to have just slipped by.

In January I completed the Story Bridge Climb as a birthday present from 2005; the views were spectacular. I became an uncle for the second time, with the birth of Maggie Grace.

Between January and April, a foolishly dedicated group of my colleagues and I undertook an insurmountable task. To this day, I’m not really sure why we did what we did; but I’m truly grateful to have gone through it with some of the finest people around.

In March, as an early birthday present for Claire, she received a very cute kitten which we named Princess. Throughout the year, Princess has given Claire and I a great deal of pleasure and we’re grateful that she came into our lives. Still on the pet front and it was time for the Lattimore family dog, Gizmo, to move onto greener pastures.

Somewhere around the middle of the year, MFS (the parent company of Stella Resorts Group) decided it was time to turn Stella Resorts Group into a force that the market would have to pay attention to. To that end, they started an aggressive acquisition trail, acquiring a slew of other tourism related businesses such as:

  • S8, which included Harvey World Travel, Gullivers, Travelscene, Travelsonic, Virgin Travel and others
  • Outrigger Australia & New Zealand
  • Tourism Hotels Leisure, which included brands such as Pacific International, Plaza Hotels, Grand Pacific and Golden Tulip
  • Sunleisure
  • and more

At the same time, Stella Resorts Group saw a need to replace their core IT infrastructure and software to accommodate the rapid expansion. The whole process has been an incredible eye opener, especially seeing first hand how swiftly changes of this magnitude can be executed in a large corporate. Its quite a daunting task however I and Stella Resorts Group are blessed to be working with a group of highly talented people and I can’t wait to see it all unfold.

August was a pretty huge month for quite a few reasons:

  • I clicked over 2 years with my current employer
  • Claire’s Grandfather Eric passed away
  • Claire’s first nephew, Oliver was born
  • We celebrated George’s 70th birthday
  • We found out that Belinda was pregnant

In September, Claire and I celebrated being married for a whole whopping year! It’s hard to believe that Claire and I have been together for the better part of ten years now, we’re old hands at this stuff.

Claire has been in almost constant employment with Education Queensland since she completed university. Unfortunately, that employment hasn’t come easily at all – at times its been a highly stressful and frustrating ordeal. Fortunately, on the last day of school last year; Claire was offered a permanent full time teaching position at a primary school! When Claire called and told me, I was jumping for joy knowing that we don’t have to worry about that aspect of our lives again. I’m really proud of Claire for continuing to kick the tyres and seeing it through. The Christmas break this year was fantastic, good company and great food. Following on from Christmas was my 26th birthday and the seeing in the new year!

2006 seemed like a big year, with quite a few lows and highs. Overall the year has been rewarding and I’ve learnt a lot about life and myself. I think 2007 is going to be even bigger but it’ll bring different challenges; which I’m really looking forward to.

WeblogToolsCollection.com Redesigned

Websites

It’s a new year and Weblog Tools Collection gets a new look.

I love:

  • the new logo
  • use of colour
  • fluid layout
  • larger fonts

I don’t love:

  • the recent news feels a little cluttered
  • the recent readers at the moment looks out of place

Even with the few small things that I don’t like at the moment, its such an improvement over the old site that I can’t wait for the new design to be deployed throughout the entire site.

Great effort!