Python Programming
Export Tables from Access to PostgreSQL
This article describes how to export a table from Access DB to postgresql via excel and python.
March 14, 2023
No Comments
Menu
- mukiraz@mukiraz.com
I explained my master’s thesis titled ” Comparison of Machine Learning Algorithms for the Detection of RPL-Based IoT Devices Vulnerability” by using this blog page. So far, I have provided detailed information about the RPL protocol and the attacks that take place in the RPL protocol. Then, I experimented with Flooding Attack, Version Number Increase Attack, and Decreased Rank Attack, extracting the raw data, and making meaning of that raw data. In this section, I will compare the results of experiments with weak motes with statistical methods. Statistical methods will tell us if machine learning methods are working properly.
In Table 3.6, the mean, median, maximum, minimum, standard deviation, skewness, and kurtosis values of the meaningful data tables of the Decreased Rank Attack Experiment with Normal Motes, Flooding Attack Experiment with Normal Node, Version Number Increase Attack Experiment with Normal Mote, Decreased Rank Attack Experiment with Malicious Mote, Flooding Attack Experiment with Malicious Mote, Version Number Increase Attack Experiment with Malicious Mote are shown. With these obtained data, it is aimed to interpret the anomaly.
In Flooding Attacks and Version Number Increase Attacks, a significant increase in packet count averages has been observed. While the packet averages of normal nodes were 21-25, these two attacks increased to 34 and 81. In flooding attacks, the average of the destination mote ratio and destination mote duration is higher than the others. In the Version Number Increase Attack, the average total packet duration remained low compared to the others. In flooding attacks, the average total packet length is 5713.654, while the data for other averages are in the 1900-2900 band. Again, in flooding attacks, the destination packet ratio remained high compared to other averages. An increase in the DIO message average was detected in all attacks. In the averages of DIS, DAO, and other message counts, flooding attacks again appear to be abnormal compared to other averages. This is because, in flood attacks, the vulnerable node constantly transmits DIS messages. The acceptance of sent DIS messages by other nodes has also increased the average of other messages. This difference is visible in the simulation.
In Flooding Attacks and Version Number Increase Attacks, packet counts are high in their median compared to other medians. In Flooding Attacks and Version Number Increase Attacks, the median of the source mote ratio, source mote duration, and source packet ratio is low compared to other medians, and the median of total packet duration and total packet length is higher than other medians. In a version number increase attack, the median number of DIOs is greatly differentiated from the others. The others are 0.5 and 7, while the median number of DIOs is 26. This is because when the version number is updated, the DODAG structure reoccurs, and the nodes transmit a greater number of DIO messages. In the version number increase attack, the increase in the median of the DIO number has an important place in detecting the anomaly.
In all attack types, the maximum values of the total packet times differ from the other maximums. In Decreased Rank Attack, the maximum total packet duration value is excessively large compared to non-attack values, while in Flooding and Version Number Increase Attacks, this value is excessively small compared to other non-attack values. In all attack types, the maximum values of the number of DIOs are quite high. In simulations with benign nodes, these values are in the 30-63 band, while in all attacks they are in the 197-214 band. In Flooding Attacks, DIS messages appear to occur abnormally because the vulnerable node is constantly transmitting DIS messages. While there are no DIS messages in other data sets, there appears to be an abnormal increase in flood attacks. Since other nodes also respond to incoming DIS messages, the obvious increase in the number of other messages has caused the maximum value to be excessively high.
In flooding attacks, the target mote ratio minimum value is differentiated from the others.
In Flooding Attacks and Version Number Increase Attacks, the number of packets, and the total packet length standard deviation values are excessively high compared to other values, and the total packet duration of standard deviation values are excessively low compared to other values. In Decreased Rank Attack, the standard deviation value of the number of DIOs remained significantly higher than other values. In Flooding Attacks, the standard deviation value is also formed because the vulnerable node sends a continuous DIS message. In Version Number Increase Attacks, the standard deviation value of the number of DAO is higher than the others.
The number of packets for overflow attacks, destination mote rate, destination mote duration, total packet duration, and destination packet rate skewness is quite small compared to other data. The skewness of the DIO number in all attacks was high compared to simulation data with the normal node. In Flooding Attacks, the skewness value is also created because the vulnerable node constantly sends DIS messages.
The number of packets in Flooding Attacks is considerably lower than the frequency of other data. In Decreased Rank Attacks, the total packet duration and DIO count skewness are extremely high compared to other flatness data. In Flooding Attacks, the number of DAO messages is extremely high compared to other skewness data.
Given all the statistical data, there is a serious anomaly in Flooding Attacks. This anomaly has also resulted in its detection at a high rate by machine learning algorithms. Especially in this attack, the formation of DIS messages made a serious difference compared to normal simulation data.
It has been observed that the statistical data of Version Number Increase Attacks also differs according to simulation data with normal nodes.
The most obvious statistical data for the detection of Decreased Rank Attacks are the total packet duration and the number of DIOs.
This article describes how to export a table from Access DB to postgresql via excel and python.
In this blog post, the Flooding Attack, Decreased Rank Attack and Version Number Increase Attack in the RPL protocol were trained and detected by “Decision Tree”, “Logistic Regression”, “Random Forest”, “Naive Bayes”, “K Nearest Neighbor” and “Artificial Neural Networks” algorithms.
The test results for the attacks were compared, as a result of the comparison, the Artificial Neural Networks algorithm with an accuracy rate of 97.2% in the detection of Flooding Attacks, the K Nearest Neighbor algorithm with an accuracy rate of 81% in the detection of Version Number Increase Attacks, and the Artificial Neural Networks with an accuracy rate of 58% in the detection of Decreased Rank attacks algorithm has been found to show success.
I continue to share how I did my master’s thesis titled Comparison of Machine Learning Algorithms for the Detection of Vulnerability of RPL-Based IoT Devices, my experiences in this process, and the codes in this thesis in a series of articles on my blog.
So far, I have provided detailed information about the RPL protocol and the attacks that take place in the RPL protocol. Then, I experimented with Flooding Attacks, Version Number Increased Attack, and Decreased Rank Attack, extracting the raw data and making sense of that raw data. I compared the results of experiments with weak knots with statistical methods.
In this section, I will interpret the numerical results of the attacks we detect with machine learning algorithms.