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.



55
56
57
 
# File 'lib/dorothy2/do-utils.rb', line 55

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 



147
148
149
150
 
# File 'lib/dorothy2/do-utils.rb', line 147

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

Instance Method Details

#begin_t ⇒ Object 



59
60
61
 
# File 'lib/dorothy2/do-utils.rb', line 59

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

#close ⇒ Object 



71
72
73
 
# File 'lib/dorothy2/do-utils.rb', line 71

def close
  @db.close
end

#commit ⇒ Object 



63
64
65
 
# File 'lib/dorothy2/do-utils.rb', line 63

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

#find_pcap ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/dorothy2/do-utils.rb', line 180

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
    LOGGER.error "DB","Error while fetching traffic_dumps table\n " + $!
  end

end

#find_seq(seq) ⇒ Object 



164
165
166
 
# File 'lib/dorothy2/do-utils.rb', line 164

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

#find_vm ⇒ Object 



192
193
194
195
196
197
198
199
200
201
 
# File 'lib/dorothy2/do-utils.rb', line 192

def find_vm
  vm = @db.exec("SELECT id, hostname, ipaddress, username, password FROM dorothy.sandboxes where is_available is true").first
  if vm.nil?
    LOGGER.debug "DB","At this time there are no free VM available"  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 



168
169
170
 
# File 'lib/dorothy2/do-utils.rb', line 168

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

#free_vm(vmid) ⇒ Object 



203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
 
# File 'lib/dorothy2/do-utils.rb', line 203

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
      LOGGER.error "DB", "An error occurred while releasing the VM"
      LOGGER.debug "DB", $!
      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 



143
144
145
 
# File 'lib/dorothy2/do-utils.rb', line 143

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

#insert(table, values) ⇒ Object 



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/dorothy2/do-utils.rb', line 81

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

  values.each { |value|
    if value == "default"
      value1 = value
    elsif value == "null"
      value1 = value
    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 => e
    LOGGER.debug "DB", $!
    LOGGER.debug "DB", e.inspect
    #self.rollback
    return false
    #exit 1
  end

  #p "Insertion OK"

end

#malware_list ⇒ Object 



172
173
174
175
176
177
178
 
# File 'lib/dorothy2/do-utils.rb', line 172

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

#raw_insert(table, data) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
 
# File 'lib/dorothy2/do-utils.rb', line 125

def raw_insert(table, data)
  begin
    @db.exec("INSERT into dorothy.#{table} values (#{data})")
  rescue
    LOGGER.error "DB", "#{$!}"
    #self.rollback
    return false
    #exit 1
  end
end

#rollback ⇒ Object 



75
76
77
78
 
# File 'lib/dorothy2/do-utils.rb', line 75

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

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



136
137
138
139
140
141
 
# File 'lib/dorothy2/do-utils.rb', line 136

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 



160
161
162
 
# File 'lib/dorothy2/do-utils.rb', line 160

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

#status ⇒ Object 



67
68
69
 
# File 'lib/dorothy2/do-utils.rb', line 67

def status
  @db.transaction_status
end

#table_empty?(table) ⇒ Boolean

Returns:

  • (Boolean) 


152
153
154
 
# File 'lib/dorothy2/do-utils.rb', line 152

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 



156
157
158
 
# File 'lib/dorothy2/do-utils.rb', line 156

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

#vm_init ⇒ Object 



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

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