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:
POSTrequests 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
Log only mysql commands that start with
truncateto 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.