-
-
Notifications
You must be signed in to change notification settings - Fork 185
/
query-table.c
586 lines (511 loc) · 16.1 KB
/
query-table.c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
/* Pi-hole: A black hole for Internet advertisements
* (c) 2019 Pi-hole, LLC (https://pi-hole.net)
* Network-wide ad blocking via your own hardware.
*
* FTL Engine
* Query table database routines
*
* This file is copyright under the latest version of the EUPL.
* Please see LICENSE file for your rights under this license. */
#include "../FTL.h"
#include "query-table.h"
#include "common.h"
// get[Domain,ClientIP,Forward]String(), etc.
#include "../datastructure.h"
// getOverTimeID()
#include "../overTime.h"
// get_FTL_db_filesize()
#include "../files.h"
// timer_elapsed_msec()
#include "../timers.h"
// logg()
#include "../log.h"
// struct config
#include "../config.h"
// getstr()
#include "../shmem.h"
static bool saving_failed_before = false;
int get_number_of_queries_in_DB(void)
{
// This routine is used by the API routines.
if(!FTL_DB_avail())
{
return DB_FAILED;
}
// Count number of rows using the index timestamp is faster than select(*)
int result = db_query_int("SELECT COUNT(timestamp) FROM queries");
return result;
}
bool DB_save_queries(void)
{
// The database may be unavailable, e.g. when disabled
if(!FTL_DB_avail())
{
return false;
}
// Start database timer
if(config.debug & DEBUG_DATABASE)
timer_start(DATABASE_WRITE_TIMER);
unsigned int saved = 0;
bool error = false;
sqlite3_stmt* stmt = NULL;
int rc = dbquery("BEGIN TRANSACTION IMMEDIATE");
if( rc != SQLITE_OK )
{
const char *text;
if( rc == SQLITE_BUSY )
text = "WARNING";
else
{
text = "ERROR";
dbclose();
}
logg("%s: Storing queries in long-term database failed: %s", text, sqlite3_errstr(rc));
return false;
}
rc = sqlite3_prepare_v2(FTL_db, "INSERT INTO queries VALUES (NULL,?,?,?,?,?,?,?)", -1, &stmt, NULL);
if( rc != SQLITE_OK )
{
const char *text, *spaces;
if( rc == SQLITE_BUSY )
{
text = "WARNING";
spaces = " ";
}
else
{
text = "ERROR";
spaces = " ";
dbclose();
}
// dbquery() above already logs the reson for why the query failed
logg("%s: Storing queries in long-term database failed: %s\n", text, sqlite3_errstr(rc));
logg("%s Keeping queries in memory for later new attempt", spaces);
saving_failed_before = true;
return false;
}
// Get last ID stored in the database
long int lastID = get_max_query_ID();
int total = 0, blocked = 0;
time_t currenttimestamp = time(NULL);
time_t newlasttimestamp = 0;
long int queryID;
for(queryID = MAX(0, lastdbindex); queryID < counters->queries; queryID++)
{
queriesData* query = getQuery(queryID, true);
if(query->db != 0)
{
// Skip, already saved in database
continue;
}
if(!query->flags.complete && query->timestamp > currenttimestamp-2)
{
// Break if a brand new query (age < 2 seconds) is not yet completed
// giving it a chance to be stored next time
break;
}
if(query->privacylevel >= PRIVACY_MAXIMUM)
{
// Skip, we never store nor count queries recorded
// while have been in maximum privacy mode in the database
continue;
}
// TIMESTAMP
sqlite3_bind_int(stmt, 1, query->timestamp);
// TYPE
if(query->type != TYPE_OTHER)
{
// Store mapped type if query->type is not OTHER
sqlite3_bind_int(stmt, 2, query->type);
}
else
{
// Store query type + offset if query-> type is OTHER
sqlite3_bind_int(stmt, 2, query->qtype + 100);
}
// STATUS
sqlite3_bind_int(stmt, 3, query->status);
// DOMAIN
const char *domain = getDomainString(query);
sqlite3_bind_text(stmt, 4, domain, -1, SQLITE_STATIC);
// CLIENT
const char *client = getClientIPString(query);
sqlite3_bind_text(stmt, 5, client, -1, SQLITE_STATIC);
// FORWARD
if(query->upstreamID > -1)
{
// Get forward pointer
const upstreamsData* upstream = getUpstream(query->upstreamID, true);
char *buffer = NULL;
if(asprintf(&buffer, "%s#%u", getstr(upstream->ippos), upstream->port) > 0)
sqlite3_bind_text(stmt, 6, buffer, -1, SQLITE_TRANSIENT);
else
sqlite3_bind_null(stmt, 6);
if(buffer != NULL)
free(buffer);
}
else
{
sqlite3_bind_null(stmt, 6);
}
// ADDITIONAL_INFO
if(query->status == QUERY_GRAVITY_CNAME ||
query->status == QUERY_REGEX_CNAME ||
query->status == QUERY_BLACKLIST_CNAME)
{
// Restore domain blocked during deep CNAME inspection if applicable
const char* cname = getCNAMEDomainString(query);
sqlite3_bind_text(stmt, 7, cname, -1, SQLITE_STATIC);
}
else if(query->status == QUERY_REGEX)
{
// Restore regex ID if applicable
const int cacheID = findCacheID(query->domainID, query->clientID, query->type);
DNSCacheData *cache = getDNSCache(cacheID, true);
if(cache != NULL)
sqlite3_bind_int(stmt, 7, cache->black_regex_idx);
else
sqlite3_bind_null(stmt, 7);
}
else
{
// Nothing to add here
sqlite3_bind_null(stmt, 7);
}
// Step and check if successful
rc = sqlite3_step(stmt);
sqlite3_clear_bindings(stmt);
sqlite3_reset(stmt);
if( rc != SQLITE_DONE )
{
logg("Encountered error while trying to store queries in long-term database: %s", sqlite3_errstr(rc));
error = true;
break;
}
saved++;
// Mark this query as saved in the database by setting the corresponding ID
query->db = ++lastID;
// Total counter information (delta computation)
total++;
if(query->flags.blocked)
blocked++;
// Update lasttimestamp variable with timestamp of the latest stored query
if(query->timestamp > newlasttimestamp)
newlasttimestamp = query->timestamp;
}
if((rc = sqlite3_finalize(stmt)) != SQLITE_OK)
{
logg("Statement finalization failed when trying to store queries to long-term database: %s",
sqlite3_errstr(rc));
if( rc == SQLITE_BUSY )
{
logg("Keeping queries in memory for later new attempt");
saving_failed_before = true;
}
else
dbclose();
return false;
}
// Finish prepared statement
if((rc = dbquery("END TRANSACTION")) != SQLITE_OK)
{
// No need to log the error string here, dbquery() did that already above
logg("END TRANSACTION failed when trying to store queries to long-term database");
if( rc == SQLITE_BUSY )
{
logg("Keeping queries in memory for later new attempt");
saving_failed_before = true;
}
else
dbclose();
return false;
}
// Store index for next loop interation round and update last time stamp
// in the database only if all queries have been saved successfully
if(saved > 0 && !error)
{
lastdbindex = queryID;
db_set_FTL_property(DB_LASTTIMESTAMP, newlasttimestamp);
db_update_counters(total, blocked);
}
if(config.debug & DEBUG_DATABASE || saving_failed_before)
{
logg("Notice: Queries stored in long-term database: %u (took %.1f ms, last SQLite ID %li)", saved, timer_elapsed_msec(DATABASE_WRITE_TIMER), lastID);
if(saving_failed_before)
{
logg(" Queries from earlier attempt(s) stored successfully");
saving_failed_before = false;
}
}
return true;
}
void delete_old_queries_in_DB(void)
{
// Open database
if(!FTL_DB_avail())
{
return;
}
int timestamp = time(NULL) - config.maxDBdays * 86400;
if(dbquery("DELETE FROM queries WHERE timestamp <= %i", timestamp) != SQLITE_OK)
{
logg("delete_old_queries_in_DB(): Deleting queries due to age of entries failed!");
return;
}
// Get how many rows have been affected (deleted)
const int affected = sqlite3_changes(FTL_db);
// Print final message only if there is a difference
if((config.debug & DEBUG_DATABASE) || affected)
logg("Notice: Database size is %.2f MB, deleted %i rows", 1e-6*get_FTL_db_filesize(), affected);
}
// Get most recent 24 hours data from long-term database
void DB_read_queries(void)
{
// Open database
if(!dbopen())
return;
// Prepare request
// Get time stamp 24 hours in the past
const time_t now = time(NULL);
const time_t mintime = now - config.maxlogage;
const char *querystr = "SELECT * FROM queries WHERE timestamp >= ?";
// Log FTL_db query string in debug mode
if(config.debug & DEBUG_DATABASE)
logg("DB_read_queries(): \"%s\" with ? = %lli", querystr, (long long)mintime);
// Prepare SQLite3 statement
sqlite3_stmt* stmt = NULL;
int rc = sqlite3_prepare_v2(FTL_db, querystr, -1, &stmt, NULL);
if( rc != SQLITE_OK ){
logg("DB_read_queries() - SQL error prepare: %s", sqlite3_errstr(rc));
dbclose();
return;
}
// Bind limit
if((rc = sqlite3_bind_int(stmt, 1, mintime)) != SQLITE_OK)
{
logg("DB_read_queries() - Failed to bind type mintime: %s", sqlite3_errstr(rc));
dbclose();
return;
}
// Loop through returned database rows
while((rc = sqlite3_step(stmt)) == SQLITE_ROW)
{
const sqlite3_int64 dbid = sqlite3_column_int64(stmt, 0);
const time_t queryTimeStamp = sqlite3_column_int(stmt, 1);
// 1483228800 = 01/01/2017 @ 12:00am (UTC)
if(queryTimeStamp < 1483228800)
{
logg("FTL_db warn: TIMESTAMP should be larger than 01/01/2017 but is %lli", (long long)queryTimeStamp);
continue;
}
if(queryTimeStamp > now)
{
if(config.debug & DEBUG_DATABASE) logg("FTL_db warn: Skipping query logged in the future (%lli)", (long long)queryTimeStamp);
continue;
}
const int type = sqlite3_column_int(stmt, 2);
const bool mapped_type = type >= TYPE_A && type < TYPE_MAX;
const bool offset_type = type > 100 && type < (100 + UINT16_MAX);
if(!mapped_type && !offset_type)
{
logg("FTL_db warn: TYPE should not be %i", type);
continue;
}
// Don't import AAAA queries from database if the user set
// AAAA_QUERY_ANALYSIS=no in pihole-FTL.conf
if(type == TYPE_AAAA && !config.analyze_AAAA)
{
continue;
}
const int status_int = sqlite3_column_int(stmt, 3);
if(status_int < QUERY_UNKNOWN || status_int >= QUERY_STATUS_MAX)
{
logg("FTL_db warn: STATUS should be within [%i,%i] but is %i", QUERY_UNKNOWN, QUERY_STATUS_MAX-1, status_int);
continue;
}
const enum query_status status = status_int;
const char * domainname = (const char *)sqlite3_column_text(stmt, 4);
if(domainname == NULL)
{
logg("FTL_db warn: DOMAIN should never be NULL, %lli", (long long)queryTimeStamp);
continue;
}
const char * clientIP = (const char *)sqlite3_column_text(stmt, 5);
if(clientIP == NULL)
{
logg("FTL_db warn: CLIENT should never be NULL, %lli", (long long)queryTimeStamp);
continue;
}
// Check if user wants to skip queries coming from localhost
if(config.ignore_localhost &&
(strcmp(clientIP, "127.0.0.1") == 0 || strcmp(clientIP, "::1") == 0))
{
continue;
}
// Lock shared memory
lock_shm();
const char *buffer = NULL;
int upstreamID = -1; // Default if not forwarded
// Try to extract the upstream from the "forward" column if non-empty
if(sqlite3_column_bytes(stmt, 6) > 0 &&
(buffer = (const char *)sqlite3_column_text(stmt, 6)) != NULL)
{
// Get IP address and port of upstream destination
char serv_addr[INET6_ADDRSTRLEN] = { 0 };
unsigned int serv_port = 53;
// We limit the number of bytes written into the serv_addr buffer
// to prevent buffer overflows. If there is no port available in
// the database, we skip extracting them and use the default port
sscanf(buffer, "%"xstr(INET6_ADDRSTRLEN)"[^#]#%u", serv_addr, &serv_port);
serv_addr[INET6_ADDRSTRLEN-1] = '\0';
upstreamID = findUpstreamID(serv_addr, (in_port_t)serv_port);
}
// Obtain IDs only after filtering which queries we want to keep
const int timeidx = getOverTimeID(queryTimeStamp);
const int domainID = findDomainID(domainname, true);
const int clientID = findClientID(clientIP, true, false);
// Set index for this query
const int queryIndex = counters->queries;
// Store this query in memory
queriesData* query = getQuery(queryIndex, false);
query->magic = MAGICBYTE;
query->timestamp = queryTimeStamp;
if(type < 100)
{
// Mapped query type
query->type = type;
}
else
{
// Offset query type
query->type = TYPE_OTHER;
query->qtype = type - 100;
}
query->status = status;
query->domainID = domainID;
query->clientID = clientID;
query->upstreamID = upstreamID;
query->timeidx = timeidx;
query->db = dbid;
query->id = 0;
query->response = 0;
query->dnssec = DNSSEC_UNSPECIFIED;
query->reply = REPLY_UNKNOWN;
query->CNAME_domainID = -1;
// Initialize flags
query->flags.complete = true; // Mark as all information is available
query->flags.blocked = false;
query->flags.whitelisted = false;
// Set lastQuery timer for network table
clientsData* client = getClient(clientID, true);
client->lastQuery = queryTimeStamp;
// Handle type counters
if(type >= TYPE_A && type < TYPE_MAX)
{
counters->querytype[type-1]++;
overTime[timeidx].querytypedata[type-1]++;
}
// Update overTime data
overTime[timeidx].total++;
// Update overTime data structure with the new client
change_clientcount(client, 0, 0, timeidx, 1);
// Increase DNS queries counter
counters->queries++;
// Get additional information from the additional_info column if applicable
if(status == QUERY_GRAVITY_CNAME ||
status == QUERY_REGEX_CNAME ||
status == QUERY_BLACKLIST_CNAME)
{
// QUERY_*_CNAME: Get domain causing the blocking
const char *CNAMEdomain = (const char *)sqlite3_column_text(stmt, 7);
if(CNAMEdomain != NULL && strlen(CNAMEdomain) > 0)
{
// Add domain to FTL's memory but do not count it. Seeing a
// domain in the middle of a CNAME trajectory does not mean
// it was queried intentionally.
const int CNAMEdomainID = findDomainID(CNAMEdomain, false);
query->CNAME_domainID = CNAMEdomainID;
}
}
else if(status == QUERY_REGEX)
{
// QUERY_REGEX: Set ID regex which was the reson for blocking
const int cacheID = findCacheID(query->domainID, query->clientID, query->type);
DNSCacheData *cache = getDNSCache(cacheID, true);
// Only load if
// a) we have a chace entry
// b) the value of additional_info is not NULL (0 bytes storage size)
if(cache != NULL && sqlite3_column_bytes(stmt, 7) != 0)
cache->black_regex_idx = sqlite3_column_int(stmt, 7);
}
// Increment status counters
switch(status)
{
case QUERY_UNKNOWN: // Unknown
counters->unknown++;
break;
case QUERY_GRAVITY: // Blocked by gravity
case QUERY_REGEX: // Blocked by regex blacklist
case QUERY_BLACKLIST: // Blocked by exact blacklist
case QUERY_EXTERNAL_BLOCKED_IP: // Blocked by external provider
case QUERY_EXTERNAL_BLOCKED_NULL: // Blocked by external provider
case QUERY_EXTERNAL_BLOCKED_NXRA: // Blocked by external provider
case QUERY_GRAVITY_CNAME: // Blocked by gravity (inside CNAME path)
case QUERY_REGEX_CNAME: // Blocked by regex blacklist (inside CNAME path)
case QUERY_BLACKLIST_CNAME: // Blocked by exact blacklist (inside CNAME path)
counters->blocked++;
query->flags.blocked = true;
// Get domain pointer
domainsData* domain = getDomain(domainID, true);
domain->blockedcount++;
change_clientcount(client, 0, 1, -1, 0);
// Update overTime data structure
overTime[timeidx].blocked++;
break;
case QUERY_FORWARDED: // Forwarded
case QUERY_RETRIED: // (fall through)
case QUERY_RETRIED_DNSSEC: // (fall through)
counters->forwarded++;
// Only update upstream if there is one (there
// won't be one for retried DNSSEC queries)
if(upstreamID > -1)
{
upstreamsData *upstream = getUpstream(upstreamID, true);
if(upstream != NULL)
{
upstream->count++;
upstream->lastQuery = queryTimeStamp;
}
}
// Update overTime data structure
overTime[timeidx].forwarded++;
break;
case QUERY_CACHE: // Cached or local config
counters->cached++;
// Update overTime data structure
overTime[timeidx].cached++;
break;
case QUERY_IN_PROGRESS:
// Nothing to be done here
break;
case QUERY_STATUS_MAX:
default:
logg("Warning: Found unknown status %i in long term database!", status);
break;
}
unlock_shm();
}
logg("Imported %i queries from the long-term database", counters->queries);
// Update lastdbindex so that the next call to DB_save_queries()
// skips the queries that we just imported from the database
lastdbindex = counters->queries;
if( rc != SQLITE_DONE ){
logg("DB_read_queries() - SQL error step: %s", sqlite3_errstr(rc));
dbclose();
return;
}
// Finalize SQLite3 statement
sqlite3_finalize(stmt);
// Close database here, we have to reopen it later (after forking)
dbclose();
}