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
Since the subject of this thesis study is “comparing machine learning methods that can be used to detect attacks on RPL-based internet of things devices,” the RPL protocol will be focused a little more.
RPL is short for “Routing Protocol for Low-Power and Lossy Networks”. The RPL protocol is a network layer protocol created by IETF in March 2012 to enable devices to operate on low-power and lossy networks. (Winter, et al., March 2012 3)
IoT devices have limited processing power, memory, and generally limited energy because they work on batteries. Therefore, it is inevitable that these devices will connect cost-effectively and use their limited resources most effectively. Moreover, suppose there are multiple IoT devices in the environment. In addition, these networks can contain thousands of nodes. They must be modeled by connecting point to multipoint or multipoint to point, not point to point, to use limited resources effectively. The origin of the RPL protocol arises from this idea.
In order to understand the RPL protocol, it is necessary to understand the terms.
DAG (Directed Acyclic Graph): Directed acyclic graph. It is the orientation of n nodes to each other so that they do not form a closed loop. The example is shown in a DAG Figure 2.5.
DAG Root: Target of nodes in DAG. In the example in Figure 2.5, the node “E” is the DAG root. It does not go to another node.
DODAG (Destination-Oriented DAG): A particular type of DAG in which each node wants to achieve a single goal. DODAG and other definitions are seen in Figure 2.6.
DODAG Root: The root to which the nodes of each resulting DODAG are connected.
Virtual DODAG root: A Virtual DODAG root results from two or more RPL routers; for example, 6LoWPAN Boundary Routers (routers) coordinate to synchronize DODAG state on low-power and lossy networks and act together, such as a single DODAG root (with multiple interfaces).
Up: Any edge directed towards the root.
Down: Any edge that is oriented away from the root.
Rank: The distance of a node to the DODAG root. Rank increases downwards and falls upwards. Rank is calculated based on DODAG’s Target Function.
Objective Function (OF): A function determines how far a node is from the root. Here the concept of distance is determined by the programmer or designer. Here “distance” is the parameter that we must minimize the grip. This parameter can be energy or latency.
Objective Code Point (OCP) An OCP is an identifier that specifies which Target Function DODAG uses.
RPLInstanceID: An RPL Instance ID is a unique identifier within a network. DODAGs with the same RPL event ID share the same target function.
RPL Instance: An RPL Instance is one or more DODAG sets that share an RPL event ID. Each RPL event runs independently of other RPL events. At most, an RPL node can belong to a DODAG in an RPL Instance.
DODAGID: The DODAG ID is the identifier of a DODAG root. It is unique as part of an RPL Example in DODAGID, LLN (Low-Power and Lossy Networks). Each DODAG has an IPv6 ID (128-bit). This identity is exported only to the DODAG root and does not change unless the root id changes.
DODAG Version: A DODAG Version is a specific iteration of a DODAG with a specific DODAGID. Each new form of DODAG means a new version.
DODAG Version Number: DODAG Version Number is a sequential counter that is increased by the root to create a new Version of a DODAG. A DODAG Version is uniquely identified by the title (RPL Event ID, DODAG ID, DODAG Version Number).
Goal: The goal is where a DODAG wants to reach. It can be a wired or wireless network. The goal is different from the Purpose function. Our goal in the purpose function is to minimize. However, Target is where we want to go.
Grounded: A DODAG is grounded when the DODAG root can satisfy the Goal. Floating: DODAG swims if not grounded. A floating DODAG is not expected to have the necessary features to meet the goal. However, it can provide connections to other nodes in DODAG.
DODAG parent: The parent is where the arrows point. A child is where the arrow comes from. Parents can have more than one child. Similarly, a child can have more than one parent.
Sub-DODAG: A subtree of a specific DODAG.
Local DODAG: Local DODRIes contain only one root node, allowing that single root node to allocate and manage an RPL Instance identified by a local RPL Event ID without coordination with other nodes. Typically, this is done to optimize routes to a destination within the LLN.
Global DODAG: Global DODAG is DODAG with RPL Event ID, which can be coordinated across many other nodes.
Stored: Stored nodes save all routing table information. These nodes know only their parent nodes.
Non-Stored: They are simple and do not store any routing table; they only know their parents.
Below are the terms for messages sent between nodes.
DIO:(DODAG Information Object): This message is multicasted downward. A specific node in a DODAG multicasts this message to let you know if other nodes are grounded or stored.
DIS (DODAG Information Solicitation-DODAG Request information): When the announcement is not heard, and a node wants to join a DODAG, it sends a control message to know if there is any DODAG for it.
DAO (DODAG Advertisement Object-DODAG Announcement Object): A request sent by a child node to the parent node or root. A node requests permission to join a DODAG as a child node with this message.
DAO-ACK (DODAG Advertisement Object Acknowledgement): A response sent to the child by a root or parent can be Yes or No. (Winter, et al., March 2012 1)
The root is a particular node in DODAG. Not all nodes have the ability to be rooted in DODAG.
Let us have a DODAG of A, B, C, D, E, F, and G nodes. A node is the root node. The offsets of the other nodes are B=1, C=1, D=5, E=8, F=8, G=9, respectively. in this case, the following steps will be
In the new case, the distance of the D, E, F, G nodes to node B is 2,3,5,6, respectively. To node C, the distance of the D, E, F, and G nodes shall be 3,5,4,7, respectively.
As a standout feature of DODAG, it is possible to say that it has only one root node, other nodes contain information about the parent node, but no node contains information about the child nodes.
The RPL protocol uses the IPv6 protocol.
1. Shelby, Z., & Bormann, C. (2009). 6LoWPAN: The Wireless Embedded Internet. UK: WILEY. (Back)
2. Raj, P., & Raman, A. (2017). The Internet of Things: Enabling Technologies, Platforms, and Use Cases. New York: CRC Press. (Back)
3. Winter, T., Thubert, P., Brandt, A., Hui, J., Kelsey, R., Levis, P., . . . Alexander, R. (Mart 2012). RPL: IPv6 Routing Protocol for Low-Power and Lossy Networks. Internet Engineering Task Force. https://www.hjp.at/doc/rfc/rfc6550.html (Back)
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.