src/libstrongswan/plugins/mysql/mysql_database.c

   1 /*
   2  * Copyright (C) 2007 Martin Willi
   3  * Hochschule fuer Technik Rapperswil
   4  *
   5  * This program is free software; you can redistribute it and/or modify it
   6  * under the terms of the GNU General Public License as published by the
   7  * Free Software Foundation; either version 2 of the License, or (at your
   8  * option) any later version.  See <http://www.fsf.org/copyleft/gpl.txt>.
   9  *
  10  * This program is distributed in the hope that it will be useful, but
  11  * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
  12  * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
  13  * for more details.
  14  */
  15
  16 #define _GNU_SOURCE
  17 #include <string.h>
  18 #include <pthread.h>
  19 #include <mysql.h>
  20
  21 #include "mysql_database.h"
  22
  23 #include <debug.h>
  24 #include <utils/mutex.h>
  25 #include <utils/linked_list.h>
  26
  27 /* Older mysql.h headers do not define it, but we need it. It is not returned
  28  * in in MySQL 4 by default, but by MySQL 5. To avoid this problem, we catch
  29  * it in all cases. */
  30 #ifndef MYSQL_DATA_TRUNCATED
  31 #define MYSQL_DATA_TRUNCATED 101
  32 #endif
  33
  34 typedef struct private_mysql_database_t private_mysql_database_t;
  35
  36 /**
  37  * private data of mysql_database
  38  */
  39 struct private_mysql_database_t {
  40
  41         /**
  42          * public functions
  43          */
  44         mysql_database_t public;
  45
  46         /**
  47          * connection pool, contains conn_t
  48          */
  49         linked_list_t *pool;
  50
  51         /**
  52          * mutex to lock pool
  53          */
  54         mutex_t *mutex;
  55
  56         /**
  57          * hostname to connect to
  58          */
  59         char *host;
  60
  61         /**
  62          * username to use
  63          */
  64         char *username;
  65
  66         /**
  67          * password
  68          */
  69         char *password;
  70
  71         /**
  72          * database name
  73          */
  74         char *database;
  75
  76         /**
  77          * tcp port
  78          */
  79         int port;
  80 };
  81
  82 typedef struct conn_t conn_t;
  83
  84 /**
  85  * connection pool entry
  86  */
  87 struct conn_t {
  88
  89         /**
  90          * MySQL database connection
  91          */
  92         MYSQL *mysql;
  93
  94         /**
  95          * connection in use?
  96          */
  97         bool in_use;
  98 };
  99
 100 /**
 101  * Release a mysql connection
 102  */
 103 static void conn_release(conn_t *conn)
 104 {
 105         conn->in_use = FALSE;
 106 }
 107 /**
 108  * thread specific initialization flag
 109  */
 110 pthread_key_t initialized;
 111
 112 /**
 113  * Initialize a thread for mysql usage
 114  */
 115 static void thread_initialize()
 116 {
 117         if (pthread_getspecific(initialized) == NULL)
 118         {
 119                 pthread_setspecific(initialized, (void*)TRUE);
 120                 mysql_thread_init();
 121         }
 122 }
 123
 124 /**
 125  * mysql library initialization function
 126  */
 127 bool mysql_database_init()
 128 {
 129         if (mysql_library_init(0, NULL, NULL))
 130         {
 131                 return FALSE;
 132         }
 133         if (pthread_key_create(&initialized, (void*)mysql_thread_end))
 134         {
 135                 mysql_library_end();
 136                 return FALSE;
 137         }
 138         return TRUE;
 139 }
 140
 141 /**
 142  * mysql library cleanup function
 143  */
 144 void mysql_database_deinit()
 145 {
 146         pthread_key_delete(initialized);
 147         mysql_thread_end();
 148         /* mysql_library_end(); would be the clean way, however, it hangs... */
 149 }
 150
 151 /**
 152  * Destroy a mysql connection
 153  */
 154 static void conn_destroy(conn_t *this)
 155 {
 156         mysql_close(this->mysql);
 157         free(this);
 158 }
 159
 160 /**
 161  * Acquire/Reuse a mysql connection
 162  */
 163 static conn_t *conn_get(private_mysql_database_t *this)
 164 {
 165         conn_t *current, *found = NULL;
 166         enumerator_t *enumerator;
 167
 168         thread_initialize();
 169
 170         while (TRUE)
 171         {
 172                 this->mutex->lock(this->mutex);
 173                 enumerator = this->pool->create_enumerator(this->pool);
 174                 while (enumerator->enumerate(enumerator, &current))
 175                 {
 176                         if (!current->in_use)
 177                         {
 178                                 found = current;
 179                                 found->in_use = TRUE;
 180                                 break;
 181                         }
 182                 }
 183                 enumerator->destroy(enumerator);
 184                 this->mutex->unlock(this->mutex);
 185                 if (found)
 186                 {       /* check connection if found, release if ping fails */
 187                         if (mysql_ping(found->mysql) == 0)
 188                         {
 189                                 break;
 190                         }
 191                         this->mutex->lock(this->mutex);
 192                         this->pool->remove(this->pool, found, NULL);
 193                         this->mutex->unlock(this->mutex);
 194                         conn_destroy(found);
 195                         found = NULL;
 196                         continue;
 197                 }
 198                 break;
 199         }
 200         if (found == NULL)
 201         {
 202                 found = malloc_thing(conn_t);
 203                 found->in_use = TRUE;
 204                 found->mysql = mysql_init(NULL);
 205                 if (!mysql_real_connect(found->mysql, this->host, this->username,
 206                                                                 this->password, this->database, this->port,
 207                                                                 NULL, 0))
 208                 {
 209                         DBG1("connecting to mysql://%s:***@%s:%d/%s failed: %s",
 210                                  this->username, this->host, this->port, this->database,
 211                                  mysql_error(found->mysql));
 212                         conn_destroy(found);
 213                         found = NULL;
 214                 }
 215                 else
 216                 {
 217                         this->mutex->lock(this->mutex);
 218                         this->pool->insert_last(this->pool, found);
 219                         DBG2("increased MySQL connection pool size to %d",
 220                                  this->pool->get_count(this->pool));
 221                         this->mutex->unlock(this->mutex);
 222                 }
 223         }
 224         return found;
 225 }
 226
 227 /**
 228  * Create and run a MySQL stmt using a sql string and args
 229  */
 230 static MYSQL_STMT* run(MYSQL *mysql, char *sql, va_list *args)
 231 {
 232         MYSQL_STMT *stmt;
 233         int params;
 234
 235         stmt = mysql_stmt_init(mysql);
 236         if (stmt == NULL)
 237         {
 238                 DBG1("creating MySQL statement failed: %s", mysql_error(mysql));
 239                 return NULL;
 240         }
 241         if (mysql_stmt_prepare(stmt, sql, strlen(sql)))
 242         {
 243                 DBG1("preparing MySQL statement failed: %s", mysql_stmt_error(stmt));
 244                 mysql_stmt_close(stmt);
 245                 return NULL;
 246         }
 247         params = mysql_stmt_param_count(stmt);
 248         if (params > 0)
 249         {
 250                 int i;
 251                 MYSQL_BIND *bind;
 252
 253                 bind = alloca(sizeof(MYSQL_BIND) * params);
 254                 memset(bind, 0, sizeof(MYSQL_BIND) * params);
 255
 256                 for (i = 0; i < params; i++)
 257                 {
 258                         switch (va_arg(*args, db_type_t))
 259                         {
 260                                 case DB_INT:
 261                                 {
 262                                         bind[i].buffer_type = MYSQL_TYPE_LONG;
 263                                         bind[i].buffer = (char*)alloca(sizeof(int));
 264                                         *(int*)bind[i].buffer = va_arg(*args, int);
 265                                         bind[i].buffer_length = sizeof(int);
 266                                         break;
 267                                 }
 268                                 case DB_UINT:
 269                                 {
 270                                         bind[i].buffer_type = MYSQL_TYPE_LONG;
 271                                         bind[i].buffer = (char*)alloca(sizeof(u_int));
 272                                         *(u_int*)bind[i].buffer = va_arg(*args, u_int);
 273                                         bind[i].buffer_length = sizeof(u_int);
 274                                         bind[i].is_unsigned = TRUE;
 275                                         break;
 276                                 }
 277                                 case DB_TEXT:
 278                                 {
 279                                         bind[i].buffer_type = MYSQL_TYPE_STRING;;
 280                                         bind[i].buffer = va_arg(*args, char*);
 281                                         if (bind[i].buffer)
 282                                         {
 283                                                 bind[i].buffer_length = strlen(bind[i].buffer);
 284                                         }
 285                                         break;
 286                                 }
 287                                 case DB_BLOB:
 288                                 {
 289                                         chunk_t chunk = va_arg(*args, chunk_t);
 290                                         bind[i].buffer_type = MYSQL_TYPE_BLOB;
 291                                         bind[i].buffer = chunk.ptr;
 292                                         bind[i].buffer_length = chunk.len;
 293                                         break;
 294                                 }
 295                                 case DB_DOUBLE:
 296                                 {
 297                                         bind[i].buffer_type = MYSQL_TYPE_DOUBLE;
 298                                         bind[i].buffer = (char*)alloca(sizeof(double));
 299                                         *(double*)bind[i].buffer = va_arg(*args, double);
 300                                         bind[i].buffer_length = sizeof(double);
 301                                         break;
 302                                 }
 303                                 case DB_NULL:
 304                                 {
 305                                         bind[i].buffer_type = MYSQL_TYPE_NULL;
 306                                         break;
 307                                 }
 308                                 default:
 309                                         DBG1("invalid data type supplied");
 310                                         mysql_stmt_close(stmt);
 311                                         return NULL;
 312                         }
 313                 }
 314                 if (mysql_stmt_bind_param(stmt, bind))
 315                 {
 316                         DBG1("binding MySQL param failed: %s", mysql_stmt_error(stmt));
 317                         mysql_stmt_close(stmt);
 318                         return NULL;
 319                 }
 320         }
 321         if (mysql_stmt_execute(stmt))
 322         {
 323                 DBG1("executing MySQL statement failed: %s", mysql_stmt_error(stmt));
 324                 mysql_stmt_close(stmt);
 325                 return NULL;
 326         }
 327         return stmt;
 328 }
 329
 330 typedef struct {
 331         /** implements enumerator_t */
 332         enumerator_t public;
 333         /** associated MySQL statement */
 334         MYSQL_STMT *stmt;
 335         /** result bindings */
 336         MYSQL_BIND *bind;
 337         /** pooled connection handle */
 338         conn_t *conn;
 339         /** value for INT, UINT, double */
 340         union {
 341                 void *p_void;;
 342                 int *p_int;
 343                 u_int *p_uint;
 344                 double *p_double;
 345         } val;
 346         /* length for TEXT and BLOB */
 347         unsigned long *length;
 348 } mysql_enumerator_t;
 349
 350 /**
 351  * create a mysql enumerator
 352  */
 353 static void mysql_enumerator_destroy(mysql_enumerator_t *this)
 354 {
 355         int columns, i;
 356
 357         columns = mysql_stmt_field_count(this->stmt);
 358
 359         for (i = 0; i < columns; i++)
 360         {
 361                 switch (this->bind[i].buffer_type)
 362                 {
 363                         case MYSQL_TYPE_STRING:
 364                         case MYSQL_TYPE_BLOB:
 365                         {
 366                                 free(this->bind[i].buffer);
 367                                 break;
 368                         }
 369                         default:
 370                                 break;
 371                 }
 372         }
 373         mysql_stmt_close(this->stmt);
 374         conn_release(this->conn);
 375         free(this->bind);
 376         free(this->val.p_void);
 377         free(this->length);
 378         free(this);
 379 }
 380
 381 /**
 382  * Implementation of database.query().enumerate
 383  */
 384 static bool mysql_enumerator_enumerate(mysql_enumerator_t *this, ...)
 385 {
 386         int i, columns;
 387         va_list args;
 388
 389         columns = mysql_stmt_field_count(this->stmt);
 390
 391         /* free/reset data set of previous call */
 392         for (i = 0; i < columns; i++)
 393         {
 394                 switch (this->bind[i].buffer_type)
 395                 {
 396                         case MYSQL_TYPE_STRING:
 397                         case MYSQL_TYPE_BLOB:
 398                         {
 399                                 free(this->bind[i].buffer);
 400                                 this->bind[i].buffer = NULL;
 401                                 this->bind[i].buffer_length = 0;
 402                                 this->bind[i].length = &this->length[i];
 403                                 this->length[i] = 0;
 404                                 break;
 405                         }
 406                         default:
 407                                 break;
 408                 }
 409         }
 410
 411         switch (mysql_stmt_fetch(this->stmt))
 412         {
 413                 case 0:
 414                 case MYSQL_DATA_TRUNCATED:
 415                         break;
 416                 case MYSQL_NO_DATA:
 417                         return FALSE;
 418                 default:
 419                         DBG1("fetching MySQL row failed: %s", mysql_stmt_error(this->stmt));
 420                         return FALSE;
 421         }
 422
 423         va_start(args, this);
 424         for (i = 0; i < columns; i++)
 425         {
 426                 switch (this->bind[i].buffer_type)
 427                 {
 428                         case MYSQL_TYPE_LONG:
 429                         {
 430                                 if (this->bind[i].is_unsigned)
 431                                 {
 432                                         u_int *value = va_arg(args, u_int*);
 433                                         *value = this->val.p_uint[i];
 434                                 }
 435                                 else
 436                                 {
 437                                         int *value = va_arg(args, int*);
 438                                         *value = this->val.p_int[i];
 439                                 }
 440                                 break;
 441                         }
 442                         case MYSQL_TYPE_STRING:
 443                         {
 444                                 char **value = va_arg(args, char**);
 445                                 this->bind[i].buffer = malloc(this->length[i]+1);
 446                                 this->bind[i].buffer_length = this->length[i];
 447                                 *value = this->bind[i].buffer;
 448                                 mysql_stmt_fetch_column(this->stmt, &this->bind[i], i, 0);
 449                                 ((char*)this->bind[i].buffer)[this->length[i]] = '\0';
 450                                 break;
 451                         }
 452                         case MYSQL_TYPE_BLOB:
 453                         {
 454                                 chunk_t *value = va_arg(args, chunk_t*);
 455                                 this->bind[i].buffer = malloc(this->length[i]);
 456                                 this->bind[i].buffer_length = this->length[i];
 457                                 value->ptr = this->bind[i].buffer;
 458                                 value->len = this->length[i];
 459                                 mysql_stmt_fetch_column(this->stmt, &this->bind[i], i, 0);
 460                                 break;
 461                         }
 462                         case MYSQL_TYPE_DOUBLE:
 463                         {
 464                                 double *value = va_arg(args, double*);
 465                                 *value = this->val.p_double[i];
 466                                 break;
 467                         }
 468                         default:
 469                                 break;
 470                 }
 471         }
 472         return TRUE;
 473 }
 474
 475 /**
 476  * Implementation of database_t.query.
 477  */
 478 static enumerator_t* query(private_mysql_database_t *this, char *sql, ...)
 479 {
 480         MYSQL_STMT *stmt;
 481         va_list args;
 482         mysql_enumerator_t *enumerator = NULL;
 483         conn_t *conn;
 484
 485         conn = conn_get(this);
 486         if (!conn)
 487         {
 488                 return NULL;
 489         }
 490
 491         va_start(args, sql);
 492         stmt = run(conn->mysql, sql, &args);
 493         if (stmt)
 494         {
 495                 int columns, i;
 496
 497                 enumerator = malloc_thing(mysql_enumerator_t);
 498                 enumerator->public.enumerate = (void*)mysql_enumerator_enumerate;
 499                 enumerator->public.destroy = (void*)mysql_enumerator_destroy;
 500                 enumerator->stmt = stmt;
 501                 enumerator->conn = conn;
 502                 columns = mysql_stmt_field_count(stmt);
 503                 enumerator->bind = calloc(columns, sizeof(MYSQL_BIND));
 504                 enumerator->length = calloc(columns, sizeof(unsigned long));
 505                 enumerator->val.p_void = calloc(columns, sizeof(enumerator->val));
 506                 for (i = 0; i < columns; i++)
 507                 {
 508                         switch (va_arg(args, db_type_t))
 509                         {
 510                                 case DB_INT:
 511                                 {
 512                                         enumerator->bind[i].buffer_type = MYSQL_TYPE_LONG;
 513                                         enumerator->bind[i].buffer = (char*)&enumerator->val.p_int[i];
 514                                         break;
 515                                 }
 516                                 case DB_UINT:
 517                                 {
 518                                         enumerator->bind[i].buffer_type = MYSQL_TYPE_LONG;
 519                                         enumerator->bind[i].buffer = (char*)&enumerator->val.p_uint[i];
 520                                         enumerator->bind[i].is_unsigned = TRUE;
 521                                         break;
 522                                 }
 523                                 case DB_TEXT:
 524                                 {
 525                                         enumerator->bind[i].buffer_type = MYSQL_TYPE_STRING;
 526                                         enumerator->bind[i].length = &enumerator->length[i];
 527                                         break;
 528                                 }
 529                                 case DB_BLOB:
 530                                 {
 531                                         enumerator->bind[i].buffer_type = MYSQL_TYPE_BLOB;
 532                                         enumerator->bind[i].length = &enumerator->length[i];
 533                                         break;
 534                                 }
 535                                 case DB_DOUBLE:
 536                                 {
 537                                         enumerator->bind[i].buffer_type = MYSQL_TYPE_DOUBLE;
 538                                         enumerator->bind[i].buffer = (char*)&enumerator->val.p_double[i];
 539                                         break;
 540                                 }
 541                                 default:
 542                                         DBG1("invalid result data type supplied");
 543                                         mysql_enumerator_destroy(enumerator);
 544                                         va_end(args);
 545                                         return NULL;
 546                         }
 547                 }
 548                 if (mysql_stmt_bind_result(stmt, enumerator->bind))
 549                 {
 550                         DBG1("binding MySQL result failed: %s", mysql_stmt_error(stmt));
 551                         mysql_enumerator_destroy(enumerator);
 552                         enumerator = NULL;
 553                 }
 554         }
 555         else
 556         {
 557                 conn_release(conn);
 558         }
 559         va_end(args);
 560         return (enumerator_t*)enumerator;
 561 }
 562
 563 /**
 564  * Implementation of database_t.execute.
 565  */
 566 static int execute(private_mysql_database_t *this, int *rowid, char *sql, ...)
 567 {
 568         MYSQL_STMT *stmt;
 569         va_list args;
 570         conn_t *conn;
 571         int affected = -1;
 572
 573         conn = conn_get(this);
 574         if (!conn)
 575         {
 576                 return -1;
 577         }
 578         va_start(args, sql);
 579         stmt = run(conn->mysql, sql, &args);
 580         if (stmt)
 581         {
 582                 if (rowid)
 583                 {
 584                         *rowid = mysql_stmt_insert_id(stmt);
 585                 }
 586                 affected = mysql_stmt_affected_rows(stmt);
 587                 mysql_stmt_close(stmt);
 588         }
 589         va_end(args);
 590         conn_release(conn);
 591         return affected;
 592 }
 593
 594 /**
 595  * Implementation of database_t.get_driver
 596  */
 597 static db_driver_t get_driver(private_mysql_database_t *this)
 598 {
 599         return DB_MYSQL;
 600 }
 601
 602 /**
 603  * Implementation of database_t.destroy
 604  */
 605 static void destroy(private_mysql_database_t *this)
 606 {
 607         this->pool->destroy_function(this->pool, (void*)conn_destroy);
 608         this->mutex->destroy(this->mutex);
 609         free(this->host);
 610         free(this->username);
 611         free(this->password);
 612         free(this->database);
 613         free(this);
 614 }
 615
 616 static bool parse_uri(private_mysql_database_t *this, char *uri)
 617 {
 618         char *username, *password, *host, *port = "0", *database, *pos;
 619
 620         /**
 621          * parse mysql://username:pass@host:port/database uri
 622          */
 623         username = strdupa(uri + 8);
 624         pos = strchr(username, ':');
 625         if (pos)
 626         {
 627                 *pos = '\0';
 628                 password = pos + 1;
 629                 pos = strrchr(password, '@');
 630                 if (pos)
 631                 {
 632                         *pos = '\0';
 633                         host = pos + 1;
 634                         pos = strrchr(host, ':');
 635                         if (pos)
 636                         {
 637                                 *pos = '\0';
 638                                 port = pos + 1;
 639                                 pos = strchr(port, '/');
 640                         }
 641                         else
 642                         {
 643                                 pos = strchr(host, '/');
 644                         }
 645                         if (pos)
 646                         {
 647                                 *pos = '\0';
 648                                 database = pos + 1;
 649
 650                                 this->host = strdup(host);
 651                                 this->username = strdup(username);
 652                                 this->password = strdup(password);
 653                                 this->database = strdup(database);
 654                                 this->port = atoi(port);
 655                                 return TRUE;
 656                         }
 657                 }
 658         }
 659         DBG1("parsing MySQL database uri '%s' failed", uri);
 660         return FALSE;
 661 }
 662
 663
 664 /*
 665  * see header file
 666  */
 667 mysql_database_t *mysql_database_create(char *uri)
 668 {
 669         conn_t *conn;
 670         private_mysql_database_t *this;
 671
 672         if (!strneq(uri, "mysql://", 8))
 673         {
 674                 return NULL;
 675         }
 676
 677         this = malloc_thing(private_mysql_database_t);
 678
 679         this->public.db.query = (enumerator_t* (*)(database_t *this, char *sql, ...))query;
 680         this->public.db.execute = (int (*)(database_t *this, int *rowid, char *sql, ...))execute;
 681         this->public.db.get_driver = (db_driver_t(*)(database_t*))get_driver;
 682         this->public.db.destroy = (void(*)(database_t*))destroy;
 683
 684         if (!parse_uri(this, uri))
 685         {
 686                 free(this);
 687                 return NULL;
 688         }
 689         this->mutex = mutex_create(MUTEX_TYPE_DEFAULT);
 690         this->pool = linked_list_create();
 691
 692         /* check connectivity */
 693         conn = conn_get(this);
 694         if (!conn)
 695         {
 696                 destroy(this);
 697                 return NULL;
 698         }
 699         conn_release(conn);
 700         return &this->public;
 701 }
 702