From 77c3db5c43ea43ad9833a7778542a7285b0891f6 Mon Sep 17 00:00:00 2001 From: Jannik Schönartz Date: Fri, 22 Dec 2017 17:37:34 +0100 Subject: [usb-lock-off] Switched to the _hw db structure. Adding a device and db contraints working properly. WIP --- .../statistics/inc/devicetype.inc.php | 1 + modules-available/usblockoff/api.inc.php | 104 +++++++++++++++++++-- modules-available/usblockoff/config.json | 2 +- modules-available/usblockoff/install.inc.php | 39 +++++++- modules-available/usblockoff/page.inc.php | 63 ++++++++++++- 5 files changed, 195 insertions(+), 14 deletions(-) (limited to 'modules-available') diff --git a/modules-available/statistics/inc/devicetype.inc.php b/modules-available/statistics/inc/devicetype.inc.php index 41ee237d..571d6d2c 100644 --- a/modules-available/statistics/inc/devicetype.inc.php +++ b/modules-available/statistics/inc/devicetype.inc.php @@ -3,4 +3,5 @@ class DeviceType { const SCREEN = 'SCREEN'; + const USB = 'USB'; } diff --git a/modules-available/usblockoff/api.inc.php b/modules-available/usblockoff/api.inc.php index 14bc6805..318f21a1 100644 --- a/modules-available/usblockoff/api.inc.php +++ b/modules-available/usblockoff/api.inc.php @@ -10,18 +10,40 @@ function HandleParameters() $serial = Request::get('serial', '', 'sting'); $name = Request::get('name', '', 'string'); $ip = Request::get('ip', 0, 'string'); - $ruleInformation['hash'] = Request::get('hash', '', 'string'); - $ruleInformation['parent-hash'] = Request::get('parent-hash', '', 'string'); - $ruleInformation['via-port'] = Request::get('via-port', '', 'string'); - $ruleInformation['with-interface'] = Request::get('with-interface', '', 'string'); - $ruleInformation['interface-policy'] = Request::get('interface-policy', '', 'string'); - newDevice($id, $serial, $name, $ip, $ruleInformation); + $client = Database::queryFirst("SELECT m.machineuuid AS 'muid', m.currentuser AS 'user' FROM machine AS m WHERE m.clientip=:ip", array('ip' => $ip)); + + // $ruleInformation['hash'] = Request::get('hash', '', 'string'); + // $ruleInformation['parent-hash'] = Request::get('parent-hash', '', 'string'); + // $ruleInformation['via-port'] = Request::get('via-port', '', 'string'); + // $ruleInformation['with-interface'] = Request::get('with-interface', '', 'string'); + // $ruleInformation['interface-policy'] = Request::get('interface-policy', '', 'string'); + // newDevice($id, $serial, $name, $ip, $ruleInformation); + // TODO: product and vendor id necessary? It's already in the hwname part. + list($vid, $pid) = explode(':', $id); + $hwProps = array( + 'vendorid' => $vid, + 'productid' => $pid, + 'name' => $name + ); + // TODO: WITH INTERFACE in the HW table?! Should be equal for every device but not guaranteed (ODROID). + $deviceProps = array( + 'hash' => Request::get('hash', '', 'string'), + 'parent-hash' => Request::get('parent-hash', '', 'string'), + 'via-port' => Request::get('via-port', '', 'string'), + 'with-interface' => Request::get('with-interface', '', 'string'), + 'interface-policy' => Request::get('interface-policy', '', 'string'), + 'machineuuid' => $client['muid'], + 'user' => $client['user'], + 'lastseen' => time() + ); + newDevice($id, $serial, $hwProps, $deviceProps); } elseif ($getAction == "deletedevice") { $serial = Request::get('serial', '', 'string'); deleteDevice($serial); } } + /** * Adds a new USB-Device to the db. * @@ -29,6 +51,73 @@ function HandleParameters() * @param string $serial USB-Device serial number. * @param string $name USB-Device name. */ +function newDevice($id, $serial, $hwProps, $deviceProps) +{ + // Add or Update the usb device in the statistic_hw table. + $hwid = (int)Database::insertIgnore('statistic_hw', 'hwid', array( + 'hwtype' => DeviceType::USB, + 'hwname' => $id)); + // TODO: Is it okay to use the id (vendor:product) as hwname to identify a usb device? + + // Add all the global prop values to the statistics_hw_prop table. + // productid, vendorid, name, interfaces + // TODO: + addHwProps('statistic_hw_prop', $hwid, $hwProps); + + // Add the hwid -> serial in the usblockoff_hw table if not already existent. + $dbquery2 = Database::queryFirst("Select * FROM `usblockoff_hw` WHERE hwid=:hwid AND serial=:serial", array( + 'hwid' => $hwid, + 'serial' => $serial)); + + if (empty($dbquery2)) { + Database::exec("INSERT INTO `usblockoff_hw` (hwid, serial) VALUES (:hwid, :serial)", array( + 'hwid' => $hwid, + 'serial' => $serial + )); + } + + // Add all the prop values to the usblockoff_hw_prop table. + // PROP: serial, machineuuid, time, user, ruleInformation, Port, hash, interface-policy + addUSBHwProps('usblockoff_hw_prop', $hwid, $serial, $deviceProps); + + echo "Successfully added"; +} + +function addHwProps($table, $hwid, $propArray) { + foreach ($propArray as $prop => $value) { + if (empty($value)) { + continue; + } + Database::exec("INSERT INTO " . $table . " (hwid, prop, value) VALUES (:hwid, :prop, :value) ON DUPLICATE KEY UPDATE value=:value", array( + 'hwid' => $hwid, + 'prop' => $prop, + 'value' => $value + )); + } +} + +function addUSBHwProps($table, $hwid, $serial, $propArray) { + foreach ($propArray as $prop => $value) { + if (empty($value)) { + continue; + } + Database::exec("INSERT INTO " . $table . " (hwid, serial, prop, value) VALUES (:hwid, :serial, :prop, :value) ON DUPLICATE KEY UPDATE value=:value", array( + 'hwid' => $hwid, + 'serial' => $serial, + 'prop' => $prop, + 'value' => $value + )); + } +} + +/** + * Adds a new USB-Device to the db. + * + * @param string $id USB-Device id. + * @param string $serial USB-Device serial number. + * @param string $name USB-Device name. + */ +/* VERSION WITH OLD DB --------------------------------------------------------------------------------------- function newDevice($id, $serial, $name, $ip, $ruleInformation) { $NOW = time(); @@ -63,12 +152,13 @@ function newDevice($id, $serial, $name, $ip, $ruleInformation) } } - +*/ /** * Deletes a device from the db given a serial number. * * @param string $serial USB-Device serial number. */ +// TODO: Edit for the new db struct. function deleteDevice($serial) { $dbquery = Database::exec("DELETE FROM `usb_devices` WHERE serial=:serial", array('serial' => $serial)); diff --git a/modules-available/usblockoff/config.json b/modules-available/usblockoff/config.json index 59b54a80..f15ba11d 100644 --- a/modules-available/usblockoff/config.json +++ b/modules-available/usblockoff/config.json @@ -1,4 +1,4 @@ { "category":"main.beta", - "dependencies": ["bootstrap_switch", "bootstrap_dialog"] + "dependencies": ["bootstrap_switch", "bootstrap_dialog", "statistics"] } diff --git a/modules-available/usblockoff/install.inc.php b/modules-available/usblockoff/install.inc.php index 690f45d4..967771d1 100644 --- a/modules-available/usblockoff/install.inc.php +++ b/modules-available/usblockoff/install.inc.php @@ -1,7 +1,7 @@ getUsbDeviceList(); +/* $dbquery = Database::simpleQuery("SELECT * FROM `usb_devices`"); while ($entry = $dbquery->fetch(PDO::FETCH_ASSOC)) { $locationquery = Database::queryFirst("SELECT l.locationname AS 'name', m.clientip AS 'ip' FROM machine AS m JOIN location AS l ON l.locationid=m.locationid @@ -211,7 +212,7 @@ class Page_usblockoff extends Page $device['via-port'] = $ruleInformation['via-port']; $device['with-interface'] = $ruleInformation['with-interface']; $usbdevices[] = $device; - } + }*/ $settings = array(); $setting = array(); @@ -255,4 +256,58 @@ class Page_usblockoff extends Page 'settings' => array_values($settings) )); } + + private function getUsbDeviceList() { + $usbdevices = array(); + + // TODO: Per USB Device 3 querys are executed.. better build a more complex sql query? + + $dbquery = Database::simpleQuery("SELECT * FROM `usblockoff_hw`"); + while ($entry = $dbquery->fetch(PDO::FETCH_ASSOC)) { + + $device = array(); + + // Get all props from the hw table. + $dbquery2 = Database::simpleQuery("SELECT * FROM `statistic_hw_prop` WHERE hwid=:hwid", array( + 'hwid' => $entry['hwid'] + )); + + while ($prop = $dbquery2->fetch(PDO::FETCH_ASSOC)) { + $device[$prop['prop']] = $prop['value']; + } + + // Get all props from the device table. + $dbquery3 = Database::simpleQuery("SELECT * FROM `usblockoff_hw_prop` WHERE hwid=:hwid AND serial=:serial", array( + 'hwid' => $entry['hwid'], + 'serial' => $entry['serial'] + )); + + while ($prop = $dbquery3->fetch(PDO::FETCH_ASSOC)) { + $device[$prop['prop']] = $prop['value']; + } + if (!empty($device['machineuuid'])) { + $locationquery = Database::queryFirst("SELECT l.locationname AS 'name', m.clientip AS 'ip' FROM machine AS m JOIN location AS l ON l.locationid=m.locationid + WHERE m.machineuuid=:machineuuid", array('machineuuid' => $entry['machineuuid'])); + $device['clientip'] = $locationquery['ip']; + $device['location'] = $locationquery['name']; + } + + //$device['uid'] = $entry['uid']; + $device['id'] = $device['vendorid'] . ":" . $device['productid']; + //$device['name'] = $entry['name']; + $device['serial'] = $entry['serial']; + //$device['machineuuid'] = $entry['machineuuid']; + //$device['user'] = $entry['user']; + $device['date'] = date('d.m.Y', $device['lastseen']); + $device['time'] = date('G:i', $device['lastseen']); + //$ruleInformation = json_decode($entry['ruleInformation'], true); + //$device['hash'] = $ruleInformation['hash']; + //$device['parent-hash'] = $ruleInformation['parent-hash']; + //$device['via-port'] = $ruleInformation['via-port']; + //$device['with-interface'] = $ruleInformation['with-interface']; + $usbdevices[] = $device; + } + + return $usbdevices; + } } -- cgit v1.2.3-55-g7522