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

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:

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.

3 Answers
3

In my shop we have two rules.

intval()

Jones

O'Brien

St. John-Smythe

'

-

.

If 2 is too hard follow rule 1.

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

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:

Few steps but bulletproof.

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.

SELECT

root

SET

LOAD DATA

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.