OpenVAS Manager  7.0.3~git
manage_sqlite3.c
Go to the documentation of this file.
1 /* OpenVAS Manager
2  * $Id$
3  * Description: Manager Manage library: SQLite specific Manage facilities.
4  *
5  * Authors:
6  * Matthew Mundell <matthew.mundell@greenbone.net>
7  *
8  * Copyright:
9  * Copyright (C) 2014 Greenbone Networks GmbH
10  *
11  * This program is free software; you can redistribute it and/or
12  * modify it under the terms of the GNU General Public License
13  * as published by the Free Software Foundation; either version 2
14  * of the License, or (at your option) any later version.
15  *
16  * This program is distributed in the hope that it will be useful,
17  * but WITHOUT ANY WARRANTY; without even the implied warranty of
18  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19  * GNU General Public License for more details.
20  *
21  * You should have received a copy of the GNU General Public License
22  * along with this program; if not, write to the Free Software
23  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
24  */
25 
26 #define _XOPEN_SOURCE /* Glibc2 needs this for strptime. */
27 
28 #include "sql.h"
29 #include "manage.h"
30 #include "manage_utils.h"
31 #include "manage_acl.h"
32 
33 #include <sqlite3.h>
34 #include <time.h>
35 #include <unistd.h>
36 
37 #include <openvas/misc/openvas_uuid.h>
38 
39 #undef G_LOG_DOMAIN
40 
43 #define G_LOG_DOMAIN "md manage"
44 
45 
46 /* Variables */
47 
48 extern sqlite3 *
49 task_db;
50 
51 
52 /* Headers of manage_sql.c functions also used here. */
53 
54 gchar*
55 clean_hosts (const char *, int *);
56 
57 char *
58 iso_time (time_t *);
59 
60 int
61 days_from_now (time_t *);
62 
63 long
64 current_offset (const char *);
65 
66 int
67 user_can_everything (const char *);
68 
69 int
70 user_owns (const char *, resource_t, int);
71 
72 int
73 resource_name (const char *, const char *, int, gchar **);
74 
75 int
76 resource_exists (const char *, resource_t, int);
77 
78 int
79 parse_time (const gchar *, int *);
80 
81 gchar *
82 tag_value (const gchar *tags, const gchar *tag);
83 
84 
85 /* Session. */
86 
92 void
93 manage_session_init (const char *uuid)
94 {
95  sql ("CREATE TEMPORARY TABLE IF NOT EXISTS current_credentials"
96  " (id INTEGER PRIMARY KEY,"
97  " uuid text UNIQUE NOT NULL,"
98  " tz_override text);");
99  sql ("DELETE FROM current_credentials;");
100  if (uuid)
101  sql ("INSERT INTO current_credentials (uuid) VALUES ('%s');", uuid);
102 }
103 
109 void
110 manage_session_set_timezone (const char *timezone)
111 {
112  return;
113 }
114 
115 
116 /* Helpers. */
117 
123 int
125 {
126  return sql_int ("SELECT count (*) FROM main.sqlite_master"
127  " WHERE type = 'table'"
128  " AND name = 'meta';")
129  == 0;
130 }
131 
132 
133 /* SQL functions. */
134 
144 void
145 sql_t (sqlite3_context *context, int argc, sqlite3_value** argv)
146 {
147  assert (argc == 0);
148 
149  sqlite3_result_int (context, 1);
150 }
151 
163 void
164 sql_strpos (sqlite3_context *context, int argc,
165  sqlite3_value** argv)
166 {
167  const unsigned char *str, *substr, *substr_in_str;
168 
169  assert (argc == 2);
170 
171  str = sqlite3_value_text (argv[0]);
172  substr = sqlite3_value_text (argv[1]);
173 
174  if (str == NULL)
175  {
176  sqlite3_result_error (context, "Failed to get string argument", -1);
177  return;
178  }
179 
180  if (substr == NULL)
181  {
182  sqlite3_result_error (context, "Failed to get substring argument", -1);
183  return;
184  }
185 
186  substr_in_str = (const unsigned char *)g_strrstr ((const gchar*)str,
187  (const gchar*)substr);
188 
189  sqlite3_result_int (context,
190  substr_in_str ? substr_in_str - str + 1 : 0);
191 }
192 
202 void
203 sql_order_inet (sqlite3_context *context, int argc, sqlite3_value** argv)
204 {
205  const char *ip;
206  unsigned int one, two, three, four;
207  one = two = three = four = 0;
208  gchar *ip_expanded;
209 
210  assert (argc == 1);
211 
212  ip = (const char *) sqlite3_value_text (argv[0]);
213  if (ip == NULL)
214  sqlite3_result_int (context, 0);
215  else
216  {
217  if (g_regex_match_simple ("^[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+$",
218  ip, 0, 0)
219  && sscanf (ip, "%u.%u.%u.%u", &one, &two, &three, &four) == 4)
220  {
221  ip_expanded = g_strdup_printf ("%03u.%03u.%03u.%03u",
222  one, two, three, four);
223  sqlite3_result_text (context,
224  ip_expanded,
225  -1, SQLITE_TRANSIENT);
226  g_free (ip_expanded);
227  }
228  else
229  sqlite3_result_text (context, ip, -1, SQLITE_TRANSIENT);
230  }
231 }
232 
242 void
243 sql_order_message_type (sqlite3_context *context, int argc,
244  sqlite3_value** argv)
245 {
246  const char *type;
247 
248  assert (argc == 1);
249 
250  type = (const char *) sqlite3_value_text (argv[0]);
251  if (type == NULL)
252  sqlite3_result_int (context, 8);
253  else if (strcmp (type, "Security Hole") == 0)
254  sqlite3_result_int (context, 1);
255  else if (strcmp (type, "Security Warning") == 0)
256  sqlite3_result_int (context, 2);
257  else if (strcmp (type, "Security Note") == 0)
258  sqlite3_result_int (context, 3);
259  else if (strcmp (type, "Log Message") == 0)
260  sqlite3_result_int (context, 4);
261  else if (strcmp (type, "Debug Message") == 0)
262  sqlite3_result_int (context, 5);
263  else if (strcmp (type, "Error Message") == 0)
264  sqlite3_result_int (context, 6);
265  else
266  sqlite3_result_int (context, 7);
267 }
268 
278 void
279 sql_order_port (sqlite3_context *context, int argc, sqlite3_value** argv)
280 {
281  const char *port;
282  int port_num;
283 
284  assert (argc == 1);
285 
286  port = (const char *) sqlite3_value_text (argv[0]);
287 
288  port_num = atoi (port);
289  if (port_num > 0)
290  sqlite3_result_int (context, port_num);
291  else if (sscanf (port, "%*s (%i/%*s)", &port_num) == 1)
292  sqlite3_result_int (context, port_num);
293  else
294  sqlite3_result_int (context, 0);
295 }
296 
306 void
307 sql_order_role (sqlite3_context *context, int argc, sqlite3_value** argv)
308 {
309  const char *name;
310 
311  assert (argc == 1);
312 
313  name = (const char *) sqlite3_value_text (argv[0]);
314  if (name == NULL)
315  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
316  else if (strcmp (name, "Admin") == 0)
317  sqlite3_result_text (context, " !", -1, SQLITE_TRANSIENT);
318  else
319  sqlite3_result_text (context, name, -1, SQLITE_TRANSIENT);
320 }
321 
331 void
332 sql_order_threat (sqlite3_context *context, int argc, sqlite3_value** argv)
333 {
334  const char *type;
335 
336  assert (argc == 1);
337 
338  type = (const char *) sqlite3_value_text (argv[0]);
339  if (type == NULL)
340  sqlite3_result_int (context, 9);
341  else if (strcmp (type, "High") == 0)
342  sqlite3_result_int (context, 1);
343  else if (strcmp (type, "Medium") == 0)
344  sqlite3_result_int (context, 2);
345  else if (strcmp (type, "Low") == 0)
346  sqlite3_result_int (context, 3);
347  else if (strcmp (type, "Log") == 0)
348  sqlite3_result_int (context, 4);
349  else if (strcmp (type, "Debug") == 0)
350  sqlite3_result_int (context, 5);
351  else if (strcmp (type, "False Positive") == 0)
352  sqlite3_result_int (context, 6);
353  else if (strcmp (type, "None") == 0)
354  sqlite3_result_int (context, 7);
355  else
356  sqlite3_result_int (context, 8);
357 }
358 
368 void
369 sql_make_uuid (sqlite3_context *context, int argc, sqlite3_value** argv)
370 {
371  char *uuid;
372 
373  assert (argc == 0);
374 
375  uuid = openvas_uuid_make ();
376  if (uuid == NULL)
377  {
378  sqlite3_result_error (context, "Failed to create UUID", -1);
379  return;
380  }
381 
382  sqlite3_result_text (context, uuid, -1, free);
383 }
384 
394 void
395 sql_hosts_contains (sqlite3_context *context, int argc, sqlite3_value** argv)
396 {
397  gchar **split, **point, *stripped_host;
398  const unsigned char *hosts, *host;
399 
400  assert (argc == 2);
401 
402  hosts = sqlite3_value_text (argv[0]);
403  if (hosts == NULL)
404  {
405  sqlite3_result_error (context, "Failed to get hosts argument", -1);
406  return;
407  }
408 
409  host = sqlite3_value_text (argv[1]);
410  if (host == NULL)
411  {
412  sqlite3_result_error (context, "Failed to get host argument", -1);
413  return;
414  }
415 
416  stripped_host = g_strstrip (g_strdup ((gchar*) host));
417  split = g_strsplit ((gchar*) hosts, ",", 0);
418  point = split;
419  while (*point)
420  {
421  if (strcmp (g_strstrip (*point), stripped_host) == 0)
422  {
423  g_strfreev (split);
424  g_free (stripped_host);
425  sqlite3_result_int (context, 1);
426  return;
427  }
428  point++;
429  }
430  g_strfreev (split);
431  g_free (stripped_host);
432 
433  sqlite3_result_int (context, 0);
434 }
435 
445 void
446 sql_clean_hosts (sqlite3_context *context, int argc, sqlite3_value** argv)
447 {
448  const unsigned char *hosts;
449  gchar *clean;
450 
451  assert (argc == 1);
452 
453  hosts = sqlite3_value_text (argv[0]);
454  if (hosts == NULL)
455  {
456  sqlite3_result_error (context, "Failed to get hosts argument", -1);
457  return;
458  }
459 
460  clean = clean_hosts ((gchar*) hosts, NULL);
461  sqlite3_result_text (context, clean, -1, SQLITE_TRANSIENT);
462  g_free (clean);
463 }
464 
476 void
477 sql_uniquify (sqlite3_context *context, int argc, sqlite3_value** argv)
478 {
479  const unsigned char *proposed_name, *type, *suffix;
480  gchar *candidate_name, *quoted_candidate_name;
481  unsigned int number;
482  sqlite3_int64 owner;
483 
484  assert (argc == 4);
485 
486  type = sqlite3_value_text (argv[0]);
487  if (type == NULL)
488  {
489  sqlite3_result_error (context, "Failed to get type argument", -1);
490  return;
491  }
492 
493  proposed_name = sqlite3_value_text (argv[1]);
494  if (proposed_name == NULL)
495  {
496  sqlite3_result_error (context,
497  "Failed to get proposed name argument",
498  -1);
499  return;
500  }
501 
502  owner = sqlite3_value_int64 (argv[2]);
503 
504  suffix = sqlite3_value_text (argv[3]);
505  if (suffix == NULL)
506  {
507  sqlite3_result_error (context,
508  "Failed to get suffix argument",
509  -1);
510  return;
511  }
512 
513  number = 0;
514  candidate_name = g_strdup_printf ("%s%s%c%i", proposed_name, suffix,
515  strcmp ((char*) type, "user") ? ' ' : '_',
516  ++number);
517  quoted_candidate_name = sql_quote (candidate_name);
518 
519  while (sql_int ("SELECT COUNT (*) FROM %ss WHERE name = '%s'"
520  " AND ((owner IS NULL) OR (owner = %llu));",
521  type,
522  quoted_candidate_name,
523  owner))
524  {
525  g_free (candidate_name);
526  g_free (quoted_candidate_name);
527  candidate_name = g_strdup_printf ("%s%s%c%u", proposed_name, suffix,
528  strcmp ((char*) type, "user")
529  ? ' '
530  : '_',
531  ++number);
532  quoted_candidate_name = sql_quote (candidate_name);
533  }
534 
535  g_free (quoted_candidate_name);
536 
537  sqlite3_result_text (context, candidate_name, -1, SQLITE_TRANSIENT);
538  g_free (candidate_name);
539 }
540 
550 void
551 sql_iso_time (sqlite3_context *context, int argc, sqlite3_value** argv)
552 {
553  time_t epoch_time;
554 
555  assert (argc == 1);
556 
557  epoch_time = sqlite3_value_int (argv[0]);
558  if (epoch_time == 0)
559  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
560  else
561  {
562  const char *iso;
563 
564  iso = iso_time (&epoch_time);
565  if (iso)
566  sqlite3_result_text (context, iso, -1, SQLITE_TRANSIENT);
567  else
568  sqlite3_result_error (context, "Failed to format time", -1);
569  }
570 }
571 
581 void
582 sql_days_from_now (sqlite3_context *context, int argc, sqlite3_value** argv)
583 {
584  time_t epoch_time;
585 
586  assert (argc == 1);
587 
588  epoch_time = sqlite3_value_int (argv[0]);
589  if (epoch_time == 0)
590  sqlite3_result_int (context, -2);
591  else
592  {
593  int days;
594 
595  days = days_from_now (&epoch_time);
596  sqlite3_result_int (context, days);
597  }
598 }
599 
611 void
612 sql_parse_time (sqlite3_context *context, int argc, sqlite3_value** argv)
613 {
614  const gchar *string;
615  int epoch_time;
616 
617  assert (argc == 1);
618 
619  string = (const gchar *) sqlite3_value_text (argv[0]);
620 
621  switch (parse_time (string, &epoch_time))
622  {
623  case -1:
624  g_warning ("%s: Failed to parse time: %s", __FUNCTION__, string);
625  sqlite3_result_int (context, 0);
626  break;
627  case -2:
628  g_warning ("%s: Failed to make time: %s", __FUNCTION__, string);
629  sqlite3_result_int (context, 0);
630  break;
631  case -3:
632  g_warning ("%s: Failed to parse timezone offset: %s",
633  __FUNCTION__,
634  string);
635  sqlite3_result_int (context, 0);
636  break;
637  default:
638  sqlite3_result_int (context, epoch_time);
639  }
640 }
641 
651 void
652 sql_next_time (sqlite3_context *context, int argc, sqlite3_value** argv)
653 {
654  time_t first;
655  time_t period;
656  int period_months, periods_offset;
657  const char *timezone;
658 
659  assert (argc == 3 || argc == 4 || argc == 5);
660 
661  first = sqlite3_value_int (argv[0]);
662  period = sqlite3_value_int (argv[1]);
663  period_months = sqlite3_value_int (argv[2]);
664  if (argc < 4 || sqlite3_value_type (argv[3]) == SQLITE_NULL)
665  timezone = NULL;
666  else
667  timezone = (char*) sqlite3_value_text (argv[3]);
668 
669  if (argc < 5 || sqlite3_value_type (argv[4]) == SQLITE_NULL)
670  periods_offset = 0;
671  else
672  periods_offset = sqlite3_value_int (argv[4]);
673 
674  sqlite3_result_int (context,
675  next_time (first, period, period_months, timezone,
676  periods_offset));
677 }
678 
688 void
689 sql_now (sqlite3_context *context, int argc, sqlite3_value** argv)
690 {
691  assert (argc == 0);
692  sqlite3_result_int (context, time (NULL));
693 }
694 
704 void
705 sql_tag (sqlite3_context *context, int argc, sqlite3_value** argv)
706 {
707  const char *tags, *tag;
708  gchar *value;
709 
710  assert (argc == 2);
711 
712  tags = (char*) sqlite3_value_text (argv[0]);
713  if (tags == NULL)
714  {
715  sqlite3_result_error (context, "Failed to get tags argument", -1);
716  return;
717  }
718 
719  tag = (char*) sqlite3_value_text (argv[1]);
720  if (tag == NULL)
721  {
722  sqlite3_result_error (context, "Failed to get tag argument", -1);
723  return;
724  }
725 
726  value = tag_value (tags, tag);
727  sqlite3_result_text (context, value, -1, SQLITE_TRANSIENT);
728  g_free (value);
729 
730  return;
731 }
732 
742 void
743 sql_max_hosts (sqlite3_context *context, int argc, sqlite3_value** argv)
744 {
745  const unsigned char *hosts, *exclude_hosts;
746  gchar *max;
747 
748  assert (argc == 2);
749 
750  hosts = sqlite3_value_text (argv[0]);
751  if (hosts == NULL)
752  {
753  /* Seems this happens when the query result is empty. */
754  sqlite3_result_text (context, "0", -1, SQLITE_TRANSIENT);
755  return;
756  }
757  exclude_hosts = sqlite3_value_text (argv[1]);
758 
759  max = g_strdup_printf ("%i", manage_count_hosts ((gchar*) hosts,
760  (gchar *) exclude_hosts));
761  sqlite3_result_text (context, max, -1, SQLITE_TRANSIENT);
762  g_free (max);
763 }
764 
773 void
774 sql_rename_column (const char *old_table, const char *new_table,
775  const char *old_name, const char *new_name)
776 {
777  iterator_t rows;
778 
779  /* Get a row with all columns. */
780 
781  init_iterator (&rows, "SELECT * FROM %s LIMIT 1;", old_table);
782  if (next (&rows))
783  {
784  GString *one, *two;
785  int end, column, first;
786 
787  /* Build the INSERT query from the column names in the row. */
788 
789  one = g_string_new ("");
790  g_string_append_printf (one, "INSERT INTO %s (", new_table);
791 
792  two = g_string_new (") SELECT ");
793 
794  end = iterator_column_count (&rows);
795  first = 1;
796  for (column = 0; column < end; column++)
797  {
798  const char *name;
799  name = iterator_column_name (&rows, column);
800  g_string_append_printf (one, "%s%s",
801  (first ? "" : ", "),
802  (strcmp (name, old_name) == 0
803  ? new_name
804  : name));
805  if (first)
806  first = 0;
807  else
808  g_string_append (two, ", ");
809  g_string_append (two, name);
810  }
811  cleanup_iterator (&rows);
812 
813  g_string_append_printf (one, "%s FROM %s;", two->str, old_table);
814 
815  /* Run the INSERT query. */
816 
817  sql (one->str);
818 
819  g_string_free (one, TRUE);
820  g_string_free (two, TRUE);
821  }
822  else
823  cleanup_iterator (&rows);
824 }
825 
835 void
836 sql_common_cve (sqlite3_context *context, int argc, sqlite3_value** argv)
837 {
838  gchar **split_1, **split_2, **point_1, **point_2;
839  const unsigned char *cve1, *cve2;
840 
841  assert (argc == 2);
842 
843  g_debug (" %s: top\n", __FUNCTION__);
844 
845  cve1 = sqlite3_value_text (argv[0]);
846  if (cve1 == NULL)
847  {
848  sqlite3_result_error (context, "Failed to get first CVE argument", -1);
849  return;
850  }
851 
852  cve2 = sqlite3_value_text (argv[1]);
853  if (cve2 == NULL)
854  {
855  sqlite3_result_error (context, "Failed to get second CVE argument", -1);
856  return;
857  }
858 
859  split_1 = g_strsplit ((gchar*) cve1, ",", 0);
860  split_2 = g_strsplit ((gchar*) cve2, ",", 0);
861  point_1 = split_1;
862  point_2 = split_2;
863  while (*point_1)
864  {
865  while (*point_2)
866  {
867  g_debug (" %s: %s vs %s\n", __FUNCTION__, g_strstrip (*point_1), g_strstrip (*point_2));
868  if (strcmp (g_strstrip (*point_1), g_strstrip (*point_2)) == 0)
869  {
870  g_strfreev (split_1);
871  g_strfreev (split_2);
872  sqlite3_result_int (context, 1);
873  return;
874  }
875  point_2++;
876  }
877  point_1++;
878  }
879  g_strfreev (split_1);
880  g_strfreev (split_2);
881 
882  sqlite3_result_int (context, 0);
883 }
884 
894 void
895 sql_cpe_title (sqlite3_context *context, int argc, sqlite3_value** argv)
896 {
897  const unsigned char *cpe_id;
898  gchar *quoted_cpe_id;
899  char *cpe_title;
900 
901  assert (argc == 1);
902 
903  cpe_id = sqlite3_value_text (argv[0]);
904 
905  if (manage_scap_loaded ()
906  && sqlite3_value_type(argv[0]) != SQLITE_NULL)
907  {
908  quoted_cpe_id = sql_quote ((gchar*) cpe_id);
909  cpe_title = sql_string ("SELECT title FROM scap.cpes"
910  " WHERE uuid = '%s';",
911  quoted_cpe_id);
912  g_free (quoted_cpe_id);
913 
914  if (cpe_title)
915  {
916  sqlite3_result_text (context, cpe_title, -1, SQLITE_TRANSIENT);
917  g_free (cpe_title);
918  }
919  else
920  {
921  sqlite3_result_null (context);
922  }
923  }
924  else
925  {
926  sqlite3_result_null (context);
927  }
928 }
929 
939 void
940 sql_credential_value (sqlite3_context *context, int argc, sqlite3_value** argv)
941 {
942  credential_t credential;
943  int trash;
944  const unsigned char* type;
945  gchar *quoted_type, *result;
946 
947  assert (argc == 3);
948 
949  credential = sqlite3_value_int64 (argv[0]);
950  trash = sqlite3_value_int (argv[1]);
951  type = sqlite3_value_text (argv[2]);
952 
953  quoted_type = sql_quote ((const char*) type);
954  if (trash)
955  {
956  result = sql_string ("SELECT value FROM credentials_trash_data"
957  " WHERE credential = %llu AND type = '%s';",
958  credential, quoted_type);
959  }
960  else
961  {
962  result = sql_string ("SELECT value FROM credentials_data"
963  " WHERE credential = %llu AND type = '%s';",
964  credential, quoted_type);
965  }
966 
967  if (result)
968  sqlite3_result_text (context, result, -1, SQLITE_TRANSIENT);
969  else
970  sqlite3_result_null (context);
971 
972  g_free (result);
973 }
974 
984 void
985 sql_current_offset (sqlite3_context *context, int argc, sqlite3_value** argv)
986 {
987  assert (argc == 1);
988  sqlite3_result_int
989  (context,
990  (int) current_offset ((const char *) sqlite3_value_text (argv[0])));
991 }
992 
1002 void
1003 sql_task_trend (sqlite3_context *context, int argc, sqlite3_value** argv)
1004 {
1005  unsigned int overrides;
1006  int min_qod;
1007  task_t task;
1008 
1009  assert (argc == 3);
1010 
1011  task = sqlite3_value_int64 (argv[0]);
1012  if (task == 0)
1013  {
1014  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1015  return;
1016  }
1017 
1018  overrides = sqlite3_value_int (argv[1]);
1019 
1020  if (sqlite3_value_type (argv[2]) == SQLITE_NULL)
1021  min_qod = MIN_QOD_DEFAULT;
1022  else
1023  min_qod = sqlite3_value_int (argv[2]);
1024 
1025  sqlite3_result_text (context, task_trend (task, overrides, min_qod), -1,
1026  SQLITE_TRANSIENT);
1027 }
1028 
1032 typedef struct
1033 {
1035  gchar *severity;
1038  int min_qod;
1039 } sql_severity_t;
1040 
1046 static void
1047 clear_cache (void *cache_arg)
1048 {
1049  sql_severity_t *cache;
1050 
1051  cache = (sql_severity_t*) cache_arg;
1052  g_debug (" %s: %llu, %llu\n", __FUNCTION__, cache->task, cache->overrides_task);
1053  cache->task = 0;
1054  cache->overrides_task = 0;
1055  free (cache->severity);
1056  cache->severity = NULL;
1057  free (cache->overrides_severity);
1058  cache->overrides_severity = NULL;
1059  cache->min_qod = -1;
1060 }
1061 
1077 static char *
1078 cached_task_severity (sqlite3_context *context, task_t task, int overrides,
1079  int min_qod)
1080 {
1081  static sql_severity_t static_cache = { .task = 0, .severity = NULL,
1082  .min_qod = MIN_QOD_DEFAULT,
1083  .overrides_task = 0,
1084  .overrides_severity = NULL };
1085  sql_severity_t *cache;
1086  char *severity;
1087 
1088  cache = sqlite3_get_auxdata (context, 1);
1089  if (cache)
1090  {
1091  if (overrides)
1092  {
1093  if (cache->overrides_task == task && cache->min_qod == min_qod)
1094  return cache->overrides_severity;
1095  /* Replace the cached severity. */
1096  cache->overrides_task = task;
1097  free (cache->overrides_severity);
1098  cache->overrides_severity = task_severity (task, 1, min_qod, 0);
1099  return cache->overrides_severity;
1100  }
1101  else
1102  {
1103  if (cache->task == task && cache->min_qod == min_qod)
1104  return cache->severity;
1105  /* Replace the cached severity. */
1106  cache->task = task;
1107  free (cache->severity);
1108  cache->severity = task_severity (task, 0, min_qod, 0);
1109  return cache->severity;
1110  }
1111  }
1112  severity = task_severity (task, overrides, min_qod, 0);
1113  /* Setup the cached severity. */
1114  cache = &static_cache;
1115  if (overrides)
1116  {
1117  cache->overrides_task = task;
1118  cache->overrides_severity = severity;
1119  }
1120  else
1121  {
1122  cache->task = task;
1123  cache->severity = severity;
1124  }
1125  sqlite3_set_auxdata (context, 1, cache, clear_cache);
1126  return severity;
1127 }
1128 
1138 void
1139 sql_task_threat_level (sqlite3_context *context, int argc, sqlite3_value** argv)
1140 {
1141  task_t task;
1142  report_t last_report;
1143  const char *threat;
1144  unsigned int overrides;
1145  int min_qod;
1146  char* severity;
1147  double severity_dbl;
1148 
1149  assert (argc == 3);
1150 
1151  task = sqlite3_value_int64 (argv[0]);
1152  if (task == 0)
1153  {
1154  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1155  return;
1156  }
1157 
1158  overrides = sqlite3_value_int (argv[1]);
1159 
1160  if (sqlite3_value_type (argv[2]) == SQLITE_NULL)
1161  min_qod = MIN_QOD_DEFAULT;
1162  else
1163  min_qod = sqlite3_value_int (argv[2]);
1164 
1165  severity = cached_task_severity (context, task, overrides, min_qod);
1166 
1167  if (severity == NULL
1168  || sscanf (severity, "%lf", &severity_dbl) != 1)
1169  threat = NULL;
1170  else
1171  threat = severity_to_level (severity_dbl, 0);
1172 
1173  g_debug (" %s: %llu: %s\n", __FUNCTION__, task, threat);
1174  if (threat)
1175  {
1176  sqlite3_result_text (context, threat, -1, SQLITE_TRANSIENT);
1177  return;
1178  }
1179 
1180  task_last_report (task, &last_report);
1181  if (last_report == 0)
1182  {
1183  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1184  return;
1185  }
1186 
1187  sqlite3_result_text (context, "None", -1, SQLITE_TRANSIENT);
1188  return;
1189 }
1190 
1200 void
1201 sql_report_progress (sqlite3_context *context, int argc, sqlite3_value** argv)
1202 {
1203  report_t report;
1204  task_t task;
1205 
1206  assert (argc == 1);
1207 
1208  report = sqlite3_value_int64 (argv[0]);
1209  if (report == 0)
1210  {
1211  sqlite3_result_int (context, -1);
1212  return;
1213  }
1214 
1215  if (report_task (report, &task))
1216  {
1217  sqlite3_result_int (context, -1);
1218  return;
1219  }
1220 
1221  sqlite3_result_int (context, report_progress (report, task, NULL));
1222  return;
1223 }
1224 
1234 void
1235 sql_report_severity (sqlite3_context *context, int argc, sqlite3_value** argv)
1236 {
1237  report_t report;
1238  double severity;
1239  unsigned int overrides;
1240  int min_qod;
1241 
1242  assert (argc == 3);
1243 
1244  report = sqlite3_value_int64 (argv[0]);
1245  if (report == 0)
1246  {
1247  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1248  return;
1249  }
1250 
1251  overrides = sqlite3_value_int (argv[1]);
1252 
1253  if (sqlite3_value_type (argv[2]) == SQLITE_NULL)
1254  min_qod = MIN_QOD_DEFAULT;
1255  else
1256  min_qod = sqlite3_value_int (argv[2]);
1257 
1258  severity = report_severity (report, overrides, min_qod);
1259 
1260  sqlite3_result_double (context, severity);
1261  return;
1262 }
1263 
1274 static int
1275 report_severity_count (report_t report, int overrides, int min_qod,
1276  char *level)
1277 {
1278  int debugs, false_positives, logs, lows, mediums, highs;
1279  get_data_t *get;
1280 
1281  if (current_credentials.uuid == NULL
1282  || strcmp (current_credentials.uuid, "") == 0)
1283  return 0;
1284  get = report_results_get_data (1 /* first */,
1285  -1, /* rows */
1286  overrides,
1287  0, /* autofp */
1288  min_qod);
1289  report_counts_id (report, &debugs, &highs, &lows, &logs, &mediums,
1290  &false_positives, NULL, get, NULL);
1291  get_data_reset (get);
1292  g_free (get);
1293 
1294  if (strcasecmp (level, "Debug") == 0)
1295  return debugs;
1296  if (strcasecmp (level, "False Positive") == 0)
1297  return false_positives;
1298  else if (strcasecmp (level, "Log") == 0)
1299  return logs;
1300  else if (strcasecmp (level, "Low") == 0)
1301  return lows;
1302  else if (strcasecmp (level, "Medium") == 0)
1303  return mediums;
1304  else if (strcasecmp (level, "High") == 0)
1305  return highs;
1306  else
1307  return 0;
1308 }
1309 
1319 void
1320 sql_report_severity_count (sqlite3_context *context, int argc,
1321  sqlite3_value** argv)
1322 {
1323  report_t report;
1324  unsigned int overrides;
1325  int min_qod;
1326  char* level;
1327  int count;
1328 
1329  assert (argc == 4);
1330 
1331  report = sqlite3_value_int64 (argv[0]);
1332  if (report == 0)
1333  {
1334  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1335  return;
1336  }
1337 
1338  overrides = sqlite3_value_int (argv[1]);
1339 
1340  if (sqlite3_value_type (argv[2]) == SQLITE_NULL)
1341  min_qod = MIN_QOD_DEFAULT;
1342  else
1343  min_qod = sqlite3_value_int (argv[2]);
1344 
1345  level = (char*) sqlite3_value_text (argv[3]);
1346  if (level == 0)
1347  {
1348  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1349  return;
1350  }
1351 
1352  count = report_severity_count (report, overrides, min_qod, level);
1353 
1354  sqlite3_result_int (context, count);
1355  return;
1356 }
1357 
1367 void
1368 sql_report_host_count (sqlite3_context *context,
1369  int argc, sqlite3_value** argv)
1370 {
1371  report_t report;
1372  int host_count;
1373 
1374  assert (argc == 1);
1375 
1376  report = sqlite3_value_int64 (argv[0]);
1377  if (report == 0)
1378  {
1379  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1380  return;
1381  }
1382 
1383  host_count = report_host_count (report);
1384 
1385  sqlite3_result_int (context, host_count);
1386  return;
1387 }
1388 
1398 void
1399 sql_report_result_host_count (sqlite3_context *context,
1400  int argc, sqlite3_value** argv)
1401 {
1402  report_t report;
1403  int min_qod;
1404  int host_count;
1405 
1406  assert (argc == 2);
1407 
1408  report = sqlite3_value_int64 (argv[0]);
1409  if (report == 0)
1410  {
1411  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1412  return;
1413  }
1414 
1415  if (sqlite3_value_type (argv[1]) == SQLITE_NULL)
1416  min_qod = MIN_QOD_DEFAULT;
1417  else
1418  min_qod = sqlite3_value_int (argv[1]);
1419 
1420  host_count = report_result_host_count (report, min_qod);
1421 
1422  sqlite3_result_int (context, host_count);
1423  return;
1424 }
1425 
1435 void
1436 sql_task_severity (sqlite3_context *context, int argc, sqlite3_value** argv)
1437 {
1438  task_t task;
1439  report_t last_report;
1440  char *severity;
1441  double severity_double;
1442  unsigned int overrides;
1443  int min_qod;
1444 
1445  assert (argc == 3);
1446 
1447  task = sqlite3_value_int64 (argv[0]);
1448  if (task == 0)
1449  {
1450  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1451  return;
1452  }
1453 
1454  overrides = sqlite3_value_int (argv[1]);
1455 
1456  if (sqlite3_value_type (argv[2]) == SQLITE_NULL)
1457  min_qod = MIN_QOD_DEFAULT;
1458  else
1459  min_qod = sqlite3_value_int (argv[2]);
1460 
1461  severity = cached_task_severity (context, task, overrides, min_qod);
1462  severity_double = severity ? g_strtod (severity, 0) : 0.0;
1463  g_debug (" %s: %llu: %s\n", __FUNCTION__, task, severity);
1464  if (severity)
1465  {
1466  sqlite3_result_double (context, severity_double);
1467  return;
1468  }
1469 
1470  task_last_report (task, &last_report);
1471  if (last_report == 0)
1472  {
1473  sqlite3_result_null (context);
1474  return;
1475  }
1476 
1477  sqlite3_result_null (context);
1478  return;
1479 }
1480 
1490 void
1491 sql_task_last_report (sqlite3_context *context, int argc, sqlite3_value** argv)
1492 {
1493  task_t task;
1494  report_t report;
1495 
1496  task = sqlite3_value_int64 (argv[0]);
1497  if (task == 0)
1498  sqlite3_result_int64 (context, 0);
1499  else if (task_last_report (task, &report))
1500  sqlite3_result_int64 (context, 0);
1501  else
1502  sqlite3_result_int64 (context, report);
1503 }
1504 
1514 void
1515 sql_severity_matches_ov (sqlite3_context *context, int argc,
1516  sqlite3_value** argv)
1517 {
1518  double severity, ov_severity;
1519 
1520  assert (argc == 2);
1521 
1522  if (sqlite3_value_type (argv[0]) == SQLITE_NULL)
1523  {
1524  sqlite3_result_int (context, 0);
1525  return;
1526  }
1527 
1528  if (sqlite3_value_type (argv[1]) == SQLITE_NULL
1529  || strcmp ((const char*) (sqlite3_value_text (argv[1])), "") == 0)
1530  {
1531  sqlite3_result_int (context, 1);
1532  return;
1533  }
1534  else
1535  {
1536  severity = sqlite3_value_double (argv[0]);
1537  ov_severity = sqlite3_value_double (argv[1]);
1538 
1539  sqlite3_result_int (context,
1540  severity_matches_ov (severity, ov_severity));
1541  return;
1542  }
1543 }
1544 
1554 void
1555 sql_severity_to_level (sqlite3_context *context, int argc,
1556  sqlite3_value** argv)
1557 {
1558  double severity;
1559  int mode;
1560 
1561  assert (argc == 2);
1562 
1563  if (sqlite3_value_type (argv[0]) == SQLITE_NULL
1564  || strcmp ((const char*)(sqlite3_value_text (argv[0])), "") == 0)
1565  {
1566  sqlite3_result_null (context);
1567  return;
1568  }
1569 
1570  mode = sqlite3_value_int (argv[1]);
1571 
1572  severity = sqlite3_value_double (argv[0]);
1573 
1574  sqlite3_result_text (context, severity_to_level (severity, mode),
1575  -1, SQLITE_TRANSIENT);
1576  return;
1577 }
1578 
1588 void
1589 sql_severity_to_type (sqlite3_context *context, int argc,
1590  sqlite3_value** argv)
1591 {
1592  double severity;
1593 
1594  assert (argc == 1);
1595 
1596  if (sqlite3_value_type (argv[0]) == SQLITE_NULL
1597  || strcmp ((const char*)(sqlite3_value_text (argv[0])), "") == 0)
1598  {
1599  sqlite3_result_null (context);
1600  return;
1601  }
1602 
1603  severity = sqlite3_value_double (argv[0]);
1604 
1605  sqlite3_result_text (context, severity_to_type (severity),
1606  -1, SQLITE_TRANSIENT);
1607  return;
1608 }
1609 
1619 void
1620 sql_regexp (sqlite3_context *context, int argc, sqlite3_value** argv)
1621 {
1622  const unsigned char *string, *regexp;
1623 
1624  assert (argc == 2);
1625 
1626  regexp = sqlite3_value_text (argv[0]);
1627  if (regexp == NULL)
1628  {
1629  /* Seems this happens when the query result is empty. */
1630  sqlite3_result_int (context, 0);
1631  return;
1632  }
1633 
1634  string = sqlite3_value_text (argv[1]);
1635  if (string == NULL)
1636  {
1637  /* Seems this happens when the query result is empty. */
1638  sqlite3_result_int (context, 0);
1639  return;
1640  }
1641 
1642  if (g_regex_match_simple ((gchar *) regexp, (gchar *) string, 0, 0))
1643  {
1644  sqlite3_result_int (context, 1);
1645  return;
1646  }
1647  sqlite3_result_int (context, 0);
1648 }
1649 
1659 void
1660 sql_run_status_name (sqlite3_context *context, int argc, sqlite3_value** argv)
1661 {
1662  const char *name;
1663  int status;
1664 
1665  assert (argc == 1);
1666 
1667  status = sqlite3_value_int (argv[0]);
1668 
1669  name = run_status_name (status);
1670  sqlite3_result_text (context, name ? name : "", -1, SQLITE_TRANSIENT);
1671  return;
1672 }
1673 
1686 void
1687 sql_resource_exists (sqlite3_context *context, int argc, sqlite3_value** argv)
1688 {
1689  const char *type;
1690  resource_t resource;
1691  int location, exists;
1692 
1693  assert (argc == 3);
1694 
1695  type = (char*) sqlite3_value_text (argv[0]);
1696  if (type == NULL)
1697  {
1698  sqlite3_result_int (context, 0);
1699  return;
1700  }
1701  if (valid_db_resource_type ((char*) type) == 0)
1702  {
1703  sqlite3_result_error (context, "Invalid resource type argument", -1);
1704  return;
1705  }
1706 
1707  resource = sqlite3_value_int64 (argv[1]);
1708  if (resource == 0)
1709  {
1710  sqlite3_result_int (context, 0);
1711  return;
1712  }
1713 
1714  location = sqlite3_value_int (argv[2]);
1715 
1716  exists = resource_exists (type, resource, location);
1717  if (exists == -1)
1718  {
1719  gchar *msg;
1720  msg = g_strdup_printf ("Invalid resource type argument: %s", type);
1721  sqlite3_result_error (context, msg, -1);
1722  g_free (msg);
1723  return;
1724  }
1725  sqlite3_result_int (context, exists);
1726  return;
1727 }
1728 
1738 void
1739 sql_resource_name (sqlite3_context *context, int argc, sqlite3_value** argv)
1740 {
1741  const char *type, *id;
1742  int location;
1743  char *name;
1744 
1745  assert (argc == 3);
1746 
1747  type = (char*) sqlite3_value_text (argv[0]);
1748  if (type == NULL)
1749  {
1750  sqlite3_result_null (context);
1751  return;
1752  }
1753 
1754  id = (char*) sqlite3_value_text (argv[1]);
1755  if (id == NULL)
1756  {
1757  sqlite3_result_null (context);
1758  return;
1759  }
1760 
1761  location = sqlite3_value_int (argv[2]);
1762 
1763  if (resource_name (type, id, location, &name))
1764  {
1765  gchar *msg;
1766  msg = g_strdup_printf ("Invalid resource type argument: %s", type);
1767  sqlite3_result_error (context, msg, -1);
1768  g_free (msg);
1769  return;
1770  }
1771 
1772  if (name)
1773  sqlite3_result_text (context, name, -1, SQLITE_TRANSIENT);
1774  else
1775  sqlite3_result_text (context, "", -1, SQLITE_TRANSIENT);
1776 
1777  free (name);
1778 
1779  return;
1780 }
1781 
1791 void
1792 sql_severity_in_level (sqlite3_context *context, int argc, sqlite3_value** argv)
1793 {
1794  double severity;
1795  const char *threat;
1796 
1797  assert (argc == 2);
1798 
1799  severity = sqlite3_value_double (argv[0]);
1800 
1801  threat = (char*) sqlite3_value_text (argv[1]);
1802  if (threat == NULL)
1803  {
1804  sqlite3_result_null (context);
1805  return;
1806  }
1807 
1808  sqlite3_result_int (context, severity_in_level (severity, threat));
1809 
1810  return;
1811 }
1812 
1822 void
1823 sql_target_credential (sqlite3_context *context, int argc, sqlite3_value** argv)
1824 {
1825  target_t target;
1826  int trash;
1827  const char *type;
1828 
1829  assert (argc == 3);
1830 
1831  target = sqlite3_value_int64 (argv[0]);
1832  trash = sqlite3_value_int (argv[1]);
1833  type = (char*) sqlite3_value_text (argv[2]);
1834 
1835  if (type == NULL)
1836  {
1837  sqlite3_result_null (context);
1838  return;
1839  }
1840 
1841  if (trash)
1842  sqlite3_result_int64 (context, trash_target_credential (target, type));
1843  else
1844  sqlite3_result_int64 (context, target_credential (target, type));
1845 
1846  return;
1847 }
1848 
1858 void
1859 sql_trash_target_credential_location (sqlite3_context *context,
1860  int argc, sqlite3_value** argv)
1861 {
1862  target_t target;
1863  const char *type;
1864 
1865  assert (argc == 2);
1866 
1867  target = sqlite3_value_int64 (argv[0]);
1868  type = (char*) sqlite3_value_text (argv[1]);
1869 
1870  if (type == NULL)
1871  {
1872  sqlite3_result_null (context);
1873  return;
1874  }
1875 
1876  sqlite3_result_int (context, trash_target_credential_location (target, type));
1877 
1878  return;
1879 }
1880 
1890 void
1891 sql_target_login_port (sqlite3_context *context, int argc, sqlite3_value** argv)
1892 {
1893  target_t target;
1894  int trash;
1895  const char *type;
1896 
1897  assert (argc == 3);
1898 
1899  target = sqlite3_value_int64 (argv[0]);
1900  trash = sqlite3_value_int (argv[1]);
1901  type = (char*) sqlite3_value_text (argv[2]);
1902 
1903  if (type == NULL)
1904  {
1905  sqlite3_result_null (context);
1906  return;
1907  }
1908 
1909  if (trash)
1910  sqlite3_result_int64 (context,
1911  trash_target_login_port (target, type));
1912  else
1913  sqlite3_result_int64 (context, target_login_port (target, type));
1914 
1915  return;
1916 }
1917 
1927 void
1928 sql_user_can_everything (sqlite3_context *context, int argc,
1929  sqlite3_value** argv)
1930 {
1931  const unsigned char *uuid;
1932 
1933  assert (argc == 1);
1934 
1935  uuid = sqlite3_value_text (argv[0]);
1936  if (uuid == NULL)
1937  {
1938  sqlite3_result_error (context, "Failed to get uuid argument", -1);
1939  return;
1940  }
1941 
1942  sqlite3_result_int (context, acl_user_can_everything ((char *) uuid));
1943 }
1944 
1954 void
1955 sql_user_owns (sqlite3_context *context, int argc,
1956  sqlite3_value** argv)
1957 {
1958  const unsigned char *type;
1959  resource_t resource;
1960 
1961  assert (argc == 2);
1962 
1963  type = sqlite3_value_text (argv[0]);
1964  if (type == NULL)
1965  {
1966  sqlite3_result_error (context, "Failed to get type argument", -1);
1967  return;
1968  }
1969 
1970  resource = sqlite3_value_int64 (argv[1]);
1971  if (resource == 0)
1972  {
1973  sqlite3_result_int (context, 0);
1974  return;
1975  }
1976 
1977  sqlite3_result_int (context, acl_user_owns ((char *) type, resource, 0));
1978 }
1979 
1985 int
1987 {
1988  if (sqlite3_create_function (task_db,
1989  "t",
1990  0, /* Number of args. */
1991  SQLITE_UTF8,
1992  NULL, /* Callback data. */
1993  sql_t,
1994  NULL, /* xStep. */
1995  NULL) /* xFinal. */
1996  != SQLITE_OK)
1997  {
1998  g_warning ("%s: failed to t", __FUNCTION__);
1999  return -1;
2000  }
2001 
2002  if (sqlite3_create_function (task_db,
2003  "strpos",
2004  2, /* Number of args. */
2005  SQLITE_UTF8,
2006  NULL, /* Callback data. */
2007  sql_strpos,
2008  NULL, /* xStep. */
2009  NULL) /* xFinal. */
2010  != SQLITE_OK)
2011  {
2012  g_warning ("%s: failed to create strpos", __FUNCTION__);
2013  return -1;
2014  }
2015 
2016  if (sqlite3_create_function (task_db,
2017  "order_inet",
2018  1, /* Number of args. */
2019  SQLITE_UTF8,
2020  NULL, /* Callback data. */
2022  NULL, /* xStep. */
2023  NULL) /* xFinal. */
2024  != SQLITE_OK)
2025  {
2026  g_warning ("%s: failed to create order_inet", __FUNCTION__);
2027  return -1;
2028  }
2029 
2030  if (sqlite3_create_function (task_db,
2031  "order_message_type",
2032  1, /* Number of args. */
2033  SQLITE_UTF8,
2034  NULL, /* Callback data. */
2036  NULL, /* xStep. */
2037  NULL) /* xFinal. */
2038  != SQLITE_OK)
2039  {
2040  g_warning ("%s: failed to create order_message_type", __FUNCTION__);
2041  return -1;
2042  }
2043 
2044  if (sqlite3_create_function (task_db,
2045  "order_port",
2046  1, /* Number of args. */
2047  SQLITE_UTF8,
2048  NULL, /* Callback data. */
2050  NULL, /* xStep. */
2051  NULL) /* xFinal. */
2052  != SQLITE_OK)
2053  {
2054  g_warning ("%s: failed to create order_port", __FUNCTION__);
2055  return -1;
2056  }
2057 
2058  if (sqlite3_create_function (task_db,
2059  "order_role",
2060  1, /* Number of args. */
2061  SQLITE_UTF8,
2062  NULL, /* Callback data. */
2064  NULL, /* xStep. */
2065  NULL) /* xFinal. */
2066  != SQLITE_OK)
2067  {
2068  g_warning ("%s: failed to create order_role", __FUNCTION__);
2069  return -1;
2070  }
2071 
2072  if (sqlite3_create_function (task_db,
2073  "order_threat",
2074  1, /* Number of args. */
2075  SQLITE_UTF8,
2076  NULL, /* Callback data. */
2078  NULL, /* xStep. */
2079  NULL) /* xFinal. */
2080  != SQLITE_OK)
2081  {
2082  g_warning ("%s: failed to create order_threat", __FUNCTION__);
2083  return -1;
2084  }
2085 
2086  if (sqlite3_create_function (task_db,
2087  "make_uuid",
2088  0, /* Number of args. */
2089  SQLITE_UTF8,
2090  NULL, /* Callback data. */
2091  sql_make_uuid,
2092  NULL, /* xStep. */
2093  NULL) /* xFinal. */
2094  != SQLITE_OK)
2095  {
2096  g_warning ("%s: failed to create make_uuid", __FUNCTION__);
2097  return -1;
2098  }
2099 
2100  if (sqlite3_create_function (task_db,
2101  "hosts_contains",
2102  2, /* Number of args. */
2103  SQLITE_UTF8,
2104  NULL, /* Callback data. */
2106  NULL, /* xStep. */
2107  NULL) /* xFinal. */
2108  != SQLITE_OK)
2109  {
2110  g_warning ("%s: failed to create hosts_contains", __FUNCTION__);
2111  return -1;
2112  }
2113 
2114  if (sqlite3_create_function (task_db,
2115  "clean_hosts",
2116  1, /* Number of args. */
2117  SQLITE_UTF8,
2118  NULL, /* Callback data. */
2120  NULL, /* xStep. */
2121  NULL) /* xFinal. */
2122  != SQLITE_OK)
2123  {
2124  g_warning ("%s: failed to create clean_hosts", __FUNCTION__);
2125  return -1;
2126  }
2127 
2128  if (sqlite3_create_function (task_db,
2129  "iso_time",
2130  1, /* Number of args. */
2131  SQLITE_UTF8,
2132  NULL, /* Callback data. */
2133  sql_iso_time,
2134  NULL, /* xStep. */
2135  NULL) /* xFinal. */
2136  != SQLITE_OK)
2137  {
2138  g_warning ("%s: failed to create iso_time", __FUNCTION__);
2139  return -1;
2140  }
2141 
2142  if (sqlite3_create_function (task_db,
2143  "days_from_now",
2144  1, /* Number of args. */
2145  SQLITE_UTF8,
2146  NULL, /* Callback data. */
2148  NULL, /* xStep. */
2149  NULL) /* xFinal. */
2150  != SQLITE_OK)
2151  {
2152  g_warning ("%s: failed to create days_from_now", __FUNCTION__);
2153  return -1;
2154  }
2155 
2156  if (sqlite3_create_function (task_db,
2157  "parse_time",
2158  1, /* Number of args. */
2159  SQLITE_UTF8,
2160  NULL, /* Callback data. */
2162  NULL, /* xStep. */
2163  NULL) /* xFinal. */
2164  != SQLITE_OK)
2165  {
2166  g_warning ("%s: failed to create parse_time", __FUNCTION__);
2167  return -1;
2168  }
2169 
2170  if (sqlite3_create_function (task_db,
2171  "tag",
2172  2, /* Number of args. */
2173  SQLITE_UTF8,
2174  NULL, /* Callback data. */
2175  sql_tag,
2176  NULL, /* xStep. */
2177  NULL) /* xFinal. */
2178  != SQLITE_OK)
2179  {
2180  g_warning ("%s: failed to create tag", __FUNCTION__);
2181  return -1;
2182  }
2183 
2184  if (sqlite3_create_function (task_db,
2185  "uniquify",
2186  4, /* Number of args. */
2187  SQLITE_UTF8,
2188  NULL, /* Callback data. */
2189  sql_uniquify,
2190  NULL, /* xStep. */
2191  NULL) /* xFinal. */
2192  != SQLITE_OK)
2193  {
2194  g_warning ("%s: failed to create uniquify", __FUNCTION__);
2195  return -1;
2196  }
2197 
2198  if (sqlite3_create_function (task_db,
2199  "next_time",
2200  3, /* Number of args. */
2201  SQLITE_UTF8,
2202  NULL, /* Callback data. */
2203  sql_next_time,
2204  NULL, /* xStep. */
2205  NULL) /* xFinal. */
2206  != SQLITE_OK)
2207  {
2208  g_warning ("%s: failed to create next_time", __FUNCTION__);
2209  return -1;
2210  }
2211 
2212  if (sqlite3_create_function (task_db,
2213  "next_time",
2214  4, /* Number of args. */
2215  SQLITE_UTF8,
2216  NULL, /* Callback data. */
2217  sql_next_time,
2218  NULL, /* xStep. */
2219  NULL) /* xFinal. */
2220  != SQLITE_OK)
2221  {
2222  g_warning ("%s: failed to create next_time", __FUNCTION__);
2223  return -1;
2224  }
2225 
2226  if (sqlite3_create_function (task_db,
2227  "next_time",
2228  5, /* Number of args. */
2229  SQLITE_UTF8,
2230  NULL, /* Callback data. */
2231  sql_next_time,
2232  NULL, /* xStep. */
2233  NULL) /* xFinal. */
2234  != SQLITE_OK)
2235  {
2236  g_warning ("%s: failed to create next_time", __FUNCTION__);
2237  return -1;
2238  }
2239 
2240  if (sqlite3_create_function (task_db,
2241  "m_now",
2242  0, /* Number of args. */
2243  SQLITE_UTF8,
2244  NULL, /* Callback data. */
2245  sql_now,
2246  NULL, /* xStep. */
2247  NULL) /* xFinal. */
2248  != SQLITE_OK)
2249  {
2250  g_warning ("%s: failed to create m_now", __FUNCTION__);
2251  return -1;
2252  }
2253 
2254  if (sqlite3_create_function (task_db,
2255  "max_hosts",
2256  2, /* Number of args. */
2257  SQLITE_UTF8,
2258  NULL, /* Callback data. */
2259  sql_max_hosts,
2260  NULL, /* xStep. */
2261  NULL) /* xFinal. */
2262  != SQLITE_OK)
2263  {
2264  g_warning ("%s: failed to create max_hosts", __FUNCTION__);
2265  return -1;
2266  }
2267 
2268  if (sqlite3_create_function (task_db,
2269  "common_cve",
2270  2, /* Number of args. */
2271  SQLITE_UTF8,
2272  NULL, /* Callback data. */
2274  NULL, /* xStep. */
2275  NULL) /* xFinal. */
2276  != SQLITE_OK)
2277  {
2278  g_warning ("%s: failed to create common_cve", __FUNCTION__);
2279  return -1;
2280  }
2281 
2282  if (sqlite3_create_function (task_db,
2283  "cpe_title",
2284  1, /* Number of args. */
2285  SQLITE_UTF8,
2286  NULL, /* Callback data. */
2287  sql_cpe_title,
2288  NULL, /* xStep. */
2289  NULL) /* xFinal. */
2290  != SQLITE_OK)
2291  {
2292  g_warning ("%s: failed to create cpe_title", __FUNCTION__);
2293  return -1;
2294  }
2295 
2296  if (sqlite3_create_function (task_db,
2297  "credential_value",
2298  3, /* Number of args. */
2299  SQLITE_UTF8,
2300  NULL, /* Callback data. */
2302  NULL, /* xStep. */
2303  NULL) /* xFinal. */
2304  != SQLITE_OK)
2305  {
2306  g_warning ("%s: failed to create credential_value", __FUNCTION__);
2307  return -1;
2308  }
2309 
2310  if (sqlite3_create_function (task_db,
2311  "current_offset",
2312  1, /* Number of args. */
2313  SQLITE_UTF8,
2314  NULL, /* Callback data. */
2316  NULL, /* xStep. */
2317  NULL) /* xFinal. */
2318  != SQLITE_OK)
2319  {
2320  g_warning ("%s: failed to create current_offset", __FUNCTION__);
2321  return -1;
2322  }
2323 
2324  if (sqlite3_create_function (task_db,
2325  "task_trend",
2326  3, /* Number of args. */
2327  SQLITE_UTF8,
2328  NULL, /* Callback data. */
2330  NULL, /* xStep. */
2331  NULL) /* xFinal. */
2332  != SQLITE_OK)
2333  {
2334  g_warning ("%s: failed to create task_trend", __FUNCTION__);
2335  return -1;
2336  }
2337 
2338  if (sqlite3_create_function (task_db,
2339  "task_threat_level",
2340  3, /* Number of args. */
2341  SQLITE_UTF8,
2342  NULL, /* Callback data. */
2344  NULL, /* xStep. */
2345  NULL) /* xFinal. */
2346  != SQLITE_OK)
2347  {
2348  g_warning ("%s: failed to create task_threat_level", __FUNCTION__);
2349  return -1;
2350  }
2351 
2352  if (sqlite3_create_function (task_db,
2353  "report_progress",
2354  1, /* Number of args. */
2355  SQLITE_UTF8,
2356  NULL, /* Callback data. */
2358  NULL, /* xStep. */
2359  NULL) /* xFinal. */
2360  != SQLITE_OK)
2361  {
2362  g_warning ("%s: failed to create report_progress", __FUNCTION__);
2363  return -1;
2364  }
2365 
2366  if (sqlite3_create_function (task_db,
2367  "report_severity",
2368  3, /* Number of args. */
2369  SQLITE_UTF8,
2370  NULL, /* Callback data. */
2372  NULL, /* xStep. */
2373  NULL) /* xFinal. */
2374  != SQLITE_OK)
2375  {
2376  g_warning ("%s: failed to create report_severity", __FUNCTION__);
2377  return -1;
2378  }
2379 
2380  if (sqlite3_create_function (task_db,
2381  "report_severity_count",
2382  4, /* Number of args. */
2383  SQLITE_UTF8,
2384  NULL, /* Callback data. */
2386  NULL, /* xStep. */
2387  NULL) /* xFinal. */
2388  != SQLITE_OK)
2389  {
2390  g_warning ("%s: failed to create report_severity_count", __FUNCTION__);
2391  return -1;
2392  }
2393 
2394  if (sqlite3_create_function (task_db,
2395  "report_host_count",
2396  1, /* Number of args. */
2397  SQLITE_UTF8,
2398  NULL, /* Callback data. */
2400  NULL, /* xStep. */
2401  NULL) /* xFinal. */
2402  != SQLITE_OK)
2403  {
2404  g_warning ("%s: failed to create report_result_host_count", __FUNCTION__);
2405  return -1;
2406  }
2407 
2408  if (sqlite3_create_function (task_db,
2409  "report_result_host_count",
2410  2, /* Number of args. */
2411  SQLITE_UTF8,
2412  NULL, /* Callback data. */
2414  NULL, /* xStep. */
2415  NULL) /* xFinal. */
2416  != SQLITE_OK)
2417  {
2418  g_warning ("%s: failed to create report_result_host_count", __FUNCTION__);
2419  return -1;
2420  }
2421 
2422  if (sqlite3_create_function (task_db,
2423  "task_severity",
2424  3, /* Number of args. */
2425  SQLITE_UTF8,
2426  NULL, /* Callback data. */
2428  NULL, /* xStep. */
2429  NULL) /* xFinal. */
2430  != SQLITE_OK)
2431  {
2432  g_warning ("%s: failed to create task_severity", __FUNCTION__);
2433  return -1;
2434  }
2435 
2436  if (sqlite3_create_function (task_db,
2437  "task_last_report",
2438  1, /* Number of args. */
2439  SQLITE_UTF8,
2440  NULL, /* Callback data. */
2442  NULL, /* xStep. */
2443  NULL) /* xFinal. */
2444  != SQLITE_OK)
2445  {
2446  g_warning ("%s: failed to create task_last_report", __FUNCTION__);
2447  return -1;
2448  }
2449 
2450  if (sqlite3_create_function (task_db,
2451  "severity_matches_ov",
2452  2, /* Number of args. */
2453  SQLITE_UTF8,
2454  NULL, /* Callback data. */
2456  NULL, /* xStep. */
2457  NULL) /* xFinal. */
2458  != SQLITE_OK)
2459  {
2460  g_warning ("%s: failed to create severity_matches_ov", __FUNCTION__);
2461  return -1;
2462  }
2463 
2464  if (sqlite3_create_function (task_db,
2465  "severity_to_level",
2466  1, /* Number of args. */
2467  SQLITE_UTF8,
2468  NULL, /* Callback data. */
2470  NULL, /* xStep. */
2471  NULL) /* xFinal. */
2472  != SQLITE_OK)
2473  {
2474  g_warning ("%s: failed to create severity_to_level", __FUNCTION__);
2475  return -1;
2476  }
2477 
2478  if (sqlite3_create_function (task_db,
2479  "severity_to_level",
2480  2, /* Number of args. */
2481  SQLITE_UTF8,
2482  NULL, /* Callback data. */
2484  NULL, /* xStep. */
2485  NULL) /* xFinal. */
2486  != SQLITE_OK)
2487  {
2488  g_warning ("%s: failed to create severity_to_level", __FUNCTION__);
2489  return -1;
2490  }
2491 
2492  if (sqlite3_create_function (task_db,
2493  "severity_to_type",
2494  1, /* Number of args. */
2495  SQLITE_UTF8,
2496  NULL, /* Callback data. */
2498  NULL, /* xStep. */
2499  NULL) /* xFinal. */
2500  != SQLITE_OK)
2501  {
2502  g_warning ("%s: failed to create severity_to_type", __FUNCTION__);
2503  return -1;
2504  }
2505 
2506  if (sqlite3_create_function (task_db,
2507  "run_status_name",
2508  1, /* Number of args. */
2509  SQLITE_UTF8,
2510  NULL, /* Callback data. */
2512  NULL, /* xStep. */
2513  NULL) /* xFinal. */
2514  != SQLITE_OK)
2515  {
2516  g_warning ("%s: failed to create run_status_name", __FUNCTION__);
2517  return -1;
2518  }
2519 
2520  if (sqlite3_create_function (task_db,
2521  "resource_exists",
2522  3, /* Number of args. */
2523  SQLITE_UTF8,
2524  NULL, /* Callback data. */
2526  NULL, /* xStep. */
2527  NULL) /* xFinal. */
2528  != SQLITE_OK)
2529  {
2530  g_warning ("%s: failed to create resource_exists", __FUNCTION__);
2531  return -1;
2532  }
2533 
2534  if (sqlite3_create_function (task_db,
2535  "regexp",
2536  2, /* Number of args. */
2537  SQLITE_UTF8,
2538  NULL, /* Callback data. */
2539  sql_regexp,
2540  NULL, /* xStep. */
2541  NULL) /* xFinal. */
2542  != SQLITE_OK)
2543  {
2544  g_warning ("%s: failed to create regexp", __FUNCTION__);
2545  return -1;
2546  }
2547 
2548  if (sqlite3_create_function (task_db,
2549  "resource_name",
2550  3, /* Number of args. */
2551  SQLITE_UTF8,
2552  NULL, /* Callback data. */
2554  NULL, /* xStep. */
2555  NULL) /* xFinal. */
2556  != SQLITE_OK)
2557  {
2558  g_warning ("%s: failed to create resource_name", __FUNCTION__);
2559  return -1;
2560  }
2561 
2562  if (sqlite3_create_function (task_db,
2563  "severity_in_level",
2564  2, /* Number of args. */
2565  SQLITE_UTF8,
2566  NULL, /* Callback data. */
2568  NULL, /* xStep. */
2569  NULL) /* xFinal. */
2570  != SQLITE_OK)
2571  {
2572  g_warning ("%s: failed to create severity_in_level", __FUNCTION__);
2573  return -1;
2574  }
2575 
2576  if (sqlite3_create_function (task_db,
2577  "target_credential",
2578  3, /* Number of args. */
2579  SQLITE_UTF8,
2580  NULL, /* Callback data. */
2582  NULL, /* xStep. */
2583  NULL) /* xFinal. */
2584  != SQLITE_OK)
2585  {
2586  g_warning ("%s: failed to create target_login_data", __FUNCTION__);
2587  return -1;
2588  }
2589 
2590  if (sqlite3_create_function (task_db,
2591  "trash_target_credential_location",
2592  2, /* Number of args. */
2593  SQLITE_UTF8,
2594  NULL, /* Callback data. */
2596  NULL, /* xStep. */
2597  NULL) /* xFinal. */
2598  != SQLITE_OK)
2599  {
2600  g_warning ("%s: failed to create target_login_data", __FUNCTION__);
2601  return -1;
2602  }
2603 
2604  if (sqlite3_create_function (task_db,
2605  "target_login_port",
2606  3, /* Number of args. */
2607  SQLITE_UTF8,
2608  NULL, /* Callback data. */
2610  NULL, /* xStep. */
2611  NULL) /* xFinal. */
2612  != SQLITE_OK)
2613  {
2614  g_warning ("%s: failed to create target_login_data", __FUNCTION__);
2615  return -1;
2616  }
2617 
2618  if (sqlite3_create_function (task_db,
2619  "user_can_everything",
2620  1, /* Number of args. */
2621  SQLITE_UTF8,
2622  NULL, /* Callback data. */
2624  NULL, /* xStep. */
2625  NULL) /* xFinal. */
2626  != SQLITE_OK)
2627  {
2628  g_warning ("%s: failed to create user_can_everything", __FUNCTION__);
2629  return -1;
2630  }
2631 
2632  if (sqlite3_create_function (task_db,
2633  "user_owns",
2634  2, /* Number of args. */
2635  SQLITE_UTF8,
2636  NULL, /* Callback data. */
2637  sql_user_owns,
2638  NULL, /* xStep. */
2639  NULL) /* xFinal. */
2640  != SQLITE_OK)
2641  {
2642  g_warning ("%s: failed to create user_owns", __FUNCTION__);
2643  return -1;
2644  }
2645 
2646  return 0;
2647 }
2648 
2649 
2650 /* Creation. */
2651 
2655 void
2657 {
2658  gchar *owned_clause;
2659 
2660  sql ("CREATE TABLE IF NOT EXISTS agents"
2661  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2662  " installer TEXT, installer_64 TEXT, installer_filename,"
2663  " installer_signature_64 TEXT, installer_trust INTEGER,"
2664  " installer_trust_time, howto_install TEXT, howto_use TEXT,"
2665  " creation_time, modification_time);");
2666  sql ("CREATE TABLE IF NOT EXISTS agents_trash"
2667  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2668  " installer TEXT, installer_64 TEXT, installer_filename,"
2669  " installer_signature_64 TEXT, installer_trust INTEGER,"
2670  " installer_trust_time, howto_install TEXT, howto_use TEXT,"
2671  " creation_time, modification_time);");
2672  sql ("CREATE TABLE IF NOT EXISTS config_preferences"
2673  " (id INTEGER PRIMARY KEY, config INTEGER, type, name, value,"
2674  " default_value, hr_name TEXT);");
2675  sql ("CREATE TABLE IF NOT EXISTS config_preferences_trash"
2676  " (id INTEGER PRIMARY KEY, config INTEGER, type, name, value,"
2677  " default_value, hr_name TEXT);");
2678  sql ("CREATE TABLE IF NOT EXISTS configs"
2679  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name,"
2680  " nvt_selector, comment, family_count INTEGER, nvt_count INTEGER,"
2681  " families_growing INTEGER, nvts_growing INTEGER, type, scanner,"
2682  " creation_time, modification_time);");
2683  sql ("CREATE TABLE IF NOT EXISTS configs_trash"
2684  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name,"
2685  " nvt_selector, comment, family_count INTEGER, nvt_count INTEGER,"
2686  " families_growing INTEGER, nvts_growing INTEGER, type, scanner,"
2687  " creation_time, modification_time);");
2688  sql ("CREATE TABLE IF NOT EXISTS alert_condition_data"
2689  " (id INTEGER PRIMARY KEY, alert INTEGER, name, data);");
2690  sql ("CREATE TABLE IF NOT EXISTS alert_condition_data_trash"
2691  " (id INTEGER PRIMARY KEY, alert INTEGER, name, data);");
2692  sql ("CREATE TABLE IF NOT EXISTS alert_event_data"
2693  " (id INTEGER PRIMARY KEY, alert INTEGER, name, data);");
2694  sql ("CREATE TABLE IF NOT EXISTS alert_event_data_trash"
2695  " (id INTEGER PRIMARY KEY, alert INTEGER, name, data);");
2696  sql ("CREATE TABLE IF NOT EXISTS alert_method_data"
2697  " (id INTEGER PRIMARY KEY, alert INTEGER, name, data);");
2698  sql ("CREATE TABLE IF NOT EXISTS alert_method_data_trash"
2699  " (id INTEGER PRIMARY KEY, alert INTEGER, name, data);");
2700  sql ("CREATE TABLE IF NOT EXISTS alerts"
2701  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2702  " event INTEGER, condition INTEGER, method INTEGER, filter INTEGER,"
2703  " creation_time, modification_time);");
2704  sql ("CREATE TABLE IF NOT EXISTS alerts_trash"
2705  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2706  " event INTEGER, condition INTEGER, method INTEGER, filter INTEGER,"
2707  " filter_location INTEGER, creation_time, modification_time);");
2708  sql ("CREATE TABLE IF NOT EXISTS credentials"
2709  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2710  " creation_time, modification_time, type TEXT,"
2711  " allow_insecure integer);");
2712  sql ("CREATE TABLE IF NOT EXISTS credentials_trash"
2713  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2714  " creation_time, modification_time, type TEXT,"
2715  " allow_insecure integer);");
2716  sql ("CREATE TABLE IF NOT EXISTS credentials_data"
2717  " (id INTEGER PRIMARY KEY, credential INTEGER, type TEXT, value TEXT);");
2718  sql ("CREATE TABLE IF NOT EXISTS credentials_trash_data"
2719  " (id INTEGER PRIMARY KEY, credential INTEGER, type TEXT, value TEXT);");
2720  sql ("CREATE TABLE IF NOT EXISTS filters"
2721  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2722  " type, term, creation_time, modification_time);");
2723  sql ("CREATE TABLE IF NOT EXISTS filters_trash"
2724  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2725  " type, term, creation_time, modification_time);");
2726  sql ("CREATE TABLE IF NOT EXISTS groups"
2727  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2728  " creation_time, modification_time);");
2729  sql ("CREATE TABLE IF NOT EXISTS groups_trash"
2730  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2731  " type, term, creation_time, modification_time);");
2732  sql ("CREATE TABLE IF NOT EXISTS group_users"
2733  " (id INTEGER PRIMARY KEY, `group` INTEGER, user INTEGER);");
2734  sql ("CREATE TABLE IF NOT EXISTS group_users_trash"
2735  " (id INTEGER PRIMARY KEY, `group` INTEGER, user INTEGER);");
2736  sql ("CREATE TABLE IF NOT EXISTS hosts"
2737  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2738  " creation_time, modification_time);");
2739  sql ("CREATE TABLE IF NOT EXISTS host_identifiers"
2740  " (id INTEGER PRIMARY KEY, uuid UNIQUE, host INTEGER, owner INTEGER, name,"
2741  " comment, value, source_type, source_id, source_data, creation_time,"
2742  " modification_time);");
2743  sql ("CREATE INDEX IF NOT EXISTS host_identifiers_by_host"
2744  " ON host_identifiers (host);");
2745  sql ("CREATE INDEX IF NOT EXISTS host_identifiers_by_value"
2746  " ON host_identifiers (value);");
2747  sql ("CREATE TABLE IF NOT EXISTS oss"
2748  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2749  " creation_time, modification_time);");
2750  sql ("CREATE TABLE IF NOT EXISTS host_oss"
2751  " (id INTEGER PRIMARY KEY, uuid UNIQUE, host INTEGER, owner INTEGER,"
2752  " name, comment, os INTEGER, source_type, source_id, source_data,"
2753  " creation_time, modification_time);");
2754  sql ("CREATE TABLE IF NOT EXISTS host_max_severities"
2755  " (id INTEGER PRIMARY KEY, host INTEGER, severity REAL, source_type,"
2756  " source_id, creation_time);");
2757  sql ("CREATE TABLE IF NOT EXISTS host_details"
2758  " (id INTEGER PRIMARY KEY, host INTEGER,"
2759  /* The report that the host detail came from. */
2760  " source_type,"
2761  " source_id,"
2762  /* The original source of the host detail, from the scanner. */
2763  " detail_source_type,"
2764  " detail_source_name,"
2765  " detail_source_description,"
2766  " name,"
2767  " value);");
2768  sql ("CREATE INDEX IF NOT EXISTS host_details_by_host"
2769  " ON host_details (host);");
2770  sql ("CREATE TABLE IF NOT EXISTS auth_cache"
2771  " (id INTEGER PRIMARY KEY, username, hash, method, creation_time);");
2772  sql ("CREATE TABLE IF NOT EXISTS meta"
2773  " (id INTEGER PRIMARY KEY, name UNIQUE, value);");
2774  sql ("CREATE TABLE IF NOT EXISTS notes"
2775  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, nvt,"
2776  " creation_time, modification_time, text, hosts, port, severity,"
2777  " task INTEGER, result INTEGER, end_time);");
2778  sql ("CREATE TABLE IF NOT EXISTS notes_trash"
2779  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, nvt,"
2780  " creation_time, modification_time, text, hosts, port, severity,"
2781  " task INTEGER, result INTEGER, end_time);");
2782  sql ("CREATE TABLE IF NOT EXISTS nvt_preferences"
2783  " (id INTEGER PRIMARY KEY, name, value);");
2784  /* nvt_selectors types: 0 all, 1 family, 2 NVT
2785  * (NVT_SELECTOR_TYPE_* in manage.h). */
2786  sql ("CREATE TABLE IF NOT EXISTS nvt_selectors"
2787  " (id INTEGER PRIMARY KEY, name, exclude INTEGER, type INTEGER,"
2788  " family_or_nvt, family);");
2789  sql ("CREATE INDEX IF NOT EXISTS nvt_selectors_by_name"
2790  " ON nvt_selectors (name);");
2791  sql ("CREATE INDEX IF NOT EXISTS nvt_selectors_by_family_or_nvt"
2792  " ON nvt_selectors (type, family_or_nvt);");
2793  sql ("CREATE TABLE IF NOT EXISTS nvts"
2794  " (id INTEGER PRIMARY KEY, uuid, oid, version, name, comment,"
2795  " copyright, cve, bid, xref, tag, category INTEGER, family, cvss_base,"
2796  " creation_time, modification_time, solution_type TEXT, qod INTEGER,"
2797  " qod_type TEXT);");
2798  sql ("CREATE INDEX IF NOT EXISTS nvts_by_oid"
2799  " ON nvts (oid);");
2800  sql ("CREATE INDEX IF NOT EXISTS nvts_by_name"
2801  " ON nvts (name);");
2802  sql ("CREATE INDEX IF NOT EXISTS nvts_by_family"
2803  " ON nvts (family);");
2804  sql ("CREATE TABLE IF NOT EXISTS nvt_cves"
2805  " (nvt, oid, cve_name)");
2806  sql ("CREATE INDEX IF NOT EXISTS nvts_by_creation_time"
2807  " ON nvts (creation_time);");
2808  sql ("CREATE INDEX IF NOT EXISTS nvts_by_modification_time"
2809  " ON nvts (modification_time);");
2810  sql ("CREATE INDEX IF NOT EXISTS nvts_by_cvss_base"
2811  " ON nvts (cvss_base);");
2812  sql ("CREATE INDEX IF NOT EXISTS nvts_by_solution_type"
2813  " ON nvts (solution_type);");
2814  sql ("CREATE INDEX IF NOT EXISTS nvt_cves_by_oid"
2815  " ON nvt_cves (oid);");
2816  sql ("CREATE TABLE IF NOT EXISTS overrides"
2817  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, nvt,"
2818  " creation_time, modification_time, text, hosts, port, severity,"
2819  " new_severity, task INTEGER, result INTEGER, end_time);");
2820  sql ("CREATE TABLE IF NOT EXISTS overrides_trash"
2821  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, nvt,"
2822  " creation_time, modification_time, text, hosts, port, severity,"
2823  " new_severity, task INTEGER, result INTEGER, end_time);");
2824  sql ("CREATE TABLE IF NOT EXISTS permissions"
2825  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner, name, comment,"
2826  " resource_type, resource, resource_uuid, resource_location,"
2827  " subject_type, subject, subject_location,"
2828  " creation_time, modification_time);");
2829  sql ("CREATE TABLE IF NOT EXISTS permissions_trash"
2830  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner, name, comment,"
2831  " resource_type, resource, resource_uuid, resource_location,"
2832  " subject_type, subject, subject_location,"
2833  " creation_time, modification_time);");
2834  /* Overlapping port ranges will cause problems, at least for the port
2835  * counting. OMP CREATE_PORT_LIST and CREATE_PORT_RANGE check for this,
2836  * but whoever creates a predefined port list must check this manually. */
2837  sql ("CREATE TABLE IF NOT EXISTS port_lists"
2838  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2839  " creation_time, modification_time);");
2840  sql ("CREATE TABLE IF NOT EXISTS port_lists_trash"
2841  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2842  " creation_time, modification_time);");
2843  sql ("CREATE TABLE IF NOT EXISTS port_names"
2844  " (id INTEGER PRIMARY KEY, number INTEGER, protocol, name,"
2845  " UNIQUE (number, protocol) ON CONFLICT REPLACE);");
2846  sql ("CREATE TABLE IF NOT EXISTS port_ranges"
2847  " (id INTEGER PRIMARY KEY, uuid UNIQUE, port_list INTEGER, type, start,"
2848  " end, comment, exclude);");
2849  sql ("CREATE TABLE IF NOT EXISTS port_ranges_trash"
2850  " (id INTEGER PRIMARY KEY, uuid UNIQUE, port_list INTEGER, type, start,"
2851  " end, comment, exclude);");
2852  sql ("CREATE TABLE IF NOT EXISTS report_host_details"
2853  " (id INTEGER PRIMARY KEY, report_host INTEGER, source_type, source_name,"
2854  " source_description, name, value);");
2855  sql ("CREATE INDEX IF NOT EXISTS"
2856  " report_host_details_by_report_host_and_name_and_value"
2857  " ON report_host_details (report_host, name, value);");
2858  sql ("CREATE TABLE IF NOT EXISTS report_hosts"
2859  " (id INTEGER PRIMARY KEY, report INTEGER, host, start_time, end_time,"
2860  " current_port, max_port);");
2861  sql ("CREATE INDEX IF NOT EXISTS report_hosts_by_host"
2862  " ON report_hosts (host);");
2863  sql ("CREATE INDEX IF NOT EXISTS report_hosts_by_report"
2864  " ON report_hosts (report);");
2865  sql ("CREATE TABLE IF NOT EXISTS report_format_param_options"
2866  " (id INTEGER PRIMARY KEY, report_format_param, value);");
2867  sql ("CREATE TABLE IF NOT EXISTS report_format_param_options_trash"
2868  " (id INTEGER PRIMARY KEY, report_format_param, value);");
2869  sql ("CREATE TABLE IF NOT EXISTS report_format_params"
2870  " (id INTEGER PRIMARY KEY, report_format, name, type INTEGER, value,"
2871  " type_min, type_max, type_regex, fallback);");
2872  sql ("CREATE TABLE IF NOT EXISTS report_format_params_trash"
2873  " (id INTEGER PRIMARY KEY, report_format, name, type INTEGER, value,"
2874  " type_min, type_max, type_regex, fallback);");
2875  sql ("CREATE TABLE IF NOT EXISTS report_formats"
2876  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, extension,"
2877  " content_type, summary, description, signature, trust INTEGER,"
2878  " trust_time, flags INTEGER, creation_time, modification_time);");
2879  sql ("CREATE TABLE IF NOT EXISTS report_formats_trash"
2880  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, extension,"
2881  " content_type, summary, description, signature, trust INTEGER,"
2882  " trust_time, flags INTEGER, original_uuid, creation_time,"
2883  " modification_time);");
2884  sql ("CREATE TABLE IF NOT EXISTS reports"
2885  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, hidden INTEGER,"
2886  " task INTEGER, date INTEGER, start_time, end_time, nbefile, comment,"
2887  " scan_run_status INTEGER, slave_progress, slave_task_uuid,"
2888  " slave_uuid, slave_name, slave_host, slave_port, source_iface,"
2889  " flags INTEGER);");
2890  sql ("CREATE TABLE IF NOT EXISTS report_counts"
2891  " (id INTEGER PRIMARY KEY, report INTEGER, user INTEGER,"
2892  " severity, count, override, end_time INTEGER, min_qod INTEGER);");
2893  sql ("CREATE INDEX IF NOT EXISTS report_counts_by_report_and_override"
2894  " ON report_counts (report, override);");
2895  sql ("CREATE TABLE IF NOT EXISTS resources_predefined"
2896  " (id INTEGER PRIMARY KEY, resource_type, resource INTEGER)");
2897  sql ("CREATE TABLE IF NOT EXISTS results"
2898  " (id INTEGER PRIMARY KEY, uuid, task INTEGER, host, port, nvt,"
2899  " type, description, report, nvt_version, severity REAL,"
2900  " qod INTEGER, qod_type TEXT, owner INTEGER, date INTEGER)");
2901  sql ("CREATE INDEX IF NOT EXISTS results_by_uuid"
2902  " ON results (uuid);");
2903  sql ("CREATE INDEX IF NOT EXISTS results_by_host"
2904  " ON results (host);");
2905  sql ("CREATE INDEX IF NOT EXISTS results_by_host_and_qod"
2906  " ON results(host, qod);");
2907  sql ("CREATE INDEX IF NOT EXISTS results_by_nvt"
2908  " ON results (nvt);");
2909  sql ("CREATE INDEX IF NOT EXISTS results_by_report"
2910  " ON results (report);");
2911  sql ("CREATE INDEX IF NOT EXISTS results_by_report_host"
2912  " ON results (report, host);");
2913  sql ("CREATE INDEX IF NOT EXISTS results_by_task"
2914  " ON results (task);");
2915  sql ("CREATE INDEX IF NOT EXISTS results_by_task_qod_severity"
2916  " ON results (task, qod, severity);");
2917  sql ("CREATE INDEX IF NOT EXISTS results_by_type"
2918  " ON results (type);");
2919  sql ("CREATE TABLE IF NOT EXISTS roles"
2920  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2921  " creation_time, modification_time);");
2922  sql ("CREATE TABLE IF NOT EXISTS roles_trash"
2923  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
2924  " creation_time, modification_time);");
2925  sql ("CREATE TABLE IF NOT EXISTS role_users"
2926  " (id INTEGER PRIMARY KEY, role INTEGER, user INTEGER);");
2927  sql ("CREATE TABLE IF NOT EXISTS role_users_trash"
2928  " (id INTEGER PRIMARY KEY, role INTEGER, user INTEGER);");
2929  sql ("CREATE TABLE IF NOT EXISTS scanners"
2930  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, comment,"
2931  " host, port, type, ca_pub, credential INTEGER,"
2932  " creation_time, modification_time);");
2933  sql ("CREATE TABLE IF NOT EXISTS scanners_trash"
2934  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, comment,"
2935  " host, port, type, ca_pub, credential INTEGER,"
2936  " credential_location INTEGER, creation_time, modification_time);");
2937  sql ("CREATE TABLE IF NOT EXISTS schedules"
2938  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, comment,"
2939  " first_time, period, period_months, duration, timezone,"
2940  " initial_offset, creation_time, modification_time);");
2941  sql ("CREATE TABLE IF NOT EXISTS schedules_trash"
2942  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, comment,"
2943  " first_time, period, period_months, duration, timezone,"
2944  " initial_offset, creation_time, modification_time);");
2945  sql ("CREATE TABLE IF NOT EXISTS settings"
2946  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, comment, value);");
2947  sql ("CREATE TABLE IF NOT EXISTS tags"
2948  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner, name, comment,"
2949  " creation_time, modification_time, resource_type, resource,"
2950  " resource_uuid, resource_location, active, value);");
2951  sql ("CREATE INDEX IF NOT EXISTS tags_by_resource"
2952  " ON tags (resource_type, resource);");
2953  sql ("CREATE INDEX IF NOT EXISTS tags_by_name"
2954  " ON tags (name);");
2955  sql ("CREATE UNIQUE INDEX IF NOT EXISTS tags_by_uuid"
2956  " ON tags (uuid);");
2957  sql ("CREATE TABLE IF NOT EXISTS tags_trash"
2958  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner, name, comment,"
2959  " creation_time, modification_time, resource_type, resource,"
2960  " resource_uuid, resource_location, active, value);");
2961  sql ("CREATE TABLE IF NOT EXISTS targets"
2962  " (id INTEGER PRIMARY KEY, uuid text UNIQUE NOT NULL,"
2963  " owner integer, name text NOT NULL,"
2964  " hosts text, exclude_hosts text,"
2965  " reverse_lookup_only integer, reverse_lookup_unify integer,"
2966  " comment text, port_list integer, alive_test integer,"
2967  " creation_time integer, modification_time integer);");
2968  sql ("CREATE TABLE IF NOT EXISTS targets_trash"
2969  " (id INTEGER PRIMARY KEY, uuid text UNIQUE NOT NULL,"
2970  " owner integer, name text NOT NULL,"
2971  " hosts text, exclude_hosts text,"
2972  " reverse_lookup_only integer, reverse_lookup_unify integer,"
2973  " comment text, port_list integer, port_list_location integer,"
2974  " alive_test integer,"
2975  " creation_time integer, modification_time integer);");
2976  sql ("CREATE TABLE IF NOT EXISTS targets_login_data"
2977  " (id INTEGER PRIMARY KEY, target INTEGER, type TEXT,"
2978  " credential INTEGER, port INTEGER);");
2979  sql ("CREATE TABLE IF NOT EXISTS targets_trash_login_data"
2980  " (id INTEGER PRIMARY KEY, target INTEGER, type TEXT,"
2981  " credential INTEGER, port INTEGER, credential_location INTEGER);");
2982  sql ("CREATE TABLE IF NOT EXISTS task_files"
2983  " (id INTEGER PRIMARY KEY, task INTEGER, name, content);");
2984  sql ("CREATE TABLE IF NOT EXISTS task_alerts"
2985  " (id INTEGER PRIMARY KEY, task INTEGER, alert INTEGER,"
2986  " alert_location INTEGER);");
2987  sql ("CREATE TABLE IF NOT EXISTS task_preferences"
2988  " (id INTEGER PRIMARY KEY, task INTEGER, name, value);");
2989  sql ("CREATE TABLE IF NOT EXISTS tasks"
2990  " (id INTEGER PRIMARY KEY, uuid, owner INTEGER, name, hidden INTEGER,"
2991  " comment, run_status INTEGER, start_time, end_time,"
2992  " config INTEGER, target INTEGER, schedule INTEGER, schedule_next_time,"
2993  " schedule_periods INTEGER, config_location INTEGER,"
2994  " target_location INTEGER, schedule_location INTEGER,"
2995  " scanner_location INTEGER, upload_result_count INTEGER,"
2996  " hosts_ordering, scanner, alterable, creation_time,"
2997  " modification_time);");
2998  /* Field password contains the hash. */
2999  /* Field hosts_allow: 0 deny, 1 allow. */
3000  /* Field ifaces_allow: 0 deny, 1 allow. */
3001  sql ("CREATE TABLE IF NOT EXISTS users"
3002  " (id INTEGER PRIMARY KEY, uuid UNIQUE, owner INTEGER, name, comment,"
3003  " password, timezone, hosts, hosts_allow, ifaces, ifaces_allow,"
3004  " method, creation_time, modification_time);");
3005 
3006  /* Result views */
3007 
3008  owned_clause = acl_where_owned_for_get ("override", "users.id");
3009 
3010  sql ("DROP VIEW IF EXISTS result_overrides;");
3011  sql ("CREATE VIEW result_overrides AS"
3012  " SELECT users.id AS user,"
3013  " results.id as result,"
3014  " overrides.id AS override,"
3015  " overrides.severity AS ov_old_severity,"
3016  " overrides.new_severity AS ov_new_severity"
3017  " FROM users, results, overrides"
3018  " WHERE overrides.nvt = results.nvt"
3019  " AND (overrides.result = 0 OR overrides.result = results.id)"
3020  " AND %s"
3021  " AND ((overrides.end_time = 0)"
3022  " OR (overrides.end_time >= m_now ()))"
3023  " AND (overrides.task ="
3024  " (SELECT reports.task FROM reports"
3025  " WHERE results.report = reports.id)"
3026  " OR overrides.task = 0)"
3027  " AND (overrides.result = results.id"
3028  " OR overrides.result = 0)"
3029  " AND (overrides.hosts is NULL"
3030  " OR overrides.hosts = ''"
3031  " OR hosts_contains (overrides.hosts, results.host))"
3032  " AND (overrides.port is NULL"
3033  " OR overrides.port = ''"
3034  " OR overrides.port = results.port)"
3035  " ORDER BY overrides.result DESC, overrides.task DESC,"
3036  " overrides.port DESC, overrides.severity ASC,"
3037  " overrides.creation_time DESC",
3038  owned_clause);
3039 
3040  g_free (owned_clause);
3041 
3042  sql ("DROP VIEW IF EXISTS result_new_severities;");
3043  sql ("CREATE VIEW result_new_severities AS"
3044  " SELECT results.id as result, users.id as user, dynamic, override,"
3045  " CASE WHEN dynamic THEN"
3046  " CASE WHEN override THEN"
3047  " coalesce ((SELECT ov_new_severity FROM result_overrides"
3048  " WHERE result = results.id"
3049  " AND result_overrides.user = users.id"
3050  " AND severity_matches_ov"
3051  " (coalesce ((CASE WHEN results.severity"
3052  " > " G_STRINGIFY
3053  (SEVERITY_LOG)
3054  " THEN (SELECT cvss_base"
3055  " FROM nvts"
3056  " WHERE nvts.oid = results.nvt)"
3057  " ELSE results.severity"
3058  " END),"
3059  " results.severity),"
3060  " ov_old_severity)),"
3061  " coalesce ((CASE WHEN results.severity"
3062  " > " G_STRINGIFY (SEVERITY_LOG)
3063  " THEN (SELECT cvss_base"
3064  " FROM nvts"
3065  " WHERE nvts.oid = results.nvt)"
3066  " ELSE results.severity"
3067  " END),"
3068  " results.severity))"
3069  " ELSE"
3070  " coalesce ((CASE WHEN results.severity"
3071  " > " G_STRINGIFY (SEVERITY_LOG)
3072  " THEN (SELECT cvss_base"
3073  " FROM nvts"
3074  " WHERE nvts.oid = results.nvt)"
3075  " ELSE results.severity"
3076  " END),"
3077  " results.severity)"
3078  " END"
3079  " ELSE"
3080  " CASE WHEN override THEN"
3081  " coalesce ((SELECT ov_new_severity FROM result_overrides"
3082  " WHERE result = results.id"
3083  " AND result_overrides.user = users.id"
3084  " AND severity_matches_ov"
3085  " (results.severity,"
3086  " ov_old_severity)),"
3087  " results.severity)"
3088  " ELSE"
3089  " results.severity"
3090  " END"
3091  " END AS new_severity"
3092  " FROM results, users"
3093  " JOIN (SELECT 0 AS override UNION SELECT 1 AS override_opts)"
3094  " JOIN (SELECT 0 AS dynamic UNION SELECT 1 AS dynamic_opts);");
3095 
3096  sql ("DROP VIEW IF EXISTS results_autofp;");
3097  sql ("CREATE VIEW results_autofp AS"
3098  " SELECT results.id as result, autofp_selection,"
3099  " (CASE autofp_selection"
3100  " WHEN 1 THEN"
3101  " (CASE WHEN"
3102  " (((SELECT family FROM nvts WHERE oid = results.nvt)"
3103  " IN (" LSC_FAMILY_LIST "))"
3104  " OR results.nvt = '0'" /* Open ports previously had 0 NVT. */
3105  " OR EXISTS"
3106  " (SELECT id FROM nvts"
3107  " WHERE oid = results.nvt"
3108  " AND"
3109  " (cve = 'NOCVE'"
3110  " OR cve NOT IN (SELECT cve FROM nvts"
3111  " WHERE oid IN (SELECT source_name"
3112  " FROM report_host_details"
3113  " WHERE report_host"
3114  " = (SELECT id"
3115  " FROM report_hosts"
3116  " WHERE report = %llu"
3117  " AND host = results.host)"
3118  " AND name = 'EXIT_CODE'"
3119  " AND value = 'EXIT_NOTVULN')"
3120  " AND family IN (" LSC_FAMILY_LIST ")))))"
3121  " THEN NULL"
3122  " WHEN severity = " G_STRINGIFY (SEVERITY_ERROR) " THEN NULL"
3123  " ELSE 1 END)"
3124  " WHEN 2 THEN"
3125  " (CASE WHEN"
3126  " (((SELECT family FROM nvts WHERE oid = results.nvt)"
3127  " IN (" LSC_FAMILY_LIST "))"
3128  " OR results.nvt = '0'" /* Open ports previously had 0 NVT.*/
3129  " OR EXISTS"
3130  " (SELECT id FROM nvts AS outer_nvts"
3131  " WHERE oid = results.nvt"
3132  " AND"
3133  " (cve = 'NOCVE'"
3134  " OR NOT EXISTS"
3135  " (SELECT cve FROM nvts"
3136  " WHERE oid IN (SELECT source_name"
3137  " FROM report_host_details"
3138  " WHERE report_host"
3139  " = (SELECT id"
3140  " FROM report_hosts"
3141  " WHERE report = results.report"
3142  " AND host = results.host)"
3143  " AND name = 'EXIT_CODE'"
3144  " AND value = 'EXIT_NOTVULN')"
3145  " AND family IN (" LSC_FAMILY_LIST ")"
3146  /* The CVE of the result NVT is outer_nvts.cve. The CVE of the
3147  * NVT that has registered the "closed" host detail is nvts.cve.
3148  * Either can be a list of CVEs. */
3149  " AND common_cve (nvts.cve, outer_nvts.cve)))))"
3150  " THEN NULL"
3151  " WHEN severity = " G_STRINGIFY (SEVERITY_ERROR) " THEN NULL"
3152  " ELSE 1 END)"
3153  " ELSE 0 END) AS autofp"
3154  " FROM results,"
3155  " (SELECT 0 AS autofp_selection"
3156  " UNION SELECT 1 AS autofp_selection"
3157  " UNION SELECT 2 AS autofp_selection) AS autofp_opts;");
3158 }
3159 
3163 void
3165 {
3166  // Do nothing because this is only relevant for PostgreSQL.
3167 }
3168 
3169 
3170 /* SecInfo. */
3171 
3175 void
3177 {
3178  /* Attach the SCAP database. */
3179 
3180  if (access (OPENVAS_SCAP_DATA_DIR "/scap.db", R_OK))
3181  switch (errno)
3182  {
3183  case ENOENT:
3184  break;
3185  default:
3186  g_warning ("%s: failed to stat SCAP database: %s\n",
3187  __FUNCTION__,
3188  strerror (errno));
3189  break;
3190  }
3191  else
3192  sql_error ("ATTACH DATABASE '" OPENVAS_SCAP_DATA_DIR "/scap.db'"
3193  " AS scap;");
3194 
3195  /* Attach the CERT database. */
3196 
3197  if (access (OPENVAS_CERT_DATA_DIR "/cert.db", R_OK))
3198  switch (errno)
3199  {
3200  case ENOENT:
3201  break;
3202  default:
3203  g_warning ("%s: failed to stat CERT database: %s\n",
3204  __FUNCTION__,
3205  strerror (errno));
3206  break;
3207  }
3208  else
3209  sql_error ("ATTACH DATABASE '" OPENVAS_CERT_DATA_DIR "/cert.db'"
3210  " AS cert;");
3211 }
3212 
3218 int
3220 {
3221  static int loaded = 0;
3222 
3223  if (loaded)
3224  return 1;
3225 
3226  if (access (OPENVAS_CERT_DATA_DIR "/cert.db", R_OK))
3227  switch (errno)
3228  {
3229  case ENOENT:
3230  return 0;
3231  break;
3232  default:
3233  g_warning ("%s: failed to stat CERT database: %s\n",
3234  __FUNCTION__,
3235  strerror (errno));
3236  return 0;
3237  }
3238 
3239  if (sql_error ("SELECT count(*) FROM cert.sqlite_master"
3240  " WHERE type = 'table' AND name = 'dfn_cert_advs';"))
3241  /* There was an error, so probably the initial ATTACH failed. */
3242  return 0;
3243 
3244  loaded = !!sql_int ("SELECT count(*) FROM cert.sqlite_master"
3245  " WHERE type = 'table' AND name = 'dfn_cert_advs';");
3246  return loaded;
3247 }
3248 
3254 int
3256 {
3257  static int loaded = 0;
3258 
3259  if (loaded)
3260  return 1;
3261 
3262  if (access (OPENVAS_SCAP_DATA_DIR "/scap.db", R_OK))
3263  switch (errno)
3264  {
3265  case ENOENT:
3266  return 0;
3267  break;
3268  default:
3269  g_warning ("%s: failed to stat SCAP database: %s\n",
3270  __FUNCTION__,
3271  strerror (errno));
3272  return 0;
3273  }
3274 
3275  if (sql_error ("SELECT count(*) FROM scap.sqlite_master"
3276  " WHERE type = 'table' AND name = 'cves';"))
3277  /* There was an error, so probably the initial ATTACH failed. */
3278  return 0;
3279 
3280  loaded = !!sql_int ("SELECT count(*) FROM scap.sqlite_master"
3281  " WHERE type = 'table' AND name = 'cves';");
3282  return loaded;
3283 }
3284 
3285 
3286 /* Backup. */
3287 
3297 static int
3298 backup_db (const gchar *database, gchar **backup_file_arg)
3299 {
3300  gchar *backup_file;
3301  sqlite3 *backup_db, *actual_task_db;
3302  sqlite3_backup *backup;
3303 
3304  backup_file = g_strdup_printf ("%s.bak", database);
3305 
3306  if (sqlite3_open (backup_file, &backup_db) != SQLITE_OK)
3307  {
3308  g_warning ("%s: sqlite3_open failed: %s\n",
3309  __FUNCTION__,
3310  sqlite3_errmsg (task_db));
3311  goto fail;
3312  }
3313 
3314  /* Turn off WAL for the backup db. */
3315  actual_task_db = task_db;
3316  task_db = backup_db;
3317  sql ("PRAGMA journal_mode=DELETE;");
3318  task_db = actual_task_db;
3319 
3320  backup = sqlite3_backup_init (backup_db, "main", task_db, "main");
3321  if (backup == NULL)
3322  {
3323  g_warning ("%s: sqlite3_backup_init failed: %s\n",
3324  __FUNCTION__,
3325  sqlite3_errmsg (backup_db));
3326  goto fail;
3327  }
3328 
3329  while (1)
3330  {
3331  int ret;
3332 
3333  ret = sqlite3_backup_step (backup, 20 /* pages */);
3334  if (ret == SQLITE_DONE)
3335  break;
3336  if (ret == SQLITE_OK)
3337  continue;
3338  if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
3339  {
3340  sqlite3_sleep (250);
3341  continue;
3342  }
3343  g_warning ("%s: sqlite3_backup_step failed: %s\n",
3344  __FUNCTION__,
3345  sqlite3_errmsg (backup_db));
3346  sqlite3_backup_finish (backup);
3347  goto fail;
3348  }
3349  sqlite3_backup_finish (backup);
3350  sqlite3_close (backup_db);
3351 
3352  if (backup_file_arg)
3353  *backup_file_arg = backup_file;
3354  else
3355  g_free (backup_file);
3356  return 0;
3357 
3358  fail:
3359  sqlite3_close (backup_db);
3360  g_free (backup_file);
3361  return -1;
3362 }
3363 
3371 int
3372 manage_backup_db (const gchar *database)
3373 {
3374  int ret;
3375  const gchar *db = database ? database : sql_default_database ();
3376 
3377  init_manage_process (0, db);
3378 
3379  ret = backup_db (db, NULL);
3380 
3381  cleanup_manage_process (TRUE);
3382 
3383  return ret;
3384 }
3385 
3386 
3387 /* Migrator helper. */
3388 
3398 void
3399 migrate_51_to_52_sql_convert (sqlite3_context *context, int argc,
3400  sqlite3_value** argv)
3401 {
3402  const unsigned char *text_time;
3403  int epoch_time;
3404  struct tm tm;
3405 
3406  assert (argc == 1);
3407 
3408  text_time = sqlite3_value_text (argv[0]);
3409  if (text_time)
3410  {
3411  /* Scanner uses ctime: "Wed Jun 30 21:49:08 1993".
3412  *
3413  * The dates being converted are in the timezone that the Scanner was using.
3414  *
3415  * As a special case for this migrator, openvasmd.c uses the timezone
3416  * from the environment, instead of forcing UTC. This allows the user
3417  * to set the timezone to be the same as the Scanner timezone, so
3418  * that these dates are converted from the Scanner timezone. Even if
3419  * the user just leaves the timezone as is, it is likely to be the same
3420  * timezone she/he is running the Scanner under.
3421  */
3422  if (text_time && (strlen ((char*) text_time) > 0))
3423  {
3424  memset (&tm, 0, sizeof (struct tm));
3425  if (strptime ((char*) text_time, "%a %b %d %H:%M:%S %Y", &tm) == NULL)
3426  {
3427  sqlite3_result_error (context, "Failed to parse time", -1);
3428  return;
3429  }
3430  epoch_time = mktime (&tm);
3431  if (epoch_time == -1)
3432  {
3433  sqlite3_result_error (context, "Failed to make time", -1);
3434  return;
3435  }
3436  }
3437  else
3438  epoch_time = 0;
3439  }
3440  else
3441  epoch_time = 0;
3442  sqlite3_result_int (context, epoch_time);
3443 }
3444 
3450 int
3452 {
3453  if (sqlite3_create_function (task_db,
3454  "convert",
3455  1, /* Number of args. */
3456  SQLITE_UTF8,
3457  NULL, /* Callback data. */
3459  NULL, /* xStep. */
3460  NULL) /* xFinal. */
3461  != SQLITE_OK)
3462  {
3463  g_warning ("%s: failed to create convert", __FUNCTION__);
3464  return -1;
3465  }
3466  return 0;
3467 }
int trash_target_credential_location(target_t, const char *)
Get whether a credential of a trash target is in trashcan.
Definition: manage_sql.c:33246
void init_manage_process(int, const gchar *)
Initialize the manage library for a process.
Definition: manage_sql.c:14303
void sql_make_uuid(sqlite3_context *context, int argc, sqlite3_value **argv)
Make a UUID.
int report_counts_id(report_t, int *, int *, int *, int *, int *, int *, double *, const get_data_t *, const char *)
Get only the filtered message counts for a report.
Definition: manage_sql.c:25133
void sql_target_login_port(sqlite3_context *context, int argc, sqlite3_value **argv)
Get a target port.
void sql_run_status_name(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the name of a task run status.
long long int credential_t
Definition: manage.h:279
void sql_uniquify(sqlite3_context *context, int argc, sqlite3_value **argv)
Make a name unique.
#define MIN_QOD_DEFAULT
Default min quality of detection percentage for filters.
Definition: manage.h:1110
void sql_clean_hosts(sqlite3_context *context, int argc, sqlite3_value **argv)
Clean a host list.
gchar * severity
Severity.
void sql_common_cve(sqlite3_context *context, int argc, sqlite3_value **argv)
Check if two CVE lists contain a common CVE.
void sql_t(sqlite3_context *context, int argc, sqlite3_value **argv)
Return 1.
void sql_resource_exists(sqlite3_context *context, int argc, sqlite3_value **argv)
Get if a resource exists by its type and ID.
void sql_regexp(sqlite3_context *context, int argc, sqlite3_value **argv)
Do a regexp match. Implements SQL REGEXP.
gchar * overrides_severity
Severity.
double report_severity(report_t, int, int)
Get the maximum severity of a report.
Definition: manage_sql.c:25154
const char * severity_to_type(double severity)
Get the message type matching a severity score.
Definition: manage.c:691
long long int target_t
Definition: manage.h:285
gchar * tag_value(const gchar *tags, const gchar *tag)
Extract a tag from an OTP tag list.
Definition: manage_sql.c:1269
char * iso_time(time_t *)
Create an ISO time from seconds since epoch.
Definition: manage_sql.c:1138
int sql_int(char *sql,...)
Get a particular cell from a SQL query, as an int.
Definition: sql.c:438
int manage_count_hosts(const char *, const char *)
Return number of hosts described by a hosts string.
Definition: manage_sql.c:32849
void sql_tag(sqlite3_context *context, int argc, sqlite3_value **argv)
Extract a tag from an OTP tag list.
void sql_report_severity(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate the severity of a report.
int report_result_host_count(report_t, int)
Count a report&#39;s total number of hosts with results.
Definition: manage_sql.c:26898
void sql_parse_time(sqlite3_context *context, int argc, sqlite3_value **argv)
Try convert an OTP NVT tag time string into epoch time.
void manage_attach_databases()
Attach external databases.
void sql_max_hosts(sqlite3_context *context, int argc, sqlite3_value **argv)
Return number of hosts.
#define LSC_FAMILY_LIST
SQL list of LSC families.
Definition: manage.h:1065
void sql_order_inet(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert an IP address into a sortable form.
gchar * clean_hosts(const char *, int *)
Clean a hosts string.
Definition: manage_sql.c:32894
A generic SQL iterator.
Definition: iterator.h:52
void cleanup_manage_process(gboolean)
Cleanup the manage library.
Definition: manage_sql.c:17128
void sql(char *sql,...)
Perform an SQL statement, retrying if database is busy or locked.
Definition: sql.c:199
gchar * acl_where_owned_for_get(const char *type, const char *user_sql)
Generate ownership part of WHERE, for getting a type of resource.
Definition: manage_acl.c:1378
int manage_backup_db(const gchar *database)
Backup the database to a file.
gboolean report_task(report_t, task_t *)
Return the task of a report.
Definition: manage_sql.c:21397
int parse_time(const gchar *, int *)
Try convert an OTP NVT tag time string into epoch time.
Definition: manage_sql.c:1308
void sql_report_result_host_count(sqlite3_context *context, int argc, sqlite3_value **argv)
Count the number of hosts of a report with results.
int severity_matches_ov(double severity, double ov_severity)
Check whether a severity matches an override&#39;s severity.
Definition: manage.c:635
void sql_task_trend(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate the trend of a task.
void sql_user_can_everything(sqlite3_context *context, int argc, sqlite3_value **argv)
Check if a user can do anything.
void sql_report_host_count(sqlite3_context *context, int argc, sqlite3_value **argv)
Count the number of hosts of a report.
gchar * sql_quote(const char *string)
Quotes a string to be passed to sql statements.
Definition: sql.c:121
void sql_cpe_title(sqlite3_context *context, int argc, sqlite3_value **argv)
Check if two CVE lists contain a common CVE.
credential_t target_credential(target_t, const char *)
Get a credential from a target.
Definition: manage_sql.c:33168
int manage_create_migrate_51_to_52_convert()
Setup SQL function for migrate_51_to_52.
void sql_current_offset(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the offset from UTC of the current time for a timezone.
const char * iterator_column_name(iterator_t *, int)
Get a column name from an iterator.
Definition: sql_pg.c:691
int task_last_report(task_t, report_t *)
Get the report from the most recently completed invocation of task.
Definition: manage_sql.c:18542
Command data for a get command.
Definition: manage.h:310
void check_db_sequences()
Ensure sequences for automatic ids are in a consistent state.
credential_t trash_target_credential(target_t, const char *)
Get a credential from a target in the trashcan.
Definition: manage_sql.c:33207
int manage_create_sql_functions()
Create functions.
void sql_target_credential(sqlite3_context *context, int argc, sqlite3_value **argv)
Get a target credential.
int acl_user_can_everything(const char *user_id)
Test whether a user may perform any operation.
Definition: manage_acl.c:179
long long int report_t
Definition: manage.h:288
int min_qod
Minimum QoD.
void sql_now(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the current time as an epoch integer.
int resource_name(const char *, const char *, int, gchar **)
void sql_days_from_now(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate difference between now and epoch time in days.
void sql_task_severity(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate the severity of a task.
void get_data_reset(get_data_t *)
Reset command data.
Definition: omp.c:2322
void sql_hosts_contains(sqlite3_context *context, int argc, sqlite3_value **argv)
Check if a host list contains a host.
#define SEVERITY_ERROR
Definition: manage_utils.h:38
void sql_credential_value(sqlite3_context *context, int argc, sqlite3_value **argv)
Get a value from the data of a credential.
const char * run_status_name(task_status_t status)
Get the name of a run status.
Definition: manage.c:1253
int sql_error(char *sql,...)
Perform an SQL statement, retrying if database is busy or locked.
Definition: sql.c:229
void sql_task_last_report(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the last report of a task.
void migrate_51_to_52_sql_convert(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert a UTC text time to an integer time since the Epoch.
void sql_rename_column(const char *old_table, const char *new_table, const char *old_name, const char *new_name)
Move data from a table to a new table, heeding column rename.
void sql_order_role(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert a role for sorting.
void sql_next_time(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate the next time from now given a start time and a period.
int manage_db_empty()
Check whether database is empty.
const char * sql_default_database()
Return name of default database.
Definition: sql_pg.c:237
void sql_resource_name(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the name of a resource by its type and ID.
void sql_severity_matches_ov(sqlite3_context *context, int argc, sqlite3_value **argv)
Test if a severity score matches an override&#39;s severity.
void cleanup_iterator(iterator_t *)
Cleanup an iterator.
Definition: sql.c:664
void manage_session_set_timezone(const char *timezone)
Setup session timezone.
void sql_task_threat_level(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate the threat level of a task.
void sql_report_progress(sqlite3_context *context, int argc, sqlite3_value **argv)
Calculate the progress of a report.
int report_progress(report_t, task_t, gchar **)
Calculate the progress of a report.
Definition: manage_sql.c:28138
int days_from_now(time_t *)
Calculate difference between now and epoch_time in days.
Definition: manage_sql.c:1072
int resource_exists(const char *, resource_t, int)
Get whether a resource exists.
Definition: manage_sql.c:4826
int report_host_count(report_t)
Count a report&#39;s total number of hosts.
Definition: manage_sql.c:26882
credentials_t current_credentials
Current credentials during any OMP command.
Definition: manage.c:717
int manage_scap_loaded()
Check whether SCAP is available.
void sql_user_owns(sqlite3_context *context, int argc, sqlite3_value **argv)
Check if a user owns or effectively owns a resource.
char * task_severity(task_t, int, int, int)
Return the severity score of a task, taking overrides into account.
Definition: manage_sql.c:19094
void create_tables()
Create all tables.
void sql_severity_in_level(sqlite3_context *context, int argc, sqlite3_value **argv)
Check whether a severity falls within a threat level.
task_t overrides_task
Task.
int iterator_column_count(iterator_t *)
Get number of columns from an iterator.
Definition: sql_pg.c:706
char * sql_string(char *sql,...)
Get a particular cell from a SQL query, as an string.
Definition: sql.c:469
void sql_order_port(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert a port into an integer for sorting.
void sql_order_threat(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert a threat into an integer for sorting.
long current_offset(const char *)
Get the current offset from UTC of a timezone.
Definition: manage_utils.c:117
int user_can_everything(const char *)
time_t next_time(time_t first, int period, int period_months, const char *timezone, int periods_offset)
Calculate the next time from now given a start time and a period.
Definition: manage_utils.c:287
void sql_trash_target_credential_location(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the location of a trash target credential.
gboolean next(iterator_t *)
Increment an iterator.
Definition: sql.c:689
void sql_order_message_type(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert a message type into an integer for sorting.
const char * severity_to_level(double severity, int mode)
Get the threat level matching a severity score.
Definition: manage.c:652
void manage_session_init(const char *uuid)
Setup session.
void sql_report_severity_count(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the number of results of a given severity level in a report.
task_t task
Task.
int user_owns(const char *, resource_t, int)
get_data_t * report_results_get_data(int first, int rows, int apply_overrides, int autofp, int min_qod)
Create a new basic get_data_t struct to get report results.
Definition: manage.c:766
void init_iterator(iterator_t *iterator, const char *sql,...)
Initialise an iterator.
Definition: sql.c:577
int valid_db_resource_type(const char *)
Check whether a resource type table name is valid.
Definition: manage_utils.c:504
void sql_iso_time(sqlite3_context *context, int argc, sqlite3_value **argv)
Convert an epoch time into a string in ISO format.
void sql_strpos(sqlite3_context *context, int argc, sqlite3_value **argv)
Get position of a substring like the strpos function in PostgreSQL.
sqlite3 * task_db
Handle on the database.
Definition: sql_sqlite3.c:73
int severity_in_level(double severity, const char *level)
Check whether a severity falls within a threat level.
Definition: manage.c:583
void sql_severity_to_type(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the message type matching a severity score.
long long int task_t
Definition: manage.h:286
int manage_cert_loaded()
Check whether CERT is available.
#define SEVERITY_LOG
Definition: manage_utils.h:32
int trash_target_login_port(target_t, const char *)
Get a port from a target in the trashcan.
Definition: manage_sql.c:33322
int target_login_port(target_t, const char *)
Get a login port from a target.
Definition: manage_sql.c:33284
int acl_user_owns(const char *type, resource_t resource, int trash)
Test whether a user effectively owns a resource.
Definition: manage_acl.c:666
void sql_severity_to_level(sqlite3_context *context, int argc, sqlite3_value **argv)
Get the threat level matching a severity score.
long long int resource_t
A resource, like a task or target.
Definition: iterator.h:42
const char * task_trend(task_t, int, int)
Return the trend of a task.
Definition: manage_sql.c:31809