ruby on rails - Regex To Detect Basic SQL Injections, But Not As A Means to Prevent SQL Injections

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.

1. We get a lot of script kiddies trying to hack us. Most of these are
   already blocked at least once because we have a file extension
   whitelist system that blocks most of these when they attempt to
   request a php file. Once though they get blacklisted the
   first time, on the second round they normally try to throw the SQL
   injection book at us. So I would like to flag these kiddies early to
   save bandwidth, and if a real actor where to attack us, it would be
   
   easy to sort them out from all the script kiddies.


2. Slow down any attempts at probing our defenses. This won't really
   effect sophisticated distributed attacks, but might slow down an
   actor that is somewhere between a script kiddie and a super hacker.


3. Flag all blocked requests and set up a log notifications which we
   would then be informed of by our logging service to increase our
   security awareness.

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?

Answer

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