What pattern to check on an SQL query for possible injection?

Question

I want to detect possible SQL injection atack by checking the SQL query. I am using PDO and prepared statement, so hopefully I am not in the danger of getting attacked by someone. However, what I want to detect is the possibility of input/resulting query string that may become a dangerous query. For example, my app--properly--will never generate "1=1" query, so I may check the generated query string for that, and flag the user/IP producing that query. Same thing with "drop table", but maybe I can check only by looping the input array; or maybe I should just check to the generated query all over again. I am using MySQL, but pattern for other drivers are also appreciated.

I have read RegEx to Detect SQL Injection and some of the comments are heading in this direction. To my help, I'm developing for users that rarely use English as input, so a simple /drop/ match on the query may be enough to log the user/query for further inspection. Some of the pattern I found while researching SQL injection are:

semicolon in the middle of sentence -- although this may be common
double dash/pound sign for commenting the rest of the query
using quote in the beginning & ending of value
using hex (my target users have small to low chance for inputting 0x in their form)
declare/exec/drop/1=1 (my app should not generate these values)
html tag (low probability coming from intended user/use case)
etc.

All of the above are easier to detect by looping the input values before the query string is generated because they haven't been escaped. But how much did I miss? (a lot, I guess) Any other obscure pattern I should check? What about checking the generated query? Any pattern that may emerge?

tl;dr: What pattern to match an SQL query (MySQL) to check for possible injection? I am using PDO with prepared statement and value binding, so the check is for logging/alert purposes.

Use parameters. Then you don't need to worry about SQL injection. — Gordon Linoff, Jun 30 '18 at 13:35
The answer is the same as the one from your link: "Don't do it. You're practically guaranteed to fail. Use PreparedStatement (or its equivalent) instead." — Paul Spiegel, Jun 30 '18 at 13:49
I'm not too wory about the injection. What I want to know is if any user is *trying* to "inject" the app. — D. Base, Jun 30 '18 at 15:32

score 2 · Answer 1 · answered Jun 30 '18 at 13:56

In my shop we have two rules.

Always use parameters in SQL queries.
If for some reason you can't follow rule one, then every piece of data put into a query must be sanitized, either with intval() for integer parameters or an appropriate function to sanitize a string variable according to its application data type. For example, a personal name might be Jones or O'Brien or St. John-Smythe but will never have special characters other than apostrophe ', hyphen -, space, or dot. A product number probably contains only letters or numbers. And so forth.

If 2 is too hard follow rule 1.

We inspect code to make sure we're doing these things.

I follow rule 1. That's not the point, though. What I want to do is to log those who are attempting to do sql injection, so I was thinking to check the input against some known injection pattern so I can mark those users; checkng the input is wee bit too cumbersome as in double loops before sanitizing, while query checking is simpler because I can hook right before/after query execution. — D. Base, Jun 30 '18 at 18:17

score 1 · Answer 2 · answered Jun 30 '18 at 14:33

1

But how much did I miss?

You guess right. Creating a huge blacklist wouldn't make your code immune. This approach is history. The other questions follow the same idea.

Your best bets are:

Validating input data (input doesn't necessarily come from an external party)
Using prepared statements.

Few steps but bulletproof.

answered Jun 30 '18 at 14:33

revo

47,783
14
74
117

I'm not trying to blacklist query. I want to know if there's any of the user is trying to. – D. Base Jun 30 '18 at 15:36
1

That's called blacklisting. – revo Jun 30 '18 at 16:10
I was thinking blacklisting would make the app reject the query. Anyway, the query, especially the _description_-like text input will not reject the user's input, but I want the app to log the user writing that kind of input. And, yes, all inputs use prepared statements and parameter binding for the query itself. – D. Base Jun 30 '18 at 17:41

score 0 · Answer 3 · answered Jul 01 '18 at 02:43

Not possible.

You will spend the rest of your life in an armament race -- you build a defense, they build a better weapon, then you build a defense against that, etc, etc.

It is probably possible to write a 'simple' SELECT that will take 24 hours to run.
Unless you lock down the tables, they can look, for example, at the encrypted passwords and re-attack with a root login.
If you allow any type of string, it will be a challenge to handle the various combinations of quoting.
There are nasty things that can be done with semi-valid utf8 strings.
And what about SET statements.
And LOAD DATA.
And Stored procs.

Instead, decide on the minimal set of queries you allow, then parameterize that so you can check, or escape, the pieces individually. Then build the query.

What pattern to check on an SQL query for possible injection?

3 Answers3