36

I'm wondering if it is possible to detect 100% of the possible SQLi attacks using a simple regex.

In other words, using very simple PHP code as an example:

if (preg_match("/select/i", $input)) {
    attack_log("Possible SELECT SQLi detected.")
}

The questions are:

  • Will that regex catch all possible SQLi attacks that use SELECT? If not, is it possible to change that regex so that it is going to detect all injections that rely on SELECT?
  • Is it possible to change that regex to so that it will catch all possible SQLi, so not only SELECT statements but also all the rest? I'm afraid that to achieve this I would need to add every possible SQL keyword to the regex, including "AND" and "OR".
  • Supposing it's not possible or feasible to detect all SQLi by trying to match all the possible SQL keywords, is there a limited subset of keywords that would allow me to detect the vast majority of possible attacks?
  • 10
    @reed The problem is that StackExchange isn't an open forum for discussing ideas. It's a Q&A site with restrictions on the types of questions that can be asked, in order to promote content that is useful to both those asking the questions and future readers. Questions like this, where the goal (intended or otherwise) is to ruminate over ideas rather than get a single concrete answer, aren't considered to be on-topic here. Since any correct answer would need to start with "don't do this, use parameters instead" it doesn't seem like there's much value that can come of it. – Polynomial Feb 19 at 15:03
  • 9
    I was sure there was a duplicate that I could point you towards, but I never found one. There should be one. – schroeder Feb 19 at 15:53
  • 35
    @reed Imagine the frustration of your pour users being frustrated they can't submit a post/comment/biography/whatever saying "I recommend you select the best tool for the job." ;) – marcelm Feb 19 at 22:01
  • 65
    If you're okay with some false positives, then this one will always catch all SQL injection: .* – Duncan X Simpson Feb 20 at 18:11
  • 33
    You don't even need a regex, just return true. Pretty high false-positive rate though. – Kevin Feb 20 at 23:15
168

Keyword filtering for SQLi is not a good technique. There are too many ways to bypass it.

Crazy things like sel/**/ect might work, for instance. Or playing games with substr(). And then there's EXEC('SEL' + 'ECT 1').

There are many guides on how to bypass common filtering techniques.

But then you might ask if there is a superset of things to filter for (like select and /**/ and substr and EXEC), but then the list gets very, very long, and you still might not get a comprehensive list.

The better approach is to understand the range of acceptable inputs and protect those or to make it ineffective to use SQLi through proper design.

  • 74
    Not to mention the false positive rate. The more things in your list, the more likely you'll match something in the query data - name, user name, email address, etc. Imagine if someone tried to register as bob@executiveselect.com or something along those lines. – alex.forencich Feb 19 at 20:30
  • 14
    @alex.forencich And sometimes the set of valid data may overlap with the set of malicious inputs. xkcd.com/327 – JAB Feb 19 at 23:37
  • 5
    "proper design" It would greatly improve the answer to mention what that proper design is, the parameterized queries part in particular. – jpmc26 Feb 20 at 4:54
  • 9
    @Nelson All that of "published material" mostly boils down to, "Use parameterized queries for any value that can't be hard coded directly into the query text, especially user input." (Literally. The only thing beyond this is the question of what to do if you need to have some kind of dynamic value for an identifier, and that isn't necessary in the vast majority of cases.) No harm would come from including a brief summary of that nature. It's really not as big a topic as you seem to think. – jpmc26 Feb 20 at 5:52
  • 4
    Also, this doesn't catch attacks that may disrupt the service, such as "; drop table users; -- which, simply, deletes the (hypothetical) users' table. This is far more devastating. Or, just a simple "; update users set password="<known value>"; --. – Ismael Miguel Feb 20 at 15:35
106

NO

Since every SQL injection is (by definition) valid SQL and since SQL is a context-free language (source), there is (again, by definition) no regex capable of matching an SQL injection, and trying to do so would probably give result similar to this.

As said by pretty much every comment, use the right tool for the job. In this case it's a prepared statement.

  • 18
    To be pedantic, this means that a regex that matches only SQL injections (and all of them) is impossible, which is true but not of much value - if it were ever to exist, a solution that also matched invalid SQL would be okay, which reopens the question. This is of course only a theoretical nitpick, in practice regexes are not the right tool or approach for the job. – Giulio Muscarello Feb 19 at 22:49
  • 5
    Another theoretical nitpick is that modern regexes are strictly more powerful than the original definition of "regular expression", due to lookarounds and backreferences and such. I wouldn't be surprised if more modern regexes are even Turing complete. – Pedro A Feb 20 at 0:09
  • 14
    That is does not follow. Every string of 1s is a valid mathematical expression, and mathematical expressions are a context-free language, but that doesn't mean I can't write a regex to match strings of 1s. – user253751 Feb 20 at 3:23
  • 1
    Thats not correct, to detect SQLi you do not detect valid SQL syntax, you just need to detect quote busting constructs. (However it is still a very brittle approach) – eckes Feb 20 at 8:33
  • 6
    @GiulioMuscarello /.*/ matches all valid SQL (and also some invalid SQL), but would presumably not be ok :) – mbrig Feb 20 at 22:01
56

Technically, this is completely possible (though doing so also renders the database useless):

  • .+ Will indeed detect any possible SQLi.

However, it will also detect any attempt to do normal queries(or any text at all), rendering the database completely useless.

You could equally say that turning the database off protects from SQLi. It's true, but it also renders the database useless for it's intended purpose.

Use prepared statements or parameterized queries. They exist to solve this issue.

  • 18
    Technically correct is the best kind of correct.. but not in this scenario :P – Dan Pantry Feb 19 at 22:23
  • 28
    @DanPantry - Nothing in the question specified that it had to allow anything. – Fake Name Feb 19 at 22:31
  • 3
    This should maybe have been a comment instead of an answer, but it has a good point within it. Instead of focusing on how to block all attacks, OP should focus on what legitimate strings need to be allowed. – Nate Eldredge Feb 20 at 1:26
  • 1
    This answer could be improved by mentioning how even the "select" regexp will cause a lot of false matches, for any text containing the word 'select'. – jpa Feb 20 at 13:10
  • 1
    @FakeName please see the question's edit history. False positives were originally not a concern but became a concern after much discussion in the comments. – schroeder Feb 20 at 13:16
21

I'm wondering if it is possible to detect 100% of the possible SQLi attacks using a simple regex.

The very fact that you're asking the question this way shows you're thinking about the problem incorrectly.

SQL injection is not a vulnerability in data. It's a vulnerability in application code that handles that data.

For example: right now I'm typing a "SQL Injection" into a textarea on a website! And I can type something like ' -- DROP TABLE Users; right here! Is my answer a SQL injection attack? Of course not.

Now, you could argue that what you're trying to do is to detect attempted SQL injection attacks. But now you need to determine the intent of some input. If I type a bare apostrophe into a field, is that a typo or a an attempted SQL injection attack? Can you detect 100% of my intentions? Of course not.

Fundamentally, attempting to identify possible attacks means that your detect rate can never be 100%.

Since a SQL injection vulnerability only exists in code, and not data, any analysis which only considers data is subject to at best a very large false positive rate. Any solution you devise which could possibly match all actual attack traffic would also interpret a very large quantity of legitimate traffic to this website, and many others, as "attacks" when no such attack was intended.

  • 3
    "Is my answer a SQL injection attack?" Well, it could be ;) – ypercubeᵀᴹ Feb 21 at 14:57
  • @ypercube: Well, maybe it's a matter of semantics. I would say: it could trigger a SQL injection effect, but to be a SQL injection attack, the effect would have to be maliciously motivated. And that's somewhat my point: if you look only at the data, you can't know for certain -- at best you can apply a heuristic, and a heuristic cannot have 100% accuracy, by definition. – Daniel Pryden Feb 21 at 15:07
8

No. First of all, there are several evil things you can do with SQL injections which don't require the use of the SELECT keyword, like the infamous universal password ' OR '1' = '1 or the common username Robert'); DROP TABLE Students;--. Also, "select" is a very common word in the English language which might appear in completely benign ways in all kinds of different contexts. So if you filter any input which matches /select/i you are going to get a ton of false positives (16 false positives and counting just on the website your are reading right now, for example).

If you want to sanitize inputs before sending data to your database, then PHP has a handy function for that purpose (these are those for mySQL, any other database APIs should provide a similar function tailored for that specific database syntax).

But when you are trying to protect yourself against SQL Injections by blocking certain inputs, then you are fighting the battle at the wrong frontline. It would be much smarter to defend yourself against SQL injections by stopping to create SQL statements by string concatenation. Common alternative ways to interact with databases in a way which makes SQL injections difficult to write unintentionally are:

  • Use an ORM wrapper which writes SQL queries for you
  • Use parameterized queries (also known as prepared statements)
  • Use a programming language where SQL is part of the language syntax
  • Use stored procedures
  • 5
    Parameterized queries are not "also known as" prepared statements. Parameterized queries are one form of a prepared statement, but not the only form. The two should not be conflated. – dotancohen Feb 21 at 12:20
  • 2
    ORM wrappers are notoriously bad at creating well-formed, efficient SQL. I'd put that at the end of your recommendations, if I'd even add it to the list. – Max Vernon Feb 21 at 15:00
  • @MaxVernon - ORMs make for clear and concise code, which 90+% of the time is more important than squeezing extra performance out of the DB. – paj28 Feb 22 at 8:17
  • 2
    Actually, @paj28, I disagree that ORMs make for clean and concise code. What they actually do is obscure arguably the most important code in the entire project. That is, if you believe the data is the raison d'être for the project in the first place. – Max Vernon Feb 22 at 16:12
  • 1
    Whether you like or dislike ORMs is a software engineering question, not a security question. – Philipp Feb 22 at 16:25
-2

No. A regexp implementation is typically a regular language (or an extension thereof that allows parsing of some non-regular grammars.)

SQL on the other hand, it is firmly a non-regular context free language. Ergo, it cannot be parsed by a regular-language regexp.

If you were to use a regexp extension that uses grouping, you might be able to parse valid SQL, but also invalid SQL or things that might look like SQL but aren't.

The best thing to be sure is to use a context-free grammar that expresses the SQL dialect you are looking for, if you really were keen to detect a SQL injection attack.

ps. My personal opinion is that one of the best ways to avoid or minimize sql injection attacks is to never invoke plain SQL from the application (relying instead on stored procedures, which themselves sanitize their arguments.)

A lot more elbow grease, and not necessarily 100% iron-clad injection-proof. But it does work very well. Caveat emptor obviously, for other developers might have different experiences with such an approach.

  • 1
    You appear to be jumping ahead in some concepts. How do we get to "context-free" from "can be described in BNF"? I think you are bringing in volumes of other concepts without explaining them (or assuming they are true). While I am not trying to assert something else to be true, it is not clear from your answer how your assertions are true. Can you connect some dots? – schroeder Feb 20 at 14:03
  • 6
    Big difference between being right and being useful. This answer is not useful without the reader delving into several computer science concepts to begin to understand the point you are making. I'm asking you to edit the answer to connect some dots to make this answer more useful. – schroeder Feb 20 at 15:39
  • 2
    To be fair, SQL injection attacks aren't necessarily valid SQL. (Once they are put into the rest of the query they are.) Because of that it isn't an issue if we can or cannot detect valid SQL with regex. – Captain Man Feb 20 at 16:18
  • 1
    A non-CF language may have a regular lexical grammar, so you don't need to match the whole SQL grammar. Just identify strings that might break token boundaries when interpolated into an existing sequence of tokens. That said, I agree with all the posters who have pointed out that filters have a poor track record compared to safety-by-construction. – Mike Samuel Feb 20 at 20:37
  • 4
    @luis.espinal I have no idea why you are getting both emotional and resistent to simply editing your answer to explain what you mean. If you see needing to explain yourself as a challenge to your ability to know the correct answer, then you need to rethink your motivations for answering at all. – schroeder Feb 22 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.