4

This is a little bit of a rant, but there's a real question at the end.

I recently installed a new perl script on a site (which will remain nameless) which failed mysteriously with an error 403. Eventually I found a clue in this error in the apache error logs

[error] mod_security: Access denied with code 403. Pattern match "select.+from" at REQUEST_URI [severity "EMERGENCY"]

Which I believe to be from an utterly simpleminded attempt to defend against SQL injection attacks, by rejecting any HTTP request which contains "select" followed by "from".

Obviously, the pattern could be made much more complex, but the whole approach looks bankrupt to me. The question is, is there any generic approach that could actually work, or is it necessarily something that has to be done closer to the actual database manipulation.

Eric G
  • 9,691
  • 4
  • 31
  • 58
ddyer
  • 1,974
  • 1
  • 12
  • 20

4 Answers4

5

The only generic approach to preventing SQL injection is to use parameterised queries, also known as prepared statements. These essentially separate out the data from the query language at the protocol level, so the DBMS software will not try to parse any query language from the parameters.

The mechanism you described looks like it's filtering requests with blacklist patterns, which isn't necessarily a bad thing (defense in depth is good!) but seems pretty redundant if queries are being handled properly, and can never replace real solid security practices.

Polynomial
  • 132,208
  • 43
  • 298
  • 379
  • It only came to my attention because it was filtering a request that had NOTHING to do with SQL. Like shooting a few suspicious customers in hope of preventing a robbery. – ddyer May 08 '13 at 10:08
  • 5
    Paramaterized queries are not the same thing as prepared statements and neither are the *only* way to prevent SQL injection - sanitizing the input before splicing into an SQL statement also provides protection (and this is what client parsed parameterized queries do - just hiding away much of the nastiness). And even then it's just possible to inject SQL but only if dynamic SQL queries are constructed from the paramaters. – symcbean May 08 '13 at 12:12
1

Web Application Firewall like Modsecurity is just an operation security measure to protect web application from malicious input. WAF is just an application layer filter that can compare each request and response with the malicious signature provided my the WAF ruleset. Modsecurity Core Rule provides comprehensive generic rule set against different attacks variation. Quality of WAF depends on quality of signatures it uses and it will not work unless system administrator tune them to his needs and discard rules that are causing false positive. But unfortunately this requires some serious effort and deep knowledge. I would break down the security into three phases

  1. Development Phase (Follow secure programming practices)
  2. Deployment Phase (Application Hardening through Selinux , apparmor)
  3. Operational Phase (Operational Security through WAF like Modsecurity)
Ali Ahmad
  • 4,784
  • 8
  • 35
  • 61
0

Continuing from my rant in response to Polynomial ;) ....

Proper escaping of parameters of parameters is a very basic preventative measure - and using parameter binding is one way to achieve that.

Another (complementary) approach to protecting your data is to disallow direct interaction with the underlying data to the session from the logic tier - don't allow the webserver account to SELECT / UPDATE / DELETE / INSERT...etc on your data tables - instead allow them to execute functions and procedures (which have delegated authority). But this requires an additional tier to implement on databases which don't support procedural languages / transferred permissions.

As Ali Ahmad says, you need to configure the tools at your disposal to work nicely together. There is no one-config-fits-all circumstances.

symcbean
  • 18,278
  • 39
  • 73
0

My preference for high security environments is to require a language translation - that is to say if my app is written in PHP then I pass the query parameters to a non-PHP app to do the query; this adds some latency and computational overhead. If you are protecting Things That Matter(tm) this is easily worth it - if you are protecting lolz maybe not.

In case that's not clear - rather than taking parameters from a user, sanitizing them, and sending to a SQL DB I take the parameters from the user, sanitize them, then pass it to a query service which unpackages, sanitizes, repackages and submits to a DB for processing. In this way vulnerabilities in one language or API or query class etc is not likely sufficient to enable a compromise. Security is best done in layers since it is hard to be perfect about anything.

Ram
  • 119
  • 2
  • You are aware that your second tier may very well have the same SQL injection vuln, right? For example, pass PHP->Perl and you won't get rid of some of the problem due to similar syntax and user input processing rules on both. You're also adding one more endpoint to maintain (= tech overhead) when parametrization does better with less. @Polynomial is right, though - the answer is stored procedures + parametrization. – Sébastien Renauld May 11 '13 at 18:47
  • @SébastienRenauld Firstly I am not saying you should not use prepared statements (in the query service interaction with the DB); that should always be done. My point is that while I am sure there are occasions where you will find the same bug in two languages implementations of similar functions it is less common than finding a bug in one. – Ram May 11 '13 at 18:51
  • you're on the right track with your solution, but using a separate service is overhead. Look up stored procedures and subroutines for MySQL, MSSQL or PL/SQL - this is effectively what you describe. Combine that with parametrization and you have Polynomial's solution with no added overhead. – Sébastien Renauld May 11 '13 at 18:53
  • @SébastienRenauld It seems we disagree. In my opinion there are plenty of systems out there that should (and do) use the technique I describe. As I said if something is valuable enough it is worth the overhead (latency, cpu, maintenance) - I've seen this approach in multiple companies and have implemented it myself in two. DB stored procedures have their advantages and their drawbacks and are no replacement for a non-db security layer. – Ram May 11 '13 at 18:58
  • This is a good answer. It's very common to hide your DB behind an API endpoint. It's easy to make sure you use parameters on the API endpoint code. Then, no matter how huge your application gets, it will always be safe because it doesn't even have direct access to the DB - only safe endpoints. Of course, if you need to do complicated operations on a large dataset, then this won't be viable since you'll need access to the query language. – Nicholas Pipitone Dec 27 '18 at 19:42