How to trace possible SQL injection attack?

Question

We just had one of our (fairly important) wordpress databases inexplicably dropped. Fortunately we keep nightly backups so it's not going to be the end of the world, but I want to avoid or at least be able to trace this in the future.

Now, we still don't know whether the database was dropped due to a junior developer accidentally entering commands into the wrong mysql command line or phpymadmin window, or whether this was a malicious SQL injection attack.

Obviously we need tighter control on the junior developers mysql user accounts, but beyond that I am wondering what the best practices are for detecting/preventing sql injection via server administration.

Note I do not want to know how to sanitize inputs on an individual basis -- I do that every time, but we write a lot of custom scripts and we're always going to have junior developers on staff who can forget or get this wrong. At the very least, I would like to know the best (easiest) way to:

1. Log all GET or POST requests in standard access logging format that contain a query string or post data with any SQL in it (I imagine using a regex like /(drop|delete|truncate|update|insert)/ to a single file for all virtualhosts that I can then grep

2. Log only mysql commands that start with drop,delete, truncate to a single file. Each entry would need to include at the minimum time and mysql user, but it would also be sweet if I could see whether it was through the command line or php, and if php what the script was.

Thanks for your help! And obviously let me know if there's any basic solution using the standard logs I've overlooked.

Solution 1

Check out GreenSQL: http://www.greensql.net/

Cheers

Solution 2

The wordpress site suggests using mod_security for this.

Comments

[Admin]

you know, maybe #1 could also be logged through php if that's easier than logging it through the web server

[Neil Sarkar]

thanks, looks intriguing I will check this out. The issue is I don't know if it was SQL injection or not, and wanted to know a definitive way to log suspicious or potentially harmful queries across a server, so as to cover detecting sql injection AND staff mistakes internally

[Neil Sarkar]

yup mod_security is solid I've used it with Apache in the past. Problem is we are on nginx. Although that wordpress documentation link points to this plugin village-idiot.org/post-logger which accomplishes my main objective which is logging all user input. However, I would still love to know a way to set this up that is independent of wordpress (we also run a lot of rails apps for example). It seems like there should be a relatively simple way to log mysql commands according to a regex and all GET/POST requests according to a regex.