140 lines
4.4 KiB
PHP
140 lines
4.4 KiB
PHP
<?php
|
|
|
|
/**
|
|
* File: parse_procedurals.php
|
|
* Purpose: Script to populate procedural database tables from Excel file
|
|
* Author: Ryan Prather
|
|
* Created: Jan 5, 2015
|
|
*
|
|
* Portions Copyright 2016: Cyber Perspectives, All rights reserved
|
|
* Released under the Apache v2.0 License
|
|
*
|
|
* Portions Copyright (c) 2012-2015, Salient Federal Solutions
|
|
* Portions Copyright (c) 2008-2011, Science Applications International Corporation (SAIC)
|
|
* Released under Modified BSD License
|
|
*
|
|
* See license.txt for details
|
|
*
|
|
* Change Log:
|
|
* - Jan 5, 2015 - File created
|
|
* - Sep 1, 2016 - Copyright Updated, added file header, and
|
|
* converted to constants
|
|
*/
|
|
$cmd = getopt("", array('debug::', "help::"));
|
|
|
|
if (isset($cmd['help'])) {
|
|
usage();
|
|
exit;
|
|
}
|
|
|
|
include_once 'config.inc';
|
|
require_once 'PHPExcel.php';
|
|
include_once 'helper.inc';
|
|
|
|
$db = new mysqli(DB_SERVER, 'web', db::decrypt_pwd());
|
|
if ($db->connect_error) {
|
|
print $db->connect_error;
|
|
die;
|
|
}
|
|
|
|
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_sqlite;
|
|
$cacheSettings = array(
|
|
'memoryCacheSize' => '512MB'
|
|
);
|
|
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
|
|
$Reader = PHPExcel_IOFactory::createReaderForFile("8500.2_IA_Controls_and_Validation_Procedures.xls");
|
|
$Reader->setReadDataOnly(true);
|
|
$objPHPExcel = $Reader->load("8500.2_IA_Controls_and_Validation_Procedures.xls");
|
|
if (false) {
|
|
$objPHPExcel = PHPExcel_IOFactory::load("8500.2_IA_Controls_and_Validation_Procedures.xls");
|
|
}
|
|
|
|
$wksht = $objPHPExcel->getSheetByName("All 8500.2 IA Controls");
|
|
$lastrow = $wksht->getHighestDataRow();
|
|
for ($row = 2; $row <= $lastrow; $row++) {
|
|
$ia_id = $wksht->getCell("A$row")->getValue();
|
|
$name = htmlentities($wksht->getCell("B$row")->getValue());
|
|
$sub = $wksht->getCell("C$row")->getValue();
|
|
$desc = htmlentities($wksht->getCell("D$row")->getValue());
|
|
$tvcm = htmlentities($wksht->getCell("E$row")->getValue());
|
|
$gen_guide = htmlentities($wksht->getCell("F$row")->getValue());
|
|
$sys_spec = htmlentities($wksht->getCell("G$row")->getValue());
|
|
$impact = strtolower($wksht->getCell("H$row")->getValue());
|
|
|
|
$sql = "REPLACE INTO sagacity.proc_ia_controls (control_id, `name`, subject_area, description, threat_vul_cm, gen_imp_guide, guide_resource, impact) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
|
|
if (!$sth = $db->prepare($sql)) {
|
|
error_log($db->error);
|
|
continue;
|
|
}
|
|
$sth->bind_param('ssssssss', $ia_id, $name, $sub, $desc, $tvcm, $gen_guide, $sys_spec, $impact);
|
|
$sth->execute();
|
|
$sth->close();
|
|
|
|
$sql = "REPLACE INTO sagacity.proc_level_type (proc_control, `type`, level, class) VALUES (?, ?, ?, ?)";
|
|
if (!$sth = $db->prepare($sql)) {
|
|
error_log($db->error);
|
|
continue;
|
|
}
|
|
|
|
$control_type = 'diacap';
|
|
|
|
for ($idx = 8; ($col = PHPExcel_Cell::stringFromColumnIndex($idx)) < 'R'; $idx++) {
|
|
$val = $wksht->getCell($col . $row)->getValue();
|
|
$head = $wksht->getCell($col . "1")->getValue();
|
|
if ($val == 'Y') {
|
|
$type = explode(' - ', $head);
|
|
$lvl = substr_count($type[0], 'I');
|
|
switch ($type[1]) {
|
|
case 'CL':
|
|
$class = 'cl';
|
|
break;
|
|
case 'S':
|
|
$class = 'sen';
|
|
break;
|
|
case 'P':
|
|
$class = 'pub';
|
|
break;
|
|
}
|
|
|
|
$sth->bind_param('ssss', $ia_id, $control_type, $lvl, $class);
|
|
$sth->execute();
|
|
}
|
|
}
|
|
|
|
$sth->close();
|
|
}
|
|
|
|
$wksht = $objPHPExcel->getSheetByName("All Validation Procedures");
|
|
$lastrow = $wksht->getHighestDataRow();
|
|
for ($row = 2; $row <= $lastrow; $row++) {
|
|
$parent_id = $wksht->getCell("A$row")->getValue();
|
|
$sub_id = $wksht->getCell("B$row")->getValue();
|
|
$name = htmlentities($wksht->getCell("C$row")->getValue());
|
|
$obj = htmlentities($wksht->getCell("D$row")->getValue());
|
|
$prep = htmlentities($wksht->getCell("E$row")->getValue());
|
|
$script = htmlentities($wksht->getCell("F$row")->getValue());
|
|
$exp = htmlentities($wksht->getCell("G$row")->getValue());
|
|
|
|
$sql = "REPLACE INTO sagacity.proc_ia_sub_controls (sub_control_id, parent_control_id, `name`, objective, prep, `script`, exp_result) VALUES (?, ?, ?, ?, ?, ?, ?)";
|
|
|
|
if (!$sth = $db->prepare($sql)) {
|
|
error_log($db->error);
|
|
continue;
|
|
}
|
|
|
|
$sth->bind_param('sssssss', $sub_id, $parent_id, $name, $obj, $prep, $script, $exp);
|
|
$sth->execute();
|
|
$sth->close();
|
|
}
|
|
|
|
function usage() {
|
|
print <<<EOO
|
|
Purpose: To import the DIACAP IA control library
|
|
|
|
Usage: php parse_procedurals.php [--help]
|
|
|
|
--help This screen
|
|
|
|
EOO;
|
|
}
|