9

I created an online store for a friend of mine.

I created a system that shoots me an email any time there is a database error, that way if it is a bug in my code I can identify it and fix it. The email includes details about the query that failed, the data that was passed, session data, etc.

Well at 3:05 this morning I got about 120 emails in five min. for a database error and looking at what was happening I can quickly tell it was a mysql injection attack. After looking at a bunch of the things the attacker tried I'm kind of wondering what some of the sql commands they were trying to pass would actually do.

After looking through the whole database as well as the files on the site, I'm nearly 99% positive nothing was harmed, deleted or changed. Which makes me glad to know that I'm knowledgeable enough in my php to know how to prevent these things.

My question is, of the mysql commands below, what was the attacker trying to do?

 or 1=convert(int,(select cast(Char(114)+Char(51)+Char(100)+Char(109)+Char(48)+Char(118)+Char(51)+Char(95)+Char(104)+Char(118)+Char(106)+Char(95)+Char(105)+Char(110)+Char(106)+Char(101)+Char(99)+Char(116)+Char(105)+Char(111)+Char(110) as nvarchar(4000))))--

; if (1=1) waitfor delay \'00:00:07\'--

union all select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null--

999999.9 union all select 0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536--

leachiancs\' and \'x\'=\'x

Thanks for the info.

More Information

The query that was run for every attack was a simple

SELECT * FROM tableName WHERE $phpVar AND price!='sold' ORDER BY id DESC

The $phpVar is populated based on if a $_GET entry matches a term in an acceptedTerms array.

so the literal query that was running and returning an error was

SELECT * FROM tableName WHERE AND price!='sold' ORDER BY id DESC

That is what threw the follow

 Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND price!='sold' ORDER BY id DESC' at line 1
0

4 Answers 4

11

The mere fact your DB ran the quoted queries (throwing you the error reports) means you do have injection vulnerability in PHP code. Note, that some methods of inspecting DB tables structure involve running multiple queries with different parameters until DB throws an error and HTML page breaks. That means the failed queries are only tip of iceberg of all queries that were run.

There're 5 queries:

1) Invalidates all previous restrictive WHERE statements by effectively adding " or 1=1" statement.

2) Check for DOS attack against SQL server DB by adding another delay query.

3) Inspecting number of columns in DB table. Number of null-s on change between passed and failed queries is the one attacker looks for.

4) Same for user auth tables: ends query with likely non-existent ID then inspects number of columns. null-s are replaced by HEX values.

5) misguiding auth check by injecting crafted $user into query PHP string like

"SELECT ... WHERE ... AND name = '" + $user + "'"

(note the exact sequence of single and double quotes) It's likely the user 'leachiancs' was real aquired by previous investigations.

Queries (1)-(4) carry SQL comment in the end for invalidating the rest of originally intended query and defeating LIMIT or other validation WHERE constructs.

The main lesson here is using only parametrized queries and not trying to reproduce all that dirty work of proper DB+PHP escaping for all possible versions and quirks in syntax.

1
  • leachiancs is actually a legitimate $_GET request that can be accessed regular navigation. (i.e. /store/browse?species=leachiancs)
    – Ryan
    Apr 27, 2013 at 3:42
10

I had some fun "decoding" two queries, so here's how I did it in PHP :p

1) or 1=convert(int,(select cast(Char(114)+Char(51)+Char(100)+Char(109)+Char(48)+Char(118)+Char(51)+Char(95)+Char(104)+Char(118)+Char(106)+Char(95)+Char(105)+Char(110)+Char(106)+Char(101)+Char(99)+Char(116)+Char(105)+Char(111)+Char(110) as nvarchar(4000))))--

So let's start with copying this piece of code in a variable:

$str = 'Char(114)+Char(51)+Char(100)+Char(109)+Char(48)+Char(118)+Char(51)+Char(95)+Char(104)+Char(118)+Char(106)+Char(95)+Char(105)+Char(110)+Char(106)+Char(101)+Char(99)+Char(116)+Char(105)+Char(111)+Char(110)';
preg_match_all('/\d+/', $str, $m); // Some regex Fu to get the numbers
$sql = implode('', array_map(function($v){return chr($v);}, $m[0])); // converting the numbers to letters
echo $sql; // output

This gave me r3dm0v3_hvj_injection and after checking the mysql convert and cast syntax it seems that it's messed up here but I'm not sure, but basically this is an obfuscation method and the intent was to end up with or 1=1.


2) ; if (1=1) waitfor delay \'00:00:07\'--

Well ; declares end of statement and the rest seems a time based attack. Though this seems useless since the mysql api in php can't perform 2 queries at the same time. -- commenting the rest of the query.


3) union all select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null--

Seems useless to me, but it can determine how many columns you have since this will throw an error if the number of null doesn't match the number of columns you have.


4) 999999.9 union all select 0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536--

Well let's copy those hex vars in a variable and do some php-fu on it:

$str = '0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536,0x31303235343830303536';

$result = implode(',', array_map('hex2str', explode(',', str_replace('0x', '', $str)))); // PHP-Fu
echo $result;// output

// function hex to ascii
function hex2str($v){
    $r = '';$l = strlen($v)-1;
    for($i=0;$i <= $l;$i+=2){
        $r .= chr(hexdec($v[$i].$v[$i+1]));
    }
    return $r;
}

This gave me: 1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056,1025480056

I though this is strange since it's all the same let's reconvert it from hex to string:

echo hex2str('1025480056');

Output is %HV, with a normal hex editor there are 2 other obscure characters.

Well I think that the purpose may be the same as in attack #3 it's only obfuscated that is.


5) leachiancs\' and \'x\'=\'x

Simple where a=b and x=x query.


Conclusion:

Le wild r3dm0v3_hvj_injection and %HV appears, with 120 emails within 5mn I highly suspect that this is an evidence that this attack was performed with an automated SQLi tool Havij.

Prevention:

Use PDO or MySQLi with prepared statements.

7

Which makes me glad to know that I'm knowledgeable enough in my php to know how to prevent these things.

This is an extraordinarily dangerous mindset to take away from this incident.

That the attacker doesn't appear to have deleted data doesn't mean they didn't read data they shouldn't have. And the fact that they were able to enter inputs that caused database errors is damning evidence that you don't actually know how to prevent these things, as prepared statements and parameterized queries would have prevented even that.

As for the provided queries themselves, they mostly appear to be attempts to find a SQL injection vulnerability in the first place.

2
  • I guess now what I said does sound pretty ignorant, it sounded like the those queries were running on my DB when in fact they weren't (see edited original post) I have since updated the clients site to not even run the query unless at least one term from the "acceptedTerms" array matches. I should have done that in the first place.
    – Ryan
    Apr 27, 2013 at 3:39
  • 1
    Again, best is to use prepared statements and/or parameterized queries. Concatenating strings to form SQL is a fundamentally broken approach, as is ever trusting third-party inputs to your application. Your original approach that you've edited into the question appears to be trivially vulnerable to SQL injection. Just let $phpVar = "1=0; DROP TABLE users; --" Apr 28, 2013 at 2:49
3

It appears that the attacker was trying to do some investigation into what sort of attacks were possible with the vulnerability he had found.

Regardless; the lesson is always the same: use parameterized queries. If you have to remember to quote and escape your data, then you're doing it wrong.

If you'd like to learn more about how typical SQL injection attacks work, look into the automated tools such as burp suite which are typically used for this type of attack.

1
  • Burp Suite is usable for vulnerability detection and manual or semi-automatic exploitation using the intruder. But sqlmap is much better here, even for the detection.
    – Gumbo
    Apr 27, 2013 at 23:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .