1 /* Copyright (C) 2009-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.c
21 * @brief Generic SQL interface
22 *
23 * This is a small generic interface for SQL database access.
24 *
25 * To add support for a specific database, like Postgres, a few functions
26 * (for example, sql_prepare_internal and sql_exec_internal) need to be
27 * implemented for that database.
28 */
29
30 #include "sql.h"
31
32 #include <assert.h>
33 #include <errno.h>
34 #include <stdlib.h>
35 #include <string.h>
36
37 #undef G_LOG_DOMAIN
38 /**
39 * @brief GLib log domain.
40 */
41 #define G_LOG_DOMAIN "md manage"
42 /**
43 * @brief amount of ms sql should wait before retrying when a deadlock occurred
44 */
45 #define DEADLOCK_SLEEP 1000
46
47 /**
48 * @brief defines the amount of retries after a deadlock is considered a warning
49 */
50 #define DEADLOCK_THRESHOLD 25
51
52
53 /* Headers of internal symbols defined in backend files. */
54
55 int
56 sql_prepare_internal (int, int, const char*, va_list, sql_stmt_t **);
57
58 int
59 sql_exec_internal (int, sql_stmt_t *);
60
61 void
62 sql_finalize (sql_stmt_t *);
63
64 double
65 sql_column_double (sql_stmt_t *, int);
66
67 const char *
68 sql_column_text (sql_stmt_t *, int);
69
70 int
71 sql_column_int (sql_stmt_t *, int);
72
73 long long int
74 sql_column_int64 (sql_stmt_t *, int);
75
76 gchar **
77 sql_column_array (sql_stmt_t *, int);
78
79
80 /* Variables. */
81
82 /**
83 * @brief Whether to log errors.
84 *
85 * Used to turn off logging when cancelling statements on exit.
86 */
87 int log_errors = 1;
88
89
90 /* Helpers. */
91
92 /**
93 * @brief Quotes a string of a known length to be passed to sql statements.
94 *
95 * @param[in] string String to quote.
96 * @param[in] length Size of \p string.
97 *
98 * @return Freshly allocated, quoted string. Free with g_free.
99 */
100 gchar*
sql_nquote(const char * string,size_t length)101 sql_nquote (const char* string, size_t length)
102 {
103 gchar *new, *new_start;
104 const gchar *start, *end;
105 int count = 0;
106
107 assert (string);
108
109 /* Count number of apostrophes. */
110
111 start = string;
112 while ((start = strchr (start, '\''))) start++, count++;
113
114 /* Allocate new string. */
115
116 new = new_start = g_malloc0 (length + count + 1);
117
118 /* Copy string, replacing apostrophes with double apostrophes. */
119
120 start = string;
121 end = string + length;
122 for (; start < end; start++, new++)
123 {
124 char ch = *start;
125 if (ch == '\'')
126 {
127 *new = '\'';
128 new++;
129 *new = '\'';
130 }
131 else
132 *new = ch;
133 }
134
135 return new_start;
136 }
137
138 /**
139 * @brief Quotes a string to be passed to sql statements.
140 *
141 * @param[in] string String to quote, has to be \\0 terminated.
142 *
143 * @return Freshly allocated, quoted string. Free with g_free.
144 */
145 gchar*
sql_quote(const char * string)146 sql_quote (const char* string)
147 {
148 assert (string);
149 return sql_nquote (string, strlen (string));
150 }
151
152 /**
153 * @brief Quotes a string for use in SQL statements, also ASCII escaping it
154 * if it is not valid UTF-8.
155 *
156 * @param[in] string String to quote, has to be \\0 terminated.
157 *
158 * @return Freshly allocated, quoted string. Free with g_free.
159 */
160 gchar*
sql_ascii_escape_and_quote(const char * string)161 sql_ascii_escape_and_quote (const char* string)
162 {
163 gchar *quoted_string;
164
165 assert (string);
166
167 if (string == NULL)
168 {
169 return NULL;
170 }
171 else if (g_utf8_validate (string, -1, NULL))
172 {
173 // Quote valid UTF-8 without ASCII escaping
174 quoted_string = sql_quote (string);
175 }
176 else
177 {
178 // Assume invalid UTF-8 uses a different, unknown encoding and
179 // ASCII-escape it.
180 gchar *escaped_string;
181 escaped_string = g_strescape (string, "");
182 quoted_string = sql_quote (escaped_string);
183 g_free (escaped_string);
184 }
185
186 return quoted_string;
187 }
188
189 /**
190 * @brief Get the SQL insert expression for a string.
191 *
192 * @param[in] string The string, which may be NULL.
193 *
194 * @return Freshly allocated expression suitable for an INSERT statement,
195 * including SQL quotation marks.
196 */
197 gchar *
sql_insert(const char * string)198 sql_insert (const char *string)
199 {
200 if (string)
201 {
202 gchar *quoted_value = sql_quote (string);
203 gchar *insert = g_strdup_printf ("'%s'", quoted_value);
204 g_free (quoted_value);
205 return insert;
206 }
207 return g_strdup ("NULL");
208 }
209
210 /**
211 * @brief Perform an SQL statement.
212 *
213 * @param[in] retry Whether to keep retrying while database is busy or locked.
214 * @param[in] sql Format string for SQL statement.
215 * @param[in] args Arguments for format string.
216 *
217 * @return 0 success, 1 gave up (even when retry given),
218 * 2 reserved (lock unavailable), 3 unique constraint violation,
219 * -1 error.
220 */
221 int
sqlv(int retry,char * sql,va_list args)222 sqlv (int retry, char* sql, va_list args)
223 {
224 while (1)
225 {
226 int ret;
227 sql_stmt_t* stmt;
228 va_list args_copy;
229
230 /* Prepare statement.
231 * Copy args for this because a va_list can only be used once.
232 */
233 va_copy (args_copy, args);
234 ret = sql_prepare_internal (retry, 1, sql, args_copy, &stmt);
235 va_end (args_copy);
236 if (ret == -1)
237 g_warning ("%s: sql_prepare_internal failed", __func__);
238 if (ret)
239 return ret;
240
241 /* Run statement. */
242
243 while ((ret = sql_exec_internal (retry, stmt)) == 1);
244 if ((ret == -1) && log_errors)
245 g_warning ("%s: sql_exec_internal failed", __func__);
246 sql_finalize (stmt);
247 if (ret == 2)
248 continue;
249 if (ret == -2)
250 return 1;
251 if (ret == -3)
252 return -1;
253 if (ret == -4)
254 return 3;
255 if (ret == -5)
256 return 4;
257 assert (ret == -1 || ret == 0);
258 return ret;
259 }
260 }
261
262 /**
263 * @brief Perform an SQL statement, retrying if database is busy or locked.
264 *
265 * @param[in] sql Format string for SQL statement.
266 * @param[in] ... Arguments for format string.
267 */
268 void
sql(char * sql,...)269 sql (char* sql, ...)
270 {
271 unsigned int deadlock_amount = 0;
272 while (1)
273 {
274 va_list args;
275 int ret;
276
277 va_start (args, sql);
278 ret = sqlv (1, sql, args);
279 va_end (args);
280 if (ret == 1)
281 /* Gave up with statement reset. */
282 continue;
283 else if (ret == 4)
284 {
285 if (deadlock_amount++ > DEADLOCK_THRESHOLD)
286 {
287 g_warning("%s: %d deadlocks detected, waiting and retrying %s", __func__, deadlock_amount, sql);
288 }
289 gvm_usleep (DEADLOCK_SLEEP);
290 continue;
291 }
292 else if (ret)
293 abort();
294 break;
295 }
296 }
297
298 /**
299 * @brief Perform an SQL statement, retrying if database is busy or locked.
300 *
301 * Return on error, instead of aborting.
302 *
303 * @param[in] sql Format string for SQL statement.
304 * @param[in] ... Arguments for format string.
305 *
306 * @return 0 success, 2 reserved (lock unavailable),
307 * 3 unique constraint violation, -1 error.
308 */
309 int
sql_error(char * sql,...)310 sql_error (char* sql, ...)
311 {
312 int ret;
313
314 while (1)
315 {
316 va_list args;
317 va_start (args, sql);
318 ret = sqlv (1, sql, args);
319 va_end (args);
320 if (ret == 1)
321 /* Gave up with statement reset. */
322 continue;
323 if (ret == -4)
324 return 3;
325 break;
326 }
327
328 return ret;
329 }
330
331 /**
332 * @brief Perform an SQL statement, giving up if database is busy or locked.
333 *
334 * @param[in] sql Format string for SQL statement.
335 * @param[in] ... Arguments for format string.
336 *
337 * @return 0 success, 1 gave up,
338 * 2 reserved (lock unavailable), 3 unique constraint violation,
339 * -1 error.
340 */
341 int
sql_giveup(char * sql,...)342 sql_giveup (char* sql, ...)
343 {
344 int ret;
345 va_list args;
346
347 va_start (args, sql);
348 ret = sqlv (0, sql, args);
349 va_end (args);
350 return ret;
351 }
352
353 /**
354 * @brief Get a particular cell from a SQL query.
355 *
356 * @param[in] sql Format string for SQL query.
357 * @param[in] args Arguments for format string.
358 * @param[out] stmt_return Return from statement.
359 *
360 * @return 0 success, 1 too few rows, -1 error.
361 */
362 int
sql_x(char * sql,va_list args,sql_stmt_t ** stmt_return)363 sql_x (char* sql, va_list args, sql_stmt_t** stmt_return)
364 {
365 int ret;
366
367 assert (stmt_return);
368
369 while (1)
370 {
371 /* Prepare statement.
372 * Copy args for this because a va_list can only be used once.
373 */
374 va_list args_copy;
375 va_copy (args_copy, args);
376 ret = sql_prepare_internal (1, 1, sql, args_copy, stmt_return);
377 va_end (args_copy);
378
379 if (ret)
380 {
381 g_warning ("%s: sql_prepare failed", __func__);
382 return -1;
383 }
384
385 /* Run statement. */
386
387 ret = sql_exec_internal (1, *stmt_return);
388 if (ret == -1 || ret == -4)
389 {
390 if (log_errors)
391 g_warning ("%s: sql_exec_internal failed", __func__);
392 return -1;
393 }
394 if (ret == 0)
395 /* Too few rows. */
396 return 1;
397 if (ret == -3 || ret == -2 || ret == 2)
398 {
399 /* Busy or locked, with statement reset. Or schema changed. */
400 sql_finalize (*stmt_return);
401 continue;
402 }
403 break;
404 }
405 assert (ret == 1);
406 g_debug (" sql_x end (%s)", sql);
407 return 0;
408 }
409
410 /**
411 * @brief Get the first value from a SQL query, as a double.
412 *
413 * @warning Aborts on invalid queries.
414 *
415 * @warning Aborts when the query returns fewer rows than \p row. The
416 * caller must ensure that the query will return sufficient rows.
417 *
418 * @param[in] sql Format string for SQL query.
419 * @param[in] ... Arguments for format string.
420 *
421 * @return Result of the query as an integer.
422 */
423 double
sql_double(char * sql,...)424 sql_double (char* sql, ...)
425 {
426 sql_stmt_t* stmt;
427 va_list args;
428 double ret;
429
430 int sql_x_ret;
431 va_start (args, sql);
432 sql_x_ret = sql_x (sql, args, &stmt);
433 va_end (args);
434 if (sql_x_ret)
435 {
436 sql_finalize (stmt);
437 abort ();
438 }
439 ret = sql_column_double (stmt, 0);
440 sql_finalize (stmt);
441 return ret;
442 }
443
444 /**
445 * @brief Get a particular cell from a SQL query, as an int.
446 *
447 * @warning Aborts on invalid queries.
448 *
449 * @warning Aborts when the query returns fewer rows than \p row. The
450 * caller must ensure that the query will return sufficient rows.
451 *
452 * @param[in] sql Format string for SQL query.
453 * @param[in] ... Arguments for format string.
454 *
455 * @return Result of the query as an integer.
456 */
457 int
sql_int(char * sql,...)458 sql_int (char* sql, ...)
459 {
460 sql_stmt_t* stmt;
461 va_list args;
462 int ret;
463
464 int sql_x_ret;
465 va_start (args, sql);
466 sql_x_ret = sql_x (sql, args, &stmt);
467 va_end (args);
468 if (sql_x_ret)
469 {
470 sql_finalize (stmt);
471 abort ();
472 }
473 ret = sql_column_int (stmt, 0);
474 sql_finalize (stmt);
475 return ret;
476 }
477
478 /**
479 * @brief Get a particular cell from a SQL query, as an string.
480 *
481 * @param[in] sql Format string for SQL query.
482 * @param[in] ... Arguments for format string.
483 *
484 * @return Freshly allocated string containing the result, NULL otherwise.
485 * NULL means that either the selected value was NULL or there were
486 * no rows in the result.
487 */
488 char*
sql_string(char * sql,...)489 sql_string (char* sql, ...)
490 {
491 sql_stmt_t* stmt;
492 const char* ret2;
493 char* ret;
494 int sql_x_ret;
495
496 va_list args;
497 va_start (args, sql);
498 sql_x_ret = sql_x (sql, args, &stmt);
499 va_end (args);
500 if (sql_x_ret)
501 {
502 sql_finalize (stmt);
503 return NULL;
504 }
505 ret2 = sql_column_text (stmt, 0);
506 ret = g_strdup (ret2);
507 sql_finalize (stmt);
508 return ret;
509 }
510
511 /**
512 * @brief Get a particular cell from a SQL query, as an int64.
513 *
514 * @param[in] ret Return value.
515 * @param[in] sql Format string for SQL query.
516 * @param[in] ... Arguments for format string.
517 *
518 * @return 0 success, 1 too few rows, -1 error.
519 */
520 int
sql_int64(long long int * ret,char * sql,...)521 sql_int64 (long long int* ret, char* sql, ...)
522 {
523 sql_stmt_t* stmt;
524 int sql_x_ret;
525 va_list args;
526
527 va_start (args, sql);
528 sql_x_ret = sql_x (sql, args, &stmt);
529 va_end (args);
530 switch (sql_x_ret)
531 {
532 case 0:
533 break;
534 case 1:
535 sql_finalize (stmt);
536 return 1;
537 break;
538 default:
539 assert (0);
540 /* Fall through. */
541 case -1:
542 sql_finalize (stmt);
543 return -1;
544 break;
545 }
546 *ret = sql_column_int64 (stmt, 0);
547 sql_finalize (stmt);
548 return 0;
549 }
550
551 /**
552 * @brief Get a first column of first row from a SQL query, as an int64.
553 *
554 * Return 0 on error.
555 *
556 * @param[in] sql Format string for SQL query.
557 * @param[in] ... Arguments for format string.
558 *
559 * @return Column value. 0 if no row.
560 */
561 long long int
sql_int64_0(char * sql,...)562 sql_int64_0 (char* sql, ...)
563 {
564 sql_stmt_t* stmt;
565 int sql_x_ret;
566 long long int ret;
567 va_list args;
568
569 va_start (args, sql);
570 sql_x_ret = sql_x (sql, args, &stmt);
571 va_end (args);
572 if (sql_x_ret)
573 {
574 sql_finalize (stmt);
575 return 0;
576 }
577 ret = sql_column_int64 (stmt, 0);
578 sql_finalize (stmt);
579 return ret;
580 }
581
582
583 /* Iterators. */
584
585 /**
586 * @brief Initialise an iterator.
587 *
588 * @param[in] iterator Iterator.
589 * @param[in] sql Format string for SQL.
590 */
591 void
init_iterator(iterator_t * iterator,const char * sql,...)592 init_iterator (iterator_t* iterator, const char* sql, ...)
593 {
594 int ret;
595 sql_stmt_t* stmt;
596 va_list args;
597
598 iterator->done = FALSE;
599 iterator->crypt_ctx = NULL;
600
601 va_start (args, sql);
602 ret = sql_prepare_internal (1, 1, sql, args, &stmt);
603 va_end (args);
604 if (ret)
605 {
606 g_warning ("%s: sql_prepare failed", __func__);
607 abort ();
608 }
609 iterator->stmt = stmt;
610 }
611
612 /**
613 * @brief Get a double column from an iterator.
614 *
615 * @param[in] iterator Iterator.
616 * @param[in] col Column offset.
617 *
618 * @return Value of given column.
619 */
620 double
iterator_double(iterator_t * iterator,int col)621 iterator_double (iterator_t* iterator, int col)
622 {
623 if (iterator->done) abort ();
624 return sql_column_double (iterator->stmt, col);
625 }
626
627 /**
628 * @brief Get a int column from an iterator.
629 *
630 * @param[in] iterator Iterator.
631 * @param[in] col Column offset.
632 *
633 * @return Value of given column.
634 */
635 int
iterator_int(iterator_t * iterator,int col)636 iterator_int (iterator_t* iterator, int col)
637 {
638 if (iterator->done) abort ();
639 return sql_column_int (iterator->stmt, col);
640 }
641
642 /**
643 * @brief Get an integer column from an iterator.
644 *
645 * @param[in] iterator Iterator.
646 * @param[in] col Column offset.
647 *
648 * @return Value of given column.
649 */
650 long long int
iterator_int64(iterator_t * iterator,int col)651 iterator_int64 (iterator_t* iterator, int col)
652 {
653 if (iterator->done) abort ();
654 return sql_column_int64 (iterator->stmt, col);
655 }
656
657 /**
658 * @brief Get a string column from an iterator.
659 *
660 * @param[in] iterator Iterator.
661 * @param[in] col Column offset.
662 *
663 * @return Value of given column.
664 */
665 const char*
iterator_string(iterator_t * iterator,int col)666 iterator_string (iterator_t* iterator, int col)
667 {
668 if (iterator->done) abort ();
669 return sql_column_text (iterator->stmt, col);
670 }
671
672 /**
673 * @brief Get a string column from an iterator.
674 *
675 * Note that sql_column_array gets the array as text and parses that text
676 * into an array, but it does not consider escaping so it probably will
677 * not work with strings that can contain commas, '{'s or '}'s.
678 *
679 * @param[in] iterator Iterator.
680 * @param[in] col Column offset.
681 *
682 * @return Value of given column.
683 */
684 gchar **
iterator_array(iterator_t * iterator,int col)685 iterator_array (iterator_t* iterator, int col)
686 {
687 if (iterator->done) abort ();
688 return sql_column_array (iterator->stmt, col);
689 }
690
691 /**
692 * @brief Cleanup an iterator.
693 *
694 * @param[in] iterator Iterator.
695 */
696 void
cleanup_iterator(iterator_t * iterator)697 cleanup_iterator (iterator_t* iterator)
698 {
699 if (iterator == NULL)
700 {
701 g_warning ("%s: null iterator pointer", __func__);
702 return;
703 }
704
705 sql_finalize (iterator->stmt);
706 if (iterator->crypt_ctx)
707 {
708 lsc_crypt_release (iterator->crypt_ctx);
709 iterator->crypt_ctx = NULL;
710 }
711 }
712
713 /**
714 * @brief Increment an iterator.
715 *
716 * @param[in] iterator Iterator.
717 *
718 * @return TRUE if there was a next item, else FALSE.
719 */
720 gboolean
next(iterator_t * iterator)721 next (iterator_t* iterator)
722 {
723 int ret;
724
725 if (iterator->done) return FALSE;
726
727 if (iterator->crypt_ctx)
728 lsc_crypt_flush (iterator->crypt_ctx);
729 while (1)
730 {
731 ret = sql_exec_internal (1, iterator->stmt);
732 if (ret == 0)
733 {
734 iterator->done = TRUE;
735 return FALSE;
736 }
737 if (ret == -1 || ret == -4)
738 {
739 if (log_errors)
740 g_warning ("%s: sql_exec_internal failed", __func__);
741 abort ();
742 }
743 if (ret == -3 || ret == -2)
744 {
745 /* Busy or locked, with statement reset. Just try step again like
746 * we used to do in sql_exec_internal. */
747 g_warning ("%s: stepping after reset", __func__);
748 continue;
749 }
750 break;
751 }
752 assert (ret == 1);
753 return TRUE;
754 }
755