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