Detecting potential SQL injection attacks, as well as other security issues

Question

We all know it is nearly impossible to produce a large website without one or two flaws. As such I've written a small monitor that checks Apache access logs for potential SQL injection attacks (amongst other things), and it's working very well. I get an alert whenever someone attempts an attack, and I've had so few false positives that the default action is now to dump them into an iptables drop list. It's even helped me identify a few (non-security) bugs and remove them.

Here's my rules (case insensitive):

PathInjection = \./\.\./(bin|boot|data|dev|etc|home|lib|lib64|media|mnt|opt|proc|root|sbin|selinux|srv|sys|tmp|usr|var)/

Havij = 0x31303235343830303536

r3dm0v3 = 0x7233646D3076335F68766A5F696E6A656374696F6E

LogicBypass = '.*?(\bor|\band|\bxor|\|\||\&\&).*?--

UnionSelect = union[^a-z-_]+((all|distinct)[^a-z-_]+)?select[^a-z-_]

What I'd like to know is, how would you bypass these checks and still produce a valid injection? Can you think of a way to improve them without introducing false positives?

A few notes:

• Case sensitivity is switched off.
• I'm using MySQL.
• The Havij and r3dm0v3 entries are used as a catch-all to prevent use of those automation tools.
• I'm checking both raw and urldecoded strings.
• I'm not looking for answers like "make more secure code instead".
• I'm not looking for a different way to do this, just a way to improve my current logic.

EDIT:
Ok, so people seem to have misunderstood my intent. That's probably my fault, since I didn't fully explain. This is being requested as a tacked-on feature to a monitoring product, and is designed to offer minimal security monitoring. As part of our dialog with the client and our documentation, we're emphasising that this is not a catch-all, nor is it a replacement for proper security infrastructure (e.g. an IDS and firewall). It's simply an informational service to help provide basic threat detection and produce statistics about the number of potential attacks. I'm not trying to write an IDS or firewall. If it were up to me, I'd leave the feature out and tell them to go install a full suite of security infrastructure with its own monitoring systems, but this isn't my call. The current situation is that I've been testing the system on my own site. Right now, I'm just looking for a way to improve the regex strings to make this more effective. Hopefully this clears things up a little.

---

Edit again, in June 2021.

I posted this question back in 2011. Back when I wrote it I was a junior developer with an interest in security but lacking experience. Since then I've switched careers to security, worked as a pentester for 5 years, and a security researcher for another two. I'm also one of the top reputation users on Security StackExchange.

The answers given here are mostly correct - there's far more value in deploying something like ModSecurity with appropriate rules, since they've already done the work. A tacked on homebrew solution is not going to compare to a project with almost two decades of maturity.

The one major caveat, though, is that I was not making the decisions. Junior developers usually have neither the privilege nor latitude to veto product decisions made by management, especially those made at the request of a customer. One can certainly explain why an idea is bad, and provide supporting material, but that often doesn't translate into changed decisions. Being able to refuse a task from your employer without consequence is an unusual privilege - the concept is a complete fantasy in the context of most employment.

My advice for folks who respond to these types of question is this: explain why it is ill-advised, but be sympathetic and helpful to those who are in a difficult position. Actually answer the question, wherever possible, so that a best-effort solution can be implemented if all else fails. In the context of security features, it's also worth considering that if the alternative is no protection or detection at all - even if that alternative is artificially being imposed by external actors - a weak capability is almost always better than no capability at all.

I don't remember what I ended up implementing for this. It was so long ago. But if you're here now, looking for answers, I recommend using ModSecurity. They now have connectors for Apache, nginx, and IIS, so you can install it on any of those web servers. If changing the server configuration is problematic, you could instead run nginx as a reverse proxy with ModSecurity enabled, so that users hit the nginx server and it proxies the requests to your actual web server. This can also be done with Apache instead.

If you're looking for a more programmatic approach, ModSecurity has language bindings for C, C++, and Python. The ModSecurity API can also be consumed via any language that has interoperability support for C APIs (e.g. P/Invoke in .NET, or JNI in Java).

ModSecurity works on rules. One of the best free rule sets out there is OWASP Core Rule Set (CRS). The rules are significantly more powerful than simple regex patterns. This makes them very effective, but it also means that you probably shouldn't try to build your own ModSecurity-like internal feature that consumes the rules, because you'd need to put in a ton of work to support all the necessary features.

If you need to parse ModSecurity logs into a format that can be automated upon, take a look at ModSecurity Log Utilities.

Hopefull this info is of use to someone in future.

Answer 1

You're talking about writing an IDS. Unless your product is an IDS, just get and install one. Snort is well-known and has a free version.

I'm not looking for a different way to do this, just a way to improve my current logic.

Sometimes when it comes to security, the wrong approach simply is. How would I mess with your current logic? Unicode or hex encoding.