Commits

György Kohut committed e0ef791

virustotal: insert record for response_code != 1 too

  • Participants
  • Parent commits 3ce6653

Comments (0)

Files changed (3)

schema/hbbackend.erm

 			</connections>
 			<display>false</display>
 			<creation_date>2011-06-10 22:42:09</creation_date>
-			<updated_date>2012-08-06 14:34:51</updated_date>
+			<updated_date>2012-08-08 00:10:57</updated_date>
 			<model_property>
 				<name>Project Name</name>
 				<value></value>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>scan_date</logical_name>
-			<physical_name>scan_date</physical_name>
-			<type>timestamp with time zone</type>
+			<logical_name>response_code</logical_name>
+			<physical_name>response_code</physical_name>
+			<type>integer</type>
 		</word>
 		<word>
 			<id>16</id>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>scan_id</logical_name>
-			<physical_name>scan_id</physical_name>
-			<type>varchar</type>
+			<logical_name>scan_date</logical_name>
+			<physical_name>scan_date</physical_name>
+			<type>timestamp with time zone</type>
 		</word>
 		<word>
 			<id>17</id>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>seen_count</logical_name>
-			<physical_name>seen_count</physical_name>
-			<type>bigint</type>
+			<logical_name>scan_id</logical_name>
+			<physical_name>scan_id</physical_name>
+			<type>varchar</type>
 		</word>
 		<word>
 			<id>18</id>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>seen_first</logical_name>
-			<physical_name>seen_first</physical_name>
-			<type>timestamp with time zone</type>
+			<logical_name>seen_count</logical_name>
+			<physical_name>seen_count</physical_name>
+			<type>bigint</type>
 		</word>
 		<word>
 			<id>22</id>
 			<unsigned>false</unsigned>
 			<args></args>
 			<description></description>
-			<logical_name>seen_last</logical_name>
-			<physical_name>seen_last</physical_name>
+			<logical_name>seen_first</logical_name>
+			<physical_name>seen_first</physical_name>
 			<type>timestamp with time zone</type>
 		</word>
 		<word>
 		</word>
 		<word>
 			<id>29</id>
+			<length>null</length>
+			<decimal>null</decimal>
+			<array>false</array>
+			<array_dimension>null</array_dimension>
+			<unsigned>false</unsigned>
+			<args></args>
+			<description></description>
+			<logical_name>seen_last</logical_name>
+			<physical_name>seen_last</physical_name>
+			<type>timestamp with time zone</type>
+		</word>
+		<word>
+			<id>30</id>
 			<length>128</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>character(n)</type>
 		</word>
 		<word>
-			<id>30</id>
+			<id>31</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>inet</type>
 		</word>
 		<word>
-			<id>31</id>
+			<id>32</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>integer</type>
 		</word>
 		<word>
-			<id>32</id>
+			<id>33</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>boolean</type>
 		</word>
 		<word>
-			<id>33</id>
+			<id>34</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>inet</type>
 		</word>
 		<word>
-			<id>34</id>
+			<id>35</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>integer</type>
 		</word>
 		<word>
-			<id>35</id>
+			<id>36</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>timestamp with time zone</type>
 		</word>
 		<word>
-			<id>36</id>
+			<id>37</id>
 			<length>null</length>
 			<decimal>null</decimal>
 			<array>false</array>
 			<type>timestamp with time zone</type>
 		</word>
 		<word>
-			<id>37</id>
+			<id>38</id>
 			<length>128</length>
 			<decimal>null</decimal>
 			<array>false</array>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>17</word_id>
+					<word_id>18</word_id>
 					<id>2</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>22</word_id>
+					<word_id>23</word_id>
 					<id>3</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>26</word_id>
+					<word_id>27</word_id>
 					<id>4</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>18</word_id>
+					<word_id>19</word_id>
 					<id>6</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>23</word_id>
+					<word_id>24</word_id>
 					<id>7</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>25</word_id>
+					<word_id>26</word_id>
 					<id>8</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>20</word_id>
+					<word_id>21</word_id>
 					<id>10</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>24</word_id>
+					<word_id>25</word_id>
 					<id>11</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>27</word_id>
+					<word_id>28</word_id>
 					<id>12</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>29</word_id>
+					<word_id>30</word_id>
 					<id>15</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>19</word_id>
+					<word_id>20</word_id>
 					<id>18</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>21</word_id>
+					<word_id>22</word_id>
 					<id>19</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>28</word_id>
+					<word_id>29</word_id>
 					<id>20</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>32</word_id>
+					<word_id>33</word_id>
 					<id>21</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>35</word_id>
+					<word_id>36</word_id>
 					<id>23</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>30</word_id>
+					<word_id>31</word_id>
 					<id>26</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>31</word_id>
+					<word_id>32</word_id>
 					<id>27</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>33</word_id>
+					<word_id>34</word_id>
 					<id>28</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>34</word_id>
+					<word_id>35</word_id>
 					<id>29</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>37</word_id>
+					<word_id>38</word_id>
 					<id>30</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 		</table>
 		<table>
 			<id>5</id>
-			<height>170</height>
-			<width>334</width>
+			<height>128</height>
+			<width>222</width>
 				<font_name>Lucida Grande</font_name>
 				<font_size>9</font_size>
-			<x>729</x>
-			<y>32</y>
+			<x>1102</x>
+			<y>52</y>
 			<color>
 				<r>128</r>
 				<g>128</g>
 			<connections>
 				<relation>
 					<id>2</id>
-					<source>3</source>
+					<source>6</source>
 					<target>5</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 					<referenced_complex_unique_key>null</referenced_complex_unique_key>
 				</relation>
 			</connections>
-			<physical_name>virustotal_reports</physical_name>
-			<logical_name>virustotal_reports</logical_name>
+			<physical_name>virustotal_results</physical_name>
+			<logical_name>virustotal_results</logical_name>
 			<description></description>
 			<constraint></constraint>
 			<primary_key_name></primary_key_name>
 			<option></option>
 			<columns>
 				<normal_column>
-					<word_id>7</word_id>
 					<id>31</id>
-					<description></description>
-					<unique_key_name></unique_key_name>
-					<logical_name></logical_name>
-					<physical_name></physical_name>
-					<type>bigserial</type>
-					<constraint></constraint>
-					<default_value></default_value>
-					<auto_increment>false</auto_increment>
-					<foreign_key>false</foreign_key>
-					<not_null>true</not_null>
-					<primary_key>true</primary_key>
-					<unique_key>true</unique_key>
-					<character_set></character_set>
-					<collation></collation>
-					<sequence>
-						<name></name>
-						<schema></schema>
-						<increment></increment>
-						<min_value></min_value>
-						<max_value></max_value>
-						<start></start>
-						<cache></cache>
-						<cycle>false</cycle>
-						<order>false</order>
-						<description></description>
-						<data_type></data_type>
-						<decimal_size>0</decimal_size>
-					</sequence>
-				</normal_column>
-				<normal_column>
-					<id>32</id>
-					<referenced_column>13</referenced_column>
+					<referenced_column>36</referenced_column>
 					<relation>2</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
-					<logical_name>binary_id</logical_name>
-					<physical_name>binary_id</physical_name>
+					<logical_name>report_id</logical_name>
+					<physical_name>report_id</physical_name>
 					<type>bigint</type>
 					<constraint></constraint>
 					<default_value></default_value>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>36</word_id>
-					<id>33</id>
+					<word_id>12</word_id>
+					<id>32</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 					<physical_name></physical_name>
-					<type>timestamp with time zone</type>
+					<type>varchar</type>
 					<constraint></constraint>
-					<default_value>transaction_timestamp()</default_value>
+					<default_value></default_value>
 					<auto_increment>false</auto_increment>
 					<foreign_key>false</foreign_key>
 					<not_null>false</not_null>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>16</word_id>
-					<id>34</id>
+					<word_id>0</word_id>
+					<id>33</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>15</word_id>
-					<id>35</id>
+					<word_id>2</word_id>
+					<id>34</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 					<physical_name></physical_name>
-					<type>timestamp with time zone</type>
+					<type>varchar</type>
 					<constraint></constraint>
 					<default_value></default_value>
 					<auto_increment>false</auto_increment>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>14</word_id>
-					<id>36</id>
+					<word_id>1</word_id>
+					<id>35</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 		</table>
 		<table>
 			<id>6</id>
-			<height>128</height>
-			<width>222</width>
+			<height>170</height>
+			<width>334</width>
 				<font_name>Lucida Grande</font_name>
 				<font_size>9</font_size>
-			<x>1102</x>
-			<y>52</y>
+			<x>729</x>
+			<y>32</y>
 			<color>
 				<r>128</r>
 				<g>128</g>
 			<connections>
 				<relation>
 					<id>3</id>
-					<source>5</source>
+					<source>3</source>
 					<target>6</target>
 					<child_cardinality>1..n</child_cardinality>
 					<parent_cardinality>1</parent_cardinality>
 					<referenced_complex_unique_key>null</referenced_complex_unique_key>
 				</relation>
 			</connections>
-			<physical_name>virustotal_results</physical_name>
-			<logical_name>virustotal_results</logical_name>
+			<physical_name>virustotal_reports</physical_name>
+			<logical_name>virustotal_reports</logical_name>
 			<description></description>
 			<constraint></constraint>
 			<primary_key_name></primary_key_name>
 			<option></option>
 			<columns>
 				<normal_column>
+					<word_id>7</word_id>
+					<id>36</id>
+					<description></description>
+					<unique_key_name></unique_key_name>
+					<logical_name></logical_name>
+					<physical_name></physical_name>
+					<type>bigserial</type>
+					<constraint></constraint>
+					<default_value></default_value>
+					<auto_increment>false</auto_increment>
+					<foreign_key>false</foreign_key>
+					<not_null>true</not_null>
+					<primary_key>true</primary_key>
+					<unique_key>true</unique_key>
+					<character_set></character_set>
+					<collation></collation>
+					<sequence>
+						<name></name>
+						<schema></schema>
+						<increment></increment>
+						<min_value></min_value>
+						<max_value></max_value>
+						<start></start>
+						<cache></cache>
+						<cycle>false</cycle>
+						<order>false</order>
+						<description></description>
+						<data_type></data_type>
+						<decimal_size>0</decimal_size>
+					</sequence>
+				</normal_column>
+				<normal_column>
 					<id>37</id>
-					<referenced_column>31</referenced_column>
+					<referenced_column>13</referenced_column>
 					<relation>3</relation>
 					<description></description>
 					<unique_key_name></unique_key_name>
-					<logical_name>report_id</logical_name>
-					<physical_name>report_id</physical_name>
+					<logical_name>binary_id</logical_name>
+					<physical_name>binary_id</physical_name>
 					<type>bigint</type>
 					<constraint></constraint>
 					<default_value></default_value>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>12</word_id>
+					<word_id>37</word_id>
 					<id>38</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 					<physical_name></physical_name>
-					<type>varchar</type>
+					<type>timestamp with time zone</type>
 					<constraint></constraint>
-					<default_value></default_value>
+					<default_value>transaction_timestamp()</default_value>
 					<auto_increment>false</auto_increment>
 					<foreign_key>false</foreign_key>
 					<not_null>false</not_null>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>0</word_id>
+					<word_id>15</word_id>
 					<id>39</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 					<physical_name></physical_name>
-					<type>varchar</type>
+					<type>integer</type>
 					<constraint></constraint>
 					<default_value></default_value>
 					<auto_increment>false</auto_increment>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>2</word_id>
+					<word_id>17</word_id>
 					<id>40</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					</sequence>
 				</normal_column>
 				<normal_column>
-					<word_id>1</word_id>
+					<word_id>16</word_id>
 					<id>41</id>
 					<description></description>
 					<unique_key_name></unique_key_name>
 					<logical_name></logical_name>
 					<physical_name></physical_name>
+					<type>timestamp with time zone</type>
+					<constraint></constraint>
+					<default_value></default_value>
+					<auto_increment>false</auto_increment>
+					<foreign_key>false</foreign_key>
+					<not_null>false</not_null>
+					<primary_key>false</primary_key>
+					<unique_key>false</unique_key>
+					<character_set></character_set>
+					<collation></collation>
+					<sequence>
+						<name></name>
+						<schema></schema>
+						<increment></increment>
+						<min_value></min_value>
+						<max_value></max_value>
+						<start></start>
+						<cache></cache>
+						<cycle>false</cycle>
+						<order>false</order>
+						<description></description>
+						<data_type></data_type>
+						<decimal_size>0</decimal_size>
+					</sequence>
+				</normal_column>
+				<normal_column>
+					<word_id>14</word_id>
+					<id>42</id>
+					<description></description>
+					<unique_key_name></unique_key_name>
+					<logical_name></logical_name>
+					<physical_name></physical_name>
 					<type>varchar</type>
 					<constraint></constraint>
 					<default_value></default_value>

schema/hbbackend.sql

 	ID BIGSERIAL NOT NULL UNIQUE,
 	BINARY_ID BIGINT NOT NULL,
 	TS TIMESTAMP WITH TIME ZONE DEFAULT transaction_timestamp(),
+	RESPONSE_CODE INT,
 	SCAN_ID VARCHAR,
 	SCAN_DATE TIMESTAMP WITH TIME ZONE,
 	PERMALINK VARCHAR,

src/main/java/org/honeynet/hbbackend/virustotal/VirusTotal.java

 			log.error(emsg);
 			throw new EJBException(emsg);
 		}
-		if (responseCodeField.getLongValue() != 1) {
-			emsg = "response_code != 1: " + responseCodeField.getLongValue();
-			log.error(emsg);
-			throw new EJBException(emsg);
+		
+		
+		// insert empty record here and return
+		if (responseCodeField.getIntValue() != 1) {
+			Connection db = null;
+			try {
+				db = ds.getConnection();
+				
+				PreparedStatement pStmt;
+				ResultSet queryRes;
+				
+				long reportId;
+				
+				pStmt = db.prepareStatement("insert into virustotal_reports (binary_id, response_code) values (?,?) returning id");
+				pStmt.setLong(1, binaryId);
+				pStmt.setInt(2, responseCodeField.getIntValue());
+				queryRes = pStmt.executeQuery();
+				queryRes.next();
+				reportId = queryRes.getLong(1);
+				queryRes.close();
+				pStmt.close();
+				log.debug("inserted into virustotal_reports | binary_id={} response_code={} id={}", new Object[] { binaryId, responseCodeField.getIntValue(), reportId });
+				
+				return;
+			}
+			catch (SQLException e) {
+				log.error("got JMSException", e);
+				throw new EJBException(e);
+			}
+			finally {
+				try {
+					if (db != null)	db.close();
+				}
+				catch (SQLException e) {
+					log.error("got SQLException while closing resource", e);
+				}
+			}	
 		}
 		
+		
 		// scans
 		if (scansObject == null) {
 			emsg = "invalid response: missing scans field";
 		}
 		permalink = permalinkField.getValueAsText();
 		
-		log.trace("parsed field: scan_id: {}", scanId);
-		log.trace("parsed field: scan_date: {}", scanDate);
-		log.trace("parsed field: permalink: {}", permalink);
+		log.trace("field: scan_id: {}", scanId);
+		log.trace("field: scan_date: {}", scanDate);
+		log.trace("field: permalink: {}", permalink);
 		
 		// insert
 		Connection db = null;
 			
 			long reportId;
 			
-			pStmt = db.prepareStatement("insert into virustotal_reports (binary_id, scan_id, scan_date, permalink) values (?,?,?,?) returning id");
+			pStmt = db.prepareStatement("insert into virustotal_reports (binary_id, response_code, scan_id, scan_date, permalink) values (?,?,?,?,?) returning id");
 			pStmt.setLong(1, binaryId);
-			pStmt.setString(2, scanId);
-			pStmt.setTimestamp(3, scanDate);
-			pStmt.setString(4, permalink);
+			pStmt.setInt(2, responseCodeField.getIntValue());
+			pStmt.setString(3, scanId);
+			pStmt.setTimestamp(4, scanDate);
+			pStmt.setString(5, permalink);
 			queryRes = pStmt.executeQuery();
 			queryRes.next();
 			reportId = queryRes.getLong(1);
 			queryRes.close();
 			pStmt.close();
-			log.debug("inserted into virustotal_reports | id={}", reportId);
+			log.debug("inserted into virustotal_reports | binary_id={} response_code={} id={}", new Object[]{ binaryId, responseCodeField.getIntValue(), reportId });
 			
 			// insert results
 			int n = 0;