JP Vossen on 26 Mar 2010 15:43:52 -0700

[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]

Re: [PLUG] slightly OT: finding SQL injection in M$ logs with grep

 > Date: Fri, 26 Mar 2010 17:40:16 -0400
 > From: Michael Lazin <>

> We have a customer who had an entire table dropped from their MS SQL
> server database, they are running a customer built ASP site with
> database backend. They are blaming us but 2nd level support believes
> it to be the result of a SQL injection attack. I have been given the
> unfortunate duty of trying to find the hack, I do security on the
> Linux servers, but there is no one else here who knows enough about
> logs who could do any better. I am working under the assumption that
> the attack will show up with in the logs as a POST, and it will be a
> 200 (successful connection). There are 295 unique IPs that have
> passed post data that are 200s. I know this from grep and wc -l.
> Anyone know enough about MS logs to give me some hints that might
> help me find the hack with grep?

Maybe a silly question, but I didn't see it elsewhere.  Did you try 
'grep -i drop' (or 'drop table') either against the whole log or the 
POST 200 sub-set?  I guess if the POST data isn't in the log that won't 
help, but I already said this was probably silly.

I know nothing about MS SQL, but I do  know that there is at least one 
MS SQL DBA on this list.  I won't "out" him myself, but can step forward 
if he chooses.  :-)  Perhaps if he wants to admit it (or can pretend he 
"asked a friend") he might be able to provide some clues.

> egrep  '/exec(\s|\+)+(s|x)p\w+/ix' *.log shows nothing and the rest of the
> regex on this page doesn't appear to work.  I'm not a regex ninja so maybe
> I'm doing something wrong.

That site talks about PCRE (Perl Compatible Regular Expressions), which 
mostly, kinda, usually won't work with grep/egrep.  Except when they do. 

GNU grep sometimes has a -P switch that enables experimental support for 
PCRE in grep.  I thought Debian and Ubuntu compiled without that switch, 
but it is in the man pages I checked.  YMMV.  Also, some "PCRE" 
sequences like \w are in grep anyway, but others like \s are not. 
Usually.  It's ugly.

So, one way to be sure that PCRE patterns work is to just use perl 
instead of grep.  We've talked about Walt's Perl One-Liner preso 
(, so you can 
use what he calls the "Poor man’s grep" (I prefer PCRE, so I'd call this 
a "Rich man's grep"):
	perl -ne ’print if /^foobar/’

There are a ton of nuances and flags, like 'i' for grep's -i, and I like 
to specify m for match (m//) so:
	perl -ne ’print if m/drop/i’ /var/log/ms_sql_yeah_right

I love but it's a dense cheat 
sheet, not a PCRE tutorial. may be 
easier to start with.  Then again, maybe not.

Good luck,

PS--Still waiting for the slides from the 'log forensics' talk.  I'm 
willing to proof-read and edit a bit before publication, if you want...
JP Vossen, CISSP            |:::======|
My Account, My Opinions     |=========|
"Microsoft Tax" = the additional hardware & yearly fees for the add-on
software required to protect Windows from its own poorly designed and
implemented self, while the overhead incidentally flattens Moore's Law.
Philadelphia Linux Users Group         --
Announcements -
General Discussion  --