| MySQL data set weirdness [message #231856] |
Mon, 02 April 2007 14:42 |
|
Originally posted by: david.vorant.com
Hi, all. I'm converting several MySQL reports from a home-brew perl
script into BIRT reports, and so far they've all been pretty straightforward.
I've saved the most complex for last, though, and now I've got a problem
that really has me stumped.
I've defined a MySQL data source, which is working well. One of my
datasets has a very complex query, though, and the results are puzzling.
First, here's the SQL:
select count(*) as cnt,
trim(LEADING '=' FROM
substring_index(
substring_index(
substr(
unhex(data_payload),
locate('=http',unhex(data_payload))),
'/', 3),
' ', 1)
) as url
from event
INNER JOIN data on
event.sid = data.sid and
event.cid = data.cid
where
(signature like '%PHP Remote File Inclusion%' or
signature like '%WEB-PHP remote include path%' or
signature like '%Generic phpbb arbitrary command%' or
signature like '%PHP Injection Attack%' or
signature like 'COMMUNITY WEB-PHP Gphoto index.php image parameter remote
file include attempt')
and
timestamp BETWEEN ? and ?
group by url
order by cnt desc
So a few words of explanation are in order. 8-)
I'm data mining my Sguil (www.sguil.net) database. Sguil is a network
security monitoring solution with a MySQL backend. What I'm doing here
is looking for all the PHP injection attack events. These typically
inject URLs into the PHP code, and if the attack is successful, the PHP
application will initiate an HTTP connection to fetch and execute
some arbitrary PHP code. What I'm doing is looking for these events,
then JOINing the table that contains packet payloads so I can extract
the injected URLs and report how many times each has been tried against
my servers.
When I run the same query in my perl script, I get output that looks
similar to this:
Num attempts Url
46 baddomain1.com
2 baddomain2.com
2 baddomain3.net
1 baddomain4.org
However, when I run the report in BIRT, I get some garbage output for
the URL parameter:
Num attempts Url
46 [B@16d616d6
2 [B@27ba27ba
2 [B@28622862
1 [B@291c291c
The data_payload field is a giant hex-encoded string, so unhex() is the
proper way to convert this to a printable string. But clearly I'm
not getting a printable string. In fact, when I rerun the report preview
with all the same data and paramters, the garbage values change a bit,
when they really shouldn't. I've even tried eliminating all the
messy string operations, and just going with a simple unhex() for the
url field, and I still don't get good values.
Has anyone encountered this before? I'm reasonably sure that my queries
are good SQL, but maybe something doesn't translate well into BIRT?
Thanks,
David
|
|
|
Powered by
FUDForum. Page generated in 0.01554 seconds
] 
Search
Help
Register
Login
Home
