First define what a "likely SQL injection attack" looks like. An attacker may simply start with, for example, O'Connor. If that produces an error, the attacker knows you're not escaping input. Does O'Connor constitute an attack?
–
decezeAug 12 '11 at 23:52

9 Answers
9

If the output of mysql_real_escape_string is different to the input, then the input contained unsafe characters. You could infer that the user might have been attempting an attack, especially if the field in question is one where you'd normally expect a low number of unsafe characters (e.g. a zip code).

But it might also be because their name happened to be Robert'); DROP TABLE Students; --.

So in general, there is no way to do this that's even close to reliable.

This is a very hard problem to solve, automatically detecting which SQL queries are attacks (or simple mistakes).

There are companies who make products that attempt to do this, like GreenSQL and DB Networks ADF-4200, by applying heuristic tests to see if queries look "suspicious."

But even they rely more on whitelisting the queries that your application runs. The heuristics are known to have both false positives and false negatives. And there are whole categories of queries that neither whitelisting nor heuristics can catch, like calls to stored procedures.

Someone mentioned mod_security too, but this requires a lot of configuration, and then you're back to hand-coding rules for whitelisting your application's legitimate queries.

Just follow good practices like parameterizing and whitelisting, so that user input (or any untrusted content) is never evaluated as code.

I would say it's safer to assume that ALL user input is an attack when you write your code and make your program secure enough to mitigate the attack rather than trying to retroactively fix something that may or may not have been an attack.

Oh, I do. But what I wanted to do was find a way to zero in on IPs in which the malicious input was unusually high, and blacklist that IP.
–
Bad ProgrammerAug 13 '11 at 0:22

The problem is that there are so many types of attacks, and more are being thought of all the time (SQL injection, CLI injection, XSS, DDoS, brute force, CSRF, social engineering, etc).
–
MikeAug 13 '11 at 0:32

Actually there is no certain way !
but it is possible to guess attacks !
simply check for most common usefull sql injection structures
for example scan this words (in case insensitive) in your inputs :

union
select
drop
--
;

if you know how to stop sql injection , you shouldn't be worried and you can run the query safely. but as I understood you want to detect injections , so i prefer you just log suspicious inputs and then decide manually ! in most cases logged queries are real injections.

You may search for certain keys (union,delete,drop, ...) and so on. Search for --, \n (new lines) if you are sure that original query would never ever have it. Create a query that always returns some value - if with user malicious input it doesn't that may indicate an attack.

But because users tend to ALWAYS make mistakes or attack servers (number, i will write letters to check how much mess it will make...) all user inputs should be filtered before used.

which bromide has downvoted my post, I said it was joke... poor bromide...
–
TMSAug 13 '11 at 0:15

-1: "parse the query without running somehow" - how would this help?
–
Oliver CharlesworthAug 13 '11 at 0:16

when downvoting, write what you consider wrong about my post! There's nothing false in it, I was the first who proposed the syntax method here...
–
TMSAug 13 '11 at 0:18

@Oli - to detect whether it was an attempt of sql injection, that what the OP asked.
–
TMSAug 13 '11 at 0:19

This will tell you no more than comparing input and output of mysql_real_escape_string. And how do you propose to determine whether the resulting query string was affected or not?
–
Oliver CharlesworthAug 13 '11 at 0:21