SQL Injection Tip of the Day: Table and Column enumeration in a single row

I will be getting around to putting together a comprehensive cheat sheet for sql injection. In the meantime, I figured I would release bits and pieces that I have found particularly useful. Today I want to talk about getting database schema metadata from Microsoft SQL Server 2005 and 2008(the technique may be slightly different for 2000).

This assumes you already have a sql inejction vector that allows serialisation of queries and union queries, and that the db user has create rights, although it can be modified to use update/insert into existing tables instead. So let's say you have found a sql injection vulnerability, but it will only return one row of results. That makes it an exceptionally arduous task to enumerate all the tables and their columns, one at a time. You can concatenate rows very easily, but you can't use concatenation against columns. This is where arrays come in to save the day. The first step is to inject a string like this:

';CREATE TABLE CT1 (tablenames VARCHAR(8000));DECLARE @tablens varchar(7999); SELECT @tablens=COALESCE(@tablens+';' , '') + name from dbo.sysobjects where xtype='U'; INSERT INTO CT1(tablenames) Select @tablens;--

Remember to encode as needed. This creates a new table called CT1 with a max size varchar as it's only column. It then creates an array called tablens, and selects the entire name column from dbo.sysobjects where the object is a user table. Finally it inserts the array in semicolon delimited format into our newly created table.

Then we just do something silly like:
' UNION Select tablenames,@@rowcount,@@servername,1,2,3,4,5 from CT1;DELETE from CT1;--

This of course returns the results, and clears the table out from behind us. We should now have all of the tablenames in this database. Using that we use the same attack vector, just slightly tweaked:


';DECLARE @tablens varchar(7999); SELECT @tablens=COALESCE(@tablens+',' , '') + name from syscolumns where id=object_id('Table1'); INSERT INTO CT1(tablenames) Select @tablens;--
and
' UNION Select tablenames,@@rowcount,@@servername,1,2,3,4,5 from CT1;DELETE from CT1;--

Now what I did, after making sure it worked, was to create a quick perl script. This perlscript took the list of tablenames, and custom generated the above attack strings for each table and put them into a text file. I then loaded this file into Burp Intruder as a custom payload, and let it run. Burp has enumerated almost all of the tables in a couple of minutes(this db had over 100 tables). Then it's just a matter of dumping all the results somewhere and pouring over it. Using this method, you can go from your proven sql injection vector to a map of the whole database in a very short amount of time.

And as ever, this showcases why Burpsuite Pro is a tester's best tool. How I ever worked without it is a mystery.