' 1=1 GROUP BY CONCAT_WS ('~',version(), FLOOR(rand(0)*2)) having min(0)

1>> My first question is what is CONCAT function is being used for in this query

2>> And Why do we use having min(0) in here.

  • possible duplicate of What is SQL injection? – tim May 15 '15 at 19:21
  • 3
    @tim - seems like he has a grasp of what SQL Injection is, but is trying to understand what specifically this attack is trying to do. Doesn't seem like a duplicate to me. – Abe Miessler May 15 '15 at 19:57
  • The CONCAT, rand, and min look like attempts to (partially) disguise the attack string. Perhaps from some automated detection algorithm? – Neil Smithline May 15 '15 at 20:18
  • @AbeMiessler You are probably right. Normally, I'm careful what I flag, but here I might have jumped the gun a bit. The question also got improved a bit already, and I still think it could be improved further (what CONCAT does seems trivial, but eg why is there FLOOR(rand(0)*2) in addition to having min might be an interesting question). – tim May 15 '15 at 20:29
  • I can't flag it as a duplicate again, but Error-based SQL injection is an exact duplicate of this question. – tim May 15 '15 at 20:38

Well, let's dissect it:

  • CONCAT_WS - Concatenate with separator.
  • version() - Returns the version of MySQL.
  • FLOOR(rand(0)*2) - Emits the following sequence of numbers: 0, 1, 1, 0, 1, ...
  • having min(0) - By itself, this is illegal, as the HAVING clause requires a condition. This, and the fact that there's no ; -- on the end, implies that the injection is expecting a condition at the end.

So, this concatenates the version string and a sequence of numbers across a group. The sequence of numbers for the group clause is derived from FLOOR(rand(0)*2).

The thing about GROUP is that it requires unique group keys. Since version() will return the same value each time, concatenating that and the output of FLOOR(rand(0)*2) three times will result in two different numbers (0, 1) then a second instance of 1, which causes an error (duplicate entry for group key), which is displayed back to the user. That error looks something like this:

Duplicate entry '5.6.24-1~1' for key 'group_key'

This causes the version number to be leaked back in the response as part of an error-based SQL injection attack. This can then be expanded to perform other attacks.

As for why the attacker chose FLOOR(rand(0)*2), it's a dead-simple expression which is reliable (the rand() function is a PRNG which takes a seed parameter; the same seed produces the same sequence always) and satisfies the requirement of having a duplicate output relatively quickly. You could pick a value other than 0 and it would still work.

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.