OpenDNSSEC-enforcer  2.1.3
db_backend_mysql.c
Go to the documentation of this file.
1 /*
2  * Copyright (c) 2014 Jerry Lundström <lundstrom.jerry@gmail.com>
3  * Copyright (c) 2014 .SE (The Internet Infrastructure Foundation).
4  * Copyright (c) 2014 OpenDNSSEC AB (svb)
5  * All rights reserved.
6  *
7  * Redistribution and use in source and binary forms, with or without
8  * modification, are permitted provided that the following conditions
9  * are met:
10  * 1. Redistributions of source code must retain the above copyright
11  * notice, this list of conditions and the following disclaimer.
12  * 2. Redistributions in binary form must reproduce the above copyright
13  * notice, this list of conditions and the following disclaimer in the
14  * documentation and/or other materials provided with the distribution.
15  *
16  * THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
17  * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18  * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
19  * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
20  * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
21  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
22  * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
23  * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
24  * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
25  * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
26  * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
27  *
28  */
29 
30 #include "db_backend_mysql.h"
31 #include "db_error.h"
32 
33 #include "log.h"
34 
35 #include <mysql/mysql.h>
36 #include <stdlib.h>
37 #include <stdio.h>
38 #include <unistd.h>
39 #include <string.h>
40 #include <time.h>
41 #include <pthread.h>
42 #include <errno.h>
43 
44 static int db_backend_mysql_transaction_rollback(void*);
45 
49 static int __mysql_initialized = 0;
50 
54 typedef struct db_backend_mysql {
55  MYSQL* db;
56  int transaction;
57  unsigned int timeout;
58 } db_backend_mysql_t;
59 
60 
61 
65 typedef struct db_backend_mysql_bind db_backend_mysql_bind_t;
66 struct db_backend_mysql_bind {
67  db_backend_mysql_bind_t* next;
68  MYSQL_BIND* bind;
69  unsigned long length;
70  my_bool error;
71  int value_enum;
72 };
73 
74 
75 
79 typedef struct db_backend_mysql_statement {
80  db_backend_mysql_t* backend_mysql;
81  MYSQL_STMT* statement;
82  MYSQL_BIND* mysql_bind_input;
83  db_backend_mysql_bind_t* bind_input;
84  db_backend_mysql_bind_t* bind_input_end;
85  MYSQL_BIND* mysql_bind_output;
86  db_backend_mysql_bind_t* bind_output;
87  db_backend_mysql_bind_t* bind_output_end;
88  db_object_field_list_t* object_field_list;
89  int fields;
90  int bound;
91 } db_backend_mysql_statement_t;
92 
93 
94 
100 static inline void __db_backend_mysql_finish(db_backend_mysql_statement_t* statement) {
101  db_backend_mysql_bind_t* bind;
102 
103  if (!statement) {
104  return;
105  }
106 
107  if (statement->statement) {
108  mysql_stmt_close(statement->statement);
109  }
110  if (statement->mysql_bind_input) {
111  free(statement->mysql_bind_input);
112  }
113  while (statement->bind_input) {
114  bind = statement->bind_input;
115  statement->bind_input = bind->next;
116  free(bind);
117  }
118  while (statement->bind_output) {
119  bind = statement->bind_output;
120  statement->bind_output = bind->next;
121  if (bind->bind && bind->bind->buffer) {
122  free(bind->bind->buffer);
123  }
124  free(bind);
125  }
126  if (statement->mysql_bind_output) {
127  free(statement->mysql_bind_output);
128  }
129  if (statement->object_field_list) {
130  db_object_field_list_free(statement->object_field_list);
131  }
132 
133  free(statement);
134 }
135 
142 static inline int __db_backend_mysql_prepare(db_backend_mysql_t* backend_mysql, db_backend_mysql_statement_t** statement, const char* sql, size_t size, const db_object_field_list_t* object_field_list) {
143  unsigned long i, params;
144  db_backend_mysql_bind_t* bind;
145  const db_object_field_t* object_field;
146  MYSQL_BIND* mysql_bind;
147  MYSQL_RES* result_metadata = NULL;
148  MYSQL_FIELD* field;
149 
150  if (!backend_mysql) {
151  return DB_ERROR_UNKNOWN;
152  }
153  if (!backend_mysql->db) {
154  return DB_ERROR_UNKNOWN;
155  }
156  if (!statement) {
157  return DB_ERROR_UNKNOWN;
158  }
159  if (*statement) {
160  return DB_ERROR_UNKNOWN;
161  }
162  if (!sql) {
163  return DB_ERROR_UNKNOWN;
164  }
165 
166  /*
167  * Prepare the statement.
168  */
169  ods_log_debug("%s", sql);
170  if (!(*statement = calloc(1, sizeof(db_backend_mysql_statement_t)))
171  || !((*statement)->statement = mysql_stmt_init(backend_mysql->db))
172  || mysql_stmt_prepare((*statement)->statement, sql, size))
173  {
174  if ((*statement)->statement) {
175  ods_log_info("DB prepare SQL %s", sql);
176  ods_log_info("DB prepare Err %d: %s", mysql_stmt_errno((*statement)->statement), mysql_stmt_error((*statement)->statement));
177  }
178  __db_backend_mysql_finish(*statement);
179  *statement = NULL;
180  return DB_ERROR_UNKNOWN;
181  }
182 
183  (*statement)->backend_mysql = backend_mysql;
184 
185  /*
186  * Create the input binding based on the number of parameters in the SQL
187  * statement.
188  */
189  if ((params = mysql_stmt_param_count((*statement)->statement)) > 0) {
190  if (!((*statement)->mysql_bind_input = calloc(params, sizeof(MYSQL_BIND)))) {
191  __db_backend_mysql_finish(*statement);
192  *statement = NULL;
193  return DB_ERROR_UNKNOWN;
194  }
195 
196  for (i = 0; i < params; i++) {
197  if (!(bind = calloc(1, sizeof(db_backend_mysql_bind_t)))) {
198  __db_backend_mysql_finish(*statement);
199  *statement = NULL;
200  return DB_ERROR_UNKNOWN;
201  }
202 
203  bind->bind = &((*statement)->mysql_bind_input[i]);
204  if (!(*statement)->bind_input) {
205  (*statement)->bind_input = bind;
206  }
207  if ((*statement)->bind_input_end) {
208  (*statement)->bind_input_end->next = bind;
209  }
210  (*statement)->bind_input_end = bind;
211  }
212  }
213 
214  /*
215  * Create the output binding based on the object field list given.
216  */
217  if (object_field_list
218  && (params = db_object_field_list_size(object_field_list)) > 0
219  && (result_metadata = mysql_stmt_result_metadata((*statement)->statement)))
220  {
221  if (!((*statement)->object_field_list = db_object_field_list_new_copy(object_field_list))
222  || !((*statement)->mysql_bind_output = calloc(params, sizeof(MYSQL_BIND))))
223  {
224  mysql_free_result(result_metadata);
225  __db_backend_mysql_finish(*statement);
226  *statement = NULL;
227  return DB_ERROR_UNKNOWN;
228  }
229 
230  (*statement)->fields = params;
231  field = mysql_fetch_field(result_metadata);
232  object_field = db_object_field_list_begin(object_field_list);
233  for (i = 0; i < params; i++) {
234  if (!field
235  || !object_field
236  || !(bind = calloc(1, sizeof(db_backend_mysql_bind_t))))
237  {
238  mysql_free_result(result_metadata);
239  __db_backend_mysql_finish(*statement);
240  *statement = NULL;
241  return DB_ERROR_UNKNOWN;
242  }
243 
244  bind->bind = (mysql_bind = &((*statement)->mysql_bind_output[i]));
245  mysql_bind->is_null = (my_bool*)0;
246  mysql_bind->error = &bind->error;
247  mysql_bind->length = &bind->length;
248 
249  switch (db_object_field_type(object_field)) {
250  case DB_TYPE_PRIMARY_KEY:
251  switch (field->type) {
252  case MYSQL_TYPE_TINY:
253  case MYSQL_TYPE_SHORT:
254  case MYSQL_TYPE_LONG:
255  case MYSQL_TYPE_INT24:
256  mysql_bind->buffer_type = MYSQL_TYPE_LONG;
257  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_uint32_t)))) {
258  mysql_free_result(result_metadata);
259  __db_backend_mysql_finish(*statement);
260  *statement = NULL;
261  return DB_ERROR_UNKNOWN;
262  }
263  mysql_bind->buffer_length = sizeof(db_type_uint32_t);
264  bind->length = mysql_bind->buffer_length;
265  mysql_bind->is_unsigned = 1;
266  break;
267 
268  case MYSQL_TYPE_LONGLONG:
269  mysql_bind->buffer_type = MYSQL_TYPE_LONGLONG;
270  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_uint64_t)))) {
271  mysql_free_result(result_metadata);
272  __db_backend_mysql_finish(*statement);
273  *statement = NULL;
274  return DB_ERROR_UNKNOWN;
275  }
276  mysql_bind->buffer_length = sizeof(db_type_uint64_t);
277  bind->length = mysql_bind->buffer_length;
278  mysql_bind->is_unsigned = 1;
279  break;
280 
281  case MYSQL_TYPE_STRING:
282  case MYSQL_TYPE_VAR_STRING:
283  mysql_bind->buffer_type = MYSQL_TYPE_STRING;
284  /*
285  * field->length does not include ending NULL character so
286  * we increase it by one.
287  */
288  bind->length = field->length + 1;
289  if (bind->length < DB_BACKEND_MYSQL_STRING_MIN_SIZE) {
290  bind->length = DB_BACKEND_MYSQL_STRING_MIN_SIZE;
291  }
292  if (!(mysql_bind->buffer = calloc(1, bind->length))) {
293  mysql_free_result(result_metadata);
294  __db_backend_mysql_finish(*statement);
295  *statement = NULL;
296  return DB_ERROR_UNKNOWN;
297  }
298  mysql_bind->buffer_length = bind->length;
299  mysql_bind->is_unsigned = 0;
300  break;
301 
302  default:
303  mysql_free_result(result_metadata);
304  __db_backend_mysql_finish(*statement);
305  *statement = NULL;
306  return DB_ERROR_UNKNOWN;
307  }
308  break;
309 
310  case DB_TYPE_ENUM:
311  /*
312  * Enum needs to be handled elsewhere since we don't know the
313  * enum_set_t here.
314  *
315  * TODO: can something be done here?
316  */
317  case DB_TYPE_INT32:
318  mysql_bind->buffer_type = MYSQL_TYPE_LONG;
319  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_int32_t)))) {
320  mysql_free_result(result_metadata);
321  __db_backend_mysql_finish(*statement);
322  *statement = NULL;
323  return DB_ERROR_UNKNOWN;
324  }
325  mysql_bind->buffer_length = sizeof(db_type_int32_t);
326  bind->length = mysql_bind->buffer_length;
327  mysql_bind->is_unsigned = 0;
328  break;
329 
330  case DB_TYPE_UINT32:
331  mysql_bind->buffer_type = MYSQL_TYPE_LONG;
332  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_uint32_t)))) {
333  mysql_free_result(result_metadata);
334  __db_backend_mysql_finish(*statement);
335  *statement = NULL;
336  return DB_ERROR_UNKNOWN;
337  }
338  mysql_bind->buffer_length = sizeof(db_type_uint32_t);
339  bind->length = mysql_bind->buffer_length;
340  mysql_bind->is_unsigned = 1;
341  break;
342 
343  case DB_TYPE_INT64:
344  mysql_bind->buffer_type = MYSQL_TYPE_LONGLONG;
345  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_int64_t)))) {
346  mysql_free_result(result_metadata);
347  __db_backend_mysql_finish(*statement);
348  *statement = NULL;
349  return DB_ERROR_UNKNOWN;
350  }
351  mysql_bind->buffer_length = sizeof(db_type_int64_t);
352  bind->length = mysql_bind->buffer_length;
353  mysql_bind->is_unsigned = 0;
354  break;
355 
356  case DB_TYPE_UINT64:
357  mysql_bind->buffer_type = MYSQL_TYPE_LONGLONG;
358  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_uint64_t)))) {
359  mysql_free_result(result_metadata);
360  __db_backend_mysql_finish(*statement);
361  *statement = NULL;
362  return DB_ERROR_UNKNOWN;
363  }
364  mysql_bind->buffer_length = sizeof(db_type_uint64_t);
365  bind->length = mysql_bind->buffer_length;
366  mysql_bind->is_unsigned = 1;
367  break;
368 
369  case DB_TYPE_TEXT:
370  mysql_bind->buffer_type = MYSQL_TYPE_STRING;
371  /*
372  * field->length does not include ending NULL character so
373  * we increase it by one.
374  */
375  bind->length = field->length + 1;
376  if (bind->length < DB_BACKEND_MYSQL_STRING_MIN_SIZE) {
377  bind->length = DB_BACKEND_MYSQL_STRING_MIN_SIZE;
378  }
379  if (!(mysql_bind->buffer = calloc(1, bind->length))) {
380  mysql_free_result(result_metadata);
381  __db_backend_mysql_finish(*statement);
382  *statement = NULL;
383  return DB_ERROR_UNKNOWN;
384  }
385  mysql_bind->buffer_length = bind->length;
386  mysql_bind->is_unsigned = 0;
387  break;
388 
389  case DB_TYPE_ANY:
390  case DB_TYPE_REVISION:
391  switch (field->type) {
392  case MYSQL_TYPE_TINY:
393  case MYSQL_TYPE_SHORT:
394  case MYSQL_TYPE_LONG:
395  case MYSQL_TYPE_INT24:
396  mysql_bind->buffer_type = MYSQL_TYPE_LONG;
397  if (field->flags & UNSIGNED_FLAG) {
398  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_uint32_t)))) {
399  mysql_free_result(result_metadata);
400  __db_backend_mysql_finish(*statement);
401  *statement = NULL;
402  return DB_ERROR_UNKNOWN;
403  }
404  mysql_bind->buffer_length = sizeof(db_type_uint32_t);
405  mysql_bind->is_unsigned = 1;
406  }
407  else {
408  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_int32_t)))) {
409  mysql_free_result(result_metadata);
410  __db_backend_mysql_finish(*statement);
411  *statement = NULL;
412  return DB_ERROR_UNKNOWN;
413  }
414  mysql_bind->buffer_length = sizeof(db_type_int32_t);
415  mysql_bind->is_unsigned = 0;
416  }
417  bind->length = mysql_bind->buffer_length;
418  break;
419 
420  case MYSQL_TYPE_LONGLONG:
421  mysql_bind->buffer_type = MYSQL_TYPE_LONGLONG;
422  if (field->flags & UNSIGNED_FLAG) {
423  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_uint64_t)))) {
424  mysql_free_result(result_metadata);
425  __db_backend_mysql_finish(*statement);
426  *statement = NULL;
427  return DB_ERROR_UNKNOWN;
428  }
429  mysql_bind->buffer_length = sizeof(db_type_uint64_t);
430  mysql_bind->is_unsigned = 1;
431  }
432  else {
433  if (!(mysql_bind->buffer = calloc(1, sizeof(db_type_int64_t)))) {
434  mysql_free_result(result_metadata);
435  __db_backend_mysql_finish(*statement);
436  *statement = NULL;
437  return DB_ERROR_UNKNOWN;
438  }
439  mysql_bind->buffer_length = sizeof(db_type_int64_t);
440  mysql_bind->is_unsigned = 0;
441  }
442  bind->length = mysql_bind->buffer_length;
443  break;
444 
445  case MYSQL_TYPE_STRING:
446  case MYSQL_TYPE_VAR_STRING:
447  mysql_bind->buffer_type = MYSQL_TYPE_STRING;
448  /*
449  * field->length does not include ending NULL character so
450  * we increase it by one.
451  */
452  bind->length = field->length + 1;
453  if (bind->length < DB_BACKEND_MYSQL_STRING_MIN_SIZE) {
454  bind->length = DB_BACKEND_MYSQL_STRING_MIN_SIZE;
455  }
456  if (!(mysql_bind->buffer = calloc(1, bind->length))) {
457  mysql_free_result(result_metadata);
458  __db_backend_mysql_finish(*statement);
459  *statement = NULL;
460  return DB_ERROR_UNKNOWN;
461  }
462  mysql_bind->buffer_length = bind->length;
463  mysql_bind->is_unsigned = 0;
464  break;
465 
466  default:
467  mysql_free_result(result_metadata);
468  __db_backend_mysql_finish(*statement);
469  *statement = NULL;
470  return DB_ERROR_UNKNOWN;
471  }
472  break;
473 
474  default:
475  return DB_ERROR_UNKNOWN;
476  }
477 
478  if (!(*statement)->bind_output) {
479  (*statement)->bind_output = bind;
480  }
481  if ((*statement)->bind_output_end) {
482  (*statement)->bind_output_end->next = bind;
483  }
484  (*statement)->bind_output_end = bind;
485  object_field = db_object_field_next(object_field);
486  field = mysql_fetch_field(result_metadata);
487  }
488  /*
489  * If we still have an object field or a MySQL field then the number of
490  * fields in both is mismatching and we should return an error.
491  */
492  if (object_field || field) {
493  mysql_free_result(result_metadata);
494  __db_backend_mysql_finish(*statement);
495  *statement = NULL;
496  return DB_ERROR_UNKNOWN;
497  }
498  }
499  if (result_metadata) {
500  mysql_free_result(result_metadata);
501  }
502 
503  return DB_OK;
504 }
505 
511 static inline int __db_backend_mysql_fetch(db_backend_mysql_statement_t* statement) {
512  int ret;
513 
514  if (!statement) {
515  return DB_ERROR_UNKNOWN;
516  }
517  if (!statement->statement) {
518  return DB_ERROR_UNKNOWN;
519  }
520 
521  /*
522  * Handle output binding if not already done.
523  */
524  if (!statement->bound) {
525  if (statement->mysql_bind_output
526  && mysql_stmt_bind_result(statement->statement, statement->mysql_bind_output))
527  {
528  ods_log_info("DB bind result Err %d: %s", mysql_stmt_errno(statement->statement), mysql_stmt_error(statement->statement));
529  return DB_ERROR_UNKNOWN;
530  }
531  statement->bound = 1;
532  }
533 
534  /*
535  * Fetch the next row.
536  */
537  ret = mysql_stmt_fetch(statement->statement);
538  if (ret == 1) {
539  ods_log_info("DB fetch Err %d: %s", mysql_stmt_errno(statement->statement), mysql_stmt_error(statement->statement));
540  return DB_ERROR_UNKNOWN;
541  }
542  else if (ret == MYSQL_DATA_TRUNCATED) {
543  int i;
544  db_backend_mysql_bind_t* bind;
545 
546  /*
547  * Scan through all of the output binds and check where the data was
548  * truncated and reallocate the buffer and try again. MySQL should have
549  * updated bind->length with the required buffer size.
550  *
551  * We can really only retry fetch on string columns, if another type had
552  * a too small buffer its more a programmable error in the prepare
553  * function.
554  */
555  for (i = 0, bind = statement->bind_output; bind; i++, bind = bind->next) {
556  if (bind->error) {
557  if (statement->mysql_bind_output[i].buffer_type != MYSQL_TYPE_STRING
558  || bind->length <= statement->mysql_bind_output[i].buffer_length)
559  {
560  ods_log_info("DB fetch Err data truncated");
561  return DB_ERROR_UNKNOWN;
562  }
563 
564  free(statement->mysql_bind_output[i].buffer);
565  statement->mysql_bind_output[i].buffer = NULL;
566  if (!(statement->mysql_bind_output[i].buffer = calloc(1, bind->length))) {
567  ods_log_info("DB fetch Err data truncated");
568  return DB_ERROR_UNKNOWN;
569  }
570  statement->mysql_bind_output[i].buffer_length = bind->length;
571  bind->error = 0;
572  if (mysql_stmt_fetch_column(statement->statement, &(statement->mysql_bind_output[i]), i, 0)
573  || bind->error)
574  {
575  ods_log_info("DB fetch Err data truncated");
576  return DB_ERROR_UNKNOWN;
577  }
578  }
579  }
580  }
581  else if (ret == MYSQL_NO_DATA) {
582  /*
583  * Not really an error but we need to indicate that there is no more
584  * data some how.
585  */
586  return DB_ERROR_UNKNOWN;
587  }
588  else if (ret) {
589  ods_log_info("DB fetch UNKNOWN %d Err %d: %s", ret, mysql_stmt_errno(statement->statement), mysql_stmt_error(statement->statement));
590  return DB_ERROR_UNKNOWN;
591  }
592 
593  return DB_OK;
594 }
595 
601 static inline int __db_backend_mysql_execute(db_backend_mysql_statement_t* statement) {
602  if (!statement) {
603  return DB_ERROR_UNKNOWN;
604  }
605  if (!statement->statement) {
606  return DB_ERROR_UNKNOWN;
607  }
608 
609  /*
610  * Bind the input parameters.
611  */
612  if (statement->mysql_bind_input
613  && mysql_stmt_bind_param(statement->statement, statement->mysql_bind_input))
614  {
615  ods_log_info("DB bind param Err %d: %s", mysql_stmt_errno(statement->statement), mysql_stmt_error(statement->statement));
616  return DB_ERROR_UNKNOWN;
617  }
618 
619  /*
620  * Execute the statement.
621  */
622  if (mysql_stmt_execute(statement->statement)) {
623  ods_log_info("DB execute Err %d: %s", mysql_stmt_errno(statement->statement), mysql_stmt_error(statement->statement));
624  return DB_ERROR_UNKNOWN;
625  }
626 
627  return DB_OK;
628 }
629 
630 static int db_backend_mysql_initialize(void* data) {
631  db_backend_mysql_t* backend_mysql = (db_backend_mysql_t*)data;
632 
633  if (!backend_mysql) {
634  return DB_ERROR_UNKNOWN;
635  }
636 
637  if (!__mysql_initialized) {
638  if (mysql_library_init(0, NULL, NULL)) {
639  return DB_ERROR_UNKNOWN;
640  }
641  __mysql_initialized = 1;
642  }
643  return DB_OK;
644 }
645 
646 static int db_backend_mysql_shutdown(void* data) {
647  db_backend_mysql_t* backend_mysql = (db_backend_mysql_t*)data;
648 
649  if (!backend_mysql) {
650  return DB_ERROR_UNKNOWN;
651  }
652 
653  if (__mysql_initialized) {
654  mysql_library_end();
655  __mysql_initialized = 0;
656  }
657  return DB_OK;
658 }
659 
660 static int db_backend_mysql_connect(void* data, const db_configuration_list_t* configuration_list) {
661