Class: Dorothy::Insertdb

Inherits:
Object
  • Object
show all
Defined in:
lib/dorothy2/do-utils.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ Insertdb

Returns a new instance of Insertdb.



203
204
205
 
# File 'lib/dorothy2/do-utils.rb', line 203

def initialize
  @db = PGconn.open(:host=> DoroSettings.dorothive[:dbhost], :dbname=>DoroSettings.dorothive[:dbname], :user=>DoroSettings.dorothive[:dbuser], :password=>DoroSettings.dorothive[:dbpass])
end

Class Method Details

.escape_bytea(data) ⇒ Object 



309
310
311
312
 
# File 'lib/dorothy2/do-utils.rb', line 309

def self.escape_bytea(data)
  escaped = PGconn.escape_bytea data
  return escaped
end

Instance Method Details

#analysis_queue_add(bin, source, filename, profile = 'default', priority = 0, user = 'system', sigh_id) ⇒ Object 



358
359
360
361
362
363
364
365
366
 
# File 'lib/dorothy2/do-utils.rb', line 358

def analysis_queue_add(bin, source, filename, profile='default', priority=0, user='system', sigh_id)
  id =  "default"
  time = Util.get_time
  values = [id, time, bin, priority, profile, check_source_db(source)["id"], user, filename, "pending", sigh_id.to_i]

  raise "A DB error occurred while adding data into the anaylsis_queue table" unless insert("analysis_queue", values)

  @id = get_curr_queue_id    #race condition?
end

#analysis_queue_mark(id, status) ⇒ Object 



368
369
370
371
372
373
374
375
 
# File 'lib/dorothy2/do-utils.rb', line 368

def analysis_queue_mark(id,status)
  begin
    @db.exec("UPDATE dorothy.analysis_queue set status = '#{status}' where id = '#{id}'")
  end
rescue PG::Error => err
  LOGGER.error "DB","Error while updating analysis_queue_mark_analysed table:  " + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
  raise err
end

#analysis_queue_mark_all ⇒ Object

Mark all the pending analyses as analyzed



390
391
392
393
394
395
 
# File 'lib/dorothy2/do-utils.rb', line 390

def analysis_queue_mark_all
  analysis_queue_pull.each do |qentry|
    analysis_queue_mark(qentry["id"], "analysed")
  end
  LOGGER.debug "DB", "Pending analyses removed from the queue"
end

#analysis_queue_pull ⇒ Object 



377
378
379
380
381
382
383
384
385
386
387
 
# File 'lib/dorothy2/do-utils.rb', line 377

def analysis_queue_pull
  @bins = []
  begin
    @db.exec("SELECT analysis_queue.id, analysis_queue.binary, samples.path, analysis_queue.filename, analysis_queue.priority, analysis_queue.profile, analysis_queue.source, analysis_queue.date FROM dorothy.analysis_queue, dorothy.samples WHERE analysis_queue.binary = samples.sha256 AND analysis_queue.status = 'pending' ORDER BY analysis_queue.priority DESC, analysis_queue.id ASC").each do |q|
      @bins.push q
    end
  rescue PG::Error => err
    LOGGER.error "DB","Error while fetching traffic_dumps table " + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
  end
  @bins
end

#analysis_queue_view ⇒ Object

List pending analyses



398
399
400
401
402
403
404
405
406
407
 
# File 'lib/dorothy2/do-utils.rb', line 398

def analysis_queue_view
  LOGGER.info "QUEUE", "Pending analyses:"
  puts "\n[" + "-".red + "] " + "\tID\tAdded\t\t\tSource\tFilename"
  puts "[" + "-".red + "] " + "\t--\t-----\t\t\t------\t--------\n"

  analysis_queue_pull.each do |qentry|
    puts "[" + "*".red + "] " + "\t#{qentry["id"]}\t#{qentry["date"]}\t#{qentry["source"]}\t#{qentry["filename"]}"
    puts ""
  end
end

#begin_t ⇒ Object 



207
208
209
 
# File 'lib/dorothy2/do-utils.rb', line 207

def begin_t
  @db.exec("BEGIN")
end

#check_source_db(source) ⇒ Object 



428
429
430
431
432
433
434
435
 
# File 'lib/dorothy2/do-utils.rb', line 428

def check_source_db(source)
  begin
    r = @db.exec("SELECT sources.id, sources.sname, sources.stype, sources.host, sources.localdir FROM dorothy.sources WHERE sources.disabled = FALSE AND sources.sname = '#{source}'")
    r.first.nil? ? nil : r.first
  rescue PG::Error => err
    LOGGER.error "DB", "An error occurred while accessing sources table" + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
  end
end

#check_sources_modifications(source) ⇒ Object 



447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
 
# File 'lib/dorothy2/do-utils.rb', line 447

def check_sources_modifications(source)

  db_sources = enabled_sources_db

  unless db_sources.nil?
    db_sources.each do |s|
      unless source.has_key?(s["sname"])
        LOGGER.warn "CheckCONF", "#{s["sname"]} was removed, disabling"
        disable_source_db(s["id"])
      end
    end
  end


  source.each do |k,v|
      values = ['default', k, v["type"], 'default', v["host"], 0, Util.get_time, Util.get_time, v["localdir"]]

      db_source = check_source_db(k)
      if db_source.nil?
        LOGGER.warn "CheckCONF", "#{k} Added"
        insert("sources", values)
      elsif v["type"] != db_source["stype"] || v["host"] != db_source["host"] || v["localdir"] != db_source["localdir"]

        LOGGER.warn "CheckCONF", "#{k} MODIFIED"

        disable_source_db(db_source["id"])
        LOGGER.warn "CheckCONF", "#{k} DISABLED"

        insert("sources", values)
        LOGGER.warn "CheckCONF", "#{k} ADDED"
      end
    end
end

#close ⇒ Object 



219
220
221
 
# File 'lib/dorothy2/do-utils.rb', line 219

def close
  @db.close
end

#commit ⇒ Object 



211
212
213
 
# File 'lib/dorothy2/do-utils.rb', line 211

def commit
  @db.exec("COMMIT")
end

#disable_source_db(id) ⇒ Object 



418
419
420
421
422
423
424
425
426
 
# File 'lib/dorothy2/do-utils.rb', line 418

def disable_source_db(id)
  begin
    @db.exec("UPDATE dorothy.sources set disabled = true, last_modified = '#{Util.get_time}'where id = '#{id}'")
    true
  rescue PG::Error => err
    LOGGER.error "DB", "An error occurred while adding data into sources table " + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
    false
  end
end

#enabled_sources_db ⇒ Object 



437
438
439
440
441
442
443
444
 
# File 'lib/dorothy2/do-utils.rb', line 437

def enabled_sources_db
  begin
    r = @db.exec("SELECT sources.id, sources.sname, sources.stype, sources.host, sources.localdir FROM dorothy.sources WHERE sources.disabled = FALSE")
    r.first.nil? ? nil : r
  rescue PG::Error => err
    LOGGER.error "DB", "An error occurred while accessing sources table" + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
  end
end

#find_last_conf_chksum(conf) ⇒ Object 



409
410
411
412
413
414
415
416
 
# File 'lib/dorothy2/do-utils.rb', line 409

def find_last_conf_chksum(conf)
  begin
    r = @db.exec("SELECT cfg_chk.md5_chksum FROM dorothy.cfg_chk WHERE cfg_chk.conf_file = '#{conf}' ORDER BY cfg_chk.id DESC LIMIT 1")
    r.first.nil? ? nil : r.first["md5_chksum"]
  rescue PG::Error => err
    LOGGER.error "DB","Error while fetching conf_chk table " + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
  end
end

#find_pcap ⇒ Object 



483
484
485
486
487
488
489
490
491
492
493
 
# File 'lib/dorothy2/do-utils.rb', line 483

def find_pcap
  @pcaps = []
  begin
    @db.exec("SELECT traffic_dumps.sha256, traffic_dumps.pcapr_id, traffic_dumps.size, traffic_dumps.binary, traffic_dumps.parsed, samples.md5 as \"sample\", analyses.date as \"date\", analyses.id as \"anal_id\" FROM dorothy.traffic_dumps, dorothy.samples, dorothy.analyses WHERE analyses.traffic_dump = traffic_dumps.sha256 AND analyses.sample = samples.sha256 AND traffic_dumps.parsed = false").each do |q|
      @pcaps.push q
    end
  rescue PG::Error => err
    LOGGER.error "DB","Error while fetching traffic_dumps table " + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
  end

end

#find_seq(seq) ⇒ Object 



326
327
328
 
# File 'lib/dorothy2/do-utils.rb', line 326

def find_seq(seq)
  @db.exec("SELECT currval('dorothy.#{seq}')")
end

#find_vm(os_type, os_version, os_lang) ⇒ Object 



499
500
501
502
503
504
505
506
507
508
 
# File 'lib/dorothy2/do-utils.rb', line 499

def find_vm(os_type, os_version, os_lang)
  vm = @db.exec("SELECT id, hostname, ipaddress, username, password FROM dorothy.sandboxes where os = '#{os_type}' AND version = '#{os_version}' AND os_lang = '#{os_lang}' AND is_available is true").first
  if vm.nil?
    LOGGER.debug "DB","At this time there are no free VM available that matches the selected profile"  if VERBOSE
    return false
  else
    @db.exec("UPDATE dorothy.sandboxes set is_available = false where id = '#{vm["id"]}'")
    return vm["id"].to_i, vm["hostname"], vm["ipaddress"], vm["username"], vm["password"]
  end
end

#flush_table(table) ⇒ Object 



330
331
332
 
# File 'lib/dorothy2/do-utils.rb', line 330

def flush_table(table)
  @db.exec("TRUNCATE dorothy.#{table} CASCADE")
end

#free_vm(vmid) ⇒ Object 



510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
 
# File 'lib/dorothy2/do-utils.rb', line 510

def free_vm(vmid)
  r = @db.exec("SELECT hostname FROM dorothy.sandboxes where id = '#{vmid}' AND is_available is false")
  if !r.first.nil? #check if the issued VM is already free
    begin
      @db.exec("UPDATE dorothy.sandboxes set is_available = true where id = '#{vmid}'")
      LOGGER.info "DB", "VM #{vmid} succesfully released"
      return true
    rescue PG::Error => err
      LOGGER.error "DB", "An error occurred while releasing the VM " + err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
      return false
    end
  else
    LOGGER.warn "DB", "Dorothy is trying to release the VM #{vmid} that is already available!!"
    return false
  end
end

#get_anal_id ⇒ Object 



292
293
294
 
# File 'lib/dorothy2/do-utils.rb', line 292

def get_anal_id
  @db.exec("SELECT nextval('dorothy.analyses_id_seq')").first["nextval"].to_i
end

#get_curr_malwares_id ⇒ Object 



495
496
497
 
# File 'lib/dorothy2/do-utils.rb', line 495

def get_curr_malwares_id
  @db.exec("SELECT nextval('dorothy.malwares_id_seq')").first["nextval"].to_i
end

#get_curr_queue_id ⇒ Object 



304
305
306
 
# File 'lib/dorothy2/do-utils.rb', line 304

def get_curr_queue_id
  @db.exec("SELECT currval('dorothy.queue_id_seq')").first["currval"].to_i
end

#get_email_id ⇒ Object 



296
297
298
 
# File 'lib/dorothy2/do-utils.rb', line 296

def get_email_id
  @db.exec("SELECT nextval('dorothy.emails_id_seq')").first["nextval"].to_i
end

#get_sighting_id ⇒ Object 



300
301
302
 
# File 'lib/dorothy2/do-utils.rb', line 300

def get_sighting_id
  @db.exec("SELECT nextval('dorothy.sightings_id_seq')").first["nextval"].to_i
end

#insert(table, values) ⇒ Object 



229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
 
# File 'lib/dorothy2/do-utils.rb', line 229

def insert(table,values)
  n = 1
  @sqlstring = ""

  values.each { |value|
    if value == "default"
      value1 = value
    elsif value == "null"
      value1 = value
    elsif value == nil
      value1 = "null"
    elsif value == "lastval()"
      value1 = value
    elsif value =~ /currval/
      value1 = value
    elsif table == "sys_procs"   #avoiding noising escape-issue for \u
      value1 = "E'#{value.inspect}'"
    else
      #if present, remove ""
      value.gsub! /^"|"$/, '' if values.class.inspect == "String"
      value1 = "E'#{value}'"
    end
    if n == values.size
      @sqlstring << value1
    elsif
    @sqlstring << value1 + ","
    end
    n += 1
  }
  #p "Inserting in dorothy.#{table}:"
  #p "#{@sqlstring}"

  begin
    @db.exec("INSERT into dorothy.#{table} values (#{@sqlstring})")
  rescue PG::Error => err
    LOGGER.error "DB", err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
    #self.rollback
    return false
    #exit 1
  end

  #p "Insertion OK"

end

#malware_list ⇒ Object 



334
335
336
337
338
339
340
 
# File 'lib/dorothy2/do-utils.rb', line 334

def malware_list
  malwares = []
  @db.exec("SELECT samples.sha256 FROM dorothy.samples").each do |q|
    malwares.push q
  end
  malwares
end

#push_email_data(m, forwarded_by = 'null') ⇒ Object 



342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
 
# File 'lib/dorothy2/do-utils.rb', line 342

def push_email_data(m, forwarded_by='null')
  #m is a message object from the Mail class
  id = get_email_id
  values = " '#{m.from[0]}', E'#{m.subject}', E'#{Insertdb.escape_bytea(m.raw_source)}', '#{id}', null, null, null, null, null, '#{Util.get_time(m.date)}', '#{m.message_id}', '#{m.has_attachments?}', '#{m.charset}', '#{Digest::SHA2.hexdigest(m.body.raw_source)}', #{forwarded_by}"
  raise "A DB error occurred while adding data into the emails table" unless  raw_insert('emails', values)

  #adding receivers
  #TO
  m.to_addrs.each  {|addr|  raise "A DB error occurred while adding data into the emails_receivers table" unless insert('email_receivers', [addr, id, 'to'])}
  #CC
  m.cc_addrs.each  {|addr|  raise "A DB error occurred while adding data into the emails_receivers table" unless insert('email_receivers', [addr, id, 'cc'])}


  id
end

#raw_insert(table, data) ⇒ Object 



274
275
276
277
278
279
280
281
282
283
 
# File 'lib/dorothy2/do-utils.rb', line 274

def raw_insert(table, data)
  begin
    @db.exec("INSERT into dorothy.#{table} values (#{data})")
  rescue PG::Error => err
    LOGGER.error "DB", err.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY )
    #self.rollback
    return false
    #exit 1
  end
end

#rollback ⇒ Object 



223
224
225
226
 
# File 'lib/dorothy2/do-utils.rb', line 223

def rollback
  LOGGER.error "DB", "DB ROLLBACK"
  @db.exec("ROLLBACK")
end

#select(table, column, value, column2 = nil, value2 = nil, column3 = nil, value3 = nil) ⇒ Object 



285
286
287
288
289
290
 
# File 'lib/dorothy2/do-utils.rb', line 285

def select(table, column, value, column2=nil, value2=nil, column3=nil, value3=nil)
  column2&&value2 ? ( column3&&value3 ? chk = @db.exec("SELECT * from dorothy.#{table} where #{column} = '#{value}' AND #{column2} = '#{value2}' AND #{column3} = '#{value3}' ") : chk = @db.exec("SELECT * from dorothy.#{table} where #{column} = '#{value}' AND #{column2} = '#{value2}'")) : chk = @db.exec("SELECT * from dorothy.#{table} where #{column} = '#{value}'")

  #puts ".::WARNING #{value} already present in dorothy.#{table}".red.bold if chk
  return chk
end

#set_analyzed(hash) ⇒ Object 



322
323
324
 
# File 'lib/dorothy2/do-utils.rb', line 322

def set_analyzed(hash)
  @db.exec("UPDATE dorothy.traffic_dumps set parsed = true where sha256 = '#{hash}'")
end

#status ⇒ Object 



215
216
217
 
# File 'lib/dorothy2/do-utils.rb', line 215

def status
  @db.transaction_status
end

#table_empty?(table) ⇒ Boolean

Returns:

  • (Boolean) 


314
315
316
 
# File 'lib/dorothy2/do-utils.rb', line 314

def table_empty?(table)
  @db.exec("SELECT CASE WHEN EXISTS (SELECT * FROM dorothy.#{table} LIMIT 1) THEN FALSE ELSE TRUE END").first["case"] == "t" ? true : false
end

#update_sample_path(sample, path) ⇒ Object 



318
319
320
 
# File 'lib/dorothy2/do-utils.rb', line 318

def update_sample_path(sample, path)
  @db.exec("UPDATE dorothy.samples set path = '#{path}' where sha256 = '#{sample}'")
end

#vm_init ⇒ Object 



527
528
529
530
531
 
# File 'lib/dorothy2/do-utils.rb', line 527

def vm_init
  @db.exec("UPDATE dorothy.sandboxes set is_available = true")
  LOGGER.debug "DB", "All VM are now available"
  #TODO - revert them too?
end