How to check web server logs for SQL injection?

Question

If I have a PHP site hosted on Apache and someone hacks it using SQL injection, is there a way I can find out exactly which script caused this by looking at my web server logs?
Or using some other way?

Solution 1

If your web server logs are logging the query parameters, then search for known SQL (e.g. SELECT) through the logs.

If they're not logging query parameters, then you're unlikely to find anything by searching the logs, rather you'll have to look for patterns - for example a repeated URL that wouldn't normally be repeated.

Solution 2

I've been using this Linux command string to identify possible SQL injections:

egrep -i '\bUNION\b|\bSELECT\b|\bCHAR\b' $access_log

You could add in other key terms as well, but those were the ones I was finding in my logs.

To generate a list of offending IP addresses, get a little fancier:

egrep -i '\bUNION\b|\bSELECT\b|\bCHAR\b' $access_log | sed -n 's/\([0-9\.]\+\).*/\1/p' | sort | uniq -c

Which tallies up the offending IPs by the number of instances:

    335 160.153.153.31
   1197 175.138.67.67
    508 76.72.165.79
    208 92.60.66.184
    111 95.143.64.185

Comments

[Frank Martin]

Ok this brings me to another question. How do you log query parameters?

[Frank Martin]

One more thing. Shall I look into access logs or error logs?

[Jenny D]

The error logs should show failed hack attempts, not successful ones. So start with the access logs. To see how to change what's getting logged, maybe making a separate log for query parameters only, look at httpd.apache.org/docs/2.2/mod/mod_log_config.html .

[4wk_]

I understand both point of view about "occurrences and impacts", and this answer does not deserve downvotes considering it's the same as the other one, but with awk, which is fine.

[ARaj123]

Can sql statements in query parameters be encoded or are they all recognizable? Is there something other than plain english sql statements that I should be looking for in these logs?