Finding Interesting Database Data
Sat 19th Jun 10
I've recently started reading Justin Clarke's excellent book SQL Injection Attacks and Defense. In one of the early chapters he discusses the Asprox Botnet and explains the way it trawls through any databases it finds looking for columns that are of a type that will take text. Any that it finds it runs through all the rows adding its own malicious Javascript to the existing data.
Reading through the source code I figured it would be fairly easy to adapt it to look for "interesting" column names rather than just look for text fields and to report on what is found rather than injecting code so I started playing. I'm defining interesting as the kind of column names you'd stop and take a look at if you spotted them on an audit, for example names including the words:
- credit
- bank
- account
- password
The resulting SQL I came up with is this:
DECLARE @dbname nvarchar(255), @id int, @sql varchar (4000);
DECLARE table_cursor CURSOR FOR
SELECT name FROM sys.databases OPEN table_cursor FETCH NEXT FROM table_cursor INTO @dbname
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @sql = 'SELECT '
SET @sql = @sql + ' ''' + @dbname + ''' AS ''Database'', '
SET @sql = @sql + 'sys.schemas.name AS ''Schema'', '
SET @sql = @sql + 'sys.objects.name AS ''Table'', '
SET @sql = @sql + 'sys.columns.name AS ''Column'', '
SET @sql = @sql + 'sys.types.name AS ''Column Type'' '
SET @sql = @sql + 'FROM ' + @dbname + '.sys.columns '
SET @sql = @sql + 'INNER JOIN ' + @dbname + '.sys.objects ON sys.objects.object_id = sys.columns.object_id '
SET @sql = @sql + 'INNER JOIN ' + @dbname + '.sys.types ON sys.types.user_type_id = sys.columns.user_type_id '
SET @sql = @sql + 'INNER JOIN ' + @dbname + '.sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id '
SET @sql = @sql + 'WHERE (lower(sys.columns.name) LIKE ''%password%'' OR '
SET @sql = @sql + 'lower(sys.columns.name) LIKE ''%bank%'' OR '
SET @sql = @sql + 'lower(sys.columns.name) LIKE ''%credit%'' OR '
SET @sql = @sql + 'lower(sys.columns.name) LIKE ''%account%'') '
SET @sql = @sql + 'AND sys.objects.type=''U'';'
EXEC (@sql)
FETCH NEXT FROM table_cursor INTO @dbname
END
CLOSE table_cursor
DEALLOCATE table_cursor
It goes through each database then through each schema and table and reports on the location of the column it finds and the data type. Here is a screenshot of this being ran against a few sample databases.
I've tested this on MSSQL 2005 and from what I've seen it should work on 2008 as well but due to the meta column names changing it won't work on 2000, if any one has any burning desire for the code to work on 2000 let me know and I'll see what I can do. I don't think it is too much effort to change it.
Next I wanted to show some sample data from the table so auditors wouldn't need to go chasing red herrings. I figured showing the first, middle and last rows would be enough to give an an idea of the data. Something I quickly found out is that MSSQL doesn't have the concept of a LIMIT command as found in MySQL so after some googling I came up with this SQL to pull the data I was after:
declare @count int;
SELECT @count=count(*) FROM AdventureWorks.Person.Contact;
SELECT 'The table has ' + CAST (@count AS nvarchar) + ' rows';
IF (@count > 3)
BEGIN
DECLARE @middle int;
SET @middle = @count / 2;
WITH tmp AS (SELECT *,ROW_NUMBER() OVER (ORDER BY PasswordHash) AS rownumber FROM AdventureWorks.Person.Contact )
SELECT * FROM tmp WHERE rownumber BETWEEN 1 and 1;
WITH tmp AS (SELECT *,ROW_NUMBER() OVER (ORDER BY PasswordHash) AS rownumber FROM AdventureWorks.Person.Contact)
SELECT * FROM tmp WHERE rownumber BETWEEN @middle and @middle;
WITH tmp AS (SELECT *,ROW_NUMBER() OVER (ORDER BY PasswordHash) AS rownumber FROM AdventureWorks.Person.Contact )
SELECT * FROM tmp WHERE rownumber BETWEEN @count and @count ;
END
ELSE
BEGIN
SELECT * FROM AdventureWorks.Person.Contact
END
The table to select from here is hardcoded but is is fairly simple to merge this query with the one above and use the data collected above to generate the table and column name used here. I tried combining the SELECTs with a UNION rather than having three separate rows returned but that failed for some reason which is why you have the three WITH clauses. Here is a sample of it being ran against one of the columns found above.
Finally I thought wouldn't it be good if all this was automated for you so I put it all into a nice Metasploit module I called MSSQL Interesting Data Finder.
Unfortunately there is a bug in the current MSSQL module in Metasploit so I'm not able to pull back the sample data, I've left most of the code in the module to do this just commented out. HDM knows about the problem and it is on list of things to fix, once it is fixed I'll put the code back in and release a new version.
Another problem is that the module doesn't support Windows authentication so you can only authenticate using SQL authentication. As far as I know, once the underlying module supports Windows authentication my module should do so automatically. Again, this is on HDM's very long list of things to do.
The Future
I can see how this can easily be extended to searching MySQL databases and while I've never used them I'd say PostgreSQL and Oracle should also be possible. When I get chance I'll create the MySQL queries, the other two are left as an exercise for the readers!
Conclusion
I'd like to thank Justin for the great book, it has inspired me to look a lot closer when I find SQLi and look a lot harder for it when I don't find it to start with. I just need to find more time to be able to sit down and read so I can get beyond half way!
