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

fieldname1	fieldvalue1	fieldname2	fieldvalue2	fieldname3	fieldvalue3	fieldname4	fieldvalue4	fieldname5	fieldvalue5
"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!

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.