1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package org.owasp.dependencycheck.data.nvdcve;
19
20 import com.google.common.base.Strings;
21 import java.sql.Connection;
22 import java.sql.PreparedStatement;
23 import java.sql.ResultSet;
24 import java.sql.SQLException;
25 import java.sql.Types;
26 import org.h2.tools.SimpleResultSet;
27
28
29
30
31
32
33 public final class H2Functions {
34
35 private H2Functions() {
36
37 }
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67 public static void insertSoftware(final Connection conn, int vulnerabilityId, String part, String vendor,
68 String product, String version, String update, String edition, String language, String swEdition,
69 String targetSw, String targetHw, String other, String ecosystem, String versionEndExcluding,
70 String versionEndIncluding, String versionStartExcluding, String versionStartIncluding, Boolean vulnerable) throws SQLException {
71 int cpeID = 0;
72 try (PreparedStatement selectCpeId = conn.prepareStatement("SELECT id, ecosystem FROM cpeEntry WHERE part=? AND vendor=? AND product=? "
73 + "AND version=? AND update_version=? AND edition=? AND lang=? AND sw_edition=? AND target_sw=? AND target_hw=? AND other=?")) {
74 selectCpeId.setString(1, part);
75 selectCpeId.setString(2, vendor);
76 selectCpeId.setString(3, product);
77 selectCpeId.setString(4, version);
78 selectCpeId.setString(5, update);
79 selectCpeId.setString(6, edition);
80 selectCpeId.setString(7, language);
81 selectCpeId.setString(8, swEdition);
82 selectCpeId.setString(9, targetSw);
83 selectCpeId.setString(10, targetHw);
84 selectCpeId.setString(11, other);
85
86 try (ResultSet rs = selectCpeId.executeQuery()) {
87 if (rs.next()) {
88 cpeID = rs.getInt(1);
89 final String e = rs.getString(2);
90 if (e == null && ecosystem != null) {
91 try (PreparedStatement updateEcosystem = conn.prepareStatement("UPDATE cpeEntry SET ecosystem=? WHERE id=?")) {
92 updateEcosystem.setString(1, ecosystem);
93 updateEcosystem.setInt(2, cpeID);
94 updateEcosystem.execute();
95 }
96 }
97 }
98 }
99 }
100 if (cpeID == 0) {
101 final String[] returnedColumns = {"id"};
102 try (PreparedStatement insertCpe = conn.prepareStatement("INSERT INTO cpeEntry (part, vendor, product, version, update_version, "
103 + "edition, lang, sw_edition, target_sw, target_hw, other, ecosystem) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
104 returnedColumns)) {
105 insertCpe.setString(1, part);
106 insertCpe.setString(2, vendor);
107 insertCpe.setString(3, product);
108 insertCpe.setString(4, version);
109 insertCpe.setString(5, update);
110 insertCpe.setString(6, edition);
111 insertCpe.setString(7, language);
112 insertCpe.setString(8, swEdition);
113 insertCpe.setString(9, targetSw);
114 insertCpe.setString(10, targetHw);
115 insertCpe.setString(11, other);
116 setStringOrNull(insertCpe, 12, ecosystem);
117 insertCpe.executeUpdate();
118 try (ResultSet rs = insertCpe.getGeneratedKeys()) {
119 if (rs.next()) {
120 cpeID = rs.getInt(1);
121 }
122 }
123 }
124 }
125
126
127 try (PreparedStatement insertSoftware = conn.prepareStatement("INSERT INTO software (cveid, cpeEntryId, "
128 + "versionEndExcluding, versionEndIncluding, versionStartExcluding, versionStartIncluding, "
129 + "vulnerable) VALUES (?, ?, ?, ?, ?, ?, ?)")) {
130 insertSoftware.setInt(1, vulnerabilityId);
131 insertSoftware.setInt(2, cpeID);
132
133 setStringOrNull(insertSoftware, 3, versionEndExcluding);
134 setStringOrNull(insertSoftware, 4, versionEndIncluding);
135 setStringOrNull(insertSoftware, 5, versionStartExcluding);
136 setStringOrNull(insertSoftware, 6, versionStartIncluding);
137 setBooleanOrNull(insertSoftware, 7, vulnerable);
138 insertSoftware.execute();
139 }
140 }
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226 public static ResultSet updateVulnerability(final Connection conn, String cve,
227 String description, String v2Severity, Float v2ExploitabilityScore,
228 Float v2ImpactScore, Boolean v2AcInsufInfo, Boolean v2ObtainAllPrivilege,
229 Boolean v2ObtainUserPrivilege, Boolean v2ObtainOtherPrivilege, Boolean v2UserInteractionRequired,
230 Float v2Score, String v2AccessVector, String v2AccessComplexity,
231 String v2Authentication, String v2ConfidentialityImpact, String v2IntegrityImpact,
232 String v2AvailabilityImpact, String v2Version, Float v3ExploitabilityScore,
233 Float v3ImpactScore, String v3AttackVector, String v3AttackComplexity,
234 String v3PrivilegesRequired, String v3UserInteraction, String v3Scope,
235 String v3ConfidentialityImpact, String v3IntegrityImpact, String v3AvailabilityImpact,
236 Float v3BaseScore, String v3BaseSeverity, String v3Version, String v4version,
237 String v4attackVector, String v4attackComplexity, String v4attackRequirements,
238 String v4privilegesRequired, String v4userInteraction, String v4vulnConfidentialityImpact,
239 String v4vulnIntegrityImpact, String v4vulnAvailabilityImpact, String v4subConfidentialityImpact,
240 String v4subIntegrityImpact, String v4subAvailabilityImpact, String v4exploitMaturity,
241 String v4confidentialityRequirement, String v4integrityRequirement, String v4availabilityRequirement,
242 String v4modifiedAttackVector, String v4modifiedAttackComplexity, String v4modifiedAttackRequirements,
243 String v4modifiedPrivilegesRequired, String v4modifiedUserInteraction, String v4modifiedVulnConfidentialityImpact,
244 String v4modifiedVulnIntegrityImpact, String v4modifiedVulnAvailabilityImpact, String v4modifiedSubConfidentialityImpact,
245 String v4modifiedSubIntegrityImpact, String v4modifiedSubAvailabilityImpact, String v4safety,
246 String v4automatable, String v4recovery, String v4valueDensity, String v4vulnerabilityResponseEffort,
247 String v4providerUrgency, Float v4baseScore, String v4baseSeverity, Float v4threatScore,
248 String v4threatSeverity, Float v4environmentalScore, String v4environmentalSeverity,
249 String v4source, String v4type) throws SQLException {
250
251 final SimpleResultSet ret = new SimpleResultSet();
252 ret.addColumn("id", Types.INTEGER, 10, 0);
253 final String url = conn.getMetaData().getURL();
254 if ("jdbc:columnlist:connection".equals(url)) {
255
256
257
258
259 return ret;
260 }
261
262 int vulnerabilityId = 0;
263 try (PreparedStatement selectVulnerabilityId = conn.prepareStatement("SELECT id FROM VULNERABILITY CVE WHERE cve=?")) {
264 selectVulnerabilityId.setString(1, cve);
265 try (ResultSet rs = selectVulnerabilityId.executeQuery()) {
266 if (rs.next()) {
267 vulnerabilityId = rs.getInt(1);
268 }
269 }
270 }
271 PreparedStatement merge = null;
272 try {
273 if (vulnerabilityId > 0) {
274
275 try (PreparedStatement refs = conn.prepareStatement("DELETE FROM reference WHERE cveid = ?")) {
276 refs.setInt(1, vulnerabilityId);
277 refs.executeUpdate();
278 }
279 try (PreparedStatement software = conn.prepareStatement("DELETE FROM software WHERE cveid = ?")) {
280 software.setInt(1, vulnerabilityId);
281 software.executeUpdate();
282 }
283 try (PreparedStatement cwe = conn.prepareStatement("DELETE FROM cweEntry WHERE cveid = ?")) {
284 cwe.setInt(1, vulnerabilityId);
285 cwe.executeUpdate();
286 }
287 merge = conn.prepareStatement("UPDATE VULNERABILITY SET description=?, "
288 + "v2Severity=?, v2ExploitabilityScore=?, "
289 + "v2ImpactScore=?, v2AcInsufInfo=?, v2ObtainAllPrivilege=?, "
290 + "v2ObtainUserPrivilege=?, v2ObtainOtherPrivilege=?, v2UserInteractionRequired=?, "
291 + "v2Score=?, v2AccessVector=?, v2AccessComplexity=?, "
292 + "v2Authentication=?, v2ConfidentialityImpact=?, v2IntegrityImpact=?, "
293 + "v2AvailabilityImpact=?, v2Version=?, v3ExploitabilityScore=?, "
294 + "v3ImpactScore=?, v3AttackVector=?, v3AttackComplexity=?, "
295 + "v3PrivilegesRequired=?, v3UserInteraction=?, v3Scope=?, "
296 + "v3ConfidentialityImpact=?, v3IntegrityImpact=?, v3AvailabilityImpact=?, "
297 + "v3BaseScore=?, v3BaseSeverity=?, v3Version=?, v4version=?, v4attackVector=?, "
298 + "v4attackComplexity=?, v4attackRequirements=?, v4privilegesRequired=?, "
299 + "v4userInteraction=?, v4vulnConfidentialityImpact=?, v4vulnIntegrityImpact=?, "
300 + "v4vulnAvailabilityImpact=?, v4subConfidentialityImpact=?, v4subIntegrityImpact=?, "
301 + "v4subAvailabilityImpact=?, v4exploitMaturity=?, "
302 + "v4confidentialityRequirement=?, v4integrityRequirement=?, "
303 + "v4availabilityRequirement=?, v4modifiedAttackVector=?, "
304 + "v4modifiedAttackComplexity=?, v4modifiedAttackRequirements=?, "
305 + "v4modifiedPrivilegesRequired=?, v4modifiedUserInteraction=?, "
306 + "v4modifiedVulnConfidentialityImpact=?, v4modifiedVulnIntegrityImpact=?, "
307 + "v4modifiedVulnAvailabilityImpact=?, v4modifiedSubConfidentialityImpact=?, "
308 + "v4modifiedSubIntegrityImpact=?, v4modifiedSubAvailabilityImpact=?, "
309 + "v4safety=?, v4automatable=?, v4recovery=?, v4valueDensity=?, "
310 + "v4vulnerabilityResponseEffort=?, v4providerUrgency=?, v4baseScore=?, "
311 + "v4baseSeverity=?, v4threatScore=?, v4threatSeverity=?, v4environmentalScore=?, "
312 + "v4environmentalSeverity=?, v4source=?, v4type=?"
313 + "WHERE id=?");
314 } else {
315
316 final String[] returnedColumns = {"id"};
317 merge = conn.prepareStatement("INSERT INTO VULNERABILITY (description, "
318 + "v2Severity, v2ExploitabilityScore, "
319 + "v2ImpactScore, v2AcInsufInfo, v2ObtainAllPrivilege, "
320 + "v2ObtainUserPrivilege, v2ObtainOtherPrivilege, v2UserInteractionRequired, "
321 + "v2Score, v2AccessVector, v2AccessComplexity, "
322 + "v2Authentication, v2ConfidentialityImpact, v2IntegrityImpact, "
323 + "v2AvailabilityImpact, v2Version, v3ExploitabilityScore, "
324 + "v3ImpactScore, v3AttackVector, v3AttackComplexity, "
325 + "v3PrivilegesRequired, v3UserInteraction, v3Scope, "
326 + "v3ConfidentialityImpact, v3IntegrityImpact, v3AvailabilityImpact, "
327 + "v3BaseScore, v3BaseSeverity, v3Version, v4version, v4attackVector, "
328 + "v4attackComplexity, v4attackRequirements, v4privilegesRequired, "
329 + "v4userInteraction, v4vulnConfidentialityImpact, v4vulnIntegrityImpact, "
330 + "v4vulnAvailabilityImpact, v4subConfidentialityImpact, v4subIntegrityImpact, "
331 + "v4subAvailabilityImpact, v4exploitMaturity,v4confidentialityRequirement, "
332 + "v4integrityRequirement, v4availabilityRequirement,v4modifiedAttackVector, "
333 + "v4modifiedAttackComplexity, v4modifiedAttackRequirements,v4modifiedPrivilegesRequired, "
334 + "v4modifiedUserInteraction, v4modifiedVulnConfidentialityImpact,v4modifiedVulnIntegrityImpact, "
335 + "v4modifiedVulnAvailabilityImpact, v4modifiedSubConfidentialityImpact,v4modifiedSubIntegrityImpact, "
336 + "v4modifiedSubAvailabilityImpact, v4safety, v4automatable, v4recovery, v4valueDensity, "
337 + "v4vulnerabilityResponseEffort, v4providerUrgency, v4baseScore, v4baseSeverity, "
338 + "v4threatScore,v4threatSeverity, v4environmentalScore, v4environmentalSeverity, "
339 + "v4source, v4type, cve) VALUES (?, ?, ?, ?, ?, ?, "
340 + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
341 + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
342 + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
343 returnedColumns);
344 }
345
346 merge.setString(1, description);
347
348 setStringOrNull(merge, 2, v2Severity);
349 setFloatOrNull(merge, 3, v2ExploitabilityScore);
350 setFloatOrNull(merge, 4, v2ImpactScore);
351 setBooleanOrNull(merge, 5, v2AcInsufInfo);
352 setBooleanOrNull(merge, 6, v2ObtainAllPrivilege);
353 setBooleanOrNull(merge, 7, v2ObtainUserPrivilege);
354 setBooleanOrNull(merge, 8, v2ObtainOtherPrivilege);
355 setBooleanOrNull(merge, 9, v2UserInteractionRequired);
356 setFloatOrNull(merge, 10, v2Score);
357 setStringOrNull(merge, 11, v2AccessVector);
358 setStringOrNull(merge, 12, v2AccessComplexity);
359 setStringOrNull(merge, 13, v2Authentication);
360 setStringOrNull(merge, 14, v2ConfidentialityImpact);
361 setStringOrNull(merge, 15, v2IntegrityImpact);
362 setStringOrNull(merge, 16, v2AvailabilityImpact);
363 setStringOrNull(merge, 17, v2Version);
364 setFloatOrNull(merge, 18, v3ExploitabilityScore);
365 setFloatOrNull(merge, 19, v3ImpactScore);
366 setStringOrNull(merge, 20, v3AttackVector);
367 setStringOrNull(merge, 21, v3AttackComplexity);
368 setStringOrNull(merge, 22, v3PrivilegesRequired);
369 setStringOrNull(merge, 23, v3UserInteraction);
370 setStringOrNull(merge, 24, v3Scope);
371 setStringOrNull(merge, 25, v3ConfidentialityImpact);
372 setStringOrNull(merge, 26, v3IntegrityImpact);
373 setStringOrNull(merge, 27, v3AvailabilityImpact);
374 setFloatOrNull(merge, 28, v3BaseScore);
375 setStringOrNull(merge, 29, v3BaseSeverity);
376 setStringOrNull(merge, 30, v3Version);
377
378
379
380 setStringOrNull(merge, 31, v4version);
381 setStringOrNull(merge, 32, v4attackVector);
382 setStringOrNull(merge, 33, v4attackComplexity);
383 setStringOrNull(merge, 34, v4attackRequirements);
384 setStringOrNull(merge, 35, v4privilegesRequired);
385 setStringOrNull(merge, 36, v4userInteraction);
386 setStringOrNull(merge, 37, v4vulnConfidentialityImpact);
387 setStringOrNull(merge, 38, v4vulnIntegrityImpact);
388 setStringOrNull(merge, 39, v4vulnAvailabilityImpact);
389 setStringOrNull(merge, 40, v4subConfidentialityImpact);
390 setStringOrNull(merge, 41, v4subIntegrityImpact);
391 setStringOrNull(merge, 42, v4subAvailabilityImpact);
392 setStringOrNull(merge, 43, v4exploitMaturity);
393 setStringOrNull(merge, 44, v4confidentialityRequirement);
394 setStringOrNull(merge, 45, v4integrityRequirement);
395 setStringOrNull(merge, 46, v4availabilityRequirement);
396 setStringOrNull(merge, 47, v4modifiedAttackVector);
397 setStringOrNull(merge, 48, v4modifiedAttackComplexity);
398 setStringOrNull(merge, 49, v4modifiedAttackRequirements);
399 setStringOrNull(merge, 50, v4modifiedPrivilegesRequired);
400 setStringOrNull(merge, 51, v4modifiedUserInteraction);
401 setStringOrNull(merge, 52, v4modifiedVulnConfidentialityImpact);
402 setStringOrNull(merge, 53, v4modifiedVulnIntegrityImpact);
403 setStringOrNull(merge, 54, v4modifiedVulnAvailabilityImpact);
404 setStringOrNull(merge, 55, v4modifiedSubConfidentialityImpact);
405 setStringOrNull(merge, 56, v4modifiedSubIntegrityImpact);
406 setStringOrNull(merge, 57, v4modifiedSubAvailabilityImpact);
407 setStringOrNull(merge, 58, v4safety);
408 setStringOrNull(merge, 59, v4automatable);
409 setStringOrNull(merge, 60, v4recovery);
410 setStringOrNull(merge, 61, v4valueDensity);
411 setStringOrNull(merge, 62, v4vulnerabilityResponseEffort);
412 setStringOrNull(merge, 63, v4providerUrgency);
413 setFloatOrNull(merge, 64, v4baseScore);
414 setStringOrNull(merge, 65, v4baseSeverity);
415 setFloatOrNull(merge, 66, v4threatScore);
416 setStringOrNull(merge, 67, v4threatSeverity);
417 setFloatOrNull(merge, 68, v4environmentalScore);
418 setStringOrNull(merge, 69, v4environmentalSeverity);
419 setStringOrNull(merge, 70, v4source);
420 setStringOrNull(merge, 71, v4type);
421
422
423 if (vulnerabilityId == 0) {
424 merge.setString(72, cve);
425 } else {
426 merge.setInt(72, vulnerabilityId);
427 }
428
429 final int count = merge.executeUpdate();
430 if (vulnerabilityId == 0) {
431 try (ResultSet rs = merge.getGeneratedKeys()) {
432 if (rs.next()) {
433 vulnerabilityId = rs.getInt(1);
434 }
435 }
436 }
437 } finally {
438 if (merge != null) {
439 merge.close();
440 }
441 }
442 ret.addRow(vulnerabilityId);
443 return ret;
444 }
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463 public static void mergeKnownExploited(final Connection conn, String cveId,
464 String vendorProject, String product, String vulnerabilityName,
465 String dateAdded, String shortDescription, String requiredAction,
466 String dueDate, String notes) throws SQLException {
467
468 String id = "";
469 try (PreparedStatement selectVulnerabilityId = conn.prepareStatement("SELECT cveID FROM knownExploited cveID WHERE cveID=?")) {
470 selectVulnerabilityId.setString(1, cveId);
471 try (ResultSet rs = selectVulnerabilityId.executeQuery()) {
472 if (rs.next()) {
473 id = rs.getString(1);
474 }
475 }
476 }
477 PreparedStatement merge = null;
478 try {
479 if (Strings.isNullOrEmpty(id)) {
480 merge = conn.prepareStatement("INSERT INTO knownExploited ("
481 + "vendorProject, product, vulnerabilityName, "
482 + "dateAdded, shortDescription, requiredAction, "
483 + "dueDate, notes, cveID) "
484 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
485 } else {
486 merge = conn.prepareStatement("UPDATE knownExploited SET "
487 + "vendorProject=?, product=?, vulnerabilityName=?, "
488 + "dateAdded=?, shortDescription=?, requiredAction=?, "
489 + "dueDate=?, notes=? WHERE cveID=?");
490 }
491
492 setStringOrNull(merge, 1, vendorProject);
493 setStringOrNull(merge, 2, product);
494 setStringOrNull(merge, 3, vulnerabilityName);
495 setStringOrNull(merge, 4, dateAdded);
496 setStringOrNull(merge, 5, shortDescription);
497 setStringOrNull(merge, 6, requiredAction);
498 setStringOrNull(merge, 7, dueDate);
499 setStringOrNull(merge, 8, notes);
500 setStringOrNull(merge, 9, cveId);
501 merge.execute();
502 } finally {
503 if (merge != null) {
504 merge.close();
505 }
506 }
507 }
508
509
510
511
512
513
514
515
516
517
518 private static void setStringOrNull(PreparedStatement ps, int i, String value) throws SQLException {
519 if (value == null) {
520 ps.setNull(i, java.sql.Types.NULL);
521 } else {
522 ps.setString(i, value);
523 }
524 }
525
526
527
528
529
530
531
532
533
534 private static void setFloatOrNull(PreparedStatement ps, int i, Float value) throws SQLException {
535 if (value == null) {
536 ps.setNull(i, java.sql.Types.NULL);
537 } else {
538 ps.setFloat(i, value);
539 }
540 }
541
542
543
544
545
546
547
548
549
550 private static void setBooleanOrNull(PreparedStatement ps, int i, Boolean value) throws SQLException {
551 if (value == null) {
552 ps.setNull(i, java.sql.Types.NULL);
553 } else {
554 ps.setBoolean(i, value);
555 }
556 }
557 }