title: Regex To Detect Basic SQL Injections, But Not As A Means to Prevent SQL Injections
body: <p>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.</p>
  
  <p>I also use <a href="https://github.com/presidentbeef/brakeman" rel="nofollow noreferrer">Brakeman</a> and the <a href="http://rails-sqli.org/" rel="nofollow noreferrer">Rails SQL Injection guide</a> regularly to review code.</p>
  
  <p>So there are three remaining reasons I would like to to block attempted SQL injectors.</p>
  
  <ol>
  <li>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.</li>
  <li>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.</li>
  <li>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.</li>
  </ol>
  
  <p>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 <a href="https://github.com/kickstarter/rack-attack" rel="nofollow noreferrer">rack-attack</a>.</p>
  
  <pre><code>injection_regex = /SOMEREGEXHERE/
  
  Rack::Attack.blacklist('sql injection blacklist') do |req|
    Rack::Attack::Fail2Ban.filter(req.ip, :maxretry =&gt; 5, :findtime =&gt; 10.minutes, :bantime =&gt; 24.hours) do
      CGI.unescape(req.query_string).match(injection_regex) || req.path.match(injection_regex)
    end
  end
  </code></pre>
  
  <p>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.</p>
  
  <p>In my searching I have found a number of questions on this subject, but they all seems to go <a href="https://stackoverflow.com/questions/45093/regex-to-detect-sql-injection">like this one</a>, 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.</p>
  
  <p>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?</p>