OpenVAS Manager  7.0.3~git
sql_pg.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 
28 #include <assert.h>
29 #include <endian.h>
30 #include <errno.h>
31 #include <arpa/inet.h>
32 #include <glib.h>
33 #include <inttypes.h>
34 #include <netinet/in.h>
35 #include <postgresql/libpq-fe.h>
36 #include <stdlib.h>
37 #include <string.h>
38 
39 #include <openvas/base/array.h>
40 
41 #undef G_LOG_DOMAIN
42 
45 #define G_LOG_DOMAIN "md manage"
46 
47 
48 /* Headers of sql.c symbols used only here. */
49 
50 int
51 sql_x (char*, va_list args, sql_stmt_t**);
52 
53 
54 /* Types. */
55 
56 struct sql_stmt
57 {
58  gchar *sql;
59  PGresult *result;
61  int executed;
62  array_t *param_values;
63  GArray *param_lengths;
64  GArray *param_formats;
65 };
66 
67 
68 /* Variables. */
69 
76 extern int log_errors;
77 
81 PGconn *conn = NULL;
82 
83 
84 /* Helpers. */
85 
91 int
93 {
94  return 0;
95 }
96 
102 const char *
104 {
105  return "public";
106 }
107 
113 const char *
115 {
116  return "greatest";
117 }
118 
126 const char *
128 {
129  static char string[20];
130  if (max < 0)
131  return "ALL";
132  if (snprintf (string, 19, "%i", max) < 0)
133  {
134  g_warning ("%s: snprintf failed\n", __FUNCTION__);
135  abort ();
136  }
137  string[19] = '\0';
138  return string;
139 }
140 
149 static void
150 sql_stmt_param_add (sql_stmt_t *stmt, const char *param_value,
151  int param_size, int param_format)
152 {
153  array_add (stmt->param_values, g_strndup (param_value, param_size));
154  g_array_append_val (stmt->param_lengths, param_size);
155  g_array_append_val (stmt->param_formats, param_format);
156 }
157 
163 static void
164 sql_stmt_init (sql_stmt_t *stmt)
165 {
166  memset (stmt, 0, sizeof (*stmt));
167  stmt->param_values = make_array ();
168  stmt->param_lengths = g_array_new (FALSE, TRUE, sizeof (int));
169  stmt->param_formats = g_array_new (FALSE, TRUE, sizeof (int));
170  stmt->current_row = -1;
171 }
172 
178 const char *
180 {
181  return "ILIKE";
182 }
183 
189 const char *
191 {
192  return "?~#";
193 }
194 
200 int
202 {
203  return conn ? 1 : 0;
204 }
205 
214 static void
215 log_notice (void *arg, const char *message)
216 {
217  g_debug ("%s", message);
218 }
219 
225 const char *
227 {
228  return PQdb (conn);
229 }
230 
236 const char *
238 {
239  return "tasks";
240 }
241 
249 int
250 sql_open (const char *database)
251 {
252  gchar *conn_info;
253  PostgresPollingStatusType poll_status;
254  int socket;
255 
256  conn_info = g_strdup_printf ("dbname='%s' application_name='%s'",
257  database
258  ? database
260  "gvmd");
261  conn = PQconnectStart (conn_info);
262  g_free (conn_info);
263  if (conn == NULL)
264  {
265  g_warning ("%s: PQconnectStart failed to allocate conn\n",
266  __FUNCTION__);
267  return -1;
268  }
269  if (PQstatus (conn) == CONNECTION_BAD)
270  {
271  g_warning ("%s: PQconnectStart to '%s' failed: %s\n",
272  __FUNCTION__,
273  database ? database : sql_default_database (),
274  PQerrorMessage (conn));
275  goto fail;
276  }
277 
278  socket = PQsocket (conn);
279  if (socket == 0)
280  {
281  g_warning ("%s: PQsocket 0\n", __FUNCTION__);
282  goto fail;
283  }
284 
285  poll_status = PGRES_POLLING_WRITING;
286 
287  g_debug ("%s: polling\n", __FUNCTION__);
288 
289  while (1)
290  {
291  if (poll_status == PGRES_POLLING_READING)
292  {
293  fd_set readfds, writefds;
294  int ret;
295 
296  FD_ZERO (&readfds);
297  FD_ZERO (&writefds);
298  FD_SET (socket, &readfds);
299  ret = select (socket + 1, &readfds, &writefds, NULL, NULL);
300  if (ret == 0)
301  continue;
302  if (ret < 0)
303  {
304  g_warning ("%s: write select failed: %s\n",
305  __FUNCTION__, strerror (errno));
306  goto fail;
307  }
308  /* Poll again. */
309  }
310  else if (poll_status == PGRES_POLLING_WRITING)
311  {
312  fd_set readfds, writefds;
313  int ret;
314 
315  FD_ZERO (&readfds);
316  FD_ZERO (&writefds);
317  FD_SET (socket, &writefds);
318  ret = select (socket + 1, &readfds, &writefds, NULL, NULL);
319  if (ret == 0)
320  continue;
321  if (ret < 0)
322  {
323  g_warning ("%s: read select failed: %s\n",
324  __FUNCTION__, strerror (errno));
325  goto fail;
326  }
327  /* Poll again. */
328  }
329  else if (poll_status == PGRES_POLLING_FAILED)
330  {
331  g_warning ("%s: PQconnectPoll failed\n",
332  __FUNCTION__);
333  goto fail;
334  }
335  else if (poll_status == PGRES_POLLING_OK)
336  /* Connection is ready, exit loop. */
337  break;
338 
339  poll_status = PQconnectPoll (conn);
340  }
341 
342  PQsetNoticeProcessor (conn, log_notice, NULL);
343 
344  g_debug ("%s: db: %s\n", __FUNCTION__, PQdb (conn));
345  g_debug ("%s: user: %s\n", __FUNCTION__, PQuser (conn));
346  g_debug ("%s: host: %s\n", __FUNCTION__, PQhost (conn));
347  g_debug ("%s: port: %s\n", __FUNCTION__, PQport (conn));
348  g_debug ("%s: socket: %i\n", __FUNCTION__, PQsocket (conn));
349  g_debug ("%s: postgres version: %i\n", __FUNCTION__, PQserverVersion (conn));
350 
351  return 0;
352 
353  fail:
354  PQfinish (conn);
355  conn = NULL;
356  return -1;
357 }
358 
362 void
364 {
365  PQfinish (conn);
366  conn = NULL;
367 }
368 
372 void
374 {
375  // FIX PQfinish?
376  conn = NULL;
377 }
378 
384 int
386 {
387  /* TODO PQcmdTuples needs a PQresult. Callers use for info only anyway. */
388  return 0;
389 }
390 
396 {
397  return sql_int ("SELECT LASTVAL ();");
398 }
399 
407 void
408 sqli (resource_t *resource, char* sql, ...)
409 {
410  gchar *new_sql;
411  sql_stmt_t* stmt;
412  int sql_x_ret;
413  va_list args;
414 
415  assert (sql && strlen (sql) && (sql[strlen (sql) - 1] != ';'));
416 
417  /* Append RETURNING clause to SQL. */
418 
419  new_sql = g_strdup_printf ("%s RETURNING id;", sql);
420 
421  /* Run statement, returning integer. */
422 
423  va_start (args, sql);
424  sql_x_ret = sql_x (new_sql, args, &stmt);
425  va_end (args);
426  g_free (new_sql);
427  switch (sql_x_ret)
428  {
429  case 0:
430  break;
431  case 1:
432  sql_finalize (stmt);
433  abort ();
434  default:
435  assert (0);
436  /* Fall through. */
437  case -1:
438  sql_finalize (stmt);
439  abort ();
440  }
441  if (resource)
442  *resource = sql_column_int64 (stmt, 0);
443  sql_finalize (stmt);
444 }
445 
457 int
458 sql_prepare_internal (int retry, int log, const char* sql, va_list args,
459  sql_stmt_t **stmt)
460 {
461  assert (stmt);
462 
463  *stmt = (sql_stmt_t*) g_malloc (sizeof (sql_stmt_t));
464  sql_stmt_init (*stmt);
465  (*stmt)->sql = g_strdup_vprintf (sql, args);
466 
467  if (log)
468  g_debug (" sql: %s\n", (*stmt)->sql);
469 
470  return 0;
471 }
472 
481 int
482 sql_exec_internal (int retry, sql_stmt_t *stmt)
483 {
484  PGresult *result;
485 
486  assert (stmt->sql);
487 
488  if (stmt->executed == 0)
489  {
490  // FIX retry?
491 
492  result = PQexecParams (conn,
493  stmt->sql,
494  stmt->param_values->len,
495  NULL, /* Default param types. */
496  (const char* const*) stmt->param_values->pdata,
497  (const int*) stmt->param_lengths->data,
498  (const int*) stmt->param_formats->data,
499  0); /* Results as text. */
500  if (PQresultStatus (result) != PGRES_TUPLES_OK
501  && PQresultStatus (result) != PGRES_COMMAND_OK)
502  {
503  char *sqlstate;
504 
505  sqlstate = PQresultErrorField (result, PG_DIAG_SQLSTATE);
506  g_debug ("%s: sqlstate: %s\n", __FUNCTION__, sqlstate);
507  if (sqlstate && (strcmp (sqlstate, "57014") == 0)) /* query_canceled */
508  {
509  log_errors = 0;
510  g_debug ("%s: canceled SQL: %s\n", __FUNCTION__, stmt->sql);
511  }
512 
513  if (log_errors)
514  {
515  g_warning ("%s: PQexec failed: %s (%i)\n",
516  __FUNCTION__,
517  PQresultErrorMessage (result),
518  PQresultStatus (result));
519  g_warning ("%s: SQL: %s\n", __FUNCTION__, stmt->sql);
520  }
521 #if 0
522  // FIX ?
523  PQclear (result);
524  PQfinish (conn);
525 #endif
526  return -1;
527  }
528 
529  stmt->result = result;
530  stmt->executed = 1;
531  }
532 
533  if (stmt->current_row < (PQntuples (stmt->result) - 1))
534  {
535  stmt->current_row++;
536  return 1;
537  }
538 
539  return 0;
540 }
541 
550 int
551 sql_explain_internal (const char* sql, va_list args)
552 {
553  char *explain_sql;
554  sql_stmt_t *explain_stmt;
555  int explain_ret;
556 
557  explain_sql = g_strconcat ("EXPLAIN ", sql, NULL);
558  if (sql_prepare_internal (1, 1, explain_sql, args, &explain_stmt))
559  {
560  if (log_errors)
561  g_warning ("%s : Failed to prepare EXPLAIN statement", __FUNCTION__);
562  g_free (explain_sql);
563  return -1;
564  }
565 
566  while (1)
567  {
568  explain_ret = sql_exec_internal (1, explain_stmt);
569  if (explain_ret == 1)
570  g_debug ("%s : %s",
571  __FUNCTION__,
572  PQgetvalue (explain_stmt->result,
573  explain_stmt->current_row,
574  0));
575  else if (explain_ret == 0)
576  break;
577  else
578  {
579  g_warning ("%s : Failed to get EXPLAIN row", __FUNCTION__);
580  sql_finalize (explain_stmt);
581  g_free (explain_sql);
582  return -1;
583  }
584  }
585 
586  sql_finalize (explain_stmt);
587  g_free (explain_sql);
588  return 0;
589 }
590 
591 
592 /* Transactions. */
593 
597 void
599 {
600  sql ("BEGIN;");
601  sql ("SELECT pg_advisory_xact_lock (1);");
602 }
603 
609 int
611 {
612  int ret;
613 
614  ret = sql_giveup ("BEGIN;");
615  if (ret)
616  return ret;
617  if (sql_int ("SELECT pg_try_advisory_xact_lock (1);"))
618  return 0;
619  sql_rollback ();
620  return 1;
621 }
622 
626 void
628 {
629  /* TODO This is just an exclusive lock. */
631 }
632 
638 int
640 {
641  /* TODO This is just an exclusive lock. */
642  return sql_begin_exclusive_giveup ();
643 }
644 
648 void
650 {
651  sql ("COMMIT;");
652 }
653 
657 void
659 {
660  sql ("ROLLBACK;");
661 }
662 
663 
664 /* Iterators. */
665 
674 int
676 {
677  if (iterator->done) abort ();
678  assert (iterator->stmt->result);
679  return PQgetisnull (iterator->stmt->result, 0, col);
680 }
681 
690 const char*
692 {
693  if (iterator->done) abort ();
694  assert (iterator->stmt->result);
695  return PQfname (iterator->stmt->result, col);
696 }
697 
705 int
707 {
708  if (iterator->done) abort ();
709  assert (iterator->stmt->result);
710  return PQnfields (iterator->stmt->result);
711 }
712 
713 
714 /* Prepared statements. */
715 
725 static void
726 bind_param (sql_stmt_t *stmt, int position, const void *param_value,
727  int param_size, int param_format)
728 {
729  if (position > stmt->param_values->len + 1)
730  {
731  g_critical ("%s: binding out of order: parameter %i after %i",
732  __FUNCTION__,
733  position,
734  stmt->param_values->len);
735  abort ();
736  }
737  sql_stmt_param_add (stmt, param_value, param_size, param_format);
738 }
739 
750 int
751 sql_bind_blob (sql_stmt_t *stmt, int position, const void *value,
752  int value_size)
753 {
754  bind_param (stmt, position, value, value_size, 1);
755  return 0;
756 }
757 
767 int
768 sql_bind_int64 (sql_stmt_t *stmt, int position, long long int *value)
769 {
770  int actual;
771  /* Caller is really binding an int4, because IDs in Postgres are int4s. */
772  actual = *value;
773  bind_param (stmt, position, &actual, sizeof (actual), 1);
774  return 0;
775 }
776 
786 int
787 sql_bind_double (sql_stmt_t *stmt, int position, double *value)
788 {
789  gchar *string;
790  string = g_strdup_printf ("%f", *value);
791  bind_param (stmt, position, string, strlen (string), 0);
792  g_free (string);
793  return 0;
794 }
795 
806 int
807 sql_bind_text (sql_stmt_t *stmt, int position, const gchar *value,
808  gsize value_size)
809 {
810  bind_param (stmt,
811  position,
812  value,
813  value_size == -1 ? strlen (value) : value_size,
814  0);
815  return 0;
816 }
817 
823 void
825 {
826  PQclear (stmt->result);
827  g_free (stmt->sql);
828  array_free (stmt->param_values);
829  g_array_free (stmt->param_lengths, TRUE);
830  g_array_free (stmt->param_formats, TRUE);
831  g_free (stmt);
832 }
833 
841 int
843 {
844  gchar *sql;
845 
846  PQclear (stmt->result);
847  array_free (stmt->param_values);
848  g_array_free (stmt->param_lengths, TRUE);
849  g_array_free (stmt->param_formats, TRUE);
850 
851  sql = stmt->sql;
852  sql_stmt_init (stmt);
853  stmt->sql = sql;
854  return 0;
855 }
856 
867 double
868 sql_column_double (sql_stmt_t *stmt, int position)
869 {
870  if (PQgetisnull (stmt->result, stmt->current_row, position))
871  return 0.0;
872 
873  return atof (PQgetvalue (stmt->result, stmt->current_row, position));
874 }
875 
886 const char *
887 sql_column_text (sql_stmt_t *stmt, int position)
888 {
889  if (PQgetisnull (stmt->result, stmt->current_row, position))
890  return NULL;
891 
892  return (const char*) PQgetvalue (stmt->result, stmt->current_row, position);
893 }
894 
905 int
906 sql_column_int (sql_stmt_t *stmt, int position)
907 {
908  char *cell;
909 
910  if (PQgetisnull (stmt->result, stmt->current_row, position))
911  return 0;
912 
913  cell = PQgetvalue (stmt->result, stmt->current_row, position);
914 
915  switch (PQftype (stmt->result, position))
916  {
917  case 16: /* BOOLOID */
918  return strcmp (cell, "f") ? 1 : 0;
919 
920  default:
921  return atoi (cell);
922  }
923 }
924 
935 long long int
936 sql_column_int64 (sql_stmt_t *stmt, int position)
937 {
938  char *cell;
939 
940  if (PQgetisnull (stmt->result, stmt->current_row, position))
941  return 0;
942 
943  cell = PQgetvalue (stmt->result, stmt->current_row, position);
944 
945  switch (PQftype (stmt->result, position))
946  {
947  case 16: /* BOOLOID */
948  return strcmp (cell, "f") ? 1 : 0;
949 
950  default:
951  return atol (cell);
952  }
953 }
954 
960 int
962 {
963  PGcancel *cancel;
964  char errbuf[256] = "";
965 
966  cancel = PQgetCancel (conn);
967  if (cancel)
968  {
969  if (PQcancel (cancel, errbuf, 256))
970  {
971  log_errors = 0;
972  PQfreeCancel (cancel);
973  }
974  else
975  {
976  PQfreeCancel (cancel);
977  return -1;
978  }
979  }
980  else
981  {
982  return -1;
983  }
984 
985  return 0;
986 }
int executed
Whether statement has been executed.
Definition: sql_pg.c:61
const char * sql_schema()
Get main schema name.
Definition: sql_pg.c:103
const char * sql_select_limit(int max)
Setup a LIMIT argument.
Definition: sql_pg.c:127
sqlite3_stmt * stmt
Definition: sql_sqlite3.c:64
array_t * param_values
Parameter values.
Definition: sql_pg.c:62
int sql_int(char *sql,...)
Get a particular cell from a SQL query, as an int.
Definition: sql.c:438
int sql_reset(sql_stmt_t *stmt)
Reset a prepared statement.
Definition: sql_pg.c:842
int sql_bind_int64(sql_stmt_t *stmt, int position, long long int *value)
Bind an int64 value to a statement.
Definition: sql_pg.c:768
int sql_begin_exclusive_giveup()
Begin an exclusive transaction, giving up on failure.
Definition: sql_pg.c:610
int sql_is_open()
Check whether the database is open.
Definition: sql_pg.c:201
void sql_begin_exclusive()
Begin an exclusive transaction.
Definition: sql_pg.c:598
int sql_prepare_internal(int retry, int log, const char *sql, va_list args, sql_stmt_t **stmt)
Prepare a statement.
Definition: sql_pg.c:458
int sql_giveup(char *sql,...)
Perform an SQL statement, giving up if database is busy or locked.
Definition: sql.c:257
A generic SQL iterator.
Definition: iterator.h:52
void sql_commit()
Commit a transaction.
Definition: sql_pg.c:649
gboolean done
End flag.
Definition: iterator.h:55
int sql_bind_blob(sql_stmt_t *stmt, int position, const void *value, int value_size)
Bind a blob to a statement.
Definition: sql_pg.c:751
const char * sql_default_database()
Return name of default database.
Definition: sql_pg.c:237
void sql_close_fork()
Close the database in a forked process.
Definition: sql_pg.c:373
int sql_open(const char *database)
Open the database.
Definition: sql_pg.c:250
int sql_begin_immediate_giveup()
Begin an immediate transaction.
Definition: sql_pg.c:639
int log_errors
Whether to log errors.
Definition: sql.c:62
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_pg.c:551
void sql_finalize(sql_stmt_t *stmt)
Free a prepared statement.
Definition: sql_pg.c:824
sql_stmt_t * stmt
SQL statement.
Definition: iterator.h:54
const char * sql_greatest()
Get keyword for "greatest" SQL function.
Definition: sql_pg.c:114
int sql_cancel_internal()
Cancels the current SQL statement.
Definition: sql_pg.c:961
int sql_is_sqlite3()
Get whether backend is SQLite3.
Definition: sql_pg.c:92
const char * sql_regexp_op()
Get regular expression operator.
Definition: sql_pg.c:190
long long int sql_column_int64(sql_stmt_t *stmt, int position)
Return a column as an int64 from a prepared statement.
Definition: sql_pg.c:936
int sql_changes()
Return 0.
Definition: sql_pg.c:385
void sql_close()
Close the database.
Definition: sql_pg.c:363
int sql_x(char *, va_list args, sql_stmt_t **)
Get a particular cell from a SQL query.
Definition: sql.c:385
const char * sql_column_text(sql_stmt_t *stmt, int position)
Return a column as text from a prepared statement.
Definition: sql_pg.c:887
void sql_begin_immediate()
Begin an immediate transaction.
Definition: sql_pg.c:627
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_pg.c:807
const char * sql_database()
Return name of current database.
Definition: sql_pg.c:226
GArray * param_lengths
Parameter lengths (int&#39;s).
Definition: sql_pg.c:63
int iterator_null(iterator_t *iterator, int col)
Get whether a column is NULL.
Definition: sql_pg.c:675
int sql_exec_internal(int retry, sql_stmt_t *stmt)
Execute a prepared statement.
Definition: sql_pg.c:482
int sql_bind_double(sql_stmt_t *stmt, int position, double *value)
Bind a double value to a statement.
Definition: sql_pg.c:787
double sql_column_double(sql_stmt_t *stmt, int position)
Return a column as a double from a prepared statement.
Definition: sql_pg.c:868
int sql_column_int(sql_stmt_t *stmt, int position)
Return a column as an integer from a prepared statement.
Definition: sql_pg.c:906
gchar * sql
SQL statement.
Definition: sql_pg.c:58
int current_row
Row position in results.
Definition: sql_pg.c:60
int iterator_column_count(iterator_t *iterator)
Get number of columns from an iterator.
Definition: sql_pg.c:706
GArray * param_formats
Parameter formats (int&#39;s).
Definition: sql_pg.c:64
PGconn * conn
Handle on the database.
Definition: sql_pg.c:81
PGresult * result
Result set.
Definition: sql_pg.c:59
void sqli(resource_t *resource, char *sql,...)
Perform an SQL statement, retrying if database is busy or locked.
Definition: sql_pg.c:408
void sql_rollback()
Roll a transaction back.
Definition: sql_pg.c:658
const char * iterator_column_name(iterator_t *iterator, int col)
Get a column name from an iterator.
Definition: sql_pg.c:691
resource_t sql_last_insert_id()
Get the ID of the last inserted row.
Definition: sql_pg.c:395
const char * sql_ilike_op()
Get case insensitive LIKE operator.
Definition: sql_pg.c:179
long long int resource_t
A resource, like a task or target.
Definition: iterator.h:42