We think we have had some sql injection attacks on our debian server and it has caused serious performance decreases, load averages of 35 are not strange since this has happened.

Talking to the dev, he found one candidate script, but it only is accessed 10 times in a timeframe when the SQL is exectued 2,334 times?!? Maybe the attackers managing to repaet the SQL statement?

Anyway now we know what area the the problem is, we want to log the apache requests (get, post, i.p, URL) with the triggered mysql queries, including postdata (as a temporary measure of course) if possible, I thought there would be a module for PHP or cakephp (which the app is made with) or apache but I can't find anything which can link the http info with the mysql info automatically and log it, I want to save our development team from having to update all the code to log the info.

Just thinking out loud I thought a PHP module could intercept the request and script, monitor the scripts thread and see what mysql or other perconfigured database module or connection and just grab the query and log it all together somewhere, sounds simple, not sure the best way to implement it without knowing php_mysql and apache mod_php or php_cgi architecture.

Any help appreciated.
Thanks, George.

If you add mod_perl to your apache configuration then you can use the request handler http.conf options to call perl code at various points of the request path. One of these handlers could easily log the current request to a file. You might need to play a little with POST requests; I've not tried.

http://search.cpan.org/~pgollucci/mod_p ... s/http.pod

_________________
Rgds
Stephen
(Linux user since kernel version 0.11)

Thanks Stephen thats a great start, I am reading the POD now, but am guessing that mod_perl cannot capture a mysql query performed by PHP, we'll see.

mysql, itself, might be capable of logging all SQL requests (but I'm not a mysql expert so I dunno). The combination of request logs and SQL logs should allow forensics.

_________________
Rgds

Stephen

(Linux user since kernel version 0.11)

Yes you are right in theory, we are logging slow_queries but it's proving a bit difficult to match the requests to the queries.

I'll take that as the best way to do it, thanks again Stephen, for a very quick, and equally informative answer.

UPDATE: Actually though, if we have concurrent instances of apache2 and equal connections to MySQL, I'm thinking the requests to queries combination (permutations) will be quite high (eg. 20 possible apache requests with 20 MySQL queries), without knowing or recording a query identifier, we wouldn't know which one of the concurrent requests caused which one of the concurrent queries, although thinking more, not all requests are started at the same time so it might be ok. I'll try it out.

Why not just turn on the normal mysql logging and/or binlog, then compare timestamps in mysql log and apache log.

I dont have a utility to compare the timestamps, and from looking manually the timestamps don't match, is this way the best way?

I have actually found mod_security might be able to log POSTDATA, so I'm gonna use that aswell, and combine with timestamp matching, probably have to write a perl script to do that, unless there is one already? I cannot see one on google.