I finally have a working draft of the SQL-injection methodology piece I have been promising the engagements team since January. It will not be published externally — it is internal team-handover material — but the substance is worth writing down here, because the pattern across the past nine months of engagements is more uniform than I expected and the structural recommendations are sharper than they were last time I wrote about this.
The starting observation is that across the engagements I have run through Hedgehog and the secondments since the start of the year, every single one has had at least one exploitable SQL-injection issue somewhere in scope. Sometimes it is a customer-facing web application; sometimes it is an internal management tool; sometimes it is a third-party plug-in or module that the client did not write themselves but is still responsible for. The proportion of engagements with at least one SQLi issue at this point is one hundred per cent. I had been writing reports for a couple of years suggesting it was around eighty per cent and feeling that was already a depressing number; the actual rate is worse, and the reason is partly that I have been more thorough this year, and partly that the third-party plug-in surface has grown.
The five places SQLi shows up, from most to least often: dynamic ORDER BY clauses in list views (sortable columns where the column name is taken from a query parameter and concatenated into the SQL); search functionality where the search term is passed into a LIKE or full-text query without parameterisation; pagination where the LIMIT and OFFSET values come from query parameters; bulk-action workflows where the action operates on a list of IDs taken from a form, which is concatenated into a WHERE id IN (...) clause; and authentication bypass via concatenated WHERE username = '...' AND password = '...'. The first two account for about seventy per cent of what I find. The fifth — classic authentication-bypass — is rarer than it used to be but still shows up in custom-written admin interfaces with surprising regularity. I had a Hedgehog engagement in May where the client's customer-portal admin login had a textbook ' OR '1'='1 vulnerability in 2011, in production, behind a WAF that did not catch it because the WAF rules were tuned to catch the textbook examples and the client had implemented their own slightly-different version which slipped through.
The defensive recommendations have not actually changed in five years, but the framing of them has sharpened. The structural answer is prepared statements with parameterised queries everywhere that user input is reaching SQL. Not "in most places". Not "unless we explicitly know it is safe". Everywhere. The reason I now write this as an absolute is that every exception I have seen in engagement work — every "we use parameterisation except for the dynamic-column ORDER BY because it would be inconvenient" — has been the actual exploited vector. Convenience-led exceptions to a parameterisation discipline are the way SQL injection vulnerabilities reach production. The argument I am now making to engagements team and to clients is that the discipline has to be absolute even at the cost of some application-design awkwardness, because the alternative is the Sony Pictures pattern where one missed exception at the bottom of an obscure feature ends up as the entry point.
For the harder cases — dynamic ORDER BY, dynamic table names, dynamic IN-clause lists — the solution is allowlisting at the application layer rather than parameterisation at the SQL layer. The application takes the user input, validates it against a pre-computed list of acceptable values, and substitutes the validated value into the query. This is more code than people would like, but it is the only structural answer for the class of cases where the user-controlled value cannot be parameterised because it is the column name or the table name rather than a value. Most of the engagements have not been doing this. Most have either been concatenating the user input directly, with the predictable consequence, or implementing some form of regex-based blacklist that the next-day's-tooling will defeat.
WAFs are the third pillar but they are an operational pillar, not a structural one. ModSecurity with the OWASP Core Rule Set will catch a meaningful proportion of textbook SQL injection attempts and a smaller proportion of automated scanner traffic. The proportion it catches goes down sharply when the attacker is targeting the application specifically and is willing to spend a day or two iterating around the rules. WAFs are a good time-buyer; they are not a defence. The clients I have been advising are starting to understand this, but there is still a meaningful contingent of "we have a WAF, that is the SQL-injection control on the risk register" that I am pushing back on. The risk register entry should be the parameterisation-and-allowlisting discipline at the application layer; the WAF should be supplementary.
The detection methodology I have been settling on for the engagement work has three layers. First, automated scanners — sqlmap is still the right tool for the textbook cases and the time-based blind cases; the recent versions handle a substantial fraction of the obfuscation tricks that newer applications throw at them. Second, manual exploration of the cases the scanners miss — dynamic-column patterns, pagination patterns, bulk-action patterns. The scanners are improving but the second-class case (where the parameter is in a less-conventional position) is still substantially manual. Third, source-code review where it is in scope, looking for the patterns that produce SQLi rather than for the SQLi itself. The third layer is the one I have been moving more weight onto over the past nine months, because it catches the cases where the application has been written carefully enough that black-box testing does not produce a clean signal but the structural pattern is still wrong.
The piece for the team has all this plus the report-writing patterns — how to frame the finding, how to give the client a remediation pathway that is structurally different from "fix this one specific issue", how to write the executive-summary line that produces an actual conversation with the board rather than a tick-box update. I will not reproduce that here because the report-writing patterns are more useful in their unwritten form than as an article. But the structural argument is one I am going to be making for the rest of the year and probably for some years after.
The three external references the team draft cites for new joiners coming up to speed are the OWASP Top 10 2010 release (where SQL injection sits at A1 for the third release running, and is unlikely to move down), the OWASP SQL Injection Prevention Cheat Sheet, and the relevant chapters of the Web Application Hacker's Handbook (which Stuttard and Pinto's second edition updated last year and which remains the right book for a methodical pen-tester to work through). None of those are surprising choices and that is the point — the structural references on this topic have been stable for a long time, the techniques are well-understood, and the persistent prevalence of SQL injection in production applications in 2011 is a defensive-discipline failure rather than a knowledge failure.
The next post is probably either Browne Jacobson, where I am taking on a CISO-advisor secondment from the middle of October, or whatever DigiNotar successor incident shows up over the next month. Or Duqu, which has been picked up by Crysys Lab in Hungary and is starting to look like the first piece of Stuxnet-derivative malware to appear in the wild. Whichever moves first.