Anything can go here, in any language... except my native language Sinhala. Be cool... anybody is warmly welcomed! :)

Data Migration – From Access 2007 to MySQL

Recently, I was assigned for a project that required one-way synchronization of data, from MS Access to MySQL. This was required to be fully automated to make the task executed once a day. It has already been implemented by someone else before, using PHP as the programming language, and SOAP protocol as the underlying technology. The implementation had several issues, so I was asked to do it in a different approach.

So, roughly, my plan was to dump the Access database into text, and then upload the dump file to the server, and then import to MySQL. So how to do that?

Microsoft Office Access 2007 provides it's own methods to export table content as CSV, and it's really nice that at the MySQL's end the CSV files can be directly read into the MySQL database. Roughly, my approach looked like the following,

Synchronize necessary tables into MySQL
  • Using VBA code, export necessary tables into CSV
  • Compress and upload the CSV files, preferably in a single file
  • At the MySQL's end, import the data upon the upload completes
Make the whole task fully automated
  • Using a MS Access 2007 macro, trigger the VBA code
  • Set up Windows task scheduler
Now, let us explore step-by-step... :-)


Synchronization of Necessary Tables into MySQL

I remember that I was doing good with Microsoft Visual Basic 6.0 six-seven years before. Almost forgotten some, I started working with VBA. Basically it's the TransferText method provided by the DoCmd object in MS Access. In brief, following is the code.

I initially wrote this part of code as a Sub in VB, but later I understood that, to be automated with a macro, I have to make this a Function. (Sub does not return a value but a Function does)

Function ExportData ()
 Dim tables (1 To 3) As String
 Dim table As String
 Dim exportpath As String

 exportpath = "C:\temp\"

 table (1) = "tblStudents"
 table (2) = "tblMarks"
 table (3) = "tblSubjects"

 For Each table In Tables
  DoCmd.TransferText acExportDelim, , table, exportpath + table + ".csv"
 Next table

 ExportData = True ' Return True to indicate (in case if necessary) all done without breaking in the middle
End Function

In my work I had to export several tables. However it's that simple. Now, the next task is to upload the exported CSV to the server, through FTP. As far as I know, VBA does not provide any native/in-built methods to handle FTP. The solution is accessing Windows API. I have programmed with Windows API for classic Visual Basic before so I know how hard it can be without a proper documentation. I have found plenty of nice articles over the Internet on how to do it, but as I do not know how it would be working in later Windows releases such as Vista and Seven and how would Internet security software handle that, I decided to use the PHP command line instead of Visual Basic. By installing the XAMPP package in Windows, you can get PHP CLI work. So, the latter part of the above VBA code goes like this,

Dim cmdline
cmdline = "C:\xampp\php\php.exe c:\uploadcsv.php "
For Each table In Tables
 DoCmd.TransferText ()
 cmdline = cmdline + Chr$ (34) + exportpath + table + ".csv" + Chr$ (34) + " "
Next table

Shell cmdline, vbNormalFocus

As you can see, all the exported file paths are passed to a PHP script called uploadcsv.php. Work to be done with VBA code ends here. Now, the next task is to handle the file upload. PHP provides very easy and nice methods to do that.

First, let us examine the command line, the cmdline variable in our VBA code. It will look like the following. Note that Chr$ (34) returns a double quotation mark (ASCII 34).

C:\xampp\php\php.exe c:\uploadcsv.php "C:\temp\tblStudents.csv" "C:\temp\tblMarks .csv" "C:\temp\tblSubjects.csv"

So, the contents of uploadcsv.php goes here...
I'll explain PHP code wherever necessary, in PHP comments.

<?php
/* 
$argv handles command line input
The first element of the array is the php script filename itself.
So we'll remove it. 
*/
array_shift ($argv);

/* 
We're going to upload several tables in different files.
To upload three files we need three FTP connections.
Archiving makes the three one. And finally we need one FTP connection.
So it reduces the time spent for the work to be done.
For archiving I used the Archive Tar library
which is available at http://pear.php.net/package/Archive_Tar/ 
*/
require_once ("Tar.php");
$tar = "exportcsv.tar";
$tarfile = new Archive_Tar ($tar);
$tarfile->create ($argv);
unset ($tarfile);

/*
Now we can delete the temporary CSV files
*/
foreach ($argv as $file)
{
 unlink ($file);
}

/*
Archiving isn't just enough when file size becomes quite large to upload.
We have several compression methods available
that can drastically reduce the file size.
PHP provides methods to compress data into bzip2 format,
where very high compression ratios are achievable.

When you compress a set of files into one in your computer,
it automatically performs the two steps of archiving and
compressing as a single step.

In my work, archive was 19.xx MB and after compression
it took just 3.xx MB
*/
$finale = $tar . ".bz2";
$bz2 = fopen ($finale, "w");
$bz2data = 

unlink ($tar);

$upload_md5 = md5_file ($finale);
/*
As we are uploading this file,
we need to verify it's integrity at the 
remote end before processing. I used MD5 to
verify the uploaded file's integrity.
Please read http://en.wikipedia.org/wiki/MD5 for more information.
*/

/*
Our data is ready now.
Next is to upload it using FTP
*/
$remote_path = "/home/example/ftp/" . $finale;
/*this is the remote directory where we're going to upload the file.
For security reasons this should be outside of the website's document root (/home/user/public_html) */
$ftp_host = "example.com";
$ftp_user = "example";
$ftp_pass = "p4ssw0r!";

$conn = ftp_connect ($ftp_host,);
ftp_login ($conn, $ftp_user, $ftp_pass);
ftp_pasv ($conn, true);

if (!ftp_put ($conn, $remote_path, $finale, FTP_BINARY))
{
 die ("Upload failed!"); // Abort if upload failed.
}

ftp_close ($conn);

/*
After the upload has finished, we need to tell
the website that the uploaded file is ready to be 
proceed.

We can do this with a HTTP GET but some part of the actual system inspired me to use SOAP.
For simplicity of this article I may use a HTTP request here.

*/

$final_result = file_get_contents ("http://www.example.com/importer.php?file=" . $finale . "&checksum=" . $upload_md5);
/* where www.example.com is the remote location where we keep the script to import data from CSV files */

if ($final_result == "Success!")
{
 echo "One way sync successful!";
}
else
{
 echo "Synchronization failed!\n";
 echo $final_result;
}

Alright. Finally, one more step ahead... data is ready to be imported to MySQL.
It's that importer.php that we called in the earlier script. We need to keep this file inside the document root of the website so it can be called over HTTP.
I'll put the code here, explaining each piece, in the same way as the above.

<?php

require_once ('Tar.php');
 
/*
MySQL Database configuration
*/
$db_server = 'localhost';
$db_user  = 'example';
$db_pass  = 'p4ssw0r!';
$db     = 'example_com';

/*
Directory path (on the remote server) where the uploaded file is kept
*/
$basedir = "/home/example/ftp/";

/*
Connect to the MySQL database
*/
$conn = mysql_connect ($db_server, $db_user, $db_pass);
if (!$conn) { die ("MQSQL database connection failed! Can't continue."); }
mysql_select_db ($db, $conn);


/*
Validate the upl;oaded file against the MD5 checksum
*/
$filename = trim ($_GET['file']);
$checksum = trim ($_GET['checksum']);

$md5 = md5_file ($basedir . $filename);
if ($md5 != $checksum)
{
 die ("File checksum mismatch. Please try again!");
}


/*
Decompress the uploaded file and extract the archive.
Whenever a file becomes no longer necessary,
it is deleted using unlink ()
*/
$arch = $basedir . substr ($filename, 0, -4);
$bz2data = implode ("", file ($basedir . $filename));
$data = bzdecompress ($bz2data, true);
$fp = fopen ($arch, "w");
fwrite ($fp, $data);
fclose ($fp);
unlink ($basedir . $filename);

$tar = new Archive_Tar ($arch);
$tar->extract ($basedir);
unlink ($arch);


/*
Now we have CSV files back, in the server space.
Next task is to find them, and import to MySQL
using a LOAD DATA LOCAL query.

When a particular directory is scanned with scandir () for files,
it returns an array containing the current directory and one level
upper directory as the first two elements. We have to
skip these two before using the return value.
*/
$fl = scandir ($basedir);
array_shift ($fl);
array_shift ($fl); 

/*
A simple loop is used to import each table.
Here each filename (without extension) is equvalant to the
table name in MySQL. That means, the table names are identicle
to the table names in the original Access database.
Otherwise we need to do a small workaround to fix any mismatch.
*/
foreach ($fl as $file)
{
 if ($file != $filename) {
  $src = realpath ($basedir . $file);

  /*
  Strip '.csv' from the filename to get the exact table name
  */
  $tblname = str_replace (".csv", "", $tblname);
  $tblname = strtolower ($tblname);

  /*
  Empty the table before importing
  to prevent duplicate records.
  */
  $q = "TRUNCATE TABLE $tblname";
  mysql_query ($q);
  /*
  This query handles importing CSV to MySQL directly.
  If this functionality was not there with MySQL
  then it would be a certain workaround to import the tables.
  */
  $q = "LOAD DATA LOCAL INFILE '$src' REPLACE INTO TABLE $tblname FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'";
  $ret = mysql_query ($q);  
  unlink ($src);

  if ($ret != true)
  {
   echo "MySQL query failed!";
  }
 }
}

/*
Everything is complete.
Close the MySQL database connection
*/
mysql_close ($conn);

Finished! Now, additionally we can automate the whole task to make sure the one way synchronization happens time to time.


Making the whole task fully automated

This actually doesn't need anything to do with coding. We need to set up one macro on Access database, and a scheduled task on Windows.

Although I have prepared a clean user manual for our client, I do not wish to do it here in detail here as I am writing this note to technical people. The macro on Access database should go like this,


ActionArgumentsComment
RunCodeExportData ()-
QuitPrompt-

Give the macro a meaningful name. Here I use Sync.

What we're going to do is, periodically open the database file with Microsoft Access, and then automatically execute the macro so it can trigger the function inside the VBA module. But, by default Microsoft Office blocks macros.

We need to prevent Microsoft Access from blocking the macro. Unless we can't automate the task. To do this, create a trusted location and place the database file there.

Finally, we need to set up a scheduled task on Windows, and the program/ command line should go like this,

msaccess.exe "C:\Database.accdb" /ro /nostartup /x Sync


This is my approach. Now, the leftover work is to apply security if necessary, and then sit back, relax, and watch it go! :-)



Security:
  • This implementation is not secure if tables carry sensitive data. Someone can sniff the data in the middle and study the pattern how it works. For additional security we can use SFTP instead of FTP upload. To do this, we can use SSH2 functions for PHP.
  • Also, when calling the 'importer' script we can use SSL encryption. Also it is possible to attach this functionality to the index.php file of the website.

Gotchas:
  • If the target MySQL tables have DATE fields, this will import the dates incorrectly as MySQL expects the date exactly in four digit year-two digit month- two digit date  (ex. "2010-12-25") format.
  • I haven't tested how this would handle primary keys and relationships in the target database. In my case the data in MySQL database was for just displaying only. So it didn't have any keys.

References:

---

This was my approach. If you have a different approach, or any comments, below you have space to write. Comments and expert advice are warmly welcome. :-)

Thanks for reading!



PS:
This wouldn't be a much workaround if there were timestamps in the source tables. But the business client was not willing to change his existing database structure. But, this is what I had and how I solved it. They ask and we got to shut up and do.

Jaffna Tour (10/09/2010 to 12/09/2010)

If you've read my last post you'll find that I had a trip to Jaffna. Yes, it was last month, but I had nothing to do other than waiting for my internet connection to be restored. Sad. :( Early in the last month, our ADSL router seemed not working properly. So we handed it over to claim warranty. This caused me miss some important events including few career opportunities. I tried to use my mobile phone to connect, but it also failed. Although Airtel provides a very attractive and pleasing 'Youth Pack', no chance for us to use at least 100 MB of the 400 MB data bundle -- no 3G connectivity. I live in Ambalangoda, a well known coastal urban area in Sri Lanka, and I don't know why would such a big telecommunication service provider fail to offer a satisfactory service.

However, things are now OK as we got the router back yesterday evening. :)

---

Okey,... let's get back to the topic -- Jaffna. I had a chance to join a group of devotees who were on their way to Nainativu. For a person living in the Southern Province it's a fairly cheap travel to Jaffna. Since I'm a novice photographer I wanted to make this a chance to enjoy the art of photography also. So,... my K770i was back in action, althogh it has begun malfunctioning a bit. Here's the journey... (please click on each image to enlarge)


Turbine of Iranamadu power plant which was built by the LTTE.



This little guy seemed not ready to leave his pals at the camp.


Kilinochchi water tower which was destroyed by the LTTE.


A panorama of A9 highway at Elephant Pass. Camera is aimed along the way South.


Monument for the legendary hero of Hasalaka, Corporal Gamini Kularatne. [read story]


A pet aligator kept at a military camp, somewhere I don't exactly remember.


Nallur Kovil, Jaffna


Interior frescoes at Naga Vihara, Jaffna.


The narrow road from the peninsula to an island.
If my memory is correct this is the way to Mandativu, as we were heading to Nainativu.


Prawn traps set in the lagoon


Way to the boatyard. Boats are used as the transportation means to Nainativu (Naga Deepa).


The lord settling up the dispute between Chulodara and Mahodara [read story]


Cattles!
(I took this one sitting on the foot-board of the bus,
and I was not able to properly aim the camera with just one hand.
Also the bus was moving at a speed)


Lieutenant General Denzil Kobbekaduwa monument [read story]


Thewarikkulam tank - uncommon, but it was somewhere near the rest house.


Remainders of a/the railroad?


Dagabas at Kadurugoda Ancient Vihara - a landmark that you should visit!


Bhikkhuni (nun) Sangamitta statue


Statues at Naguleswaram Kovil


Cultivation in Jaffna


Nilavarai water well - a natural well believed to have no bottom
This is the last landmark we could vi
sit during the tour


The danger zone
Landmines are not yet 100% cleared in Muhamalai.
As you can see in the photo, only the narrow area surrounded by the ribbon is safe.
This is almost aside along the A9 highway.


The Monument of Victory!


Dignity of the LIONS... towards the infinity...!


I have many more. Not all are worthy shots, but I've kept them because I believe that in any particular graphic any viewer other than myself may see something that I may never see. They're available to download.


---

Since sunlight is too heavy in the North province, lighting quality of raw photographs were poor. Even automatic white balance settings in my camera didn't work. So I had to do some post-fixes such as curves and levels, with the use of GIMP 2.6 software. This is something usually I don't like to do becuse I believe that the photographer must retain the original lighting conditions whenever possible; simply because photography is the art of capturing light.

Overall I'm not satisfied with my photography experience during this tour. I even missed several 24k shots. If you're a reader of my Sinhala blog, you could know that I had a wondeful experience in our tour last year. This time it was with a group of devotees and the tour was to worship Nainativu and some other religious activities. No no time to wait and capture light... what do do? :(

Okey... thanks for reading! Have a great time!!


PS:
If you use Airtel be sure to keep your phone in 'Flight mode' to save battery. Your Airtel SIM will act as a world class dumb SIM, as it is required for nothing but keeping few other phone functionalities other than communication. X(  

K770i, Photography and Sorting Problem


Hi fellas, after few days... I'm back again with some cool cool stuff. My internship is over... and now I look for new career opportunities. Anyhow, I was not so quick to apply for a new job like others did. After completing my internship, I wanted a 'free time' of just one or two weeks to have a tour around Sri Lanka.

So, I just had the tour. Amazingly, it was to Jaffna. I live in  Southern Province and going for a tour in Jaffna is just a daydream for a busy man. However, in any tour I have two main intentions, photography and flirting :P . (however I was not able to photograph her since her mom was there :-O )

I have been using a Sony Ericsson K770i phone for more than one year and that phone has made me a photography freak. But recently, it had encountered a problem with it's memory stick reader compartment. This really made me upset as my phone now can't read memory sticks. However, by deleting some wallpapers, ringtones, themes and midlets I managed to make some free space of 15 MB in the phone's internal memory. This is just enough for 15 photos at maximum possible quality. Sad! :(

Luckily I found another phone (a Chinese phone without a brand) which had a memory stick installed and bluetooth also. So my plan was to transfer photos into the Chinese phone over bluetooth after taking each 15 photographs, and delete originals to make free space on my phone for new photos... just like using a revolver ;) . It worked, but since bluetooth file transfer took longer than expected each time I missed few imoprtant shots as well. :(


Sony Cyber-shot cameras use a specific file naming convention. It's the DSC (Digital Still Camera) prefix before the five-digit file number (i.e. DSC00001.JPG, DSC00002.JPG, ...). Due to my 'revolver methodology' sometimes when I make free space my phone seemed to reset the file number. This resulted duplicate filenames, and upon transfer those duplicates were added a Dup(xx) prefix in their filenames. However, thanks to the Chinese phone, during the three days of tour I have been able to take nearly 300 photos, including few panoramas.

After coming home I wanted to arrange all the photos in the correct timeline. Since file naming has already been messed up it was no use arranging them by name. So the next chance is to arrange them by the modification time (unix mtime). Due to some unknown reason, there were some small misconjusnctions in the series when arranging by mtime. It was like Elephant Pass coming before Kilinochchi when heading to Jaffna. :P

So here comes the sorting problem... it's not the Sorting Problem that we learn at the computer science lecture, although we use the tools built upon those theories. I realized that playing with ctime, mtime and atime is just a waste of time... so I was looking for another solution. Yes!, there is. It's the Exif data stored with each file. I can extract the DateTimeOriginal tag from Exif and arrange the file accordingly. I am a Linux geek, so rather than doing a web search for an automated GUI tool  I wanted to do it by myself.

So my ultimate plan was to write a bash script that will check date-time from each file and rename them in the correct order. Also, I wanted them to follow the same DSC convention. There's a handy command line tool for reading image exif on Linux. It's exifprobe. Just an apt-get install is enough to get it installed on the computer. With exifprobe we have another handy tool called exifgrep. Now let us go ahead.

Here we extract the origianal date-time:
$ exifgrep -n DateTimeOriginal DSC00001.JPG

and the output will look like,
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:10 12:04:16'    # DSC00001.JPG:

What I want to do is to list each file against it's OriginalDateTime and then sort with Unix sort command. So it's just one more simple step, (I wanted the output in a text file too)

$ exifgrep -n DateTimeOriginal *.JPG | sort > sorted.txt

Here's a random portion of the output I got,

JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 12:18:54'    # Dup(01)DSC00046.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 12:20:39'    # DSC00048.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 12:23:13'    # Dup(01)DSC00052.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 13:31:30'    # DSC00001.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 13:31:49'    # DSC00002.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 13:32:38'    # DSC00003.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 13:34:07'    # Dup(01)DSC00004.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 13:48:40'    # DSC00007.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 14:01:13'    # Dup(02)DSC00008.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 14:24:49'    # Dup(01)DSC00014.JPG:
JPEG.APP1.Ifd0.Exif.DateTimeOriginal = '2010:09:11 14:25:43'    # Dup(02)DSC00015.JPG:

Now, it's the time to write the actual shell script. It's not a big deal actually, as you can see, so I don't need to explain.
#!/bin/bash

mkdir sorted
lines=`cat sorted.txt | wc -l`
for (( line=1; line<=$lines; line++ ))
do
  src=`cat sorted.txt | head -$line | tail -1 | awk '{ print $6 }' | cut -d: -f1`
  dest="DSC"`printf "%05d\n" $line`".JPG"
  cp $src sorted/$dest -v
done

Finally, after running this script I managed to get all photographs according to the timeline. I'll be writing a note about my Jaffna tour as soon as possible, and will be releasing some photographs under the Creative Commons Attribution-Share Alike 3.0 Unported license.

Few of them (such as File:JaffnaPeninsula.JPG) are already available on Wikipedia! :-)

See also:
Thanks for reading... have a nice day! :) :) :)

After BSOD -- Windows Se7en

As most of my readers know my day-to-day desktop OS is Ubuntu (Linux). However I still use Windows (on VirtualBox) in certain cases, usually software testing. These days I'm involved in some work that involve 'eavesdropping' on COM port for nothing but පිටිපස්සේ අමාරුව. :P

When I switch from seamless mode to windowed mode, the machine held itself. On the top there was a greenish stripe with some random dots, and mouse pointer animation has stopped. However I could move the mouse over -- of course I should be able to do that since it supports mouse pointer integration.

I waited about 5 mins for the VM to respond; and there was no response and I rebooted. After reboot I got to know that it was a BSOD :P . I never knew before -- so no screenshot.


So that's how BSOD in Windows Se7en on VirtualBox! (It's not really blue :P )

Be Aware of Social Engineering | Know Your Weaknesses


Email account hacked? Somebody has accessed your personal email?? If you have experienced this before, surely this blog post will be useful. Today I'm writing this note for those who do not have much experience with the Internet and WWW.

First of all, we'll follow up a small hypothetical case. Suppose I am a novice computer user; like one of the most of our community. I have a Facebook account. One day, a nice lady appears in... violah! She wants to be my friend!! Of course I don't know her... but who cares? She's at my doorstep, knocking my door. Accepted! (And she's here for a Relationship, Dating,.... blah blah)

After some time... it looks like something has gone wrong... I can't login to my Facebook account!!! Oh Jeasus..! Some weird status updates on my wall.... :( What on Earth is happening?

May be resetting my Facebook password may work. So I'm trying the “Forgot password” link. OMG!!! I can't access my email account...!! It's HACKED!!! O_o

(phone rings)

Hello, is this J?

“Yes, Speaking...

Idiot! What's the meaning of that $#$%# email you have sent to me????

Hey I'm sorry; I'm really sorry... my email account was hacked by someone. I didn't send that by myself... somebody has taken over.... believe me,.. sorry..........!!!!

(conversation continues, and so and so)



Fine. The story is enough for us. Let us see what has really happened. The nice lady is actually an online predator. In reality, 'she' might potentially be 'he'. Remember the second training that Morpheus gives to Neo in the movie Matrix? Yes, the lady in red dress!

The very first advice that I might give you is, do not accept friend requests from unknown people on whatever social networking website you are using. It's always better to limit your connections to those who you know in reality. If the lady is too cute to be denied, you can just ask somebody and find out who she really is.

Then, how was (s)he able to hijack your all the accounts? I'm making one assumption here, that the victim in the above example is bit lazy in remembering passwords. So he uses his birthday as the email password!

(S)he just looks at your Facebook profile info, and then finds the victim's email and birthday on it. Suppose it's 06 July 1988. The predator might try 880706 on his/her first attempt. May be (s)he will fail. There is a second attempt... and of course subsequent attempts. So (s)he may re-attempt with,
060788
070688
07061988
19880706


… and so on...

If the victim has set one of those as the email password, and if our 'nice lady' has been able to match it, accidentally or somehow... what will happen?

You might have used your email account to create accounts/ profiles on various web-based services such as Facebook and Twitter. Almost all of them have the 'password reset' (or 'forgot password') feature, directly associated with your email accont. This means your email account is the one that you should keep eye on most. It's like the queen bee in a population. Once somebody has access, they can do almost anything.

So, now in our case, not only the email but also,
  • (S)he can overtake victim's Facebook account
  • (S)he can overtake victim's eBay Account
  • (S)he can overtake victim's Paypal Account
Panic!!

Then, my next point goes like this,...
Never use your sensitive personal information to fomulate passwords. May be your birthday, name of the spouce, phone number, national ID card/ social security number – avoid useing them in passwords.

Those who know your personal information can GUESS your password. And that's what we call “Social Engineering”!

A good password should consist of capital letters, simple letters, numbers, and punctuation. Also, it should not be less than 8 characters. Preferred length for a stong password is 14 characters and as mentioned above.

Finally, see it... You do “Social Networking”; and they do “Social Engineering”... Be aware..!



The above case was not something that I have experienced in my real life, but I can show you dozens of people who have had this real world nightmare.

So, thanks for reading... take your time and think... it's about your privacy. Ciao.........!!!!!! :-)

Join Asia Pacific Telecenter Network!

The Asia Pacific Telecentre Network web portal just got a new domain name, www.aptn.asia . Now you can find your regional telecenter network by entering easy URL, www.aptn.asia .


Join today..! Discus, blog, share your ideas...

Is it a legit Facebook app?

Hi folks, just a small story with four screenshots...

If you use Facebook, you might have got game/application requests from  your 'friends'. But, did you know that some of those requests are actually not made by your friend? He/she might even doesn't know. They just add the application, and then the application automatically sends requests to each friend. And you think that they might get upset, and Allow the app.

In some cases, the application can even steal our privacy. So who do we get aware? Just go through the following screenshots:
(please click on each screenshot if you can't read them properly)


Note the fake application icon, and five-star rating.
Click on the application's name (that I've highlighted) to learn more about the application...

Look carefully, the app holds the Facebook logo as it's logo, but, this is NOT developed by Facebook! (see the left side pane)

Go to the Reviews tab and see the comments from the community. The TRUTH!!!

And finally, you might want to Block the application, so you won't get requests anymore... :-)




After Facebook has introduced their new privacy model, things have become ever worst. They have made some limits on capability of blocking stuff, and eventually we get addicted. Facebook is marketing our privacy. All they want us to spend more on Facebook. We fools trap in their strategies - they make profit - and we make loss to our boss.


So think wisely. USE FACEBOOK, BUT DON'T LET FACEBOOK TO USE YOU!

And finally I must say,.... I willingly misspell Facebook founder's name,... SUCKERberg!!! :-)

Tangalle, Sri Lanka – 16/Feb/2010

Hi folks, since I wrote my last post on a malware attack that I had to defend, still didn't find any interesting technological stuff to work on. So no blogging. :( Also, I was so busy with examination at UCSC,... ohh I hate cramming and re-writing stuff! :-S

Anyway, there's a good news too. Our internships begin this semester. I have been able to pass my very first job interview and now I'm about to go for my very first job! :) It's gonna start on 2nd of March and I wonder whether time would permit me for more blogging since the start. :|

By the time, on 16th, I had a visit to a one of my relatives living in Tangalle. We spent most of that day at beach, and I have been able to take some remarkable captures with my K770i. Photography has become one of my hobbies since the day I bought my phone. ;) Hell yeah, it's an amazing beach in Sri Lanka.

So I thought of sharing my captres with the World. All photos are available under the Creative Commons Attribute-Share Alike 3.0 Unported license. Some will appear in this post, and everything is available for download.

---
Please click on the images to see full resolution.

sea shells

clear seawater

an urchin - not so away from the shore


sunlight, urchin, and the rock - i put one on the rock and took this..

a common bivalve mollusc often served as food in sri lanka
however i don't know a proper name, just know the taste :P may be it's blue mussels?

coast - you can see fishermen's boats

fishermen launching the boat that carries large seine for fishing

---

Had a nice and remarkable day,... and here's the download link for the entire collection (63 photos). And... one more thing to promote my beloved open source software... photo editing was done using GIMP on Ubuntu. :)


Thanks for reading!

Attack and the Defence




Hi dear readers! First of all, I WISH YOU A HAPPY AND PROSPEROUS NEW YEAR WITH LOT OF ACHIEVEMENTS, GLORY AND JOYNESS!! Anyway, 2012 is also approaching.. :D (just kidding)

After a long long time, I've got an interesting problem to solve. I'm not an expert. I'm just writing my own way that I followed in the situation.

This happened during the Christmas days in 2009, and after all, I feel it like a Christmas gift, seriously! :-) I wrote two blog posts in my native language, I you can read, just visit the following links. You'll find it more interesting than this one if you can read. :)
  1. http://blog.shaakunthala.com/2009/12/hacker.html
  2. http://blog.shaakunthala.com/2009/12/bash.html
Alright, then... I'm responsible for the administration of several websites. As I feel, an administrator's job is very much similar to the job of a sea captain. He has to look after the system, like his own... be vigilant of the attacks and other problems,.. and many more work.

Recently, I've been notified that one of my sites is down. An empty page with an error message is displayed when the site is visited, and according to that error message, there's an error on index.php, line 38 and the character < is the cause. This is the way how PHP shows error messages. As my immediate actions, I logged on to the FTP server where our website is hosted and opened the index.php

The website was developed using a CMS. The code looked some kind of strange for me because it had no corresponding ?> tag for its beginning <php tag. An unknown HTML/ Javascript code snippet has appended at the end of the file.

Yes, that's the cause of that error. Somebody has injected a malicious code snippet at the end of the index document, and the PHP engine on the server side has tried to interpret it as PHP. As this has caused a syntax error in PHP, the whole site has gone down as the final result.

Here's the structure of index.php :

<?php
/* PHP
codings of
CMS */

<script> // The foreign Javascript code snippet </script>

What I did is, just copy-pasted the code into a separate text file (for analyzing), and cleaned index.php. Then everything looked normal, but sooner I got to know, actually it is not.

I've never experienced such a situation before. I didn't know where to start, and what to do. But I wanted to find out what the code says. It was some kind of scary and big JavaScript code in a single line. However, it's not so scary!


Okey,... a closer look...


Right... and this is our troublemaker...


Just see carefully,.. you don't need to be a JavaScript guru. :-)


It's not a big deal to identify such big codes. Vigilance is what matters here. They have used the replace () method in JavaScript. See... strategically hiding text by just randomly mixing punctuation, retrieving the original text at run time. Wow!

Finally, it's this:


Looks like it has been created for phishing purposes... but I'm not sure exactly. However, this URL points to an empty page. What I expected was a JavaScript code, but this resulted nothing... I don't know a reason. :-/

Within few hours after fixing the issue, I got to know that our website is down again. The same thing has happened, same style, but the malicious code resulted a different URL. I fixed it again, and started thinking... what on earth could be happened here? :-O

Whoever the attacker has done is injecting some malicious code into the index document, and letting it execute at the client's (browser) end. However, as the code has blindly appended at the end of the file despite the structure of it, I came to a conclusion -- definitely this is done by a bot / script or some other automated mechanism.

So, I did the same operation as before for cure, and then tried to find some solution. Yes, it's gonna be a new experience. To prevent further attacks, I put the following line at the end of the index.php file. It prevented interpreting any code below the line. When I say die!, no further interpretation of code at all. Hence, the site is safe from being down, but the risk is still their till I find where the attack comes from and where the security hole is.

die ();

I tried to find any clue on site logs,.. but no luck. If this attack was carried out through HTTP, site logs (not the CMS logs) should indicate that. What I suspect is, somebody has gained access to the server, and executed a script. By adjusting file permissions on the index document, I found out that the malicious script on the server (or bot) has gained the root access.

Later, I got to know, this has recursed into directory hierarchy through the entire site. And also, I saw that some JavaScript files are also infected. It was shocking! Everything throughout the site can be potentially infected with malicious code and hence unsafe for visitors!! I didn't know how serious the attack was. I have never faced such a situation before, and as the responsible personnel, I have to fix this as soon as possible, with my best efforts.

According to all observations, my conclusion was, this is happened due to the fault of the web hosting provider. I know that CMS' sometimes can contain security holes, but if it was, there should be at least something on the site logs.

All of the above is the summary of my first blog post, mentioned at the top of this post. The next few paragraphs in this post explain how I performed the disinfection.

 ---

The only backup we had was bit old, so I forget the idea of restoring from a bacup archive. The challenge was to find out how serious the attack was, and to disinfect everything.

What I suspect so far:
Every JavaScript file and index document is infected -- but not sure about other text-based file formats.

So I have to check each file for malicious code, and then clean them.

First, I thought of writing a PHP script for the purpose. But, PHP is bit insecure with this work. I know, it's not a big deal to fix the security with PHP, but, I was more interested in bash scripting. As a daily Linux-only computer user, I am very familiar with bash, and feel more reliability with that.

Luckily, the web hosting service provider has offered remote access through ssh. Yes, that's great! I was very keen, the rest's gonna be a party!! ;)


Here's the match highlights... :P

Access through ssh, compress the entire site, and then download it. This is necessary because the safe way is to keep a backup + do a testing when doing something serious. One mistake, could ruin everything!!

Here we go, ssh
$ ssh user@mysite.com

Create an archive, (make it tar.bz2 for higher compression ratio -- easy to download). Then exit ssh.
$ tar cvfj mysite.tar.bz2 mysite/
$ exit

Download the backup, through ssh copy.
$ scp user@mysite.com:/home/user/mysite.tar.bz2 /home/shaakunthala/

Unpack on my computer, to be tested with the script.
$ tar xvjf mysite.tar.bz

Now, next step is to write the script. Fired up my favourite vim editor, and then started thinking. ;) Before writing the script it's necessary to exactly identify the nature of the malicious code. Here's what I've identified:
  • If a file is infected, the malicious code is at the end of the file.
  • The foreign code snippet is different from point to point. But, following text portions can be recognized as a common pattern.
    • GNU GPL
    • window.onload
    • .replace
  • Although it seemed like the infection is only with JavaScript and index documents, I refused to accept that. Also, as we didn't have any gigantic files with our website, I decided the script to test all files throughout the site.
Although it was such an easy task to write a script for malware removal, I had to separate the program into two scripts because find -exec does not recognize functions in bash. So, here's what I wrote:

sitefix.sh
#!/bin/bash
# Author: Sameera Shaakunthala

rm fixlog.txt
rootdir=`pwd`/mysite/
sup=`pwd`"/fixfile.sh"
find $rootdir -exec $sup {} \;
echo "JOB DONE!"

fixfile.sh
#!/bin/bash
# Author: Sameera Shaakunthala

echo "Processing file: "$1
code=`tail --lines=1 $1 | grep "GNU GPL" | grep window.onload | grep .replace`
l=`echo $code | wc -m | awk '{ print $1 }'`

if [ $l -ne 1 ]
then
 lc=`wc -l $1 | awk '{ print $1 }'`
 lc=`expr $lc - 1`
 head $1 -n $lc > tempfile.tmp
 mv tempfile.tmp $1
 echo "File "$1" has been fixed!" | tee -a fixlog.txt
fi

Now, the next task is the test run on my local machine. If this succeeds, it is safe to run the script on the server.

$ chmod +x sitefix.sh fixfile.sh
$ ./sitefix.sh

After execution, I checked the fixlog.txt, which is the output log of the script. OMG! 602 infected files!! :-O I vigorously checked some randomly selected files, they were clean, and as everything seemed to be clean, I uploaded the script to the server, and then executed. :)

$ scp sitefix.sh fixfile.sh user@mysite.com:/home/user
$ ssh user@mysite.com
$ chmod +x sitefix.sh fixfile.sh
$ ./sitefix.sh

Finally, we have set this as a cron job, till we find the actual security hole.

The final result was, the disinfection of the entire website, within few minutes. As I got to know that virus scanners no longer block our website, it was confirmed that the site is clean. Just see the spirit of Linux bash scripting! :)

Hallelujah!

Finally, I put a link to a shocking article that must be read... Just click and see! :(

Finally, captain Shaakunthala saved the day, with the support of other captains and sailors, yeah it's an amazing Christmas gift for a newbie administrator! :D

Followers

Subscription Options

 Subscribe in a reader

or

Enter your email address:

and
to inscrease

and make me happy. :)

Other Blogs by the Author

The Author

My photo
Ambalangoda, Southern Province, Sri Lanka
Shaakunthala works as a Systems Support Engineer at a World's leading IT solutions company. A wannabe hacker, FOSS enthusiast, cat lover and an insane motorcyclist. And he comes from Sri Lanka!! :)

www.shaakunthala.com
www.flickr.com
ශාකුන්තල | Shaakunthala's items Go to ශාකුන්තල | Shaakunthala's photostream
Related Posts with Thumbnails