List exiting order while looping

Question

The script below captures the INTERFACE network of a HOST, and inserts into the database (POSTGRES), I swapped the cursor method for a print to check how it was being inserted into the database, and noticed that in the finished amount of inserts, the values that were to be in the IPV4 column are going to MAC column, I believe the error is in this passage:

# insere rede
rows = len(interface) 
rows2 = (len(address) - 1)
rede = 0
MAC = 0
contv4 = 1
contV6 = 2
while rede < rows:
    while contv4 < rows2:
        print("INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('%s','%s','%s','%s','%s','%s','%s','%s');" % (platform.node(),interface[rede],incoming[rede],outgoing[rede],address[MAC],address[contv4],address[contV6],str(timestamp))+"\n")
        rede = rede + 1
        MAC = MAC + 3
        contv4 = contv4 + 3
        contV6 = contV6 + 3
    break

SCRIPT:

import psutil
from datetime import datetime
import time
import threading
import itertools
import sys
from psutil._common import bytes2human
import socket
from socket import AF_INET, SOCK_STREAM, SOCK_DGRAM
import platform

timestamp = datetime.now()
p = psutil.Process()
address = []
interface = []
incoming = []
outgoing = []
stats = []
broadcast = []
netmask = []
done = False

af_map = {
    socket.AF_INET: 'IPv4',
    socket.AF_INET6: 'IPv6',
    psutil.AF_LINK: 'MAC',
}

duplex_map = {
    psutil.NIC_DUPLEX_FULL: "full",
    psutil.NIC_DUPLEX_HALF: "half",
    psutil.NIC_DUPLEX_UNKNOWN: "?",
}

AD = "-"
AF_INET6 = getattr(socket, 'AF_INET6', object())
proto_map = {
    (AF_INET, SOCK_STREAM): 'tcp',
    (AF_INET6, SOCK_STREAM): 'tcp6',
    (AF_INET, SOCK_DGRAM): 'udp',
    (AF_INET6, SOCK_DGRAM): 'udp6',
}

def animate():
    for c in itertools.cycle(['|', '/', '-', '\\']):
        if done:
            break
        sys.stdout.write('\rCarregando, aguarde por favor... ' + c + '\r')
        sys.stdout.flush()
        time.sleep(0.1)

def agent_colect():

# REDE
    stats = psutil.net_if_stats()
    io_counters = psutil.net_io_counters(pernic=True)
    for nic, addrs in psutil.net_if_addrs().items():
        interface.append("%s:" % (nic))
        if nic in io_counters:
            io = io_counters[nic]
            incoming.append("bytes=%s, pkts=%s, errs=%s, drops=%s" % (
                bytes2human(io.bytes_recv), io.packets_recv, io.errin,
                io.dropin))
            outgoing.append("bytes=%s, pkts=%s, errs=%s, drops=%s" % (
                bytes2human(io.bytes_sent), io.packets_sent, io.errout,
                io.dropout))
        for addr in addrs:
            address.append(af_map.get(addr.family)+" "+addr.address)
            if addr.broadcast:
                broadcast.append("         broadcast : %s" % addr.broadcast)
            if addr.netmask:
                netmask.append("         netmask   : %s" % addr.netmask)
    # insere rede
    rows = len(interface) 
    rows2 = (len(address) - 1)
    rede = 0
    MAC = 0
    contv4 = 1
    contV6 = 2
    while rede < rows:
        while contv4 < rows2:
            print("INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('%s','%s','%s','%s','%s','%s','%s','%s');" % (platform.node(),interface[rede],incoming[rede],outgoing[rede],address[MAC],address[contv4],address[contV6],str(timestamp))+"\n")
            rede = rede + 1
            MAC = MAC + 3
            contv4 = contv4 + 3
            contV6 = contV6 + 3
        break

    done = True
    return done

load = threading.Thread(target=animate)
load.start()
worker = threading.Thread(target=agent_colect)
done = agent_colect()

OUTPUT:

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','vEthernet (DockerNAT):','bytes=0.0B, pkts=0, errs=0, drops=0','bytes=1.1M, pkts=6654, errs=0, drops=0','MAC 00-15-5D-38-01-20','IPv4 10.0.75.1','IPv6 fe80::9de9:8978:cfc4:d262','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','ConexÒo de Rede Bluetooth:','bytes=0.0B, pkts=0, errs=0, drops=0','bytes=0.0B, pkts=0, errs=0, drops=0','MAC 04-D3-B0-C2-A9-6F','IPv4 169.254.240.79','IPv6 fe80::b1c5:80a9:f354:f04f','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','VMware Network Adapter VMnet8:','bytes=38.0B, pkts=38, errs=0, drops=0','bytes=4.6K, pkts=4699, errs=0, drops=0','MAC 00-50-56-C0-00-08','IPv4 192.168.233.1','IPv6 fe80::4d79:a42e:a656:feb','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','vEthernet (Default Switch):','bytes=0.0B, pkts=0, errs=0, drops=0','bytes=803.6K, pkts=3696, errs=0, drops=0','MAC 02-15-B7-9D-2F-F3','IPv4 192.168.187.1','IPv6 fe80::60b9:8a8c:d43f:9402','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','VirtualBox Host-Only Network:','bytes=0.0B, pkts=0, errs=0, drops=0','bytes=0.0B, pkts=0, errs=0, drops=0','MAC 0A-00-27-00-00-0A','IPv4 192.168.56.1','IPv6 fe80::741e:194f:1bc4:54b8','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','VMware Network Adapter VMnet1:','bytes=38.0B, pkts=38, errs=0, drops=0','bytes=1.4K, pkts=1436, errs=0, drops=0','MAC 00-50-56-C0-00-01','IPv4 192.168.199.1','IPv6 fe80::c490:5dfc:8b51:3fd6','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','Loopback Pseudo-Interface 1:','bytes=0.0B, pkts=0, errs=0, drops=0','bytes=0.0B, pkts=0, errs=0, drops=0','IPv4 127.0.0.1','IPv6 ::1','MAC 64-1C-67-A3-C1-1C','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','Ethernet:','bytes=234.1M, pkts=340631, errs=0, drops=0','bytes=22.8M, pkts=130525, errs=0, drops=0','IPv4 9.18.235.171','IPv6 fe80::6569:4181:ab3c:721c','MAC 04-D3-B0-C2-A9-6B','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','Wi-Fi:','bytes=58.2K, pkts=414, errs=0, drops=0','bytes=121.7K, pkts=852, errs=0, drops=0','IPv4 9.86.236.141','IPv6 fe80::69e4:d30d:fa99:b325','MAC 06-D3-B0-C2-A9-6B','2019-08-28 13:11:08.806000');

INSERT INTO tbl_rede_hosts (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) VALUES('DESKTOP-USER','ConexÒo Local* 2:','bytes=0.0B, pkts=0, errs=0, drops=0','bytes=0.0B, pkts=0, errs=0, drops=0','IPv4 169.254.151.189','IPv6 fe80::ccd7:4c4d:8a4f:97bd','MAC 04-D3-B0-C2-A9-6C','2019-08-28 13:11:08.806000');

Answer 1

The problem is that you are trying to insert your text data directly into the query, and you are using the equivalent of an 'SQL Injection" there - simply one of the text strings you are putting into the query includes a character '. As you are interpolating the string from in your code, without any care of escaping or sanitizing the data, the query is breaking.

Python has solved this problem for, possibly decades (plural), by delegating the interpolation of data to SQL queries to happen within of the call .execute SQL driver. The syntax, depending on the driver, is very similar with direct interpolation - in this case using the % - which you are using, but this way, Python executes the expression between parentheses, builds the incorrect string with the operator %, and then calls the method .execute. The correct is to pass the same arguments, but as an argument to the . execute function - and let the string interpolation happen inside.

Now, at the time of writing the example, I realized that you is not connecting to the database by Python - printing queries in stdout and using pipe to consume queries.

It’s by no means the best way to do this - perhaps the biggest reason is precisely that you miss the interpolation of parameters from the Postgresql Python driver - and miss other optimizations and security checks.

IF you were using Python in the normal way, you would connect to Postgresql with the psycopg2 driver, your query would be executed as:

    ...
    connection.execute("""INSERT INTO tbl_rede_hosts
         (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta)
         VALUES({},{},{},{},{},{},{},{})""",(
         platform.node(),interface[rede],incoming[rede],
         outgoing[rede],address[MAC],address[contv4],
         address[contV6],str(timestamp)
    )

(notice how to use triple strings, and break the statement into multiple lines facilitates as well, but that’s something part of the main problem)

Well, the recommendation strong what gets you is you change your program to connect using a postgresql driver instead of using stdout for it.

If for some reason you insist on sewing things up by shell, it will be necessary at least to escape all ' within its strings - to keep it short, it is possible to do this as an comprehension:

  print ("""INSERT INTO tbl_rede_hosts \
         (hostname,interface_rede,incoming,outcoing,mac,ipv4,ipv6,data_coleta) \
         VALUES({},{},{},{},{},{},{},{});\n""".format(
             *(parameter.replace("'", "''") for parameter in (
                  platform.node(),interface[rede],incoming[rede],
                  outgoing[rede],address[MAC],address[contv4],
                  address[contV6],str(timestamp)) )
   )