Wednesday, January 29, 2014

Muse - Panic Station (Bass Cover)

Thursday, January 16, 2014

Hedonism (Skunk Anansie) - Bass Cover

What a hiatus!

So for those who just follow my blog you might have wondered what I have been up to. Let's face it, my last blog post was published last April, almost nine months ago. Many things happened and I think it's best to summarize them as follows:

1. I am back in Malta! Yes I missed this tiny island so much.
2. I am working in a new company, as a Senior DevOps engineer.
3. I joined a new heavy metal band, we are still in the process of finding a singer but I think we are almost there!
4. I started taking photography more seriously and now I have my own flickr account! I think photography was a long dormant passion inside me!

I have so much stuff to share with you in this blog, mostly IT, photography and music related, so I'll that in future posts.

Peace and stay tuned!

Friday, April 26, 2013

Exporting Salesforce Data

A quick tip for myself (as I might need it in the future) and for others searching how to export Salesforce data on a daily basis (example to feed a data mart)...just read on.

The best way is to use SOQL, a SQL like language adopted for Salesforce to query its objects. An even better way is to use a wrapper which combines the power of a scripting language like Python with SOQL.

In fact I use SQLForceForJython, an opensource tool which you can easily integrate with a Jython script. The process is very simple:

  1. Download the most recent distribution.
  2. Add sqlforce.jar to your java CLASSPATH
  3. Import the SQLForce module

The following is a script I use to export the Contact table (to be precise, we refer to it as Object in Salesforce):

import SQLForce

session = SQLForce.Session()
session.connect( "PRODUCTION", "", "password", "security-token" )

for rec in session.selectRecords("SELECT LastName, FirstName, MailingCountry FROM Contact"):
   print rec.LastName, rec.FirstName, rec.MailingCountry

You can filter by the date column and query by a timestamp as you would normally do in SQL to simulate a daily export...

Tuesday, April 23, 2013

Generate Google API Refresh and Access Tokens in PHP

I don't want to sound like Captain Obvious but here's the thing: Google API is great and flexible but the lack of documentation from Google makes me think that these guys assume that everyone out there is as smart as them. This is what happened the first time I put my hands on trying out to use OAuth2 for Google API to generate a refresh token for offline access. Luckily for you, humble reader of my humble blog, I am sharing my wisdom with you.

But first, a bit of theory. You are able to access private user information through Google API by means of an access token which expires after 3600s. So what happens when you want to retrieve information offline like what most web applications do? Here comes the refresh token. This guy does not expire and you are able to generate a new access token using REST.

All you need to make this happening is to download my script and put it in your PHP project root. If you are using a laptop on localhost, just point your browser to http://localhost/get_google_tokens.php. Authorize the app to access your personal data, and a token will be generated for you and printed on the screen. But before doing all that, read the section below as you need to modify the script to enter your own credentials.

How does the script work? First of all you need to go to Google API Console and create a new project. Let's say that you are interested in accessing the Google Drive API - from the services menu just enable Drive. Last but not least, go to API Access and create a client ID (Web application or desktop application, does not really matter). Copy the client ID and secret and paste them in the script. Some notes for the geek who want to understand more:

$access_type = "offline";

This line tells Google API that we want it to generate a refresh token.

$approval_prompt = "force";

This line asks Google to prompt us again for approval even if we have previously approved it. I use this in testing scenarios when I have run the script a number of times and end up with a blank refresh token.

$scope = "";

This is an important line of code. This defines the access scope of the application. So if our application is only interested in accessing the Drive API, put that in the scope. If you want to access more APIs, place the URIs separated by a space. Check this site for a full list of API scopes.

Finally note that the response we get from Google is JSON encoded and therefore needs to be decoded to extract the refresh and access tokens.

Thursday, April 18, 2013

View Oracle sessions by CPU usage and status

As a database guy one of the most common questions I get asked is to display a list of Oracle processes sorted by CPU usage. This might not be entirely accurate but proves to be a bit useful when you do not have visibility of the operating system such as Amazon Oracle RDS. So whether looking which sessions consume the most CPU, or to get a list of all Oracle processes sorted by CPU usage, displaying machine they are coming from, their waiting time and whether they are active or not, run the following query:

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage", seconds_in_wait, machine, status
FROM v$session se, v$sesstat ss, v$statname st 
WHERE ss.statistic# = st.statistic# 
   AND name LIKE  '%CPU used by this session%' 
   AND se.sid = ss.SID  
   AND se.username IS NOT NULL 
  ORDER BY value DESC; 

And the result would be something like this:

Username SID  CPU Wait(s) Machine      Status
james    2841 81  937     AMAZONA-ABC2 INACTIVE
james    1436 68  78      machine1     INACTIVE
james    49   61  100     machine2     INACTIVE
james    47   45  3297    machine1     INACTIVE
james    48   36  4669    machine3     INACTIVE

Now you would ask me what sort of CPU gives you more than 100% total CPU Usage? A multicore one of course..

Monday, April 15, 2013

Sanitize Ruby strings for safe use in shells

This is one of those awkward titled articles, unless you purposely searched it on Google. As many of Ruby developers know, you can use any of the following methods to call shell scripts from Ruby:

1. system()
2. backticks `` or %x shortcut
3. Kernel.exec()

The problem comes when you are aware of information security abuses, such as shell injection which might exploit unsanitized input. Let me portray this with an example:

1.9.3-p392 :001 > system ("ls")
file1 file2 file3
 => true 

Let's try shell injection:

1.9.3-p392 :002 > system ("ls; rm *")
file1 file2 file3
 => true 
1.9.3-p392 :003 > system ("ls")
 => true 

Ouch! Imagine what would have happened with user input. This is dangerous indeed and wise Rubyists often don't recommend you using system() commands for user input facing situations. If you still can't live without it, Ruby has a library called Shellwords, and you can use it as follows:

1.9.3-p392 :004 > require 'shellwords'
 => true 
1.9.3-p392 :005 > a1 = %w{ls -l}
 => ["ls", "-l"] 
1.9.3-p392 :006 > system(a1.shelljoin)
total 0
-rw-r--r--  1 jamesattard  wheel  0 15 Apr 14:47 file1
-rw-r--r--  1 jamesattard  wheel  0 15 Apr 14:47 file2
-rw-r--r--  1 jamesattard  wheel  0 15 Apr 14:47 file3
 => true  

Now let's try to drop a bomb:

1.9.3-p392 :007 > a2 = %w{ls -l; rm *}
 => ["ls", "-l;", "rm", "*"] 
1.9.3-p392 :008 > system(a2.shelljoin)
ls: illegal option -- ;
usage: ls [-ABCFGHLOPRSTUWabcdefghiklmnopqrstuwx1] [file ...]
 => false 
1.9.3-p392 :009 > system("ls")
file1 file2 file3
 => true 

As you can see, Shellwords sanitized the string to make it safe for use in a shell.