Let me start off by saying that I am confidante in the measures I have taken to make sure SQL Injection attacks fail. All SQL query values are done via active record prepared statements, and all operators if not hard coded are done through a system of numeric whitelists. Meaning if someone want to search via "ILIKE" they would pass a 6, and if they want to search via "=" they pass a 1, etc.
I also use Brakeman and the Rails SQL Injection guide regularly to review code.
So there are three remaining reasons I would like to to block attempted SQL injectors.
At the moment my idea is to run a simple regex match against the request path, and parameters in order to flag the most flagrant SQL injection attempts and blacklist those ips, so something like this, using rack-attack.
injection_regex = /SOMEREGEXHERE/
Rack::Attack.blacklist('sql injection blacklist') do |req|
Rack::Attack::Fail2Ban.filter(req.ip, :maxretry => 5, :findtime => 10.minutes, :bantime => 24.hours) do
CGI.unescape(req.query_string).match(injection_regex) || req.path.match(injection_regex)
end
end
My issue though is creating a regex that correctly flags a simple SQL injection attempt, but doesn't cause any issues for regular users. I figure some false positives are ok, and that is why the above blacklisting system doesn't blacklist after the first match.
In my searching I have found a number of questions on this subject, but they all seems to go like this one, person ask questions on using regex to detect SQL injection, another person answers you shouldn't be stopping SQL injection this way, the person that asked the questions responds that they wouldn't be using the regex to stop, but merely to detect, then a bunch of un helpful comments follow.
So, is there even a possibility for such a regex to work in only as a means of detection, with minimal false positives, or is this such a rabbit whole that it wouldn't be worth the effort?
This link should give you the patterns to start with.
http://larrysteinle.com/2011/02/20/use-regular-expressions-to-detect-sql-code-injection/
Text Blocks
'(''|[^'])*'
SQL Statements
\b(ALTER|CREATE|DELETE|DROP|EXEC(UTE){0,1}|INSERT( +INTO){0,1}|MERGE|SELECT|UPDATE|UNION( +ALL){0,1})\b