Is it possible to detect 100% of SQLi with a simple regex?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

Of course it better to use Parameterized Queries if it is available. But sometime it is not an option. For example https://stackoverflow.com/questions/13831472/using-a-variable-in-openrowset-query.

In this case, input validation using regex might be the only choice to reduce the chance of sql injection. If you know the pattern of the string variables you are going to use, the chance to avoid sql injection is high. In above example, if the input variable is the bulk parameter (file path), then lots of special characters are not allowed. The regex pattern will be '[a-zA-Z0-9_/.]*' or simply '[\\w/.]*' . I think it is very hard, if not 100% impossible, to write any injection sql with only these characters.

Answer 7

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.

Answer 8

No, Your application will not be 100% secured if you use some pattern matching or regex to identify SQL Injection attack. You will face miss detection of SQLI if you use such kind of approach. The best way to achieve 100% protection is using Runtime Application Self-protection (RASP) in your application. In case of Web application Firewall(WAF), you will get false negative results.