toolsmith: Log Parser Lizard
toolsmith: Log Parser Lizard:
Prerequisites
DimceKuzmanov, lead developer and founder, Lizard Labs
Prerequisites
Windows
Introduction
At RSA Conference 2012 Igave a presentation called Evil Through The Lens of Web Logs. This presentationis built on research I’m conducting for a SANS Gold paper for graduate school andpays particular attention to SQL injection and Remote File Include attacks. Oneof the tools discussed as very useful for analysis tactics is Log Parser Lizard.You’re probably familiar with Log Parser, but I’ll bet you didn’t there was agreat GUI-based tool with which to leverage its raw power with ease. Log ParserLizard (LPL) is the brainchild of Dimce Kuzmanov, a Macedonian softwareengineer, who started Lizard Labs in 1998. In 2006 while also working as a parttime sysadmin on financial systems, Dimce recognized that he was using Logparseron a daily basis for creating reports, analyzing logs, automatic errorreporting, transferring data with txt files, etc. Over time his collection ofqueries became unmanageable and difficult to maintain so he created LPL for hispersonal use and because, having benefited from free software himself, wantedto release a useful freeware product to give back to the community. While LPL verysuccessfully harnesses Log Parser’s capabilities Dimce firmly believes that asa great UI it help users learn and organize their queries with less effort. Whenhe added log4net and regex input support the Logparser community really beganto embrace LPL. LPL releases are a bit sporadic, usually based on a few newfeatures, bug or code fixes and future releases are planned but not with aknown frequency. Today LPL has a user base of about 2000 installations each monthbased on trend analysis for the last three years and approximately 80000 usersworldwide.
The current productionrelease of LPL is 2.1 and features include:
· Ability toorganize queries along with an improved source code editor that includesenhanced source navigation and analysis capability, syntax-highlighting,automatic source code completion, method insight, undo/redo, bookmarks, andmore
· Support forFacebook Query Language (FQL). This feature was introduced to help Facebookdevelopers organize their queries
· Code snippets(code templates) and constants. Log Parser Lizard also supports “constants”binding to static/shared properties from Microsoft .Net
· Numerous otheruser-interface features including advanced grid with filtering and grouping aswell as support for charts without requiring a Microsoft Office installation asis a dependcy for a standalone instanceof Logparser
· Support forprinting and exporting results to Excel and PDF documents
o For registered users ($26.51 USD)
· Support forinline VB.Net code to create LogParser SQL queries
Inline VB.net supportallows you to drop your code between <% and %> marks; it will then beexecuted and the resulting string will be replaced in the query. Lizard Labs believesthis feature will be very useful for LPL users. Before parsing logs you canmove-copy-rename files, download via FTP, shutdown IIS, etc. You can also use.Net data types like DateTime for arithmetic operations and/or System.Environmentsettings in query parameters.
As I write this I’mtesting the beta for LPL 2.5 and the new feature set includes:
· Conditional field formatting (color, font, size,image) to identify required information. As an example, you can set theconditions to change error colors to red, warnings to yellow, etc. or highlighta specific field if it contains a string value of interest
· Store and organize queries in SQL Serverdatabase for ease of use among multiple users and computers in an organization aswell as backups, auditing and all other benefits that database storage allows
· Excel-style row filtering
· Ability to add columns with Excel style formulas(with most Excel functions) and support for exporting in Excel 2007 format(more than 65365 rows)
What would a toolsmith article be without a tool roadmapso let’s not break a good habit, eh? LPL 3.0 will likely include out of the boxqueries for IIS web reports (as in other commercial log analysis products),support for query execution scheduling, reports sent via e-mail from LPL,command line support, a query builder tool, text file input format (where a singlefile is one record and fields can be extracted with RegEx or with Logparserfunctions), and improved log4net input format. As with most of the tools wediscuss, Dimce is certainly open to good ideas for the product and welcomesfeedback and ideas from the user community. In total fantasy land the future ofLPL may even include queries “in the cloud”, an LPL ASP.net web app that can beinstalled right on the server, a web service supporting LPL, mobile apps thatcan use this service, and a global query dictionary that users can submit,comment and rate the queries. “The future’s so bright, I gotta wear shades.”Whoa, 80’s flashback, sorry.
Using Log Parser Lizard
Installing Log Parser Lizardis so straightforward it doesn’t even warrant a section. Ensure you have LogParser and .Net 3.5 installed, then execute the LPL installer. Finito.
As described above, I’ve beenworking on research for a paper which includes analysis of a mass SQL injectionattack, well described in detail this past December by Mark Hofman on the SANSInternet Storm Center Diary. Inaddition to Mark’s analysis, this popular post included many comments andreplies from readers who had suffered or noted the attack in their logs andeven some helpful folks who submitted log samples. You likely remember the LizaMoonattack and the Lilupophilupop attack was quite similar. In both cases, injectedsites offered a URL that then caused redirection to a fake antivirus offering.Specifically, wasembedded in victim sites where sl.php bounced you to the likes of hxxp://ift72hbot.rr.nu,the on to rogue AV. I actually had to look up the .rr.nu TLD; it’s the Republicof Moldova, and has been implicated recently in massive SPAM campaigns as wellas the current WordPress hacks (as of this writing).
Figure 1 represents a victimsite still exhibiting typical signs of compromise.
 |
| Figure 1: Lilupophilupop victim site |
Victim sites were most oftenrunning ASP.net apps on IIS with MS-SQL back-ends. It was quickly learned thata few identifying traits of the Lilupophilupop attack included the fact that arather large hex blob that was evident in IIS logs. I’ve always found thatchecking logs for 500 errors when analyzing for SQL injection attacks cantypically point you down the right path. Using a log file submitted by an ISCreader (anonymized for obvious reasons), I first built a query to seek ASP applicationerrors from a default query included in LPL. I launched LPL, clicked IIS Logs, then ASP App Errors, replaced #IISW3C#in the FROM statement with thepath to my anonymized log file, and finally clicked Run Query as seen in Figure 2. Email me if you’d like mesend you the log file so you can experiment for yourself.
 |
| Figure 2: LPL parsing error messages |
Using this query, including FROM D:\logs\lilupophilupop\ex111201anon.logWHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asp'), prior to beingaware of lilupophilupop as akeyword or part of an injected URL, would have immediately narrowed the searchvectors.
Also common to attacks ofthis nature might be a DECLAREstatement (defines variable(s)) visible in logs. A query as seen in Figure 3produced three results that included a DECLARE statement followed by a CAST (convertsan expression of one data type to another) statement wherein an attempt to passthe hex blob to the backend was noted.
 |
| Figure 3: LPL parsing DECLARE statements |
I clicked one of the resultsfrom 78.46.28.97, chose Select All,then Copy, and dropped thecontent to a text editor. I then grabbed the hex from just after the CASTstatement to just prior to the AS VARCHAR statement and copied into a BurpSuite decoder window and chose decode as ascii hex.
Figure 4 shows the convertedattack string.
 |
| Figure 4: Burp decoder converts hex |
Long and short of it, theattack loops through all columns in all tables and updates their value byadding JavaScript to point to hxxp://lilupophilupop.com/sl.php.
This took all of 5 to 10minutes with LPL and a little experimentation. Yes, you can do all of this withLog Parser at the command line but if you’re looking for strong querymanagement, tidy reporting exports including charts, and downright convenience,LPL is the way to go.
In Conclusion
Log Parser Lizard is one of those indispensable toolsthat treads lightly on your system but offers a huge bang for the buck. Free or$26? Puhleeze. Keep in mind that while I used an IIS log sample for the articleyou can throw LPL at generic XML, CSV, TSV and W3C based logs all day long.Download it and put it to good use right away. Dimce would love to hear fromyou, and I look forward to hearing your success stories.
Ping me via email if you have questions (russ atholisticinfosec dot org).
Cheers…until next month.
Acknowledgements
Comments