source3/passdb/pdb_sql.c

   1 /*
   2  * Common PDB SQL backend functions
   3  * Copyright (C) Jelmer Vernooij 2003-2004
   4  *
   5  * This program is free software; you can redistribute it and/or modify it under
   6  * the terms of the GNU General Public License as published by the Free
   7  * Software Foundation; either version 2 of the License, or (at your option)
   8  * any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful, but WITHOUT
  11  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
  12  * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
  13  * more details.
  14  *
  15  * You should have received a copy of the GNU General Public License along with
  16  * this program; if not, write to the Free Software Foundation, Inc., 675
  17  * Mass Ave, Cambridge, MA 02139, USA.
  18  */
  19
  20 #include "includes.h"
  21
  22 #define CONFIG_TABLE_DEFAULT                            "user"
  23 #define CONFIG_LOGON_TIME_DEFAULT                       "logon_time"
  24 #define CONFIG_LOGOFF_TIME_DEFAULT                      "logoff_time"
  25 #define CONFIG_KICKOFF_TIME_DEFAULT                     "kickoff_time"
  26 #define CONFIG_PASS_LAST_SET_TIME_DEFAULT               "pass_last_set_time"
  27 #define CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT             "pass_can_change_time"
  28 #define CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT            "pass_must_change_time"
  29 #define CONFIG_USERNAME_DEFAULT                         "username"
  30 #define CONFIG_DOMAIN_DEFAULT                           "domain"
  31 #define CONFIG_NT_USERNAME_DEFAULT                      "nt_username"
  32 #define CONFIG_FULLNAME_DEFAULT                         "nt_fullname"
  33 #define CONFIG_HOME_DIR_DEFAULT                         "home_dir"
  34 #define CONFIG_DIR_DRIVE_DEFAULT                        "dir_drive"
  35 #define CONFIG_LOGON_SCRIPT_DEFAULT                     "logon_script"
  36 #define CONFIG_PROFILE_PATH_DEFAULT                     "profile_path"
  37 #define CONFIG_ACCT_DESC_DEFAULT                        "acct_desc"
  38 #define CONFIG_WORKSTATIONS_DEFAULT                     "workstations"
  39 #define CONFIG_UNKNOWN_STR_DEFAULT                      "unknown_str"
  40 #define CONFIG_MUNGED_DIAL_DEFAULT                      "munged_dial"
  41 #define CONFIG_USER_SID_DEFAULT                         "user_sid"
  42 #define CONFIG_GROUP_SID_DEFAULT                        "group_sid"
  43 #define CONFIG_LM_PW_DEFAULT                            "lm_pw"
  44 #define CONFIG_NT_PW_DEFAULT                            "nt_pw"
  45 #define CONFIG_PLAIN_PW_DEFAULT                         "NULL"
  46 #define CONFIG_ACCT_CTRL_DEFAULT                        "acct_ctrl"
  47 #define CONFIG_LOGON_DIVS_DEFAULT                       "logon_divs"
  48 #define CONFIG_HOURS_LEN_DEFAULT                        "hours_len"
  49 #define CONFIG_BAD_PASSWORD_COUNT_DEFAULT               "bad_password_count"
  50 #define CONFIG_LOGON_COUNT_DEFAULT                      "logon_count"
  51 #define CONFIG_UNKNOWN_6_DEFAULT                        "unknown_6"
  52
  53 /* Used to construct insert and update queries */
  54
  55 typedef struct pdb_sql_query {
  56         char update;
  57         TALLOC_CTX *mem_ctx;
  58         char *part1;
  59         char *part2;
  60 } pdb_sql_query;
  61
  62 static void pdb_sql_int_field(struct pdb_sql_query *q, const char *name, int value)
  63 {
  64         if (!name || strchr(name, '\''))
  65                 return;                 /* This field shouldn't be set by us */
  66
  67         if (q->update) {
  68                 q->part1 =
  69                         talloc_asprintf_append(q->mem_ctx, q->part1,
  70                                                                    "%s = %d,", name, value);
  71         } else {
  72                 q->part1 =
  73                         talloc_asprintf_append(q->mem_ctx, q->part1, "%s,", name);
  74                 q->part2 =
  75                         talloc_asprintf_append(q->mem_ctx, q->part2, "%d,", value);
  76         }
  77 }
  78
  79 char *sql_escape_string(const char *unesc)
  80 {
  81         char *esc = SMB_MALLOC(strlen(unesc) * 2 + 3);
  82         size_t pos_unesc = 0, pos_esc = 0;
  83
  84         for(pos_unesc = 0; unesc[pos_unesc]; pos_unesc++) {
  85                 switch(unesc[pos_unesc]) {
  86                 case '\\':
  87                 case '\'':
  88                 case '"':
  89                         esc[pos_esc] = '\\'; pos_esc++;
  90                 default:
  91                         esc[pos_esc] = unesc[pos_unesc]; pos_esc++;
  92                         break;
  93                 }
  94         }
  95
  96         esc[pos_esc] = '\0';
  97
  98         return esc;
  99 }
 100
 101 static NTSTATUS pdb_sql_string_field(struct pdb_sql_query *q,
 102                                            const char *name, const char *value)
 103 {
 104         char *esc_value;
 105
 106         if (!name || !value || !strcmp(value, "") || strchr(name, '\''))
 107                 return NT_STATUS_INVALID_PARAMETER;   /* This field shouldn't be set by module */
 108
 109         esc_value = sql_escape_string(value);
 110
 111         if (q->update) {
 112                 q->part1 =
 113                         talloc_asprintf_append(q->mem_ctx, q->part1,
 114                                                                    "%s = '%s',", name, esc_value);
 115         } else {
 116                 q->part1 =
 117                         talloc_asprintf_append(q->mem_ctx, q->part1, "%s,", name);
 118                 q->part2 =
 119                         talloc_asprintf_append(q->mem_ctx, q->part2, "'%s',",
 120                                                                    esc_value);
 121         }
 122
 123         SAFE_FREE(esc_value);
 124
 125         return NT_STATUS_OK;
 126 }
 127
 128 #define config_value(data,name,default_value) \
 129         lp_parm_const_string(GLOBAL_SECTION_SNUM, data, name, default_value)
 130
 131 static const char * config_value_write(const char *location, const char *name, const char *default_value)
 132 {
 133         char const *v = NULL;
 134         char const *swrite = NULL;
 135
 136         v = lp_parm_const_string(GLOBAL_SECTION_SNUM, location, name, default_value);
 137
 138         if (!v)
 139                 return NULL;
 140
 141         swrite = strrchr(v, ':');
 142
 143         /* Default to the same field as read field */
 144         if (!swrite) {
 145
 146                 /* Updating NULL does not make much sense */
 147                 if (!strcmp(v, "NULL"))
 148                         return NULL;
 149
 150                 return v;
 151         }
 152
 153         swrite++;
 154
 155         /* If the field is 0 chars long, we shouldn't write to it */
 156         if (!strlen(swrite) || !strcmp(swrite, "NULL"))
 157                 return NULL;
 158
 159         /* Otherwise, use the additionally specified */
 160         return swrite;
 161 }
 162
 163 static const char * config_value_read(const char *location, const char *name, const char *default_value)
 164 {
 165         char *v = NULL;
 166         char *swrite;
 167
 168         v = lp_parm_talloc_string(GLOBAL_SECTION_SNUM, location, name, default_value);
 169
 170         if (!v)
 171                 return "NULL";
 172
 173         swrite = strrchr(v, ':');
 174
 175         /* If no write is specified, there are no problems */
 176         if (!swrite) {
 177                 if (strlen(v) == 0)
 178                         return "NULL";
 179                 return (const char *)v;
 180         }
 181
 182         /* Otherwise, we have to cut the ':write_part' */
 183         *swrite = '\0';
 184         if (strlen(v) == 0)
 185                 return "NULL";
 186
 187         return (const char *)v;
 188 }
 189
 190 char *sql_account_query_select(const char *data, BOOL update, enum sql_search_field field, const char *value)
 191 {
 192         const char *field_string;
 193         char *query;
 194
 195         switch(field) {
 196         case SQL_SEARCH_NONE:
 197                 field_string = "'1'";
 198                 value = "1";
 199                 break;
 200
 201         case SQL_SEARCH_USER_SID:
 202                 field_string = config_value_read(data, "user sid column",
 203                                                                                  CONFIG_USER_SID_DEFAULT);
 204                 break;
 205
 206         case SQL_SEARCH_USER_NAME:
 207                 field_string = config_value_read(data, "username column",
 208                                                                                  CONFIG_USERNAME_DEFAULT);
 209                 break;
 210         default:
 211                 field_string = "unknown";
 212                 break;
 213         }
 214
 215         asprintf(&query,
 216                          "SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s WHERE %s = '%s'",
 217                          config_value_read(data, "logon time column",
 218                                                            CONFIG_LOGON_TIME_DEFAULT),
 219                          config_value_read(data, "logoff time column",
 220                                                            CONFIG_LOGOFF_TIME_DEFAULT),
 221                          config_value_read(data, "kickoff time column",
 222                                                            CONFIG_KICKOFF_TIME_DEFAULT),
 223                          config_value_read(data, "pass last set time column",
 224                                                            CONFIG_PASS_LAST_SET_TIME_DEFAULT),
 225                          config_value_read(data, "pass can change time column",
 226                                                            CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
 227                          config_value_read(data, "pass must change time column",
 228                                                            CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
 229                          config_value_read(data, "username column",
 230                                                            CONFIG_USERNAME_DEFAULT),
 231                          config_value_read(data, "domain column",
 232                                                            CONFIG_DOMAIN_DEFAULT),
 233                          config_value_read(data, "nt username column",
 234                                                            CONFIG_NT_USERNAME_DEFAULT),
 235                          config_value_read(data, "fullname column",
 236                                                            CONFIG_FULLNAME_DEFAULT),
 237                          config_value_read(data, "home dir column",
 238                                                            CONFIG_HOME_DIR_DEFAULT),
 239                          config_value_read(data, "dir drive column",
 240                                                            CONFIG_DIR_DRIVE_DEFAULT),
 241                          config_value_read(data, "logon script column",
 242                                                            CONFIG_LOGON_SCRIPT_DEFAULT),
 243                          config_value_read(data, "profile path column",
 244                                                            CONFIG_PROFILE_PATH_DEFAULT),
 245                          config_value_read(data, "acct desc column",
 246                                                            CONFIG_ACCT_DESC_DEFAULT),
 247                          config_value_read(data, "workstations column",
 248                                                            CONFIG_WORKSTATIONS_DEFAULT),
 249                          config_value_read(data, "unknown string column",
 250                                                            CONFIG_UNKNOWN_STR_DEFAULT),
 251                          config_value_read(data, "munged dial column",
 252                                                            CONFIG_MUNGED_DIAL_DEFAULT),
 253                          config_value_read(data, "user sid column",
 254                                                            CONFIG_USER_SID_DEFAULT),
 255                          config_value_read(data, "group sid column",
 256                                                            CONFIG_GROUP_SID_DEFAULT),
 257                          config_value_read(data, "lanman pass column",
 258                                                            CONFIG_LM_PW_DEFAULT),
 259                          config_value_read(data, "nt pass column",
 260                                                            CONFIG_NT_PW_DEFAULT),
 261                          config_value_read(data, "plain pass column",
 262                                                            CONFIG_PLAIN_PW_DEFAULT),
 263                          config_value_read(data, "acct ctrl column",
 264                                                            CONFIG_ACCT_CTRL_DEFAULT),
 265                          config_value_read(data, "logon divs column",
 266                                                            CONFIG_LOGON_DIVS_DEFAULT),
 267                          config_value_read(data, "hours len column",
 268                                                            CONFIG_HOURS_LEN_DEFAULT),
 269                          config_value_read(data, "bad password count column",
 270                                                            CONFIG_BAD_PASSWORD_COUNT_DEFAULT),
 271                          config_value_read(data, "logon count column",
 272                                                            CONFIG_LOGON_COUNT_DEFAULT),
 273                          config_value_read(data, "unknown 6 column",
 274                                                            CONFIG_UNKNOWN_6_DEFAULT),
 275                          config_value(data, "table", CONFIG_TABLE_DEFAULT),
 276                          field_string, value
 277                                  );
 278          return query;
 279 }
 280
 281 char *sql_account_query_delete(const char *data, const char *esc)
 282 {
 283         char *query;
 284
 285         asprintf(&query, "DELETE FROM %s WHERE %s = '%s'",
 286                          config_value(data, "table", CONFIG_TABLE_DEFAULT),
 287                          config_value_read(data, "username column",
 288                                                            CONFIG_USERNAME_DEFAULT), esc);
 289         return query;
 290 }
 291
 292 char *sql_account_query_update(const char *location, const SAM_ACCOUNT *newpwd, char isupdate)
 293 {
 294         char *ret;
 295         pstring temp;
 296         pdb_sql_query query;
 297         fstring sid_str;
 298
 299         query.update = isupdate;
 300
 301         /* I know this is somewhat overkill but only the talloc
 302          * functions have asprint_append and the 'normal' asprintf
 303          * is a GNU extension */
 304         query.mem_ctx = talloc_init("sql_query_update");
 305         query.part2 = talloc_asprintf(query.mem_ctx, "%s", "");
 306         if (query.update) {
 307                 query.part1 =
 308                         talloc_asprintf(query.mem_ctx, "UPDATE %s SET ",
 309                                                         config_value(location, "table",
 310                                                                                  CONFIG_TABLE_DEFAULT));
 311         } else {
 312                 query.part1 =
 313                         talloc_asprintf(query.mem_ctx, "INSERT INTO %s (",
 314                                                         config_value(location, "table",
 315                                                                                  CONFIG_TABLE_DEFAULT));
 316         }
 317
 318         if (IS_SAM_CHANGED(newpwd, PDB_ACCTCTRL)) {
 319                 pdb_sql_int_field(&query,
 320                                                 config_value_write(location, "acct ctrl column",
 321                                                                                    CONFIG_ACCT_CTRL_DEFAULT),
 322                                                 pdb_get_acct_ctrl(newpwd));
 323         }
 324
 325         if (IS_SAM_CHANGED(newpwd, PDB_LOGONTIME)) {
 326                 pdb_sql_int_field(&query,
 327                                                         config_value_write(location,
 328                                                                                            "logon time column",
 329                                                                                            CONFIG_LOGON_TIME_DEFAULT),
 330                                                         pdb_get_logon_time(newpwd));
 331         }
 332
 333         if (IS_SAM_CHANGED(newpwd, PDB_LOGOFFTIME)) {
 334                 pdb_sql_int_field(&query,
 335                                                         config_value_write(location,
 336                                                                                            "logoff time column",
 337                                                                                            CONFIG_LOGOFF_TIME_DEFAULT),
 338                                                         pdb_get_logoff_time(newpwd));
 339         }
 340
 341         if (IS_SAM_CHANGED(newpwd, PDB_KICKOFFTIME)) {
 342                 pdb_sql_int_field(&query,
 343                                                         config_value_write(location,
 344                                                                                            "kickoff time column",
 345                                                                                            CONFIG_KICKOFF_TIME_DEFAULT),
 346                                                         pdb_get_kickoff_time(newpwd));
 347         }
 348
 349         if (IS_SAM_CHANGED(newpwd, PDB_CANCHANGETIME)) {
 350                 pdb_sql_int_field(&query,
 351                                                         config_value_write(location,
 352                                                                                            "pass can change time column",
 353                                                                                            CONFIG_PASS_CAN_CHANGE_TIME_DEFAULT),
 354                                                         pdb_get_pass_can_change_time(newpwd));
 355         }
 356
 357         if (IS_SAM_CHANGED(newpwd, PDB_MUSTCHANGETIME)) {
 358                 pdb_sql_int_field(&query,
 359                                                         config_value_write(location,
 360                                                                                            "pass must change time column",
 361                                                                                            CONFIG_PASS_MUST_CHANGE_TIME_DEFAULT),
 362                                                         pdb_get_pass_must_change_time(newpwd));
 363         }
 364
 365         if (IS_SAM_CHANGED(newpwd, PDB_PASSLASTSET)) {
 366                 pdb_sql_int_field(&query,
 367                                                         config_value_write(location,
 368                                                                                            "pass last set time column",
 369                                                                                            CONFIG_PASS_LAST_SET_TIME_DEFAULT),
 370                                                         pdb_get_pass_last_set_time(newpwd));
 371         }
 372
 373         if (IS_SAM_CHANGED(newpwd, PDB_HOURSLEN)) {
 374                 pdb_sql_int_field(&query,
 375                                                         config_value_write(location,
 376                                                                                            "hours len column",
 377                                                                                            CONFIG_HOURS_LEN_DEFAULT),
 378                                                         pdb_get_hours_len(newpwd));
 379         }
 380
 381         if (IS_SAM_CHANGED(newpwd, PDB_LOGONDIVS)) {
 382                 pdb_sql_int_field(&query,
 383                                                         config_value_write(location,
 384                                                                                            "logon divs column",
 385                                                                                            CONFIG_LOGON_DIVS_DEFAULT),
 386                                                         pdb_get_logon_divs(newpwd));
 387         }
 388
 389         if (IS_SAM_CHANGED(newpwd, PDB_USERSID)) {
 390                 pdb_sql_string_field(&query,
 391                                                    config_value_write(location, "user sid column",
 392                                                                                           CONFIG_USER_SID_DEFAULT),
 393                                                    sid_to_string(sid_str,
 394                                                                                  pdb_get_user_sid(newpwd)));
 395         }
 396
 397         if (IS_SAM_CHANGED(newpwd, PDB_GROUPSID)) {
 398                 pdb_sql_string_field(&query,
 399                                                    config_value_write(location, "group sid column",
 400                                                                                           CONFIG_GROUP_SID_DEFAULT),
 401                                                    sid_to_string(sid_str,
 402                                                                                  pdb_get_group_sid(newpwd)));
 403         }
 404
 405         if (IS_SAM_CHANGED(newpwd, PDB_USERNAME)) {
 406                 pdb_sql_string_field(&query,
 407                                                    config_value_write(location, "username column",
 408                                                                                           CONFIG_USERNAME_DEFAULT),
 409                                                    pdb_get_username(newpwd));
 410         }
 411
 412         if (IS_SAM_CHANGED(newpwd, PDB_DOMAIN)) {
 413                 pdb_sql_string_field(&query,
 414                                                    config_value_write(location, "domain column",
 415                                                                                           CONFIG_DOMAIN_DEFAULT),
 416                                                    pdb_get_domain(newpwd));
 417         }
 418
 419         if (IS_SAM_CHANGED(newpwd, PDB_USERNAME)) {
 420                 pdb_sql_string_field(&query,
 421                                                    config_value_write(location,
 422                                                                                           "nt username column",
 423                                                                                           CONFIG_NT_USERNAME_DEFAULT),
 424                                                    pdb_get_nt_username(newpwd));
 425         }
 426
 427         if (IS_SAM_CHANGED(newpwd, PDB_FULLNAME)) {
 428                 pdb_sql_string_field(&query,
 429                                                    config_value_write(location, "fullname column",
 430                                                                                           CONFIG_FULLNAME_DEFAULT),
 431                                                    pdb_get_fullname(newpwd));
 432         }
 433
 434         if (IS_SAM_CHANGED(newpwd, PDB_LOGONSCRIPT)) {
 435                 pdb_sql_string_field(&query,
 436                                                    config_value_write(location,
 437                                                                                           "logon script column",
 438                                                                                           CONFIG_LOGON_SCRIPT_DEFAULT),
 439                                                    pdb_get_logon_script(newpwd));
 440         }
 441
 442         if (IS_SAM_CHANGED(newpwd, PDB_PROFILE)) {
 443                 pdb_sql_string_field(&query,
 444                                                    config_value_write(location,
 445                                                                                           "profile path column",
 446                                                                                           CONFIG_PROFILE_PATH_DEFAULT),
 447                                                    pdb_get_profile_path(newpwd));
 448         }
 449
 450         if (IS_SAM_CHANGED(newpwd, PDB_DRIVE)) {
 451                 pdb_sql_string_field(&query,
 452                                                    config_value_write(location, "dir drive column",
 453                                                                                           CONFIG_DIR_DRIVE_DEFAULT),
 454                                                    pdb_get_dir_drive(newpwd));
 455         }
 456
 457         if (IS_SAM_CHANGED(newpwd, PDB_SMBHOME)) {
 458                 pdb_sql_string_field(&query,
 459                                                    config_value_write(location, "home dir column",
 460                                                                                           CONFIG_HOME_DIR_DEFAULT),
 461                                                    pdb_get_homedir(newpwd));
 462         }
 463
 464         if (IS_SAM_CHANGED(newpwd, PDB_WORKSTATIONS)) {
 465                 pdb_sql_string_field(&query,
 466                                                    config_value_write(location,
 467                                                                                           "workstations column",
 468                                                                                           CONFIG_WORKSTATIONS_DEFAULT),
 469                                                    pdb_get_workstations(newpwd));
 470         }
 471
 472         if (IS_SAM_CHANGED(newpwd, PDB_UNKNOWNSTR)) {
 473                 pdb_sql_string_field(&query,
 474                                                    config_value_write(location,
 475                                                                                           "unknown string column",
 476                                                                                           CONFIG_UNKNOWN_STR_DEFAULT),
 477                                                    pdb_get_workstations(newpwd));
 478         }
 479
 480         if (IS_SAM_CHANGED(newpwd, PDB_LMPASSWD)) {
 481                 pdb_sethexpwd(temp, pdb_get_lanman_passwd(newpwd),
 482                                           pdb_get_acct_ctrl(newpwd));
 483                 pdb_sql_string_field(&query,
 484                                                    config_value_write(location,
 485                                                                                           "lanman pass column",
 486                                                                                           CONFIG_LM_PW_DEFAULT), temp);
 487         }
 488
 489         if (IS_SAM_CHANGED(newpwd, PDB_NTPASSWD)) {
 490                 pdb_sethexpwd(temp, pdb_get_nt_passwd(newpwd),
 491                                           pdb_get_acct_ctrl(newpwd));
 492                 pdb_sql_string_field(&query,
 493                                                    config_value_write(location, "nt pass column",
 494                                                                                           CONFIG_NT_PW_DEFAULT), temp);
 495         }
 496
 497         if (query.update) {
 498                 query.part1[strlen(query.part1) - 1] = '\0';
 499                 query.part1 =
 500                         talloc_asprintf_append(query.mem_ctx, query.part1,
 501                                                                    " WHERE %s = '%s'",
 502                                                                    config_value_read(location,
 503                                                                                                          "user sid column",
 504                                                                                                          CONFIG_USER_SID_DEFAULT),
 505                                                                    sid_to_string(sid_str, pdb_get_user_sid (newpwd)));
 506         } else {
 507                 query.part2[strlen(query.part2) - 1] = ')';
 508                 query.part1[strlen(query.part1) - 1] = ')';
 509                 query.part1 =
 510                         talloc_asprintf_append(query.mem_ctx, query.part1,
 511                                                                    " VALUES (%s", query.part2);
 512         }
 513
 514         ret = SMB_STRDUP(query.part1);
 515         talloc_destroy(query.mem_ctx);
 516         return ret;
 517 }
 518
 519 BOOL sql_account_config_valid(const char *data)
 520 {
 521         const char *sid_column, *username_column;
 522
 523     sid_column = config_value_read(data, "user sid column", CONFIG_USER_SID_DEFAULT);
 524     username_column = config_value_read(data, "username column", CONFIG_USERNAME_DEFAULT);
 525
 526     if(!strcmp(sid_column,"NULL") || !strcmp(username_column, "NULL")) {
 527         DEBUG(0,("Please specify both a valid 'user sid column' and a valid 'username column' in smb.conf\n"));
 528         return False;
 529     }
 530
 531         return True;
 532 }