OpenVAS Manager  7.0.3~git
sql_sqlite3.c
Go to the documentation of this file.
1 /* OpenVAS Manager
2  * $Id$
3  * Description: Manager Manage library: the SQL library.
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 #include "sql.h"
27 #include "utils.h"
28 
29 #include <assert.h>
30 #include <sqlite3.h>
31 #include <stdlib.h>
32 #include <stdio.h>
33 #include <string.h>
34 #include <sys/stat.h>
35 #include <errno.h>
36 
40 #define DB_CHUNK_SIZE 1 * 1024 * 1024
41 
42 #undef G_LOG_DOMAIN
43 
46 #define G_LOG_DOMAIN "md manage"
47 
51 #define BUSY_TIMEOUT 1000
52 
53 
54 /* Headers of sql.c symbols used only here. */
55 
56 int
57 sqlv (int, char*, va_list);
58 
59 
60 /* Types. */
61 
62 struct sql_stmt
63 {
64  sqlite3_stmt *stmt;
65 };
66 
67 
68 /* Variables. */
69 
73 sqlite3* task_db = NULL;
74 
75 
76 /* Helpers. */
77 
83 int
85 {
86  return 1;
87 }
88 
94 const char *
96 {
97  return "main";
98 }
99 
105 const char *
107 {
108  return "max";
109 }
110 
118 const char *
120 {
121  static char string[20];
122  if (max < 0)
123  return "-1";
124  if (snprintf (string, 19, "%i", max) < 0)
125  {
126  g_warning ("%s: snprintf failed\n", __FUNCTION__);
127  abort ();
128  }
129  string[19] = '\0';
130  return string;
131 }
132 
138 const char *
140 {
141  return "LIKE";
142 }
143 
149 const char *
151 {
152  return "REGEXP";
153 }
154 
160 int
162 {
163  return task_db ? 1 : 0;
164 }
165 
171 const char *
173 {
174  /* This is currently only used by Postgres. */
175  return "TODO";
176 }
177 
183 const char *
185 {
186  return OPENVAS_STATE_DIR "/mgr/tasks.db";
187 }
188 
196 int
197 sql_open (const char *database)
198 {
199  int chunk_size = DB_CHUNK_SIZE;
200  struct stat state;
201  int err, ret;
202  gchar *mgr_dir;
203 
204  /* Ensure the mgr directory exists. */
205 
206  mgr_dir = g_build_filename (OPENVAS_STATE_DIR, "mgr", NULL);
207  ret = g_mkdir_with_parents (mgr_dir, 0755 /* "rwxr-xr-x" */);
208  g_free (mgr_dir);
209  if (ret == -1)
210  {
211  g_warning ("%s: failed to create mgr directory: %s\n",
212  __FUNCTION__,
213  strerror (errno));
214  abort ();
215  }
216 
217  err = stat (database ? database : sql_default_database (),
218  &state);
219  if (err)
220  switch (errno)
221  {
222  case ENOENT:
223  break;
224  default:
225  g_warning ("%s: failed to stat database: %s\n",
226  __FUNCTION__,
227  strerror (errno));
228  abort ();
229  }
230  else if (state.st_mode & (S_IXUSR | S_IRWXG | S_IRWXO))
231  {
232  g_warning ("%s: database permissions are too loose, repairing\n",
233  __FUNCTION__);
234  if (chmod (database ? database : sql_default_database (),
235  S_IRUSR | S_IWUSR))
236  {
237  g_warning ("%s: chmod failed: %s\n",
238  __FUNCTION__,
239  strerror (errno));
240  abort ();
241  }
242  }
243 
244  /* Workaround for SQLite temp file name conflicts that can occur if
245  * concurrent forked processes have the same PRNG state. */
246 #if SQLITE_VERSION_NUMBER < 3008003
247  sqlite3_test_control (SQLITE_TESTCTRL_PRNG_RESET);
248 #endif
249 
250  if (sqlite3_open (database ? database : sql_default_database (),
251  &task_db))
252  {
253  g_warning ("%s: sqlite3_open failed: %s\n",
254  __FUNCTION__,
255  sqlite3_errmsg (task_db));
256  return -1;
257  }
258 
259  sqlite3_busy_timeout (task_db, BUSY_TIMEOUT);
260 
261  g_debug (" %s: db open, max retry sleep time is %i\n",
262  __FUNCTION__,
263  OPENVAS_SQLITE_SLEEP_MAX);
264 
265  sqlite3_file_control (task_db, NULL, SQLITE_FCNTL_CHUNK_SIZE, &chunk_size);
266 
267  sql ("PRAGMA journal_mode=WAL;");
268  sql ("PRAGMA journal_size_limit=134217728;"); /* 128 MB. */
269 
270  return 0;
271 }
272 
276 void
278 {
279  if (sqlite3_close (task_db) == SQLITE_BUSY)
280  /* Richard Hipp on how to find the open statements:
281  *
282  * There is no published way to do this. If you run in a debugger,
283  * you can look at the linked list of "struct Vdbe" objects that
284  * sqlite3.pVdbe points to. This is the list of open statements
285  * in the current implementation (and subject to change without
286  * notice). */
287  g_warning ("%s: attempt to close db with open statement(s)\n",
288  __FUNCTION__);
289  task_db = NULL;
290 }
291 
295 void
297 {
298  task_db = NULL;
299 }
300 
304 int
306 {
307  return sqlite3_changes (task_db);
308 }
309 
315 {
316  return sqlite3_last_insert_rowid (task_db);
317 }
318 
326 void
327 sqli (resource_t *resource, char* sql, ...)
328 {
329  va_list args;
330 
331  va_start (args, sql);
332  if (sqlv (1, sql, args) == -1)
333  abort ();
334  va_end (args);
335  if (resource)
336  *resource = sql_last_insert_id ();
337 }
338 
350 int
351 sql_prepare_internal (int retry, int log, const char* sql, va_list args,
352  sql_stmt_t **stmt)
353 {
354  const char* tail;
355  int ret;
356  unsigned int retries;
357  gchar* formatted;
358  sqlite3_stmt *sqlite_stmt;
359 
360  assert (stmt);
361 
362  formatted = g_strdup_vprintf (sql, args);
363 
364  if (log)
365  g_debug (" sql: %s\n", formatted);
366 
367  if (retry == 0)
368  sqlite3_busy_timeout (task_db, 0);
369 
370  retries = 0;
371  *stmt = (sql_stmt_t*) g_malloc0 (sizeof (sql_stmt_t));
372  sqlite_stmt = NULL;
373  while (1)
374  {
375  ret = sqlite3_prepare_v2 (task_db, (char*) formatted, -1, &sqlite_stmt,
376  &tail);
377  if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED)
378  {
379  if (retry)
380  {
381  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
382  openvas_usleep (MIN ((retries - 10) * 10000,
383  OPENVAS_SQLITE_SLEEP_MAX));
384  retries++;
385  continue;
386  }
387  if (retries++ < 10)
388  continue;
389  g_free (formatted);
390  if (retry == 0)
391  sqlite3_busy_timeout (task_db, BUSY_TIMEOUT);
392  return 1;
393  }
394  g_free (formatted);
395  (*stmt)->stmt = sqlite_stmt;
396  if (ret == SQLITE_OK)
397  {
398  if (sqlite_stmt == NULL)
399  {
400  g_warning ("%s: sqlite3_prepare failed with NULL stmt: %s\n",
401  __FUNCTION__,
402  sqlite3_errmsg (task_db));
403  if (retry == 0)
404  sqlite3_busy_timeout (task_db, BUSY_TIMEOUT);
405  return -1;
406  }
407  break;
408  }
409  g_warning ("%s: sqlite3_prepare failed: %s\n",
410  __FUNCTION__,
411  sqlite3_errmsg (task_db));
412  if (retry == 0)
413  sqlite3_busy_timeout (task_db, BUSY_TIMEOUT);
414  return -1;
415  }
416 
417  if (retry == 0)
418  sqlite3_busy_timeout (task_db, BUSY_TIMEOUT);
419  return 0;
420 }
421 
432 int
434 {
435  unsigned int retries;
436 
437  if (retry == 0)
438  sqlite3_busy_timeout (task_db, 0);
439 
440  retries = 0;
441  while (1)
442  {
443  int ret;
444  ret = sqlite3_step (stmt->stmt);
445  if (ret == SQLITE_BUSY)
446  {
447  if (retry)
448  {
449  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
450  openvas_usleep (MIN ((retries - 10) * 10000,
451  OPENVAS_SQLITE_SLEEP_MAX));
452  retries++;
453  continue;
454  }
455  if (retries++ < 10)
456  continue;
457  return -2;
458  }
459  if (retry == 0)
460  sqlite3_busy_timeout (task_db, BUSY_TIMEOUT);
461  if (ret == SQLITE_DONE)
462  return 0;
463  if (ret == SQLITE_ROW)
464  return 1;
465  g_warning ("%s: sqlite3_step failed: %s\n",
466  __FUNCTION__,
467  sqlite3_errmsg (task_db));
468  return -1;
469  }
470 }
471 
480 int
481 sql_explain_internal (const char* sql, va_list args)
482 {
483  char *explain_sql;
484  sql_stmt_t *explain_stmt;
485  int explain_ret;
486 
487  explain_sql = g_strconcat ("EXPLAIN QUERY PLAN ", sql, NULL);
488  if (sql_prepare_internal (1, 1, explain_sql, args, &explain_stmt))
489  {
490  g_warning ("%s : Failed to prepare EXPLAIN statement", __FUNCTION__);
491  g_free (explain_sql);
492  return -1;
493  }
494 
495  while (1)
496  {
497  explain_ret = sql_exec_internal (1, explain_stmt);
498  if (explain_ret == 1)
499  g_debug ("%s : %s|%s|%s|%s",
500  __FUNCTION__,
501  sqlite3_column_text (explain_stmt->stmt, 0),
502  sqlite3_column_text (explain_stmt->stmt, 1),
503  sqlite3_column_text (explain_stmt->stmt, 2),
504  sqlite3_column_text (explain_stmt->stmt, 3));
505  else if (explain_ret == 0)
506  break;
507  else
508  {
509  g_warning ("%s : Failed to get EXPLAIN row", __FUNCTION__);
510  sql_finalize (explain_stmt);
511  g_free (explain_sql);
512  return -1;
513  }
514  }
515 
516  sql_finalize (explain_stmt);
517  g_free (explain_sql);
518  return 0;
519 }
520 
521 
522 /* Transactions. */
523 
527 void
529 {
530  sql ("BEGIN EXCLUSIVE;");
531 }
532 
538 int
540 {
541  return sql_giveup ("BEGIN EXCLUSIVE;");
542 }
543 
547 void
549 {
550  sql ("BEGIN IMMEDIATE;");
551 }
552 
558 int
560 {
561  return sql_giveup ("BEGIN IMMEDIATE;");
562 }
563 
567 void
569 {
570  sql ("COMMIT;");
571 }
572 
576 void
578 {
579  sql ("ROLLBACK;");
580 }
581 
582 
583 /* Iterators. */
584 
593 int
595 {
596  if (iterator->done) abort ();
597  return sqlite3_column_type (iterator->stmt->stmt, col) == SQLITE_NULL;
598 }
599 
608 const char*
610 {
611  if (iterator->done) abort ();
612  return (const char*) sqlite3_column_name (iterator->stmt->stmt, col);
613 }
614 
622 int
624 {
625  if (iterator->done) abort ();
626  return sqlite3_column_count (iterator->stmt->stmt);
627 }
628 
629 
630 /* Prepared statements. */
631 
642 int
643 sql_bind_blob (sql_stmt_t *stmt, int position, const void *value,
644  int value_size)
645 {
646  unsigned int retries;
647  retries = 0;
648  while (1)
649  {
650  int ret;
651  ret = sqlite3_bind_blob (stmt->stmt,
652  position,
653  value,
654  value_size,
655  SQLITE_TRANSIENT);
656  if (ret == SQLITE_BUSY)
657  {
658  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
659  openvas_usleep (MIN ((retries - 10) * 10000,
660  OPENVAS_SQLITE_SLEEP_MAX));
661  retries++;
662  continue;
663  }
664  if (ret == SQLITE_OK) break;
665  g_warning ("%s: sqlite3_bind_blob failed: %s\n",
666  __FUNCTION__,
667  sqlite3_errmsg (task_db));
668  return -1;
669  }
670  return 0;
671 }
672 
682 int
683 sql_bind_int64 (sql_stmt_t *stmt, int position, long long int *value)
684 {
685  unsigned int retries;
686  retries = 0;
687  while (1)
688  {
689  int ret;
690  ret = sqlite3_bind_int64 (stmt->stmt, position, *value);
691  if (ret == SQLITE_BUSY)
692  {
693  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
694  openvas_usleep (MIN ((retries - 10) * 10000,
695  OPENVAS_SQLITE_SLEEP_MAX));
696  retries++;
697  continue;
698  }
699  if (ret == SQLITE_OK) break;
700  g_warning ("%s: sqlite3_bind_int64 failed: %s\n",
701  __FUNCTION__,
702  sqlite3_errmsg (task_db));
703  return -1;
704  }
705  return 0;
706 }
707 
717 int
718 sql_bind_double (sql_stmt_t *stmt, int position, double *value)
719 {
720  unsigned int retries;
721  retries = 0;
722  while (1)
723  {
724  int ret;
725  ret = sqlite3_bind_double (stmt->stmt, position, *value);
726  if (ret == SQLITE_BUSY)
727  {
728  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
729  openvas_usleep (MIN ((retries - 10) * 10000,
730  OPENVAS_SQLITE_SLEEP_MAX));
731  retries++;
732  continue;
733  }
734  if (ret == SQLITE_OK) break;
735  g_warning ("%s: sqlite3_bind_double failed: %s\n",
736  __FUNCTION__,
737  sqlite3_errmsg (task_db));
738  return -1;
739  }
740  return 0;
741 }
742 
753 int
754 sql_bind_text (sql_stmt_t *stmt, int position, const gchar *value,
755  gsize value_size)
756 {
757  unsigned int retries;
758  retries = 0;
759  while (1)
760  {
761  int ret;
762  ret = sqlite3_bind_text (stmt->stmt,
763  position,
764  value,
765  value_size,
766  SQLITE_TRANSIENT);
767  if (ret == SQLITE_BUSY)
768  {
769  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
770  openvas_usleep (MIN ((retries - 10) * 10000,
771  OPENVAS_SQLITE_SLEEP_MAX));
772  retries++;
773  continue;
774  }
775  if (ret == SQLITE_OK) break;
776  g_warning ("%s: sqlite3_bind_text failed: %s\n",
777  __FUNCTION__,
778  sqlite3_errmsg (task_db));
779  return -1;
780  }
781  return 0;
782 }
783 
789 void
791 {
792  if (stmt->stmt)
793  sqlite3_finalize (stmt->stmt);
794  g_free (stmt);
795 }
796 
804 int
806 {
807  unsigned int retries;
808  sqlite3_clear_bindings (stmt->stmt);
809  retries = 0;
810  while (1)
811  {
812  int ret;
813  ret = sqlite3_reset (stmt->stmt);
814  if (ret == SQLITE_BUSY)
815  {
816  if ((retries > 10) && (OPENVAS_SQLITE_SLEEP_MAX > 0))
817  openvas_usleep (MIN ((retries - 10) * 10000,
818  OPENVAS_SQLITE_SLEEP_MAX));
819  retries++;
820  continue;
821  }
822  if (ret == SQLITE_DONE || ret == SQLITE_OK) break;
823  if (ret == SQLITE_ERROR || ret == SQLITE_MISUSE)
824  {
825  g_warning ("%s: sqlite3_reset failed: %s\n",
826  __FUNCTION__,
827  sqlite3_errmsg (task_db));
828  return -1;
829  }
830  }
831  return 0;
832 }
833 
842 double
844 {
845  return sqlite3_column_double (stmt->stmt, position);
846 }
847 
856 const char *
858 {
859  return (const char*) sqlite3_column_text (stmt->stmt, position);
860 }
861 
870 int
872 {
873  return sqlite3_column_int (stmt->stmt, position);
874 }
875 
884 long long int
886 {
887  return sqlite3_column_int64 (stmt->stmt, position);
888 }
889 
895 int
897 {
898  if (task_db)
899  {
900  sqlite3_interrupt (task_db);
901  return 0;
902  }
903  else
904  {
905  return -1;
906  }
907 }
int sql_bind_double(sql_stmt_t *stmt, int position, double *value)
Bind a double value to a statement.
Definition: sql_sqlite3.c:718
int sql_is_open()
Check whether the database is open.
Definition: sql_sqlite3.c:161
void sql_close_fork()
Close the database in a forked process.
Definition: sql_sqlite3.c:296
sqlite3 * task_db
Handle on the database.
Definition: sql_sqlite3.c:73
int sql_begin_immediate_giveup()
Begin an exclusive transaction.
Definition: sql_sqlite3.c:559
sqlite3_stmt * stmt
Definition: sql_sqlite3.c:64
const char * sql_greatest()
Get keyword for "greatest" SQL function.
Definition: sql_sqlite3.c:106
#define BUSY_TIMEOUT
Busy timeout, in milliseconds.
Definition: sql_sqlite3.c:51
#define DB_CHUNK_SIZE
Chunk size for SQLite memory allocation.
Definition: sql_sqlite3.c:40
int sql_prepare_internal(int retry, int log, const char *sql, va_list args, sql_stmt_t **stmt)
Prepare a statement.
Definition: sql_sqlite3.c:351
const char * iterator_column_name(iterator_t *iterator, int col)
Get a column name from an iterator.
Definition: sql_sqlite3.c:609
int sql_explain_internal(const char *sql, va_list args)
Write debug messages with the query plan for an SQL query to the log.
Definition: sql_sqlite3.c:481
int sql_column_int(sql_stmt_t *stmt, int position)
Return a column as an integer from a prepared statement.
Definition: sql_sqlite3.c:871
int sql_giveup(char *sql,...)
Perform an SQL statement, giving up if database is busy or locked.
Definition: sql.c:257
int sql_changes()
Get the number of rows changed or inserted in last statement.
Definition: sql_sqlite3.c:305
A generic SQL iterator.
Definition: iterator.h:52
gboolean done
End flag.
Definition: iterator.h:55
resource_t sql_last_insert_id()
Get the ID of the last inserted row.
Definition: sql_sqlite3.c:314
void sql_begin_immediate()
Begin an exclusive transaction.
Definition: sql_sqlite3.c:548
void sql_begin_exclusive()
Begin an exclusive transaction.
Definition: sql_sqlite3.c:528
int sql_cancel_internal()
Cancels the current SQL statement.
Definition: sql_sqlite3.c:896
void sql_commit()
Commit a transaction.
Definition: sql_sqlite3.c:568
const char * sql_database()
Return file name of current database.
Definition: sql_sqlite3.c:172
int sql_bind_int64(sql_stmt_t *stmt, int position, long long int *value)
Bind an int64 value to a statement.
Definition: sql_sqlite3.c:683
void sql_close()
Close the database.
Definition: sql_sqlite3.c:277
int sql_open(const char *database)
Open the database.
Definition: sql_sqlite3.c:197
int sql_begin_exclusive_giveup()
Begin an exclusive transaction, giving up on failure.
Definition: sql_sqlite3.c:539
void sql_rollback()
Roll a transaction back.
Definition: sql_sqlite3.c:577
int sql_exec_internal(int retry, sql_stmt_t *stmt)
Execute a prepared statement.
Definition: sql_sqlite3.c:433
sql_stmt_t * stmt
SQL statement.
Definition: iterator.h:54
const char * sql_select_limit(int max)
Setup a LIMIT argument.
Definition: sql_sqlite3.c:119
const char * sql_schema()
Get main schema name.
Definition: sql_sqlite3.c:95
const char * sql_ilike_op()
Get case insensitive LIKE operator.
Definition: sql_sqlite3.c:139
int openvas_usleep(unsigned int microseconds)
Sleep for some number of microseconds, handling interrupts.
Definition: utils.c:39
int sql_reset(sql_stmt_t *stmt)
Reset a prepared statement.
Definition: sql_sqlite3.c:805
const char * sql_regexp_op()
Get regular expression operator.
Definition: sql_sqlite3.c:150
int iterator_column_count(iterator_t *iterator)
Get number of columns from an iterator.
Definition: sql_sqlite3.c:623
const char * sql_column_text(sql_stmt_t *stmt, int position)
Return a column as text from a prepared statement.
Definition: sql_sqlite3.c:857
int sqlv(int, char *, va_list)
Perform an SQL statement.
Definition: sql.c:158
int sql_bind_text(sql_stmt_t *stmt, int position, const gchar *value, gsize value_size)
Bind a text value to a statement.
Definition: sql_sqlite3.c:754
gchar * sql
SQL statement.
Definition: sql_pg.c:58
long long int sql_column_int64(sql_stmt_t *stmt, int position)
Return a column as an int64 from a prepared statement.
Definition: sql_sqlite3.c:885
const char * sql_default_database()
Return name of default database file.
Definition: sql_sqlite3.c:184
void sqli(resource_t *resource, char *sql,...)
Perform an SQL statement, retrying if database is busy or locked.
Definition: sql_sqlite3.c:327
int iterator_null(iterator_t *iterator, int col)
Get whether a column is NULL.
Definition: sql_sqlite3.c:594
int sql_bind_blob(sql_stmt_t *stmt, int position, const void *value, int value_size)
Bind a blob to a statement.
Definition: sql_sqlite3.c:643
void sql_finalize(sql_stmt_t *stmt)
Free a prepared statement.
Definition: sql_sqlite3.c:790
double sql_column_double(sql_stmt_t *stmt, int position)
Return a column as a double from a prepared statement.
Definition: sql_sqlite3.c:843
int sql_is_sqlite3()
Get whether backend is SQLite3.
Definition: sql_sqlite3.c:84
long long int resource_t
A resource, like a task or target.
Definition: iterator.h:42