Friday, December 25, 2009

Parsing Excel Spreadsheets in Perl

Last week at work I was playing around trying to read Nessus Scan results which were pre dumped in an Excel file. I used Jon McNamara's Perl modules to parse and write Excel files. They largely worked very well, just as it was documented.

One place though where I got a little stuck was when I was reading rows from one file and conditionally writing it to another Excel file. So for eg. My first Excel file had say 5 rows and I wanted to write only 2,4 and 5 but not 1 and 3. What kept happening was that 2,4 and 5 did get written, but 1 and 3 also got written as blank rows leaving gaps in the Excel sheet. I worked around it by forcing zeros to get written for Rows 1 and 3 as I didn't want blank rows in the destination file (Cant Autofilter if they are present) but it did get a little messy as complexity increased.

So I decided to use the modules just for vanilla read and write. I read data from Excel and instead of parsing it then and there dumped it into a TAB separated text file. I parsed , stripped and did what I wanted with the contents of the text file and then used the WriteExcel module to write it back to the new Excel sheet. That got the job done just like I wanted it.

Now maybe I was missing something and should have RTFM more carefully, but this little hack kind of helped me when I was under a little time pressure so figured I'd put it out so someone else stuck..could try this out.

Formshield Captcha Advisory

Tested the Formshield Captcha a while back and published an advisory on Bugtraq for the same.

Writing Code in a New Language

I'm not a developer by profession. I do enjoy a little bit of programming though. It keeps me not being utterly bored with my job following procedures that are put down by me or someone else. Also considering the fact that we do a lot of Source Code Reviews it makes sense how to learn how to write Secure Code. So I decided to learn PHP. I learnt a few things during the same; which should be globally applicable, thought I'd share it here.

-- Decide your requirements first of all. What do you need to do? You need to learn code just enough to do that.

-- Don't sit down with a huge 800 page book and learn its syntax inside out. It'll only get you frustrated. You might not want to learn a lot of it right now so read just enough so you can start writing. Its best to start writing..fast.

--Once your reqs are clear. Decide your program flow. What should happen when you click on that link? Where should it go? Get that entire flow clear in your head and even draw a little diagram ensuring your clear about the same.

--Once your "click click" flow is clear design your database if you have one. Decide which clicks need to display something on the screen. Those clicks will most probably need DB interaction. Based on what data you need to display - create your database and as many tables as you can. Don't make it too complex at this stage - design only as much as you can think of and visualize clearly. The further you can think though, the easier you will find it at a later stage.

--Once your database arch is clear decide what part of your code needs to read from which table and what part needs to write. Create database users and GRANT rights accordingly. Again like above.. you can change as you code but the further you can envision here the easier it is.

--Document all your database queries, all your GRANTS..everything. You'll be suprised at how helpful it is if you want to recreate from scratch. And trust me .. when you're new to a language you WILL recreate from scratch ..many times ;)

--Now read your first requirement - write pseudo code for it. Decide what function names you will use. Try and maintain a pattern here. You'll be surprised how much you forget about your own code.

--Once you know the structure of your code, open all the online references for the relevant functions as well as your book if you studied from one. Understand if there is existing code / functions which does what you want to do(Remember your requirements phase?)

--Start coding. Run code regularly. After every 10-20 lines print things out and see if things are working as you expected them to work. This is much better than writing 200 lines and finding out nothing works. Later when you're experienced(even then I wonder) you might not need this approach but initially when you're getting your feet wet - please code-run-code-run as much as you can.

--Enable warning/strict/all security protections you know of while you start to code. Helps you know what you're doing wrong.

--Lastly comment your code. Please.PLEASE.

Management

This is a post where I put in a lot of my thoughts based on my experiences at work with people on and off projects. It isn't something that is unique or anything, just things that I have learnt over the years and feel that anyone new to the job might benefit from. I plan to add to this list .. all the time.

Juniors:
Keep your calm, at all times specially in a crisis and in front of your juniors.
Set an example for your juniors ; follow yourself what you tell them.
Keep taking feedback from your juniors and peers.. its the best way that you will learn about yourself.
Give people chances, a lot of them - Don't write them off or judge them on 1 instance.
Trust people - give them responsibility , you wont know how good or bad they are at it unless you do.
Delegate Work where you can - Don't get greedy and want to do it all yourself...you can't raise your own level if you don't do this.

People in general:
Argue when you need to , not just for the heck of it each time you disagree a little.
Remain open to newer ideas , you wont grow if you're not.
Don't be scared of being wrong - in public or in private..you only learn from this.

Your own Work Ethics:
Automate boring time taking tasks , don't be lazy- its worth the effort you put in just that once.
Reply to every personal Email - its shocking to see how few do it
In a positive environment a weak guy goes up, try and help create one
Try and get rid of negativity - as far as you can; it isn't easy but its worth trying
Live selfishly - Have a deep desire to obtain only the best around you - on merit. Don't settle for something less if you can help it
Do not delay things you can do now
Remain grateful - Remain humble - No matter how big you become -- Remember there was a time you knew nothing

Contests I played

I do enjoy taking part in hacking contests online, off and on. Recently took part in a little contest on Chris Christianson's site and hey...I won it :) . Here's the challenge solution that I submitted.

Another one which I did but wasn't as lucky in is here. I got to the semifinals though for what its worth.

And another where I got even closer.

FWAuto

I wrote a little parser for analyzing Firewall Rulebases of a PIX firewall. It works nicely and gives you an idea of where all your rules are over flexible. Not working on it anymore - but the latest version works well.

Here it is:
https://sourceforge.net/projects/fwauto/