1/* 2** 3** SQL Auxprop plugin 4** 5** Ken Murchison 6** Maya Nigrosh -- original store() and txn support 7** Simon Loader -- original mysql plugin 8** Patrick Welche -- original pgsql plugin 9** 10** $Id: sql.c,v 1.38 2009/04/11 10:48:07 mel Exp $ 11** 12*/ 13 14#include <config.h> 15 16#include <stdio.h> 17#include <assert.h> 18#include <stdlib.h> 19#include <string.h> 20 21#include "sasl.h" 22#include "saslutil.h" 23#include "saslplug.h" 24 25#include <ctype.h> 26 27#include "plugin_common.h" 28 29#define sql_max(a, b) ((a) > (b) ? (a) : (b)) 30#define sql_len(input) ((input) ? strlen(input) : 0) 31#define sql_exists(input) ((input) && (*input)) 32 33typedef struct sql_engine { 34 const char *name; 35 void *(*sql_open)(char *host, char *port, int usessl, 36 const char *user, const char *password, 37 const char *database, const sasl_utils_t *utils); 38 int (*sql_escape_str)(char *to, const char *from); 39 int (*sql_begin_txn)(void *conn, const sasl_utils_t *utils); 40 int (*sql_commit_txn)(void *conn, const sasl_utils_t *utils); 41 int (*sql_rollback_txn)(void *conn, const sasl_utils_t *utils); 42 int (*sql_exec)(void *conn, const char *cmd, char *value, size_t size, 43 size_t *value_len, const sasl_utils_t *utils); 44 void (*sql_close)(void *conn); 45} sql_engine_t; 46 47typedef struct sql_settings { 48 const sql_engine_t *sql_engine; 49 const char *sql_user; 50 const char *sql_passwd; 51 const char *sql_hostnames; 52 const char *sql_database; 53 const char *sql_select; 54 const char *sql_insert; 55 const char *sql_update; 56 int sql_usessl; 57} sql_settings_t; 58 59static const char * SQL_BLANK_STRING = ""; 60static const char * SQL_WILDCARD = "*"; 61static const char * SQL_NULL_VALUE = "NULL"; 62 63 64#ifdef HAVE_MYSQL 65#include <mysql.h> 66 67static void *_mysql_open(char *host, char *port, int usessl, 68 const char *user, const char *password, 69 const char *database, const sasl_utils_t *utils) 70{ 71 MYSQL *mysql; 72 73 if (!(mysql = mysql_init(NULL))) { 74 utils->log(NULL, SASL_LOG_ERR, 75 "sql plugin: could not execute mysql_init()"); 76 return NULL; 77 } 78 79 return mysql_real_connect(mysql, host, user, password, database, 80 port ? strtoul(port, NULL, 10) : 0, NULL, 81 usessl ? CLIENT_SSL : 0); 82} 83 84static int _mysql_escape_str(char *to, const char *from) 85{ 86 return mysql_escape_string(to, from, strlen(from)); 87} 88 89static int _mysql_exec(void *conn, const char *cmd, char *value, size_t size, 90 size_t *value_len, const sasl_utils_t *utils) 91{ 92 MYSQL_RES *result; 93 MYSQL_ROW row; 94 int row_count, len; 95 96 len = strlen(cmd); 97 /* mysql_real_query() doesn't want a terminating ';' */ 98 if (cmd[len-1] == ';') len--; 99 100 /* 101 * Run the query. It is important to note that mysql_real_query 102 * will return success even if the sql statement 103 * had an error in it. However, mysql_errno() will alsways 104 * tell us if there was an error. Therefore we can ignore 105 * the result from mysql_real_query and simply check mysql_errno() 106 * to decide if there was really an error. 107 */ 108 (void)mysql_real_query(conn, cmd, len); 109 110 if(mysql_errno(conn)) { 111 utils->log(NULL, SASL_LOG_ERR, "sql query failed: %s", 112 mysql_error(conn)); 113 return -1; 114 } 115 116 /* see if we should expect some results */ 117 if (!mysql_field_count(conn)) { 118 /* no results (BEGIN, COMMIT, DELETE, INSERT, UPDATE) */ 119 return 0; 120 } 121 122 /* get the results */ 123 result = mysql_store_result(conn); 124 if (!result) { 125 /* umm nothing found */ 126 utils->log(NULL, SASL_LOG_NOTE, "sql plugin: no result found"); 127 return -1; 128 } 129 130 /* quick row check */ 131 row_count = mysql_num_rows(result); 132 if (!row_count) { 133 /* umm nothing found */ 134 mysql_free_result(result); 135 utils->log(NULL, SASL_LOG_NOTE, "sql plugin: no result found"); 136 return -1; 137 } 138 if (row_count > 1) { 139 utils->log(NULL, SASL_LOG_WARN, 140 "sql plugin: found duplicate row for query %s", cmd); 141 } 142 143 /* now get the result set value and value_len */ 144 /* we only fetch one because we don't care about the rest */ 145 row = mysql_fetch_row(result); 146 if (!row || !row[0]) { 147 /* umm nothing found */ 148 utils->log(NULL, SASL_LOG_NOTE, "sql plugin: no result found"); 149 mysql_free_result(result); 150 return -1; 151 } 152 if (value) { 153 strncpy(value, row[0], size-2); 154 value[size-1] = '\0'; 155 if (value_len) *value_len = strlen(value); 156 } 157 158 /* free result */ 159 mysql_free_result(result); 160 161 return 0; 162} 163 164static int _mysql_begin_txn(void *conn, const sasl_utils_t *utils) 165{ 166 return _mysql_exec(conn, 167#if MYSQL_VERSION_ID >= 40011 168 "START TRANSACTION", 169#else 170 "BEGIN", 171#endif 172 NULL, 0, NULL, utils); 173} 174 175static int _mysql_commit_txn(void *conn, const sasl_utils_t *utils) 176{ 177 return _mysql_exec(conn, "COMMIT", NULL, 0, NULL, utils); 178} 179 180static int _mysql_rollback_txn(void *conn, const sasl_utils_t *utils) 181{ 182 return _mysql_exec(conn, "ROLLBACK", NULL, 0, NULL, utils); 183} 184 185static void _mysql_close(void *conn) 186{ 187 mysql_close(conn); 188} 189#endif /* HAVE_MYSQL */ 190 191#ifdef HAVE_PGSQL 192#include <libpq-fe.h> 193 194static void *_pgsql_open(char *host, char *port, int usessl, 195 const char *user, const char *password, 196 const char *database, const sasl_utils_t *utils) 197{ 198 PGconn *conn = NULL; 199 char *conninfo, *sep; 200 201 /* create the connection info string */ 202 /* The 64 represents the number of characters taken by 203 * the keyword tokens, plus a small pad 204 */ 205 conninfo = utils->malloc(64 + sql_len(host) + sql_len(port) 206 + sql_len(user) + sql_len(password) 207 + sql_len(database)); 208 if (!conninfo) { 209 MEMERROR(utils); 210 return NULL; 211 } 212 213 /* add each term that exists */ 214 conninfo[0] = '\0'; 215 sep = ""; 216 if (sql_exists(host)) { 217 strcat(conninfo, sep); 218 strcat(conninfo, "host='"); 219 strcat(conninfo, host); 220 strcat(conninfo, "'"); 221 sep = " "; 222 } 223 if (sql_exists(port)) { 224 strcat(conninfo, sep); 225 strcat(conninfo, "port='"); 226 strcat(conninfo, port); 227 strcat(conninfo, "'"); 228 sep = " "; 229 } 230 if (sql_exists(user)) { 231 strcat(conninfo, sep); 232 strcat(conninfo, "user='"); 233 strcat(conninfo, user); 234 strcat(conninfo, "'"); 235 sep = " "; 236 } 237 if (sql_exists(password)) { 238 strcat(conninfo, sep); 239 strcat(conninfo, "password='"); 240 strcat(conninfo, password); 241 strcat(conninfo, "'"); 242 sep = " "; 243 } 244 if (sql_exists(database)) { 245 strcat(conninfo, sep); 246 strcat(conninfo, "dbname='"); 247 strcat(conninfo, database); 248 strcat(conninfo, "'"); 249 sep = " "; 250 } 251 if (usessl) { 252 strcat(conninfo, sep); 253 strcat(conninfo, "requiressl='1'"); 254 } 255 256 conn = PQconnectdb(conninfo); 257 free(conninfo); 258 259 if ((PQstatus(conn) != CONNECTION_OK)) { 260 utils->log(NULL, SASL_LOG_ERR, "sql plugin: %s", PQerrorMessage(conn)); 261 return NULL; 262 } 263 264 return conn; 265} 266 267static int _pgsql_escape_str(char *to, const char *from) 268{ 269 return PQescapeString(to, from, strlen(from)); 270} 271 272static int _pgsql_exec(void *conn, const char *cmd, char *value, size_t size, 273 size_t *value_len, const sasl_utils_t *utils) 274{ 275 PGresult *result; 276 int row_count; 277 ExecStatusType status; 278 279 /* run the query */ 280 result = PQexec(conn, cmd); 281 282 /* check the status */ 283 status = PQresultStatus(result); 284 if (status == PGRES_COMMAND_OK) { 285 /* no results (BEGIN, COMMIT, DELETE, INSERT, UPDATE) */ 286 PQclear(result); 287 return 0; 288 } 289 else if (status != PGRES_TUPLES_OK) { 290 /* error */ 291 utils->log(NULL, SASL_LOG_DEBUG, "sql plugin: %s ", 292 PQresStatus(status)); 293 PQclear(result); 294 return -1; 295 } 296 297 /* quick row check */ 298 row_count = PQntuples(result); 299 if (!row_count) { 300 /* umm nothing found */ 301 utils->log(NULL, SASL_LOG_NOTE, "sql plugin: no result found"); 302 PQclear(result); 303 return -1; 304 } 305 if (row_count > 1) { 306 utils->log(NULL, SASL_LOG_WARN, 307 "sql plugin: found duplicate row for query %s", cmd); 308 } 309 310 /* now get the result set value and value_len */ 311 /* we only fetch one because we don't care about the rest */ 312 if (value) { 313 strncpy(value, PQgetvalue(result,0,0), size-2); 314 value[size-1] = '\0'; 315 if (value_len) *value_len = strlen(value); 316 } 317 318 /* free result */ 319 PQclear(result); 320 return 0; 321} 322 323static int _pgsql_begin_txn(void *conn, const sasl_utils_t *utils) 324{ 325 return _pgsql_exec(conn, "BEGIN;", NULL, 0, NULL, utils); 326} 327 328static int _pgsql_commit_txn(void *conn, const sasl_utils_t *utils) 329{ 330 return _pgsql_exec(conn, "COMMIT;", NULL, 0, NULL, utils); 331} 332 333static int _pgsql_rollback_txn(void *conn, const sasl_utils_t *utils) 334{ 335 return _pgsql_exec(conn, "ROLLBACK;", NULL, 0, NULL, utils); 336} 337 338static void _pgsql_close(void *conn) 339{ 340 PQfinish(conn); 341} 342#endif /* HAVE_PGSQL */ 343 344#ifdef HAVE_SQLITE 345#include <sqlite.h> 346 347static void *_sqlite_open(char *host __attribute__((unused)), 348 char *port __attribute__((unused)), 349 int usessl __attribute__((unused)), 350 const char *user __attribute__((unused)), 351 const char *password __attribute__((unused)), 352 const char *database, const sasl_utils_t *utils) 353{ 354 int rc; 355 sqlite *db; 356 char *zErrMsg = NULL; 357 358 db = sqlite_open(database, 0, &zErrMsg); 359 if (db == NULL) { 360 utils->log(NULL, SASL_LOG_ERR, "sql plugin: %s", zErrMsg); 361 sqlite_freemem (zErrMsg); 362 return NULL; 363 } 364 365 rc = sqlite_exec(db, "PRAGMA empty_result_callbacks = ON", NULL, NULL, &zErrMsg); 366 if (rc != SQLITE_OK) { 367 utils->log(NULL, SASL_LOG_ERR, "sql plugin: %s", zErrMsg); 368 sqlite_freemem (zErrMsg); 369 sqlite_close(db); 370 return NULL; 371 } 372 373 return (void*)db; 374} 375 376static int _sqlite_escape_str(char *to, const char *from) 377{ 378 char s; 379 380 while ( (s = *from++) != '\0' ) { 381 if (s == '\'' || s == '\\') { 382 *to++ = '\\'; 383 } 384 *to++ = s; 385 } 386 *to = '\0'; 387 388 return 0; 389} 390 391static int sqlite_my_callback(void *pArg, int argc __attribute__((unused)), 392 char **argv, 393 char **columnNames __attribute__((unused))) 394{ 395 char **result = (char**)pArg; 396 397 if (argv == NULL) { 398 *result = NULL; /* no record */ 399 } else if (argv[0] == NULL) { 400 *result = strdup(SQL_NULL_VALUE); /* NULL IS SQL_NULL_VALUE */ 401 } else { 402 *result = strdup(argv[0]); 403 } 404 405 return /*ABORT*/1; 406} 407 408static int _sqlite_exec(void *db, const char *cmd, char *value, size_t size, 409 size_t *value_len, const sasl_utils_t *utils) 410{ 411 int rc; 412 char *result = NULL; 413 char *zErrMsg = NULL; 414 415 rc = sqlite_exec((sqlite*)db, cmd, sqlite_my_callback, (void*)&result, &zErrMsg); 416 if (rc != SQLITE_OK && rc != SQLITE_ABORT) { 417 utils->log(NULL, SASL_LOG_DEBUG, "sql plugin: %s ", zErrMsg); 418 sqlite_freemem (zErrMsg); 419 return -1; 420 } 421 422 if (rc == SQLITE_OK) { 423 /* no results (BEGIN, COMMIT, DELETE, INSERT, UPDATE) */ 424 return 0; 425 } 426 427 if (result == NULL) { 428 /* umm nothing found */ 429 utils->log(NULL, SASL_LOG_NOTE, "sql plugin: no result found"); 430 return -1; 431 } 432 433 /* XXX: Duplication cannot be found by this method. */ 434 435 /* now get the result set value and value_len */ 436 /* we only fetch one because we don't care about the rest */ 437 if (value) { 438 strncpy(value, result, size - 2); 439 value[size - 1] = '\0'; 440 if (value_len) { 441 *value_len = strlen(value); 442 } 443 } 444 445 /* free result */ 446 free(result); 447 return 0; 448} 449 450static int _sqlite_begin_txn(void *db, const sasl_utils_t *utils) 451{ 452 return _sqlite_exec(db, "BEGIN TRANSACTION", NULL, 0, NULL, utils); 453} 454 455static int _sqlite_commit_txn(void *db, const sasl_utils_t *utils) 456{ 457 return _sqlite_exec(db, "COMMIT TRANSACTION", NULL, 0, NULL, utils); 458} 459 460static int _sqlite_rollback_txn(void *db, const sasl_utils_t *utils) 461{ 462 return _sqlite_exec(db, "ROLLBACK TRANSACTION", NULL, 0, NULL, utils); 463} 464 465static void _sqlite_close(void *db) 466{ 467 sqlite_close((sqlite*)db); 468} 469#endif /* HAVE_SQLITE */ 470 471#ifdef HAVE_SQLITE3 472#include <sqlite3.h> 473 474static void *_sqlite3_open(char *host __attribute__((unused)), 475 char *port __attribute__((unused)), 476 int usessl __attribute__((unused)), 477 const char *user __attribute__((unused)), 478 const char *password __attribute__((unused)), 479 const char *database, const sasl_utils_t *utils) 480{ 481 int rc; 482 sqlite3 *db; 483 char *zErrMsg = NULL; 484 485 rc = sqlite3_open(database, &db); 486 if (SQLITE_OK != rc) { 487 if (db) 488 utils->log(NULL, SASL_LOG_ERR, "sql plugin: %s", sqlite3_errmsg(db)); 489 else 490 utils->log(NULL, SASL_LOG_ERR, "sql plugin: %d", rc); 491 sqlite3_close(db); 492 return NULL; 493 } 494 495 rc = sqlite3_exec(db, "PRAGMA empty_result_callbacks = ON", NULL, NULL, &zErrMsg); 496 if (rc != SQLITE_OK) { 497 if (zErrMsg) { 498 utils->log(NULL, SASL_LOG_ERR, "sql plugin: %s", zErrMsg); 499 sqlite3_free(zErrMsg); 500 } else 501 utils->log(NULL, SASL_LOG_DEBUG, "sql plugin: %d", rc); 502 sqlite3_close(db); 503 return NULL; 504 } 505 506 return (void*)db; 507} 508 509static int _sqlite3_escape_str(char *to, const char *from) 510{ 511 char s; 512 513 while ( (s = *from++) != '\0' ) { 514 if (s == '\'' || s == '\\') { 515 *to++ = '\\'; 516 } 517 *to++ = s; 518 } 519 *to = '\0'; 520 521 return 0; 522} 523 524static int sqlite3_my_callback(void *pArg, int argc __attribute__((unused)), 525 char **argv, 526 char **columnNames __attribute__((unused))) 527{ 528 char **result = (char**)pArg; 529 530 if (argv == NULL) { 531 *result = NULL; /* no record */ 532 } else if (argv[0] == NULL) { 533 *result = strdup(SQL_NULL_VALUE); /* NULL IS SQL_NULL_VALUE */ 534 } else { 535 *result = strdup(argv[0]); 536 } 537 538 return 0; 539} 540 541static int _sqlite3_exec(void *db, 542 const char *cmd, 543 char *value, 544 size_t size, 545 size_t *value_len, 546 const sasl_utils_t *utils) 547{ 548 int rc; 549 char *result = NULL; 550 char *zErrMsg = NULL; 551 552 rc = sqlite3_exec((sqlite3*)db, cmd, sqlite3_my_callback, (void*)&result, &zErrMsg); 553 if (rc != SQLITE_OK) { 554 if (zErrMsg) { 555 utils->log(NULL, SASL_LOG_DEBUG, "sql plugin: %s", zErrMsg); 556 sqlite3_free(zErrMsg); 557 } else { 558 utils->log(NULL, SASL_LOG_DEBUG, "sql plugin: %d", rc); 559 } 560 return -1; 561 } 562 563 if (value == NULL && rc == SQLITE_OK) { 564 /* no results (BEGIN, COMMIT, DELETE, INSERT, UPDATE) */ 565 return 0; 566 } 567 568 if (result == NULL) { 569 /* umm nothing found */ 570 utils->log(NULL, SASL_LOG_NOTE, "sql plugin: no result found"); 571 return -1; 572 } 573 574 /* XXX: Duplication cannot be found by this method. */ 575 576 /* now get the result set value and value_len */ 577 /* we only fetch one because we don't care about the rest */ 578 if (value) { 579 strncpy(value, result, size - 2); 580 value[size - 1] = '\0'; 581 if (value_len) { 582 *value_len = strlen(value); 583 } 584 } 585 586 free(result); 587 return 0; 588} 589 590static int _sqlite3_begin_txn(void *db, const sasl_utils_t *utils) 591{ 592 return _sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, 0, NULL, utils); 593} 594 595static int _sqlite3_commit_txn(void *db, const sasl_utils_t *utils) 596{ 597 return _sqlite3_exec(db, "COMMIT TRANSACTION;", NULL, 0, NULL, utils); 598} 599 600static int _sqlite3_rollback_txn(void *db, const sasl_utils_t *utils) 601{ 602 return _sqlite3_exec(db, "ROLLBACK TRANSACTION;", NULL, 0, NULL, utils); 603} 604 605static void _sqlite3_close(void *db) 606{ 607 sqlite3_close((sqlite3*)db); 608} 609#endif /* HAVE_SQLITE3 */ 610 611static const sql_engine_t sql_engines[] = { 612#ifdef HAVE_MYSQL 613 { "mysql", &_mysql_open, &_mysql_escape_str, 614 &_mysql_begin_txn, &_mysql_commit_txn, &_mysql_rollback_txn, 615 &_mysql_exec, &_mysql_close }, 616#endif /* HAVE_MYSQL */ 617#ifdef HAVE_PGSQL 618 { "pgsql", &_pgsql_open, &_pgsql_escape_str, 619 &_pgsql_begin_txn, &_pgsql_commit_txn, &_pgsql_rollback_txn, 620 &_pgsql_exec, &_pgsql_close }, 621#endif 622#ifdef HAVE_SQLITE 623 { "sqlite", &_sqlite_open, &_sqlite_escape_str, 624 &_sqlite_begin_txn, &_sqlite_commit_txn, &_sqlite_rollback_txn, 625 &_sqlite_exec, &_sqlite_close }, 626#endif 627#ifdef HAVE_SQLITE3 628 { "sqlite3", &_sqlite3_open, &_sqlite3_escape_str, 629 &_sqlite3_begin_txn, &_sqlite3_commit_txn, &_sqlite3_rollback_txn, 630 &_sqlite3_exec, &_sqlite3_close }, 631#endif 632 { NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL } 633}; 634 635/* 636** Sql_create_statement 637** uses statement line and allocate memory to replace 638** Parts with the strings provided. 639** %<char> = no change 640** %% = % 641** %u = user 642** %p = prop 643** %r = realm 644** %v = value of prop 645** e.g select %p from auth where user = %u and domain = %r; 646** Note: calling function must free memory. 647** 648*/ 649 650static char *sql_create_statement(const char *statement, const char *prop, 651 const char *user, const char *realm, 652 const char *value, 653 const sasl_utils_t *utils) 654{ 655 const char *ptr, *line_ptr; 656 char *buf, *buf_ptr; 657 int filtersize; 658 int ulen, plen, rlen, vlen; 659 int numpercents=0; 660 int biggest; 661 size_t i; 662 663 /* calculate memory needed for creating the complete query string. */ 664 ulen = (int)strlen(user); 665 rlen = (int)strlen(realm); 666 plen = (int)strlen(prop); 667 vlen = (int)sql_len(value); 668 669 /* what if we have multiple %foo occurrences in the input query? */ 670 for (i = 0; i < strlen(statement); i++) { 671 if (statement[i] == '%') { 672 numpercents++; 673 } 674 } 675 676 /* find the biggest of ulen, rlen, plen, vlen */ 677 biggest = sql_max(sql_max(ulen, rlen), sql_max(plen, vlen)); 678 679 /* plus one for the semicolon...and don't forget the trailing 0x0 */ 680 filtersize = (int)strlen(statement) + 1 + (numpercents*biggest)+1; 681 682 /* ok, now try to allocate a chunk of that size */ 683 buf = (char *) utils->malloc(filtersize); 684 685 if (!buf) { 686 MEMERROR(utils); 687 return NULL; 688 } 689 690 buf_ptr = buf; 691 line_ptr = statement; 692 693 /* replace the strings */ 694 while ( (ptr = strchr(line_ptr, '%')) ) { 695 /* copy up to but not including the next % */ 696 memcpy(buf_ptr, line_ptr, ptr - line_ptr); 697 buf_ptr += ptr - line_ptr; 698 ptr++; 699 switch (ptr[0]) { 700 case '%': 701 buf_ptr[0] = '%'; 702 buf_ptr++; 703 break; 704 case 'u': 705 memcpy(buf_ptr, user, ulen); 706 buf_ptr += ulen; 707 break; 708 case 'r': 709 memcpy(buf_ptr, realm, rlen); 710 buf_ptr += rlen; 711 break; 712 case 'p': 713 memcpy(buf_ptr, prop, plen); 714 buf_ptr += plen; 715 break; 716 case 'v': 717 if (value != NULL) { 718 memcpy(buf_ptr, value, vlen); 719 buf_ptr += vlen; 720 } 721 else { 722 utils->log(NULL, SASL_LOG_ERR, 723 "'%%v' shouldn't be in a SELECT or DELETE"); 724 } 725 break; 726 default: 727 buf_ptr[0] = '%'; 728 buf_ptr[1] = ptr[0]; 729 buf_ptr += 2; 730 break; 731 } 732 ptr++; 733 line_ptr = ptr; 734 } 735 736 memcpy(buf_ptr, line_ptr, strlen(line_ptr)+1); 737 /* Make sure the current portion of the statement ends with a semicolon */ 738 if (buf_ptr[strlen(buf_ptr-1)] != ';') { 739 strcat(buf_ptr, ";"); 740 } 741 742 return (buf); 743} 744 745/* sql_get_settings 746 * 747 * Get the auxprop settings and put them in the global context array 748*/ 749static void sql_get_settings(const sasl_utils_t *utils, void *glob_context) 750{ 751 sql_settings_t *settings; 752 int r; 753 const char *usessl, *engine_name; 754 const sql_engine_t *e; 755 756 settings = (sql_settings_t *) glob_context; 757 758 r = utils->getopt(utils->getopt_context,"SQL", "sql_engine", 759 &engine_name, NULL); 760 if (r || !engine_name) { 761 engine_name = "mysql"; 762 } 763 764 /* find the correct engine */ 765 e = sql_engines; 766 while (e->name) { 767 if (!strcasecmp(engine_name, e->name)) break; 768 e++; 769 } 770 771 if (!e->name) { 772 utils->log(NULL, SASL_LOG_ERR, "SQL engine '%s' not supported", 773 engine_name); 774 } 775 776 settings->sql_engine = e; 777 778 r = utils->getopt(utils->getopt_context,"SQL","sql_user", 779 &settings->sql_user, NULL); 780 if ( r || !settings->sql_user ) { 781 settings->sql_user = SQL_BLANK_STRING; 782 } 783 784 r = utils->getopt(utils->getopt_context,"SQL", "sql_passwd", 785 &settings->sql_passwd, NULL); 786 if (r || !settings->sql_passwd ) { 787 settings->sql_passwd = SQL_BLANK_STRING; 788 } 789 790 r = utils->getopt(utils->getopt_context,"SQL", "sql_hostnames", 791 &settings->sql_hostnames, NULL); 792 if (r || !settings->sql_hostnames ) { 793 settings->sql_hostnames = SQL_BLANK_STRING; 794 } 795 796 r = utils->getopt(utils->getopt_context,"SQL", "sql_database", 797 &settings->sql_database, NULL); 798 if (r || !settings->sql_database ) { 799 settings->sql_database = SQL_BLANK_STRING; 800 } 801 802 r = utils->getopt(utils->getopt_context,"SQL", "sql_select", 803 &settings->sql_select, NULL); 804 if (r || !settings->sql_select ) { 805 /* backwards compatibility */ 806 r = utils->getopt(utils->getopt_context,"SQL", "sql_statement", 807 &settings->sql_select, NULL); 808 if (r || !settings->sql_select) { 809 settings->sql_select = SQL_BLANK_STRING; 810 } 811 } 812 813 r = utils->getopt(utils->getopt_context, "SQL", "sql_insert", 814 &settings->sql_insert, NULL); 815 if (r || !settings->sql_insert) { 816 settings->sql_insert = SQL_BLANK_STRING; 817 } 818 819 r = utils->getopt(utils->getopt_context, "SQL", "sql_update", 820 &settings->sql_update, NULL); 821 if (r || !settings->sql_update) { 822 settings->sql_update = SQL_BLANK_STRING; 823 } 824 825 r = utils->getopt(utils->getopt_context, "SQL", "sql_usessl", 826 &usessl, NULL); 827 if (r || !usessl) usessl = "no"; 828 829 if (*usessl == '1' || *usessl == 'y' || *usessl == 't' || 830 (*usessl == 'o' && usessl[1] == 'n')) { 831 settings->sql_usessl = 1; 832 } else { 833 settings->sql_usessl = 0; 834 } 835} 836 837static void *sql_connect(sql_settings_t *settings, const sasl_utils_t *utils) 838{ 839 void *conn = NULL; 840 char *db_host_ptr = NULL; 841 char *db_host = NULL; 842 char *cur_host, *cur_port; 843 844 /* loop around hostnames till we get a connection 845 * it should probably save the connection but for 846 * now we will just disconnect everytime 847 */ 848 utils->log(NULL, SASL_LOG_DEBUG, 849 "sql plugin try and connect to a host\n"); 850 851 /* create a working version of the hostnames */ 852 _plug_strdup(utils, settings->sql_hostnames, &db_host_ptr, NULL); 853 db_host = db_host_ptr; 854 cur_host = db_host; 855 while (cur_host != NULL) { 856 db_host = strchr(db_host,','); 857 if (db_host != NULL) { 858 db_host[0] = '\0'; 859 860 /* loop till we find some text */ 861 while (!isalnum(db_host[0])) db_host++; 862 } 863 864 utils->log(NULL, SASL_LOG_DEBUG, 865 "sql plugin trying to open db '%s' on host '%s'%s\n", 866 settings->sql_database, cur_host, 867 settings->sql_usessl ? " using SSL" : ""); 868 869 /* set the optional port */ 870 if ((cur_port = strchr(cur_host, ':'))) *cur_port++ = '\0'; 871 872 conn = settings->sql_engine->sql_open(cur_host, cur_port, 873 settings->sql_usessl, 874 settings->sql_user, 875 settings->sql_passwd, 876 settings->sql_database, 877 utils); 878 if (conn) break; 879 880 utils->log(NULL, SASL_LOG_ERR, 881 "sql plugin could not connect to host %s", cur_host); 882 883 cur_host = db_host; 884 } 885 886 if (db_host_ptr) utils->free(db_host_ptr); 887 888 return conn; 889} 890 891static int sql_auxprop_lookup(void *glob_context, 892 sasl_server_params_t *sparams, 893 unsigned flags, 894 const char *user, 895 unsigned ulen) 896{ 897 char *userid = NULL; 898 /* realm could be used for something clever */ 899 char *realm = NULL; 900 const char *user_realm = NULL; 901 const struct propval *to_fetch, *cur; 902 char value[8192]; 903 size_t value_len; 904 char *user_buf; 905 char *query = NULL; 906 char *escap_userid = NULL; 907 char *escap_realm = NULL; 908 sql_settings_t *settings; 909 int verify_against_hashed_password; 910 int saw_user_password = 0; 911 void *conn = NULL; 912 int do_txn = 0; 913 int ret; 914 915 if (!glob_context || !sparams || !user) return SASL_BADPARAM; 916 917 /* setup the settings */ 918 settings = (sql_settings_t *) glob_context; 919 920 sparams->utils->log(NULL, SASL_LOG_DEBUG, 921 "sql plugin Parse the username %s\n", user); 922 923 user_buf = sparams->utils->malloc(ulen + 1); 924 if (!user_buf) { 925 ret = SASL_NOMEM; 926 goto done; 927 } 928 929 memcpy(user_buf, user, ulen); 930 user_buf[ulen] = '\0'; 931 932 if(sparams->user_realm) { 933 user_realm = sparams->user_realm; 934 } else { 935 user_realm = sparams->serverFQDN; 936 } 937 938 if ((ret = _plug_parseuser(sparams->utils, 939 &userid, 940 &realm, 941 user_realm, 942 sparams->serverFQDN, 943 user_buf)) != SASL_OK ) { 944 goto done; 945 } 946 947 /* just need to escape userid and realm now */ 948 /* allocate some memory */ 949 escap_userid = (char *)sparams->utils->malloc(strlen(userid)*2+1); 950 escap_realm = (char *)sparams->utils->malloc(strlen(realm)*2+1); 951 952 if (!escap_userid || !escap_realm) { 953 ret = SASL_NOMEM; 954 goto done; 955 } 956 957 /*************************************/ 958 959 /* find out what we need to get */ 960 /* this corrupts const char *user */ 961 to_fetch = sparams->utils->prop_get(sparams->propctx); 962 if (!to_fetch) { 963 ret = SASL_NOMEM; 964 goto done; 965 } 966 967 conn = sql_connect(settings, sparams->utils); 968 if (!conn) { 969 sparams->utils->log(NULL, SASL_LOG_ERR, 970 "sql plugin couldn't connect to any host\n"); 971 /* TODO: in the future we might want to extend the internal 972 SQL driver API to return a more detailed error */ 973 ret = SASL_FAIL; 974 goto done; 975 } 976 977 /* escape out */ 978 settings->sql_engine->sql_escape_str(escap_userid, userid); 979 settings->sql_engine->sql_escape_str(escap_realm, realm); 980 981 verify_against_hashed_password = flags & SASL_AUXPROP_VERIFY_AGAINST_HASH; 982 983 /* Assume that nothing is found */ 984 ret = SASL_NOUSER; 985 for (cur = to_fetch; cur->name; cur++) { 986 char *realname = (char *) cur->name; 987 988 /* Only look up properties that apply to this lookup! */ 989 if (cur->name[0] == '*' 990 && (flags & SASL_AUXPROP_AUTHZID)) 991 continue; 992 if (!(flags & SASL_AUXPROP_AUTHZID)) { 993 if(cur->name[0] != '*') 994 continue; 995 else 996 realname = (char*)cur->name + 1; 997 } 998 999 /* If it's there already, we want to see if it needs to be 1000 * overridden. userPassword is a special case, because it's value 1001 is always present if SASL_AUXPROP_VERIFY_AGAINST_HASH is specified. 1002 When SASL_AUXPROP_VERIFY_AGAINST_HASH is set, we just clear userPassword. */ 1003 if (cur->values && !(flags & SASL_AUXPROP_OVERRIDE) && 1004 (verify_against_hashed_password == 0 || 1005 strcasecmp(realname, SASL_AUX_PASSWORD_PROP) != 0)) { 1006 continue; 1007 } else if (cur->values) { 1008 sparams->utils->prop_erase(sparams->propctx, cur->name); 1009 } 1010 1011 if (strcasecmp(realname, SASL_AUX_PASSWORD_PROP) == 0) { 1012 saw_user_password = 1; 1013 } 1014 1015 if (!do_txn) { 1016 do_txn = 1; 1017 sparams->utils->log(NULL, SASL_LOG_DEBUG, "begin transaction"); 1018 if (settings->sql_engine->sql_begin_txn(conn, sparams->utils)) { 1019 sparams->utils->log(NULL, SASL_LOG_ERR, 1020 "Unable to begin transaction\n"); 1021 } 1022 } 1023 1024 sparams->utils->log(NULL, SASL_LOG_DEBUG, 1025 "sql plugin create statement from %s %s %s\n", 1026 realname, escap_userid, escap_realm); 1027 1028 /* create a statement that we will use */ 1029 query = sql_create_statement(settings->sql_select, 1030 realname,escap_userid, 1031 escap_realm, NULL, 1032 sparams->utils); 1033 if (query == NULL) { 1034 ret = SASL_NOMEM; 1035 break; 1036 } 1037 1038 sparams->utils->log(NULL, SASL_LOG_DEBUG, 1039 "sql plugin doing query %s\n", query); 1040 1041 value[0] = '\0'; 1042 value_len = 0; 1043 /* run the query */ 1044 if (!settings->sql_engine->sql_exec(conn, query, value, sizeof(value), 1045 &value_len, sparams->utils)) { 1046 sparams->utils->prop_set(sparams->propctx, 1047 cur->name, 1048 value, 1049 (int)value_len); 1050 ret = SASL_OK; 1051 } 1052 1053 sparams->utils->free(query); 1054 } 1055 1056 if (flags & SASL_AUXPROP_AUTHZID) { 1057 /* This is a lie, but the caller can't handle 1058 when we return SASL_NOUSER for authorization identity lookup. */ 1059 if (ret == SASL_NOUSER) { 1060 ret = SASL_OK; 1061 } 1062 } else { 1063 if (ret == SASL_NOUSER && saw_user_password == 0) { 1064 /* Verify user existence by checking presence of 1065 the userPassword attribute */ 1066 if (!do_txn) { 1067 do_txn = 1; 1068 sparams->utils->log(NULL, SASL_LOG_DEBUG, "begin transaction"); 1069 if (settings->sql_engine->sql_begin_txn(conn, sparams->utils)) { 1070 sparams->utils->log(NULL, SASL_LOG_ERR, 1071 "Unable to begin transaction\n"); 1072 } 1073 } 1074 1075 sparams->utils->log(NULL, SASL_LOG_DEBUG, 1076 "sql plugin create statement from %s %s %s\n", 1077 SASL_AUX_PASSWORD_PROP, 1078 escap_userid, 1079 escap_realm); 1080 1081 /* create a statement that we will use */ 1082 query = sql_create_statement(settings->sql_select, 1083 SASL_AUX_PASSWORD_PROP, 1084 escap_userid, 1085 escap_realm, 1086 NULL, 1087 sparams->utils); 1088 if (query == NULL) { 1089 ret = SASL_NOMEM; 1090 } else { 1091 sparams->utils->log(NULL, SASL_LOG_DEBUG, 1092 "sql plugin doing query %s\n", query); 1093 1094 value[0] = '\0'; 1095 value_len = 0; 1096 /* run the query */ 1097 if (!settings->sql_engine->sql_exec(conn, 1098 query, 1099 value, 1100 sizeof(value), 1101 &value_len, 1102 sparams->utils)) { 1103 ret = SASL_OK; 1104 } 1105 1106 sparams->utils->free(query); 1107 } 1108 } 1109 } 1110 1111 1112 if (do_txn) { 1113 sparams->utils->log(NULL, SASL_LOG_DEBUG, "commit transaction"); 1114 if (settings->sql_engine->sql_commit_txn(conn, sparams->utils)) { 1115 sparams->utils->log(NULL, SASL_LOG_ERR, 1116 "Unable to commit transaction\n"); 1117 /* Failure of the commit is non fatal when reading values */ 1118 } 1119 } 1120 1121 done: 1122 if (escap_userid) sparams->utils->free(escap_userid); 1123 if (escap_realm) sparams->utils->free(escap_realm); 1124 if (conn) settings->sql_engine->sql_close(conn); 1125 if (userid) sparams->utils->free(userid); 1126 if (realm) sparams->utils->free(realm); 1127 if (user_buf) sparams->utils->free(user_buf); 1128 1129 return (ret); 1130} 1131 1132static int sql_auxprop_store(void *glob_context, 1133 sasl_server_params_t *sparams, 1134 struct propctx *ctx, 1135 const char *user, 1136 unsigned ulen) 1137{ 1138 char *userid = NULL; 1139 char *realm = NULL; 1140 const char *user_realm = NULL; 1141 int ret = SASL_FAIL; 1142 const struct propval *to_store, *cur; 1143 1144 char *user_buf; 1145 char *statement = NULL; 1146 char *escap_userid = NULL; 1147 char *escap_realm = NULL; 1148 const char *cmd; 1149 1150 sql_settings_t *settings; 1151 void *conn = NULL; 1152 1153 settings = (sql_settings_t *) glob_context; 1154 1155 /* just checking if we are enabled */ 1156 if (!ctx && 1157 sql_exists(settings->sql_insert) && 1158 sql_exists(settings->sql_update)) return SASL_OK; 1159 1160 /* make sure our input is okay */ 1161 if (!glob_context || !sparams || !user) return SASL_BADPARAM; 1162 1163 sparams->utils->log(NULL, SASL_LOG_DEBUG, 1164 "sql plugin Parse the username %s\n", user); 1165 1166 user_buf = sparams->utils->malloc(ulen + 1); 1167 if (!user_buf) { 1168 ret = SASL_NOMEM; 1169 goto done; 1170 } 1171 1172 memcpy(user_buf, user, ulen); 1173 user_buf[ulen] = '\0'; 1174 1175 if (sparams->user_realm) { 1176 user_realm = sparams->user_realm; 1177 } 1178 else { 1179 user_realm = sparams->serverFQDN; 1180 } 1181 1182 ret = _plug_parseuser(sparams->utils, &userid, &realm, user_realm, 1183 sparams->serverFQDN, user_buf); 1184 if (ret != SASL_OK) goto done; 1185 1186 /* just need to escape userid and realm now */ 1187 /* allocate some memory */ 1188 1189 escap_userid = (char *) sparams->utils->malloc(strlen(userid)*2+1); 1190 escap_realm = (char *) sparams->utils->malloc(strlen(realm)*2+1); 1191 1192 if (!escap_userid || !escap_realm) { 1193 MEMERROR(sparams->utils); 1194 goto done; 1195 } 1196 1197 to_store = sparams->utils->prop_get(ctx); 1198 1199 if (!to_store) { 1200 ret = SASL_BADPARAM; 1201 goto done; 1202 } 1203 1204 conn = sql_connect(settings, sparams->utils); 1205 if (!conn) { 1206 sparams->utils->log(NULL, SASL_LOG_ERR, 1207 "sql plugin couldn't connect to any host\n"); 1208 goto done; 1209 } 1210 1211 settings->sql_engine->sql_escape_str(escap_userid, userid); 1212 settings->sql_engine->sql_escape_str(escap_realm, realm); 1213 1214 if (settings->sql_engine->sql_begin_txn(conn, sparams->utils)) { 1215 sparams->utils->log(NULL, SASL_LOG_ERR, 1216 "Unable to begin transaction\n"); 1217 } 1218 for (cur = to_store; ret == SASL_OK && cur->name; cur++) { 1219 1220 if (cur->name[0] == '*') { 1221 continue; 1222 } 1223 1224 /* determine which command we need */ 1225 /* see if we already have a row for this user */ 1226 statement = sql_create_statement(settings->sql_select, 1227 SQL_WILDCARD, escap_userid, 1228 escap_realm, NULL, 1229 sparams->utils); 1230 if (!settings->sql_engine->sql_exec(conn, statement, NULL, 0, NULL, 1231 sparams->utils)) { 1232 /* already have a row => UPDATE */ 1233 cmd = settings->sql_update; 1234 } else { 1235 /* new row => INSERT */ 1236 cmd = settings->sql_insert; 1237 } 1238 sparams->utils->free(statement); 1239 1240 /* create a statement that we will use */ 1241 statement = sql_create_statement(cmd, cur->name, escap_userid, 1242 escap_realm, 1243 cur->values && cur->values[0] ? 1244 cur->values[0] : SQL_NULL_VALUE, 1245 sparams->utils); 1246 1247 { 1248 char *log_statement = 1249 sql_create_statement(cmd, cur->name, 1250 escap_userid, 1251 escap_realm, 1252 cur->values && cur->values[0] ? 1253 "<omitted>" : SQL_NULL_VALUE, 1254 sparams->utils); 1255 sparams->utils->log(NULL, SASL_LOG_DEBUG, 1256 "sql plugin doing statement %s\n", 1257 log_statement); 1258 sparams->utils->free(log_statement); 1259 } 1260 1261 /* run the statement */ 1262 if (settings->sql_engine->sql_exec(conn, statement, NULL, 0, NULL, 1263 sparams->utils)) { 1264 ret = SASL_FAIL; 1265 } 1266 1267 sparams->utils->free(statement); 1268 } 1269 if (ret != SASL_OK) { 1270 sparams->utils->log(NULL, SASL_LOG_ERR, 1271 "Failed to store auxprop; aborting transaction\n"); 1272 if (settings->sql_engine->sql_rollback_txn(conn, sparams->utils)) { 1273 sparams->utils->log(NULL, SASL_LOG_ERR, 1274 "Unable to rollback transaction\n"); 1275 } 1276 } 1277 else if (settings->sql_engine->sql_commit_txn(conn, sparams->utils)) { 1278 sparams->utils->log(NULL, SASL_LOG_ERR, 1279 "Unable to commit transaction\n"); 1280 } 1281 1282 done: 1283 if (escap_userid) sparams->utils->free(escap_userid); 1284 if (escap_realm) sparams->utils->free(escap_realm); 1285 if (conn) settings->sql_engine->sql_close(conn); 1286 if (userid) sparams->utils->free(userid); 1287 if (realm) sparams->utils->free(realm); 1288 if (user_buf) sparams->utils->free(user_buf); 1289 1290 return ret; 1291 1292 /* do a little dance */ 1293} 1294 1295 1296static void sql_auxprop_free(void *glob_context, const sasl_utils_t *utils) 1297{ 1298 sql_settings_t *settings; 1299 1300 settings = (sql_settings_t *)glob_context; 1301 1302 if (!settings) return; 1303 1304 utils->log(NULL, SASL_LOG_DEBUG, "sql freeing memory\n"); 1305 1306 utils->free(settings); 1307} 1308 1309static sasl_auxprop_plug_t sql_auxprop_plugin = { 1310 0, /* Features */ 1311 0, /* spare */ 1312 NULL, /* glob_context */ 1313 sql_auxprop_free, /* auxprop_free */ 1314 sql_auxprop_lookup, /* auxprop_lookup */ 1315 "sql", /* name */ 1316 sql_auxprop_store /* auxprop_store */ 1317}; 1318 1319int sql_auxprop_plug_init(const sasl_utils_t *utils, 1320 int max_version, 1321 int *out_version, 1322 sasl_auxprop_plug_t **plug, 1323 const char *plugname __attribute__((unused))) 1324{ 1325 sql_settings_t *settings; 1326 1327 if (!out_version || !plug) return SASL_BADPARAM; 1328 1329 if (max_version < SASL_AUXPROP_PLUG_VERSION) return SASL_BADVERS; 1330 *out_version = SASL_AUXPROP_PLUG_VERSION; 1331 1332 *plug = &sql_auxprop_plugin; 1333 1334 settings = (sql_settings_t *) utils->malloc(sizeof(sql_settings_t)); 1335 1336 if (!settings) { 1337 MEMERROR(utils); 1338 return SASL_NOMEM; 1339 } 1340 1341 memset(settings, 0, sizeof(sql_settings_t)); 1342 sql_get_settings(utils, settings); 1343 1344 if (!settings->sql_engine->name) return SASL_NOMECH; 1345 1346 if (!sql_exists(settings->sql_select)) { 1347 utils->log(NULL, SASL_LOG_ERR, "sql_select option missing"); 1348 utils->free(settings); 1349 return SASL_NOMECH; 1350 } 1351 1352 utils->log(NULL, SASL_LOG_DEBUG, 1353 "sql auxprop plugin using %s engine\n", 1354 settings->sql_engine->name); 1355 1356 sql_auxprop_plugin.glob_context = settings; 1357 1358 return SASL_OK; 1359} 1360