Wednesday, 25 July 2012

How to unserialize data using mysql without using php

I'm working on a system that has data in a column serialized with php - not my design I should add...

Rather than pulling the data and unserializing each record in php, I wanted to do this in mysql. It seems to be a common problem for developers.

After hunting through all the mysql functions I found SUBSTRING_INDEX.

For example if you have a table like this (data has been modified to protect the innocent!)

old_data
a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:5;s:14:"broughtforward";i:3;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}
a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:8;s:14:"broughtforward";i:5;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}
a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:10;s:14:"broughtforward";i:8;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}
a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:11;s:14:"broughtforward";i:10;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}
a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:13;s:14:"broughtforward";i:11;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}
a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:14;s:14:"broughtforward";i:13;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";}
a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:101;s:14:"broughtforward";i:100;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";}
a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:102;s:14:"broughtforward";i:101;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";}
a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:103;s:14:"broughtforward";i:102;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";}
a:5:{s:9:"invoiceid";s:3:"492";s:8:"balance";i:3;s:14:"broughtforward";i:2;s:6:"userid";s:5:"13908";s:10:"customerid";s:3:"179";}


Use this query, the a:5 above tells me that there are 5 columns. It does depend on the developer knowing how many columns are in the serialized data and the structure being the same. But you get the idea.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM audit
WHERE object_type=42


Which gives me this




fieldname1fieldvalue1fieldname2fieldvalue2fieldname3fieldvalue3fieldname4fieldvalue4fieldname5fieldvalue5
"invoiceid""8""balance"5"broughtforward"3"userid""13908""customerid""3"
"invoiceid""8""balance"8"broughtforward"5"userid""13908""customerid""3"
"invoiceid""8""balance"10"broughtforward"8"userid""13908""customerid""3"
"invoiceid""8""balance"11"broughtforward"10"userid""13908""customerid""3"
"invoiceid""8""balance"13"broughtforward"11"userid""13908""customerid""3"
"invoiceid""8""balance"14"broughtforward"13"userid""13908""customerid""3"
"invoiceid""387""balance"101"broughtforward"100"userid""13908""customerid""4"
"invoiceid""387""balance"102"broughtforward"101"userid""13908""customerid""4"
"invoiceid""387""balance"103"broughtforward"102"userid""13908""customerid""4"
"invoiceid""492""balance"3"broughtforward"2"userid""13908""customerid""179"

Then all I need to do is use some column aliases


SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS invoiceid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS balance,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS broughtforward,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS userid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS customerid
FROM audit
WHERE object_type=42


Simples!

Thursday, 5 July 2012

Reset windows password

Had a friend who forgot her Windows 7 password....

Straight away I tried ophcrack - installed to a usb stick via unetbootin.

Unfortutately it wasn't able to crack the password...

So I tried pogostick which will remove the password. Just download the lastest usb??????.zip and extract to a usb stick. This worked! But there is a caveat, it will lose any encrypted files.

Note: to use the usb stick, switch off your windows computer, insert the usb stick, then switch it on and follow the on screen instructions. If you get windows then you will need to change the boot order in your bios/cmos set up. To set up this up, you will need to press a key immediately after switching on - they key varies between manufacturers, usually it is F2.

Saturday, 16 June 2012

Open source languages - job trends

I was chatting to a colleague yesterday about switching to open source. I don't think he took much convincing anyway, but these job trends speak for themselves...






Tuesday, 12 June 2012

Easy peasy way to convert Microsoft DOCX to PDF in Linux

I use Linux, so have no need to use Microsoft Office - I either use Google Docs or OpenOffice.

But I often receive Microsoft documents from clients and colleagues.

Unfortunately, I've found that if I open a Microsoft Word (docx) file in OpenOffice, the formatting is wonky (technical term) - Often the images are excluded altogether...

Google Docs doesn't always cope with the formatting either...

So the easy peasy way is to sign into https://skydrive.live.com with a hotmail/live/msn account.

Upload and open the docx file, then go to the file->print option in SkyDrive (not the browser print option)


If you are using Chrome or Chromium, it will then bring up the printer options - there is an option to save as pdf


et viola!! I now have a readable version.

Friday, 1 June 2012

ssh via Nautilus

D'oh!!

Just discovered I can use ssh with Nautilus...

Select Go -> Location from the menu (or press ctrl+L)

Then type ssh://username@website.com

Easy peasy...

Wednesday, 2 May 2012

How to install Oracle JRE 6 in Ubuntu

There's a couple of quirks in Netbeans which seems to be a problem with OpenJDK.

Most irritating is being unable to copy and paste to/from a browser.

Installing Oracle Java 6 seems to solve the quirks

First run this which will create a package on your computer
https://github.com/flexiondotorg/oab-java6#how-do-i-download-and-run-this-thing

Then install the package
https://github.com/flexiondotorg/oab-java6#what-gets-installed

Then edit  netbeans.conf  to use the Oracle Java 6

gksudo gedit ~/netbeans-7.1.1/etc/netbeans.conf 

Change to

netbeans_jdkhome="/usr/lib/jvm/java-6-sun/jre"

And restart Netbeans