1 /* Copyright (C) 2014-2021 Greenbone Networks GmbH
2 *
3 * SPDX-License-Identifier: AGPL-3.0-or-later
4 *
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU Affero General Public License as
7 * published by the Free Software Foundation, either version 3 of the
8 * License, or (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU Affero General Public License for more details.
14 *
15 * You should have received a copy of the GNU Affero General Public License
16 * along with this program. If not, see <http://www.gnu.org/licenses/>.
17 */
18
19 /**
20 * @file sql_pg.c
21 * @brief Generic SQL interface: PostgreSQL backend
22 *
23 * PostreSQL backend of the SQL interface.
24 */
25
26 #include "sql.h"
27
28 #include <assert.h>
29 #include <sys/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 <libpq-fe.h>
36 #include <stdlib.h>
37 #include <string.h>
38
39 #include <gvm/base/array.h>
40
41 #undef G_LOG_DOMAIN
42 /**
43 * @brief GLib log domain.
44 */
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 /**
57 * @brief An SQL statement.
58 */
59 struct sql_stmt
60 {
61 gchar *sql; ///< SQL statement.
62 PGresult *result; ///< Result set.
63 int current_row; ///< Row position in results.
64 int executed; ///< Whether statement has been executed.
65 array_t *param_values; ///< Parameter values.
66 GArray *param_lengths; ///< Parameter lengths (int's).
67 GArray *param_formats; ///< Parameter formats (int's).
68 };
69
70
71 /* Variables. */
72
73 /**
74 * @brief Whether to log errors.
75 *
76 * Used to turn off logging when cancelling statements on exit. Defined
77 * in sql.c.
78 */
79 extern int log_errors;
80
81 /**
82 * @brief Handle on the database.
83 */
84 static PGconn *conn = NULL;
85
86
87 /* Helpers. */
88
89 /**
90 * @brief Get main schema name.
91 *
92 * @return Schema name.
93 */
94 const char *
sql_schema()95 sql_schema ()
96 {
97 return "public";
98 }
99
100 /**
101 * @brief Get keyword for "greatest" SQL function.
102 *
103 * @return Keyword.
104 */
105 const char *
sql_greatest()106 sql_greatest ()
107 {
108 return "greatest";
109 }
110
111 /**
112 * @brief Setup a LIMIT argument.
113 *
114 * @param[in] max Max.
115 *
116 * @return Argument for LIMIT as a static string.
117 */
118 const char *
sql_select_limit(int max)119 sql_select_limit (int max)
120 {
121 static char string[20];
122 if (max < 0)
123 return "ALL";
124 if (snprintf (string, 19, "%i", max) < 0)
125 {
126 g_warning ("%s: snprintf failed", __func__);
127 abort ();
128 }
129 string[19] = '\0';
130 return string;
131 }
132
133 /**
134 * @brief Init statement, preserving SQL.
135 *
136 * @param[in] stmt Statement.
137 */
138 static void
sql_stmt_init(sql_stmt_t * stmt)139 sql_stmt_init (sql_stmt_t *stmt)
140 {
141 memset (stmt, 0, sizeof (*stmt));
142 stmt->param_values = make_array ();
143 stmt->param_lengths = g_array_new (FALSE, TRUE, sizeof (int));
144 stmt->param_formats = g_array_new (FALSE, TRUE, sizeof (int));
145 stmt->current_row = -1;
146 }
147
148 /**
149 * @brief Get case insensitive LIKE operator.
150 *
151 * @return Like operator.
152 */
153 const char *
sql_ilike_op()154 sql_ilike_op ()
155 {
156 return "ILIKE";
157 }
158
159 /**
160 * @brief Get regular expression operator.
161 *
162 * @return Regexp operator.
163 */
164 const char *
sql_regexp_op()165 sql_regexp_op ()
166 {
167 return "?~#";
168 }
169
170 /**
171 * @brief Check whether the database is open.
172 *
173 * @return 1 if open, else 0.
174 */
175 int
sql_is_open()176 sql_is_open ()
177 {
178 return conn ? 1 : 0;
179 }
180
181 #ifndef NDEBUG
182 #include <execinfo.h>
183
184 /**
185 * @brief Maximum number of frames in backtrace.
186 *
187 * For debugging backtrace in \ref log_notice.
188 */
189 #define BA_SIZE 100
190 #endif
191
192 /**
193 * @brief Log a NOTICE message.
194 *
195 * @param[in] arg Dummy arg.
196 * @param[in] result Arg.
197 */
198 static void
log_notice(void * arg,const PGresult * result)199 log_notice (void *arg, const PGresult *result)
200 {
201 g_debug ("PQ notice: %s", PQresultErrorMessage (result));
202
203 #ifndef NDEBUG
204 char *verbose;
205
206 verbose = PQresultVerboseErrorMessage (result, PQERRORS_VERBOSE, PQSHOW_CONTEXT_ALWAYS);
207 g_debug ("PQ notice: verbose: %s", verbose);
208 PQfreemem (verbose);
209 #endif
210
211 g_debug ("PQ notice: detail: %s",
212 PQresultErrorField (result, PG_DIAG_MESSAGE_DETAIL));
213 g_debug ("PQ notice: hint: %s",
214 PQresultErrorField (result, PG_DIAG_MESSAGE_HINT));
215 g_debug ("PQ notice: table %s.%s",
216 PQresultErrorField (result, PG_DIAG_SCHEMA_NAME),
217 PQresultErrorField (result, PG_DIAG_TABLE_NAME));
218 g_debug ("PQ notice: from %s in %s:%s",
219 PQresultErrorField (result, PG_DIAG_SOURCE_FUNCTION),
220 PQresultErrorField (result, PG_DIAG_SOURCE_FILE),
221 PQresultErrorField (result, PG_DIAG_SOURCE_LINE));
222 g_debug ("PQ notice: context:\n%s",
223 PQresultErrorField (result, PG_DIAG_CONTEXT));
224
225 #ifndef NDEBUG
226 void *frames[BA_SIZE];
227 int frame_count, index;
228 char **frames_text;
229
230 /* Print a backtrace. */
231 frame_count = backtrace (frames, BA_SIZE);
232 frames_text = backtrace_symbols (frames, frame_count);
233 if (frames_text == NULL)
234 {
235 perror ("backtrace symbols");
236 frame_count = 0;
237 }
238 for (index = 0; index < frame_count; index++)
239 g_debug ("%s", frames_text[index]);
240 free (frames_text);
241 #endif
242 }
243
244 /**
245 * @brief Return name of current database.
246 *
247 * @return Name of database.
248 */
249 const char *
sql_database()250 sql_database ()
251 {
252 return PQdb (conn);
253 }
254
255 /**
256 * @brief Return name of default database.
257 *
258 * @return Name.
259 */
260 const char *
sql_default_database()261 sql_default_database ()
262 {
263 return "gvmd";
264 }
265
266 /**
267 * @brief Open the database.
268 *
269 * @param[in] database Database, or NULL for default.
270 *
271 * @return 0 success, -1 error.
272 */
273 int
sql_open(const db_conn_info_t * database)274 sql_open (const db_conn_info_t *database)
275 {
276 gchar *conn_info;
277 PostgresPollingStatusType poll_status;
278 int socket;
279
280 conn_info = g_strdup_printf ("dbname='%s'"
281 " host='%s'"
282 " port='%s'"
283 " user='%s'"
284 " application_name='%s'",
285 database->name
286 ? database->name
287 : sql_default_database (),
288 database->host ? database->host : "",
289 database->port ? database->port : "",
290 database->user ? database->user : "",
291 "gvmd");
292 conn = PQconnectStart (conn_info);
293 g_free (conn_info);
294 if (conn == NULL)
295 {
296 g_warning ("%s: PQconnectStart failed to allocate conn",
297 __func__);
298 return -1;
299 }
300 if (PQstatus (conn) == CONNECTION_BAD)
301 {
302 g_warning ("%s: PQconnectStart to '%s' failed: %s",
303 __func__,
304 database->name ? database->name : sql_default_database (),
305 PQerrorMessage (conn));
306 goto fail;
307 }
308
309 socket = PQsocket (conn);
310 if (socket == 0)
311 {
312 g_warning ("%s: PQsocket 0", __func__);
313 goto fail;
314 }
315
316 poll_status = PGRES_POLLING_WRITING;
317
318 g_debug ("%s: polling", __func__);
319
320 while (1)
321 {
322 if (poll_status == PGRES_POLLING_READING)
323 {
324 fd_set readfds, writefds;
325 int ret;
326
327 FD_ZERO (&readfds);
328 FD_ZERO (&writefds);
329 FD_SET (socket, &readfds);
330 ret = select (socket + 1, &readfds, &writefds, NULL, NULL);
331 if (ret == 0)
332 continue;
333 if (ret < 0)
334 {
335 g_warning ("%s: write select failed: %s",
336 __func__, strerror (errno));
337 goto fail;
338 }
339 /* Poll again. */
340 }
341 else if (poll_status == PGRES_POLLING_WRITING)
342 {
343 fd_set readfds, writefds;
344 int ret;
345
346 FD_ZERO (&readfds);
347 FD_ZERO (&writefds);
348 FD_SET (socket, &writefds);
349 ret = select (socket + 1, &readfds, &writefds, NULL, NULL);
350 if (ret == 0)
351 continue;
352 if (ret < 0)
353 {
354 g_warning ("%s: read select failed: %s",
355 __func__, strerror (errno));
356 goto fail;
357 }
358 /* Poll again. */
359 }
360 else if (poll_status == PGRES_POLLING_FAILED)
361 {
362 g_warning ("%s: PQconnectPoll failed",
363 __func__);
364 g_warning ("%s: PQerrorMessage (conn): %s", __func__,
365 PQerrorMessage (conn));
366 goto fail;
367 }
368 else if (poll_status == PGRES_POLLING_OK)
369 /* Connection is ready, exit loop. */
370 break;
371
372 poll_status = PQconnectPoll (conn);
373 }
374
375 PQsetNoticeReceiver (conn, log_notice, NULL);
376
377 g_debug ("%s: db: %s", __func__, PQdb (conn));
378 g_debug ("%s: user: %s", __func__, PQuser (conn));
379 g_debug ("%s: host: %s", __func__, PQhost (conn));
380 g_debug ("%s: port: %s", __func__, PQport (conn));
381 g_debug ("%s: socket: %i", __func__, PQsocket (conn));
382 g_debug ("%s: postgres version: %i", __func__, PQserverVersion (conn));
383
384 if (PQserverVersion (conn) < 90600)
385 {
386 g_warning ("%s: PostgreSQL version 9.6 (90600) or higher is required",
387 __func__);
388 g_warning ("%s: Current version is %i", __func__,
389 PQserverVersion (conn));
390 goto fail;
391 }
392
393 return 0;
394
395 fail:
396 PQfinish (conn);
397 conn = NULL;
398 return -1;
399 }
400
401 /**
402 * @brief Close the database.
403 */
404 void
sql_close()405 sql_close ()
406 {
407 PQfinish (conn);
408 conn = NULL;
409 }
410
411 /**
412 * @brief Close the database in a forked process.
413 */
414 void
sql_close_fork()415 sql_close_fork ()
416 {
417 conn = NULL;
418 }
419
420 /**
421 * @brief Return 0.
422 *
423 * @return 0.
424 */
425 int
sql_changes()426 sql_changes ()
427 {
428 /* TODO PQcmdTuples needs a PQresult. Callers use for info only anyway. */
429 return 0;
430 }
431
432 /**
433 * @brief Get the ID of the last inserted row.
434 *
435 * @return Resource.
436 */
437 resource_t
sql_last_insert_id()438 sql_last_insert_id ()
439 {
440 return sql_int ("SELECT LASTVAL ();");
441 }
442
443 /**
444 * @brief Prepare a statement.
445 *
446 * @param[in] retry Whether to keep retrying while database is busy or locked.
447 * @param[in] log Whether to keep retrying while database is busy or locked.
448 * @param[in] sql Format string for SQL statement.
449 * @param[in] args Arguments for format string.
450 * @param[out] stmt Statement return.
451 *
452 * @return 0 success, 1 gave up, -1 error.
453 */
454 int
sql_prepare_internal(int retry,int log,const char * sql,va_list args,sql_stmt_t ** stmt)455 sql_prepare_internal (int retry, int log, const char* sql, va_list args,
456 sql_stmt_t **stmt)
457 {
458 assert (stmt);
459
460 *stmt = (sql_stmt_t*) g_malloc (sizeof (sql_stmt_t));
461 sql_stmt_init (*stmt);
462 (*stmt)->sql = g_strdup_vprintf (sql, args);
463
464 if (log)
465 g_debug (" sql: %s", (*stmt)->sql);
466
467 return 0;
468 }
469
470 /**
471 * @brief Execute a statement.
472 *
473 * @param[in] retry Whether to keep retrying while database is busy or locked.
474 * @param[in] stmt Statement.
475 *
476 * @return 0 complete, 1 row available in results, -1 error, -2 gave up,
477 * -3 lock unavailable, -4 unique constraint violation.
478 */
479 int
sql_exec_internal(int retry,sql_stmt_t * stmt)480 sql_exec_internal (int retry, sql_stmt_t *stmt)
481 {
482 PGresult *result;
483
484 assert (stmt->sql);
485
486 if (stmt->executed == 0)
487 {
488 result = PQexecParams (conn,
489 stmt->sql,
490 stmt->param_values->len,
491 NULL, /* Default param types. */
492 (const char* const*) stmt->param_values->pdata,
493 (const int*) stmt->param_lengths->data,
494 (const int*) stmt->param_formats->data,
495 0); /* Results as text. */
496 if (PQresultStatus (result) != PGRES_TUPLES_OK
497 && PQresultStatus (result) != PGRES_COMMAND_OK)
498 {
499 char *sqlstate;
500
501 sqlstate = PQresultErrorField (result, PG_DIAG_SQLSTATE);
502 g_debug ("%s: sqlstate: %s", __func__, sqlstate);
503 if (sqlstate && (strcmp (sqlstate, "57014") == 0))
504 {
505 /* query_canceled */
506 log_errors = 0;
507 g_debug ("%s: canceled SQL: %s", __func__, stmt->sql);
508 }
509 else if (sqlstate && (strcmp (sqlstate, "55P03") == 0))
510 {
511 /* lock_not_available */
512 g_debug ("%s: lock unavailable: %s",
513 __func__,
514 PQresultErrorMessage(result));
515 return -3;
516 }
517 else if (sqlstate && (strcmp (sqlstate, "23505") == 0))
518 {
519 /* unique_violation */
520 g_warning ("%s: constraint violation: %s",
521 __func__,
522 PQresultErrorMessage (result));
523 g_warning ("%s: SQL: %s", __func__, stmt->sql);
524 return -4;
525 }
526 else if (sqlstate && (strcmp (sqlstate, "40P01") == 0))
527 {
528 /* deadlock_detected */
529 g_debug ("%s: deadlock: %s",
530 __func__,
531 PQresultErrorMessage (result));
532 g_debug ("%s: SQL: %s", __func__, stmt->sql);
533 return -5;
534 }
535 if (log_errors)
536 {
537 g_warning ("%s: PQexec failed: %s (%i)",
538 __func__,
539 PQresultErrorMessage (result),
540 PQresultStatus (result));
541 g_warning ("%s: SQL: %s", __func__, stmt->sql);
542 }
543 return -1;
544 }
545
546 stmt->result = result;
547 stmt->executed = 1;
548 }
549
550 if (stmt->current_row < (PQntuples (stmt->result) - 1))
551 {
552 stmt->current_row++;
553 return 1;
554 }
555
556 return 0;
557 }
558
559
560 /* Transactions. */
561
562 /**
563 * @brief Begin an immediate transaction.
564 */
565 void
sql_begin_immediate()566 sql_begin_immediate ()
567 {
568 sql ("BEGIN;");
569 }
570
571 /**
572 * @brief Begin an immediate transaction.
573 *
574 * @return 0 got lock, 1 gave up, -1 error.
575 */
576 int
sql_begin_immediate_giveup()577 sql_begin_immediate_giveup ()
578 {
579 int ret;
580
581 ret = sql_giveup ("BEGIN;");
582 if (ret)
583 return ret;
584 return 0;
585 }
586
587 /**
588 * @brief Commit a transaction.
589 */
590 void
sql_commit()591 sql_commit ()
592 {
593 sql ("COMMIT;");
594 }
595
596 /**
597 * @brief Roll a transaction back.
598 */
599 void
sql_rollback()600 sql_rollback ()
601 {
602 sql ("ROLLBACK;");
603 }
604
605
606 /* Iterators. */
607
608 /**
609 * @brief Get whether a column is NULL.
610 *
611 * @param[in] iterator Iterator.
612 * @param[in] col Column offset.
613 *
614 * @return 1 if NULL, else 0.
615 */
616 int
iterator_null(iterator_t * iterator,int col)617 iterator_null (iterator_t* iterator, int col)
618 {
619 if (iterator->done) abort ();
620 assert (iterator->stmt->result);
621 return PQgetisnull (iterator->stmt->result, iterator->stmt->current_row, col);
622 }
623
624 /**
625 * @brief Rewind an iterator to the beginning.
626 *
627 * This lets the caller iterate over the data again.
628 *
629 * @param[in] iterator Iterator.
630 */
631 void
iterator_rewind(iterator_t * iterator)632 iterator_rewind (iterator_t* iterator)
633 {
634 iterator->done = FALSE;
635 iterator->stmt->current_row = -1;
636 }
637
638
639 /* Statements. */
640
641 /**
642 * @brief Free a statement.
643 *
644 * @param[in] stmt Statement.
645 */
646 void
sql_finalize(sql_stmt_t * stmt)647 sql_finalize (sql_stmt_t *stmt)
648 {
649 PQclear (stmt->result);
650 g_free (stmt->sql);
651 array_free (stmt->param_values);
652 g_array_free (stmt->param_lengths, TRUE);
653 g_array_free (stmt->param_formats, TRUE);
654 g_free (stmt);
655 }
656
657 /**
658 * @brief Return a column as a double from a statement.
659 *
660 * It's up to the caller to ensure that there is a row available.
661 *
662 * @param[in] stmt Statement.
663 * @param[in] position Column position.
664 *
665 * @return 0 success, -1 error.
666 */
667 double
sql_column_double(sql_stmt_t * stmt,int position)668 sql_column_double (sql_stmt_t *stmt, int position)
669 {
670 if (PQgetisnull (stmt->result, stmt->current_row, position))
671 return 0.0;
672
673 return atof (PQgetvalue (stmt->result, stmt->current_row, position));
674 }
675
676 /**
677 * @brief Return a column as text from a statement.
678 *
679 * It's up to the caller to ensure that there is a row available.
680 *
681 * @param[in] stmt Statement.
682 * @param[in] position Column position.
683 *
684 * @return Column value. NULL if column is NULL.
685 */
686 const char *
sql_column_text(sql_stmt_t * stmt,int position)687 sql_column_text (sql_stmt_t *stmt, int position)
688 {
689 if (PQgetisnull (stmt->result, stmt->current_row, position))
690 return NULL;
691
692 return (const char*) PQgetvalue (stmt->result, stmt->current_row, position);
693 }
694
695 /**
696 * @brief Return a column as an integer from a statement.
697 *
698 * It's up to the caller to ensure that there is a row available.
699 *
700 * @param[in] stmt Statement.
701 * @param[in] position Column position.
702 *
703 * @return Column value. 0 if column is NULL or false. 1 if column true.
704 */
705 int
sql_column_int(sql_stmt_t * stmt,int position)706 sql_column_int (sql_stmt_t *stmt, int position)
707 {
708 char *cell;
709
710 if (PQgetisnull (stmt->result, stmt->current_row, position))
711 return 0;
712
713 cell = PQgetvalue (stmt->result, stmt->current_row, position);
714
715 switch (PQftype (stmt->result, position))
716 {
717 case 16: /* BOOLOID */
718 return strcmp (cell, "f") ? 1 : 0;
719
720 default:
721 return atoi (cell);
722 }
723 }
724
725 /**
726 * @brief Return a column as an int64 from a statement.
727 *
728 * It's up to the caller to ensure that there is a row available.
729 *
730 * @param[in] stmt Statement.
731 * @param[in] position Column position.
732 *
733 * @return Column value. 0 if column is NULL or false. 1 if column true.
734 */
735 long long int
sql_column_int64(sql_stmt_t * stmt,int position)736 sql_column_int64 (sql_stmt_t *stmt, int position)
737 {
738 char *cell;
739
740 if (PQgetisnull (stmt->result, stmt->current_row, position))
741 return 0;
742
743 cell = PQgetvalue (stmt->result, stmt->current_row, position);
744
745 switch (PQftype (stmt->result, position))
746 {
747 case 16: /* BOOLOID */
748 return strcmp (cell, "f") ? 1 : 0;
749
750 default:
751 return atol (cell);
752 }
753 }
754
755 /**
756 * @brief Return a column as text from a statement.
757 *
758 * It's up to the caller to ensure that there is a row available.
759 *
760 * @param[in] stmt Statement.
761 * @param[in] position Column position.
762 *
763 * @return Column value. NULL if column is NULL.
764 */
765 gchar **
sql_column_array(sql_stmt_t * stmt,int position)766 sql_column_array (sql_stmt_t *stmt, int position)
767 {
768 const char *text;
769
770 if (PQgetisnull (stmt->result, stmt->current_row, position))
771 return NULL;
772
773 /* {DFN-CERT-2017-1238,DFN-CERT-2014-1366,DFN-CERT-2014-1354} */
774
775 text = (const char*) PQgetvalue (stmt->result, stmt->current_row, position);
776 if (text && text[0] == '{')
777 {
778 gchar **array, **point, **last;
779
780 if (text[1] == '}')
781 return (gchar **) g_malloc0 (sizeof (gchar *));
782
783 array = g_strsplit (text + 1, ",", 0);
784 point = last = array;
785 while (*point)
786 {
787 last = point;
788 point++;
789 }
790 if (*last)
791 {
792 gchar *last_element;
793
794 last_element = *last;
795 if (*last_element == '\0')
796 /* Weird, last element should always have a }. */
797 g_warning ("%s: last element missing closing }", __func__);
798 else
799 {
800 while (*last_element)
801 last_element++;
802 last_element--;
803 /* Clip the trailing }. */
804 *last_element = '\0';
805 }
806 }
807 return array;
808 }
809
810 /* This shouldn't happen. */
811 assert (0);
812 g_warning ("%s: array column not NULL and does not contain array",
813 __func__);
814 return NULL;
815 }
816
817 /**
818 * @brief Cancels the current SQL statement.
819 *
820 * @return 0 on success, -1 on error.
821 */
822 int
sql_cancel_internal()823 sql_cancel_internal ()
824 {
825 PGcancel *cancel;
826 char errbuf[256] = "";
827
828 cancel = PQgetCancel (conn);
829 if (cancel)
830 {
831 if (PQcancel (cancel, errbuf, 256))
832 {
833 log_errors = 0;
834 PQfreeCancel (cancel);
835 }
836 else
837 {
838 PQfreeCancel (cancel);
839 return -1;
840 }
841 }
842 else
843 {
844 return -1;
845 }
846
847 return 0;
848 }
849