Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
you know, maybe #1 could also be logged through php if that's easier than logging it through the web server –  Neil Sarkar Dec 9 '09 at 17:28
add comment

4 Answers

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

Cheers

share|improve this answer
    
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 Dec 9 '09 at 19:09
add comment

The wordpress site suggests using mod_security for this.

share|improve this answer
    
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. –  Neil Sarkar Dec 9 '09 at 22:27
add comment

Just to add along with GET and POST you should also log COOKIE, FILE, REQUEST.

share|improve this answer
add comment

Few suggestions to you:

-Enable MySQL logging. Use OSSEC to monitor these logs. http://www.ossec.net/wiki/SQL%5FLogging#MySQL%5FLogging

-Enable Wordpress logging plugin ( http://www.ossec.net/main/wpsyslog2 ). It will generate logs for everything that happens inside Wordpress (new posts, new users, logins, logouts, etc).

-Analyze your Apache logs (my recommendation is, again, OSSEC).

With these 3 audit trails, it will be easily to monitor and detect issues like this or any other kind of attack.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.