-- # Documentation how the attacks_with_seconds table was created from fieldbus_traffic_knx.attack_free_referencedataset_with_seconds .
-- ------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `attack_free_referencedataset_with_seconds` (
  `sequence_number` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp(6) NULL DEFAULT NULL,
  `source_addr` varchar(9) DEFAULT NULL,
  `destination_addr` varchar(9) DEFAULT NULL,
  `extended_frame` tinyint(1) unsigned DEFAULT NULL,
  `priority` varchar(6) DEFAULT NULL,
  `repeat` tinyint(1) unsigned DEFAULT NULL,
  `ack_req` tinyint(1) unsigned DEFAULT NULL,
  `confirm` tinyint(1) unsigned DEFAULT NULL,
  `system_broadcast` tinyint(1) unsigned DEFAULT NULL,
  `hop_count` tinyint(1) unsigned DEFAULT NULL,
  `tpci` varchar(25) DEFAULT NULL,
  `tpci_sequence` tinyint(2) unsigned DEFAULT NULL,
  `apci` varchar(50) DEFAULT NULL,
  `payload_data` varchar(256) DEFAULT NULL,
  `payload_length` smallint(5) unsigned DEFAULT NULL,
  `cemi` blob DEFAULT NULL,
  `is_manipulated` tinyint(1) unsigned DEFAULT NULL,
  `attack_type_id` int(10) unsigned DEFAULT NULL,
  `sensor_addr` varchar(9) DEFAULT NULL,
  PRIMARY KEY (`sequence_number`),
  UNIQUE KEY `sequence_number_UNIQUE` (`sequence_number`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


-- 0) Duplicate fieldbus_traffic_knx.attack_free_referencedataset_with_seconds.

CREATE TABLE IF NOT EXISTS attacks_with_seconds LIKE attack_free_referencedataset_with_seconds; 
INSERT attacks_with_seconds SELECT * FROM attack_free_referencedataset_with_seconds;


-- 1) Introduce Attack Type(AT) 1 [AT1 = manipulated HopCount]
-- description: 3.5.67 sends 76 times allways to 0/3/3 with hop count 4 -> replacing hop count with new value of 5

UPDATE attacks_with_seconds
SET hop_count = 5, attack_type_id = 1, is_manipulated = TRUE WHERE source_addr = '3.5.67';

-- 2) Introduce Attack Type(AT) 2 [AT2 = Introduce unknown src-address]
-- description: New source_address '3.5.76' sends to 0/3/3 with hop count 4 (using the same telegrams as 3.5.67 sended before alteration)

CREATE TABLE IF NOT EXISTS `unknown_src_address_telegrams` LIKE attack_free_referencedataset_with_seconds; 

INSERT INTO unknown_src_address_telegrams(`timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
 confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr)
    SELECT `timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
 confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr FROM
 attack_free_referencedataset_with_seconds
 WHERE source_addr = '3.5.67';

UPDATE unknown_src_address_telegrams
SET attack_Type_id = 2, is_manipulated = TRUE, source_addr = '3.5.76';
  
INSERT INTO attacks_with_seconds(`timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
 confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr)
    SELECT `timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
 confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr FROM unknown_src_address_telegrams;

DROP TABLE `unknown_src_address_telegrams`;

 -- 3) Introduce AT 3 - Series [moved timestamps] (AT 3={AT 301, AT 302, AT 303, AT 304})
 -- decription: shifting entire timewindow of telegrams to differnt time without altering intermeediate time of shifted telegrams
 
   --  AT 301) move some telegrams 1 second into the future [possibly a MITM attack simulation],
        -- Src 3.6.14 sends 193 times to 1/1/28
        
        UPDATE attacks_with_seconds
        SET `timestamp` = DATE_ADD(`timestamp`, INTERVAL '1' SECOND), is_manipulated = TRUE, attack_type_id = 301 WHERE source_addr = '3.6.14' and destination_addr = '1/1/28';

        
   --  AT 302) move 12 h into future [unusual night activity],
        -- Src 3.6.14 sends 220 times to 1/1/26. 85 times with timestamps < '2016-08-01 00:00:00'. 
        -- Those 85 sendings are placed 12 hours into their future to mimic activity during unusual hours for a office building
        
        UPDATE attacks_with_seconds
        SET `timestamp` = DATE_ADD(`timestamp`, INTERVAL '12' HOUR), is_manipulated = TRUE, attack_type_id = 302 WHERE source_addr = '3.6.14' and timestamp < '2016-08-01 00:00:00' and destination_addr = '1/1/26';
           
        
   --  AT 303) move 2 days into future [replay log attack, unusual Weekend activity],
        -- Src 3.6.14 sends 220 times to 1/1/26. 135 times with timestamps >= '2016-08-01 00:00:00'. 
        -- Those 135 sendings are placed 2 days into their future to mimic activity during unusual activities for an office building such as on weekends.
        
        UPDATE attacks_with_seconds
        SET `timestamp` = DATE_ADD(`timestamp`, INTERVAL '2' DAY), is_manipulated = TRUE, attack_type_id = 303 WHERE source_addr = '3.6.14' and timestamp >= '2016-08-01 00:00:00' and destination_addr = '1/1/26';        
        
        
   --  AT 304) move 1 h into past [timing attack on collectors, daylight saving time change],
        -- Src 3.6.14 sends 189 times to 1/1/27
        
        UPDATE attacks_with_seconds
        SET `timestamp` = DATE_ADD(`timestamp`, INTERVAL '-1' Hour), is_manipulated = TRUE, attack_type_id = 304 WHERE source_addr = '3.6.14' and destination_addr = '1/1/27';    


 -- 4) Introduce AT 4 [Replay Attack telegrams]
  -- decription: simulate replay attack by duplicating telegrams send from 3.6.46 to 1/1/75 and 1/1/76 with small temporal dalay of 20 seconds on '2016-08-24' and '2016-08-25'
 
CREATE TABLE IF NOT EXISTS `replay_telegrams` LIKE attack_free_referencedataset_with_seconds; 
INSERT INTO `replay_telegrams` SELECT * FROM attack_free_referencedataset_with_seconds WHERE source_addr = '3.6.46' and `timestamp` >= '2016-08-24 00:00:00' and `timestamp` < '2016-08-26 00:00:00';

UPDATE `replay_telegrams`
SET `timestamp` = DATE_ADD(`timestamp`, INTERVAL '20' SECOND), is_manipulated = TRUE, attack_type_id = 4;

INSERT INTO attacks_with_seconds(`timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
 confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr)
 SELECT `timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
 confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr FROM `replay_telegrams`;


 -- 5) Introduce AT 5 [Negation Attack telegrams]
   -- decription: simulate negation attacks. Every time 3.6.35 sends a telegram with payload_data 1 (turning on the light) to 1/1/12 or 1/1/13, immediately send
    -- a telegram to the same target with payload_data 0 (turning off the light again), effectively rendering the light useless

CREATE TABLE IF NOT EXISTS `negation_telegrams` LIKE attack_free_referencedataset_with_seconds; 
INSERT INTO `negation_telegrams` SELECT * FROM attack_free_referencedataset_with_seconds WHERE source_addr = '3.6.35' and payload_data = 1;

UPDATE `negation_telegrams`
SET is_manipulated = TRUE, attack_type_id = 5, payload_data = 0;

INSERT INTO attacks_with_seconds(`timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
  confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr)
  SELECT `timestamp`, source_addr, destination_addr, extended_frame, priority, `repeat`, ack_req,
  confirm, system_broadcast, hop_count, tpci, tpci_sequence, apci, payload_data, payload_length, cemi, is_manipulated, attack_type_id, sensor_addr FROM `negation_telegrams`;
   
   
 -- 6) Introduce AT 6 [remove telegrams]
 -- description: 3.5.66 sends 1068 telegrams in `attack_free_referencedataset_with_seconds` that have been removed from the log in `attacks_with_seconds`, simulating an attack that removes a device from the bus
 
DELETE FROM attacks_with_seconds WHERE source_addr = '3.5.66';

-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- # General information
-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- ## Temporal order in attacks_with_seconds:
-- ----------------------_-------------------
-- Temporal order of telegrams within the resulting attacks_with_seconds table is described by timestamp and in case of identical timestamps the lower sequence_number 'happened' first.

-- ## Attack Type IDs:
-- -------------------
-- In order to filter various different attacks later on, the db-entries contain a field attack_type_id. The values are explained below:

-- |----------------------------------------------------------------------------------------------------------------------------------------------------|
-- | attack_type_id | meaning                                                      | affected addresses                                                 |
-- |----------------------------------------------------------------------------------------------------------------------------------------------------|
-- | 1              | manipulated hop_count values (5 instead of 4)                | 3.5.67                                                             |
-- | 2              | new source_addr                                              | 3.5.76                                                             |
-- | 301            | telegrams moved 1 second into the future                     | 3.6.14 --> 1/1/28                                                  |
-- | 302            | telegrams moved 12 hours into the future                     | 3.6.14 --> 1/1/26 (with timestamps < '2016-08-01 00:00:00')        |
-- | 303            | telegrams moved 2 days into the future                       | 3.6.14 --> 1/1/26 (with timestamps >= '2016-08-01 00:00:00')       |
-- | 304            | telegrams moved 1 hour into the past                         | 3.6.14 --> 1/1/27                                                  |
-- | 4              | replay attack (resending telegrams from 20 seconds ago)      | 3.6.46 --> 1/1/75 and 1/1/76 ( on '2016-08-24' and '2016-08-25')   |
-- | 5              | negation attacks (sending opposite payload - 0 instead of 1) | 3.6.35 -> 1/1/12 and 1/1/13                                        |
-- | 6              | deleted telegrams                                            | 3.5.66                                                             |
-- |----------------------------------------------------------------------------------------------------------------------------------------------------|
     
-- ## Sequence Number matching:
-- ----------------------------
-- In case one wants to match sequence_numbers of telegrams from attacks_with_seconds with those from attack_free_referencedataset_with_seconds, the following applies:
-- For AT 1: They match directly
-- For AT 2: New introduced telegrams do not have a sequence_number in attack_free_referencedataset_with_seconds, since they have not existed yet
-- For AT 3: They match directly
-- For AT 4: Table replay_telegrams shows the same telegrams that have been introduced into attacks_with_seconds but with the original sequence_number as in attack_free_referencedataset_with_seconds
-- For AT 5: Table negation_telegrams shows the same telegrams that have been introduced into attacks_with_seconds but with the original sequence_number as in attack_free_referencedataset_with_seconds
-- For AT 6: Deleted telegrams do not have a sequence_number in attacks_with_seconds anymore            
