| skipped 35 lines |
36 | 36 | | db_file.close() |
37 | 37 | | |
38 | 38 | | |
39 | | - | |
40 | 39 | | def create_views(database, verbose): |
41 | 40 | | '''Function to create the Views in the database''' |
42 | 41 | | script_path = os.path.dirname(os.path.abspath(__file__)) |
| skipped 14 lines |
57 | 56 | | views_file.close() |
58 | 57 | | |
59 | 58 | | |
60 | | - | |
61 | 59 | | def insertAP(cursor, verbose, bssid, essid, manuf, channel, freqmhz, carrier, |
62 | 60 | | encryption, packets_total, lat, lon, cloaked): |
63 | 61 | | '''''' |
| skipped 7 lines |
71 | 69 | | try: |
72 | 70 | | if verbose: |
73 | 71 | | print("insertAP " + str(error)) |
74 | | - | cursor.execute( |
75 | | - | "UPDATE AP SET channel = CASE WHEN channel=='-1' THEN ('%s') " |
76 | | - | "ELSE channel END " |
77 | | - | "WHERE bssid = '%s'" % (channel, bssid)) |
78 | 72 | | |
79 | 73 | | # Write if empty |
80 | | - | sql = """UPDATE AP SET ssid = CASE WHEN ssid=='' THEN (?) WHEN ssid |
81 | | - | IS NULL THEN (?) ELSE ssid END WHERE bssid = (?)""" |
| 74 | + | sql = """UPDATE AP SET ssid = CASE WHEN ssid = '' OR |
| 75 | + | ssid IS NULL THEN (?) ELSE ssid END WHERE bssid = (?)""" |
| 76 | + | if verbose: |
| 77 | + | print(sql, (essid, bssid)) |
| 78 | + | cursor.execute(sql, (essid, bssid,)) |
| 79 | + | |
| 80 | + | # Update `manuf` column |
| 81 | + | sql = """UPDATE AP SET manuf = CASE WHEN manuf = '' OR manuf IS NULL |
| 82 | + | THEN (?) ELSE manuf END WHERE bssid = (?)""" |
| 83 | + | if verbose: |
| 84 | + | print(sql, (manuf, bssid)) |
| 85 | + | cursor.execute(sql, (manuf, bssid,)) |
| 86 | + | |
| 87 | + | # Update `channel` column |
| 88 | + | sql = """UPDATE AP SET channel = CASE WHEN channel = '' OR channel IS NULL |
| 89 | + | OR channel = 0 THEN (?) ELSE channel END WHERE bssid = (?)""" |
| 90 | + | if verbose: |
| 91 | + | print(sql, (channel, bssid)) |
| 92 | + | cursor.execute(sql, (channel, bssid,)) |
| 93 | + | |
| 94 | + | # Update `frequency` column |
| 95 | + | sql = """UPDATE AP SET frequency = CASE WHEN frequency = '' OR |
| 96 | + | frequency IS NULL OR frequency < 2000 THEN (?) ELSE frequency END WHERE bssid = (?)""" |
82 | 97 | | if verbose: |
83 | | - | print(sql, (essid, essid, bssid)) |
84 | | - | cursor.execute(sql, (essid, essid, bssid,)) |
| 98 | + | print(sql, (freqmhz, bssid)) |
| 99 | + | cursor.execute(sql, (freqmhz, bssid,)) |
85 | 100 | | |
86 | | - | cursor.execute( |
87 | | - | "UPDATE AP SET manuf = CASE WHEN manuf=='' THEN ('%s') " |
88 | | - | "WHEN manuf IS NULL THEN ('%s') ELSE manuf END " |
89 | | - | "WHERE bssid = '%s'" % (manuf, manuf, bssid)) |
| 101 | + | # Update `carrier` column |
| 102 | + | sql = """UPDATE AP SET carrier = CASE WHEN carrier = '' OR carrier IS NULL |
| 103 | + | THEN (?) ELSE carrier END WHERE bssid = (?)""" |
| 104 | + | if verbose: |
| 105 | + | print(sql, (carrier, bssid)) |
| 106 | + | cursor.execute(sql, (carrier, bssid,)) |
90 | 107 | | |
91 | | - | cursor.execute( |
92 | | - | "UPDATE AP SET channel = CASE WHEN channel=='' THEN ('%s') " |
93 | | - | "WHEN channel IS NULL THEN ('%s') ELSE channel END " |
94 | | - | "WHERE bssid = '%s'" % (channel, channel, bssid)) |
| 108 | + | # Update `encryption` column |
| 109 | + | sql = """UPDATE AP SET encryption = CASE WHEN encryption = '' OR |
| 110 | + | encryption IS NULL THEN (?) ELSE encryption END WHERE bssid = (?)""" |
| 111 | + | if verbose: |
| 112 | + | print(sql, (encryption, bssid)) |
| 113 | + | cursor.execute(sql, (encryption, bssid,)) |
95 | 114 | | |
96 | | - | cursor.execute( |
97 | | - | "UPDATE AP SET frequency = CASE WHEN frequency=='' THEN ('%s')" |
98 | | - | " WHEN frequency IS NULL THEN ('%s') ELSE frequency END " |
99 | | - | "WHERE bssid = '%s'" % (freqmhz, freqmhz, bssid)) |
| 115 | + | # Update `packetsTotal` column |
| 116 | + | sql = """UPDATE AP SET packetsTotal = packetsTotal + (?) |
| 117 | + | WHERE bssid = (?)""" |
| 118 | + | if verbose: |
| 119 | + | print(sql, (packets_total, bssid)) |
| 120 | + | cursor.execute(sql, (packets_total, bssid,)) |
100 | 121 | | |
101 | | - | cursor.execute( |
102 | | - | "UPDATE AP SET carrier = CASE WHEN carrier=='' THEN ('%s') " |
103 | | - | "WHEN carrier IS NULL THEN ('%s') ELSE carrier END " |
104 | | - | "WHERE bssid = '%s'" % (carrier, carrier, bssid)) |
| 122 | + | # Update `lat_t` and `lon_t` columns |
| 123 | + | sql = """UPDATE AP SET lat_t = CASE WHEN lat_t = 0.0 THEN (?) |
| 124 | + | ELSE lat_t END, lon_t = CASE WHEN lon_t = 0.0 THEN (?) |
| 125 | + | ELSE lon_t END WHERE bssid = (?)""" |
| 126 | + | if verbose: |
| 127 | + | print(sql, (lat, lon, bssid)) |
| 128 | + | cursor.execute(sql, (lat, lon, bssid,)) |
105 | 129 | | |
106 | | - | cursor.execute( |
107 | | - | "UPDATE AP SET encryption = CASE WHEN encryption=='' " |
108 | | - | "THEN ('%s') WHEN encryption IS NULL THEN ('%s') " |
109 | | - | "ELSE encryption END " |
110 | | - | "WHERE bssid = '%s'" % (encryption, encryption, bssid)) |
| 130 | + | # Update `cloaked` column |
| 131 | + | sql = """UPDATE AP SET cloaked = CASE WHEN cloaked = 'False' THEN (?) |
| 132 | + | ELSE cloaked END WHERE bssid = (?)""" |
| 133 | + | if verbose: |
| 134 | + | print(sql, (cloaked, bssid)) |
| 135 | + | cursor.execute(sql, (cloaked, bssid,)) |
111 | 136 | | |
112 | | - | cursor.execute( |
113 | | - | "UPDATE AP SET packetsTotal = packetsTotal + %s " |
114 | | - | "WHERE bssid = '%s'" |
115 | | - | % (packets_total, bssid)) |
116 | 137 | | |
117 | | - | cursor.execute( |
118 | | - | "UPDATE AP SET lat_t = CASE WHEN lat_t == 0.0 THEN ('%s')" |
119 | | - | "ELSE lat_t END, lon_t = CASE WHEN lon_t == 0.0 THEN ('%s') " |
120 | | - | "ELSE lon_t END " |
121 | | - | "WHERE bssid = '%s'" % (lat, lon, bssid)) |
122 | 138 | | |
123 | | - | cursor.execute( |
124 | | - | "UPDATE AP SET cloaked = CASE WHEN cloaked == False THEN ('%s')" |
125 | | - | "ELSE cloaked END " |
126 | | - | "WHERE bssid = '%s'" |
127 | | - | % (cloaked, bssid)) |
128 | 139 | | return int(0) |
129 | 140 | | except sqlite3.IntegrityError as error: |
130 | 141 | | |
| skipped 231 lines |
362 | 373 | | |
363 | 374 | | |
364 | 375 | | # obfuscated the database AA:BB:CC:XX:XX:XX-DEFG, needs database and not cursos to commit |
365 | | - | def obfuscateDB(database, verbose ): |
| 376 | + | def obfuscateDB(database, verbose): |
366 | 377 | | # APs! |
367 | 378 | | try: |
368 | 379 | | # Get all APs |
| skipped 47 lines |
416 | 427 | | return int(1) |
417 | 428 | | |
418 | 429 | | # exists = '11:22:33:44:55:77' in whitelist |
| 430 | + | |
| 431 | + | |
419 | 432 | | def clear_whitelist(database, verbose, whitelist): |
420 | 433 | | with open(whitelist) as f: |
421 | 434 | | whitelist = f.read().splitlines() |
| skipped 28 lines |